Outdated Version
You are viewing an older version of this section. View current production version.
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. EXPLAIN plans can also be visualized via a graphical interface using MemSQL Studio Visual Explain.
See the examples section below for descriptions of the most common types of information in EXPLAIN output.
Syntax
EXPLAIN [EXTENDED | JSON] [INTO OUTFILE file_name]
{ 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,
EXPLAINoutput displays estimated statistics and costs. - The
INTO OUTFILE file_nameclause writes the explain information in JSON or text format to a file. It creates a new file at the specified location and writes the query profile details to the file. Enter the absolute path to the file as thefile_nameparameter. The output file can be a JSON or a text file.
Explain Modes
EXPLAINoutputs the explain information in a human readable formatEXPLAIN EXTENDEDoutputs more detailed information for the query plan in addition to the information displayed byEXPLAINEXPLAIN JSONoutputs the 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 MBCoutputs MemSQL Byte Code generated for the queryEXPLAIN MPLoutputs MemSQL Programming Language code generated for the queryEXPLAIN LLVMoutput LLVM byte code generated for the queryEXPLAIN ASMoutputs assembly code generated for the query
EXPLAIN
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
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
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
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
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
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
........
Example - EXPLAIN JSON INTO OUTFILE
EXPLAIN JSON INTO OUTFILE '/tmp/t1.json' SELECT * FROM t1;
To view the contents of the t1.json file, run the following command at the root prompt:
cat '/tmp/t1.json';
Output file:
{
"plan_warnings": {
},
"explain":[
{
"executor":"Gather",
"partitions":"all",
"query":"SELECT `t1`.`a` AS `a`, `t1`.`b` AS `b` FROM `db_0`.`t1` as `t1` \/*!90623 OPTION(NO_QUERY_REWRITE=1, INTERPRETER_MODE=INTERPRET_FIRST)*\/",
"alias":"remote_0",
"inputs":[
{
"executor":"Project",
"out":[
{
"alias":"",
"projection":"t1.a"
},
{
"alias":"",
"projection":"t1.b"
}
],
"subselects":[],
"inputs":[
{
"executor":"TableScan",
"db":"db",
"table":"t1",
"alias":"t1",
"index":"PRIMARY KEY ()",
"storage":"lf_skiplist",
"dir":"forward",
"table_type":"sharded_rowstore",
"inputs":[]
}
]
}
]
}
],
"version":"4",
"info":{
"memsql_version":"7.1.2",
"memsql_version_hash":"5ae405fc1fe7cf3b9fc8cab1115e8080e67f2476",
"num_online_leaves":"1",
"num_online_aggs":"1",
"context_database":"db"
},
"query_info":{
"query_text":"EXPLAIN JSON INTO OUTFILE '\/tmp\/t1.json' SELECT * FROM t1",
"text_explain":"Gather partitions:all alias:remote_0\nProject [t1.a, t1.b]\nTableScan db.t1 table_type:sharded_rowstore\n"
}
Related Topics
- MemSQL Studio Visual Explain
- Query Plan Operations describes the components of a query plan.
- PROFILE displays detailed execution statistics in addition to execution operations.
