EXPLAIN

Returns detailed information on how the query is executed.

EXPLAIN displays query execution operations as a tree, where the operator at the top is the last executed before returning the result to the client, and operators below are executed before their parents.

See the examples section below for descriptions of the most common types of information in EXPLAIN output.

Syntax

EXPLAIN [EXTENDED | JSON]
  { select_statement
   | insert_select_statement
   | update_statement
   | delete_statement }

Remarks

  • EXPLAIN can be run with an arbitrary SELECT, INSERT…SELECT, UPDATE, or DELETE statement.
  • EXPLAIN displays the plan MemSQL would generate if the query were reoptimized and recompiled now. If the query has already been run and a plan has already been saved in the plancache, that plan will be reused, even though it may differ from the plan displayed by EXPLAIN. You may run DROP … FROM PLANCACHE to force the query to be reoptimized and recompiled. Note that running ANALYZE will automatically drop and recompile query plans if data statistics have changed significantly.
  • In most cases when the optimizer makes a cost-based decision, EXPLAIN output displays estimated statistics and costs.
  • If the enable_disk_plan_explain global variable is enabled, the explain tree information is also stored in the persisted plancache, in human readable format. When enable_disk_plan_explain is enabled, the SHOW PLAN [JSON] plan_id command can also be used to display the explain information.

    ---To display the explain information in text format
    memsql> SHOW PLAN 3;
    
    ---To display the explain information in JSON format
    memsql> SHOW PLAN JSON 3;
    

Explain Modes

  • EXPLAIN outputs the explain information in a human readable format
  • EXPLAIN EXTENDED outputs more detailed information for the query plan in addition to the information displayed by EXPLAIN
  • EXPLAIN JSON outputs explain information in JSON format, which may be more machine readable

Additionally, the following EXPLAIN modes are provided for informational and debugging purposes only and may be changed at any time:

  • EXPLAIN MBC outputs MemSQL Byte Code generated for the query
  • EXPLAIN MPL outputs MemSQL Programming Language code generated for the query
  • EXPLAIN LLVM output LLVM byte code generated for the query
  • EXPLAIN ASM outputs assembly code generated for the query

EXPLAIN

memsql> explain select * from t;
+-----------------------------------+
| EXPLAIN                           |
+-----------------------------------+
| Project [t.id, t.a, t.b]          |
| Gather partitions:all             |
| Project [t.id, t.a, t.b]          |
| TableScan db1.t, PRIMARY KEY (id) |
+-----------------------------------+

EXPLAIN EXTENDED

memsql> explain extended select * from t;
+----------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                        |
+----------------------------------------------------------------------------------------------------------------+
| Project [t.id, t.a, t.b]                                                                                       |
| Gather partitions:all query:[SELECT `t`.`id` AS `id`, `t`.`a` AS `a`, `t`.`b` AS `b` FROM `db1_0`.`t` as `t` ] |
| Project [t.id, t.a, t.b]                                                                                       |
| TableScan db1.t, PRIMARY KEY (id)                                                                              |
+----------------------------------------------------------------------------------------------------------------+

EXPLAIN JSON

memsql> explain json select * from t;
----------------------------------------------
{
    "executor":"Project",
    "out":{
        "":"t.id",
        "":"t.a",
        "":"t.b"
    },
    "inputs":[
        {
            "executor":"Gather",
            "partitions":"all",
            "query":"SELECT `t`.`id` AS `id`, `t`.`a` AS `a`, `t`.`b` AS `b` FROM `db1_0`.`t` as `t` ",
            "inputs":[
                {
                    "executor":"Project",
                    "out":{
                        "":"t.id",
                        "":"t.a",
                        "":"t.b"
                    },
                    "subselects":[],
                    "inputs":[
                        {
                            "executor":"TableScan",
                            "db":"db1",
                            "table":"t",
                            "alias":"t",
                            "index":"PRIMARY KEY (id)",
                            "storage":"lf_skiplist",
                            "dir":"forward",
                            "inputs":[]
                        }
                    ]
                }
            ]
        }
    ]
}

EXPLAIN MBC

memsql> explain mbc select * from t;
----------------------------------------------
EXPLAIN
# explain mbc select * from t

Data:

                   static:  offset=0         size=125       align=1         ata="USING `\?`::`\?` AGGREGATOR_ID = \?, CONNECTION_ID = \? SELECT `t`.`id` AS `id`, `t`.`a` AS `a`, `t`.`b` AS `b` FROM `t` as `t` \x00"""

Function 0 <100000:CreateProjectState>:

     Locals:
         local      rvToReturn:  offset=0         size=8         align=8         type=int64_t

         local     rvToReceive:  offset=16        size=16        align=16        type=uint128_t
         param   hiddenRvParam:  offset=32        size=8         align=8         type={ state1: { RemoteElement: RemoteElement, a: { value: int32_t, opt: Opt, pack(4) }, b: { value: int32_t, opt: Opt, pack(4) }, : int64_t, id: int32_t } }*
|         local    state1Addr_1:  offset=40        size=8         align=8         type={ RemoteElement: RemoteElement, a: { value: int32_t, opt: Opt, pack(4) }, b: { value: int32_t, opt: Opt, pack(4) }, : int64_t, id: int32_t }*

     0x0000  Junk                                                      local=hiddenRvParam  i32=32
     0x000c  Lea                                                       local=&state1Addr_1  local=hiddenRvParam  i32=0
     0x001c  Call                                                      func=10 <row_0tmp1_internal_ctor>  local=state1Addr_1
     0x002c  Junk                                                      local=&state1Addr_1  i32=8
     0x0038  Literal8                                                  local=&rvToReturn  i64=1
     0x0048  Return

Function 1 <100000:CreateMatchedRowContext>:
                    ........

EXPLAIN LLVM

memsql> explain llvm select * from t;
----------------------------------------------
EXPLAIN

; Function Attrs: nounwind readnone uwtable
define void @MbcTypeDummyFunc(%struct.MbcTypeDummyStruct* nocapture) #2 {
  ret void
}


; Function Attrs: noinline noreturn nounwind
define linkonce_odr hidden void @__clang_call_terminate(i8*) #6 {
  %2 = tail call i8* @__cxa_begin_catch(i8* %0) #4
  tail call void @_ZSt9terminatev() #9
  unreachable
}


; Function Attrs: noreturn nounwind
define linkonce_odr void @"100000:RowToRowElement"(%struct.InterpVars* nocapture readnone, %struct.MemSqlClientValue* nocapture readnone, i8* nocapture readnone) #9 {
EntryBlock:
	call void @__assert_fail(i8* getelementptr inbounds ([6 x i8]* @.str770, i64 0, i64 0), i8* getelementptr inbounds ([45 x i8]* @.str710, i64 0, i64 0), i32 338, i8* getelementptr inbounds ([35 x i8]* @__PRETTY_FUNCTION__.iopAssert, i64 0, i64 0)) #9, !dbg !19
	unreachable, !dbg !19
                    ........

EXPLAIN ASM

memsql> explain asm select * from t;
----------------------------------------------
EXPLAIN
       .text
       .file   "/home/vagrant/memsql/interp_ops.bc"
       .section        .debug_info,"",@progbits
 .Lsection_info:
       .section        .debug_abbrev,"",@progbits
 .Lsection_abbrev:
       .section        .debug_line,"",@progbits
 .Lsection_line:
       .section        .debug_pubnames,"",@progbits
       .section        .debug_pubtypes,"",@progbits
       .section        .debug_str,"MS",@progbits,1
 .Linfo_string:
       .section        .debug_loc,"",@progbits
 .Lsection_debug_loc:
       .section        .debug_ranges,"",@progbits
 .Ldebug_range:
       .text
       .globl  MbcTypeDummyFunc
       .align  16, 0x90
       .type   MbcTypeDummyFunc,@function
 MbcTypeDummyFunc:
       .cfi_startproc
       pushq   %rbp
 .Ltmp0:
       .cfi_def_cfa_offset 16
 .Ltmp1:
       .cfi_offset %rbp, -16
       movq    %rsp, %rbp
.Ltmp2:
       .cfi_def_cfa_register %rbp
       popq    %rbp
       retq
                    ........

Related Topics

  • Query Plan Operations describes the components of a query plan.
  • PROFILE displays detailed execution statistics in addition to execution operations.
Was this article useful?