Display logical or physical execution plan for an SQL statement.
EXPLAIN [ [BEFORE|AFTER] {LOGICAL UNNEST | LOGICAL REWRITE [posint] [posint] | PHYSICAL} ]
[SHOW DETAILS]
sql-statement
Plain EXPLAIN sql-statement returns the logical (rel) plan.
EXPLAIN PHYSICAL sql-statement returns the physical (mal) plan.
Use LOGICAL UNNEST or LOGICAL REWRITE or PHYSICAL to specify the compilation step to show.
Optionally add BEFORE or AFTER to specify moment of compilation step to output. The default is AFTER.
Two positive numbers can be passed to LOGICAL REWRITE to stop at a specific
rewriter index or rewrite loop cycle, respectively.
If only one positive number is passed to LOGICAL REWRITE, rewrite stop cycle defaults to 0.
SHOW DETAILS displays column properties, rewriter number of changes and time spent.
-- Logical plans
sql>EXPLAIN SELECT count(*) FROM sys.tables;
+-------------------------------------------------------------------------+
| rel |
+=========================================================================+
| project ( |
| | group by ( |
| | | munion ( |
| | | | group by ( |
| | | | | project ( |
| | | | | | select ( |
| | | | | | | table("sys"."_tables") [ "_tables"."id", "_tables"."type" ] |
| | | | | | ) [ ("_tables"."type") != (smallint(15) "2") ] |
| | | | | ) [ "_tables"."id" as "tables"."id" ] |
| | | | ) [ ] [ "sys"."count"() as "%3"."%3" ], |
| | | | group by ( |
| | | | | project ( |
| | | | | | table("tmp"."_tables") [ "_tables"."id" ] |
| | | | | ) [ "_tables"."id" as "tables"."id" ] |
| | | | ) [ ] [ "sys"."count"() as "%3"."%3" ] |
| | | ) [ "%3"."%3" ] |
| | ) [ ] [ "sys"."sum" no nil ("%3"."%3") as "%3"."%3" ] |
| ) [ "%3"."%3" ] |
+-------------------------------------------------------------------------+
18 tuples
sql>EXPLAIN SHOW DETAILS SELECT count(*) FROM sys.tables;
+------------------------------------------------------------------------------------------------------------+
| rel |
+============================================================================================================+
| project ( |
| | group by ( |
| | | munion ( |
| | | | group by ( |
| | | | | project ( |
| | | | | | select ( |
| | | | | | | table("sys"."_tables") [ "_tables"."id" NOT NULL UNIQUE, "_tables"."type" NOT NULL ] COUNT 146 |
| | | | | | ) [ ("_tables"."type" NOT NULL) != (smallint(15) "2") ] COUNT 146 |
| | | | | ) [ "_tables"."id" NOT NULL UNIQUE as "tables"."id" ] COUNT 146 |
| | | | ) [ ] [ "sys"."count"() NOT NULL UNIQUE as "%3"."%3" ] COUNT 1, |
| | | | group by ( |
| | | | | project ( |
| | | | | | table("tmp"."_tables") [ "_tables"."id" NOT NULL UNIQUE ] COUNT 0 |
| | | | | ) [ "_tables"."id" NOT NULL UNIQUE as "tables"."id" ] COUNT 0 |
| | | | ) [ ] [ "sys"."count"() NOT NULL UNIQUE as "%3"."%3" ] COUNT 1 |
| | | ) [ "%3"."%3" NOT NULL ] COUNT 2 |
| | ) [ ] [ "sys"."sum" no nil ("%3"."%3" NOT NULL) NOT NULL UNIQUE as "%3"."%3" ] COUNT 1 |
| ) [ "%3"."%3" NOT NULL UNIQUE ] COUNT 1 |
| # 00 split_select 0 actions 10 usec |
| # 01 push_project_down 2 actions 23 usec |
| # 02 merge_projects 0 actions 45 usec |
| # 03 push_project_up 0 actions 48 usec |
| # 04 split_project 0 actions 3 usec |
| # 06 simplify_math 0 actions 16 usec |
| # 07 optimize_exps 1 actions 44 usec |
| # 08 optimize_select_and_joins_bottomup 0 actions 27 usec |
| # 09 project_reduce_casts 0 actions 0 usec |
| # 10 optimize_unions_bottomup 0 actions 15 usec |
| # 11 optimize_unions_topdown 3 actions 105 usec |
| # 12 optimize_projections 1 actions 71 usec |
| # 16 optimize_select_and_joins_topdown 0 actions 26 usec |
| # 17 dce 0 actions 41 usec |
| # 18 push_func_and_select_down 0 actions 14 usec |
| # 26 get_statistics 0 actions 45 usec |
| # 28 final_optimization_loop 0 actions 6 usec |
+------------------------------------------------------------------------------------------------------------+
35 tuples
sql>EXPLAIN BEFORE LOGICAL UNNEST SELECT count(*) FROM sys.tables;
+-----------------------------------------------------------------------------------------------------+
| rel |
+=====================================================================================================+
| project ( |
| | group by ( |
| | | project ( |
| | | | munion ( |
| | | | | project ( |
| | | | | | project ( |
| | | | | | | select ( |
| | | | | | | | table("sys"."_tables") [ "_tables"."id", "_tables"."name", "_tables"."schema_id", "_t |
: ables"."query", "_tables"."type", "_tables"."system", "_tables"."commit_action", "_tables"."access" :
: , "_tables"."%TID%" ] :
| | | | | | | ) [ ("_tables"."type") != (smallint(15) "2") ] |
| | | | | | ) [ "_tables"."id", "_tables"."name", "_tables"."schema_id", "_tables"."query", smallint( |
: 15)["sys"."case"("_tables"."system", "sys"."sql_add"("_tables"."type", smallint(4) "10"), "sys"."ca :
: se"(("_tables"."commit_action") = (smallint(2) "0"), int(16)["_tables"."type"], "sys"."sql_add"("_t :
: ables"."type", smallint(5) "20")))] as "type", "_tables"."system", "_tables"."commit_action", "_tab :
: les"."access", "sys"."case"(.AND("sys"."not"("_tables"."system"), ("_tables"."commit_action") > (sm :
: allint(2) "0")), tinyint(1) "1", tinyint(1) "0") as "temporary" ] :
| | | | | ) [ "_tables"."id", "_tables"."name", "_tables"."schema_id", "_tables"."query", "type", "_t |
: ables"."system", "_tables"."commit_action", "_tables"."access", "temporary" ], :
| | | | | project ( |
| | | | | | project ( |
| | | | | | | table("tmp"."_tables") [ "_tables"."id", "_tables"."name", "_tables"."schema_id", "_tab |
: les"."query", "_tables"."type", "_tables"."system", "_tables"."commit_action", "_tables"."access", :
: "_tables"."%TID%" ] :
| | | | | | ) [ "_tables"."id", "_tables"."name", "_tables"."schema_id", "_tables"."query", smallint( |
: 15)["sys"."sql_add"("_tables"."type", smallint(5) "30")] as "type", "_tables"."system", "_tables"." :
: commit_action", "_tables"."access", tinyint(1) "1" as "temporary" ] :
| | | | | ) [ "_tables"."id", "_tables"."name", "_tables"."schema_id", "_tables"."query", "type", "_t |
: ables"."system", "_tables"."commit_action", "_tables"."access", "temporary" ] :
| | | | ) [ "_tables"."id", "_tables"."name", "_tables"."schema_id", "_tables"."query", "type", "_tab |
: les"."system", "_tables"."commit_action", "_tables"."access", "temporary" ] :
| | | ) [ "_tables"."id" as "tables"."id", "_tables"."name" as "tables"."name", "_tables"."schema_id" |
: as "tables"."schema_id", "_tables"."query" as "tables"."query", "type" as "tables"."type", "_table :
: s"."system" as "tables"."system", "_tables"."commit_action" as "tables"."commit_action", "_tables". :
: "access" as "tables"."access", "temporary" as "tables"."temporary" ] :
| | ) [ ] [ "sys"."count"() as "%3"."%3" ] |
| ) [ "%3"."%3" ] |
+-----------------------------------------------------------------------------------------------------+
20 tuples
sql>EXPLAIN AFTER LOGICAL REWRITE SELECT count(*) FROM sys.tables;
-------------------------------------------------------------------------+
| rel |
+=========================================================================+
| project ( |
| | group by ( |
| | | munion ( |
| | | | group by ( |
| | | | | project ( |
| | | | | | select ( |
| | | | | | | table("sys"."_tables") [ "_tables"."id", "_tables"."type" ] |
| | | | | | ) [ ("_tables"."type") != (smallint(15) "2") ] |
| | | | | ) [ "_tables"."id" as "tables"."id" ] |
| | | | ) [ ] [ "sys"."count"() as "%3"."%3" ], |
| | | | group by ( |
| | | | | project ( |
| | | | | | table("tmp"."_tables") [ "_tables"."id" ] |
| | | | | ) [ "_tables"."id" as "tables"."id" ] |
| | | | ) [ ] [ "sys"."count"() as "%3"."%3" ] |
| | | ) [ "%3"."%3" ] |
| | ) [ ] [ "sys"."sum" no nil ("%3"."%3") as "%3"."%3" ] |
| ) [ "%3"."%3" ] |
+-------------------------------------------------------------------------+
18 tuples
sql>EXPLAIN AFTER LOGICAL REWRITE SHOW DETAILS SELECT count(*) FROM sys.tables;
+------------------------------------------------------------------------------------------------------------+
| rel |
+============================================================================================================+
| project ( |
| | group by ( |
| | | munion ( |
| | | | group by ( |
| | | | | project ( |
| | | | | | select ( |
| | | | | | | table("sys"."_tables") [ "_tables"."id" NOT NULL UNIQUE, "_tables"."type" NOT NULL ] COUNT 146 |
| | | | | | ) [ ("_tables"."type" NOT NULL) != (smallint(15) "2") ] COUNT 146 |
| | | | | ) [ "_tables"."id" NOT NULL UNIQUE as "tables"."id" ] COUNT 146 |
| | | | ) [ ] [ "sys"."count"() NOT NULL UNIQUE as "%3"."%3" ] COUNT 1, |
| | | | group by ( |
| | | | | project ( |
| | | | | | table("tmp"."_tables") [ "_tables"."id" NOT NULL UNIQUE ] COUNT 0 |
| | | | | ) [ "_tables"."id" NOT NULL UNIQUE as "tables"."id" ] COUNT 0 |
| | | | ) [ ] [ "sys"."count"() NOT NULL UNIQUE as "%3"."%3" ] COUNT 1 |
| | | ) [ "%3"."%3" NOT NULL ] COUNT 2 |
| | ) [ ] [ "sys"."sum" no nil ("%3"."%3" NOT NULL) NOT NULL UNIQUE as "%3"."%3" ] COUNT 1 |
| ) [ "%3"."%3" NOT NULL UNIQUE ] COUNT 1 |
| # 00 split_select 0 actions 16 usec |
| # 01 push_project_down 2 actions 34 usec |
| # 02 merge_projects 0 actions 62 usec |
| # 03 push_project_up 0 actions 75 usec |
| # 04 split_project 0 actions 5 usec |
| # 06 simplify_math 0 actions 21 usec |
| # 07 optimize_exps 1 actions 63 usec |
| # 08 optimize_select_and_joins_bottomup 0 actions 38 usec |
| # 09 project_reduce_casts 0 actions 3 usec |
| # 10 optimize_unions_bottomup 0 actions 22 usec |
| # 11 optimize_unions_topdown 3 actions 192 usec |
| # 12 optimize_projections 1 actions 91 usec |
| # 16 optimize_select_and_joins_topdown 0 actions 47 usec |
| # 17 dce 0 actions 52 usec |
| # 18 push_func_and_select_down 0 actions 16 usec |
| # 26 get_statistics 0 actions 24 usec |
| # 28 final_optimization_loop 0 actions 4 usec |
+------------------------------------------------------------------------------------------------------------+
35 tuples
-- Physical plans
sql>EXPLAIN PHYSICAL SELECT count(*) FROM sys.tables;
+-----------------------------------------------------------------------------------------------------------+
| mal |
+===========================================================================================================+
| function user.main():void; |
| X_1:void := querylog.define("explain physical select count(*) from sys.tables;":str, "default_pipe":s |
: tr, 26:int); :
| barrier X_58:bit := language.dataflow(); |
| X_4:int := sql.mvc(); |
| X_5:bat[:oid] := sql.tid(X_4:int, "sys":str, "_tables":str); |
| X_15:bat[:sht] := sql.bind(X_4:int, "sys":str, "_tables":str, "type":str, 0:int); |
| (X_17:bat[:oid], X_18:bat[:sht]) := sql.bind(X_4:int, "sys":str, "_tables":str, "type":str, 2:int); |
| C_55:bat[:oid] := algebra.thetaselect(X_15:bat[:sht], X_5:bat[:oid], 2:sht, "!=":str); |
| C_56:bat[:oid] := algebra.thetaselect(X_18:bat[:sht], nil:bat[:oid], 2:sht, "!=":str); |
| X_21:bat[:oid] := sql.subdelta(C_55:bat[:oid], X_5:bat[:oid], X_17:bat[:oid], C_56:bat[:oid]); |
| X_60:void := language.pass(X_5:bat[:oid]); |
| X_25:lng := aggr.count(X_21:bat[:oid]); |
| X_26:bat[:oid] := sql.tid(X_4:int, "tmp":str, "_tables":str); |
| X_37:lng := aggr.count(X_26:bat[:oid]); |
| X_38:bat[:lng] := bat.single(X_25:lng); |
| X_39:bat[:lng] := mat.packIncrement(X_38:bat[:lng], 2:int); |
| X_40:bat[:lng] := bat.single(X_37:lng); |
| X_41:bat[:lng] := mat.packIncrement(X_39:bat[:lng], X_40:bat[:lng]); |
| X_42:lng := aggr.sum(X_41:bat[:lng]); |
| exit X_58:bit; |
| X_44:int := sql.resultSet(".%3":str, "%3":str, "bigint":str, 63:int, 0:int, 7:int, X_42:lng); |
| end user.main; |
| # |
| # 1 optimizers 180 usecs |
+-----------------------------------------------------------------------------------------------------------+
24 tuples
sql>EXPLAIN BEFORE PHYSICAL SELECT count(*) FROM sys.tables;
+-----------------------------------------------------------------------------------------------------------+
| mal |
+===========================================================================================================+
| function user.main():void; |
| X_1:void := querylog.define("explain before physical select count(*) from sys.tables;":str, "default_ |
: pipe":str, 26:int); :
| X_4:int := sql.mvc(); |
| X_5:bat[:oid] := sql.tid(X_4:int, "sys":str, "_tables":str); |
| X_8:bat[:int] := sql.bind(X_4:int, "sys":str, "_tables":str, "id":str, 0:int); |
| (X_11:bat[:oid], X_12:bat[:int]) := sql.bind(X_4:int, "sys":str, "_tables":str, "id":str, 2:int); |
| X_14:bat[:int] := sql.delta(X_8:bat[:int], X_11:bat[:oid], X_12:bat[:int]); |
| X_15:bat[:sht] := sql.bind(X_4:int, "sys":str, "_tables":str, "type":str, 0:int); |
| (X_17:bat[:oid], X_18:bat[:sht]) := sql.bind(X_4:int, "sys":str, "_tables":str, "type":str, 2:int); |
| X_19:bat[:sht] := sql.delta(X_15:bat[:sht], X_17:bat[:oid], X_18:bat[:sht]); |
| X_21:bat[:oid] := algebra.thetaselect(X_19:bat[:sht], X_5:bat[:oid], 2:sht, "!=":str); |
| X_23:bat[:int] := algebra.projection(X_21:bat[:oid], X_14:bat[:int]); |
| X_24:bat[:sht] := algebra.projection(X_21:bat[:oid], X_19:bat[:sht]); |
| X_25:lng := aggr.count(X_21:bat[:oid]); |
| X_26:bat[:oid] := sql.tid(X_4:int, "tmp":str, "_tables":str); |
| X_29:bat[:int] := sql.bind(X_4:int, "tmp":str, "_tables":str, "id":str, 0:int); |
| (X_32:bat[:oid], X_33:bat[:int]) := sql.bind(X_4:int, "tmp":str, "_tables":str, "id":str, 2:int); |
| X_35:bat[:int] := sql.delta(X_29:bat[:int], X_32:bat[:oid], X_33:bat[:int]); |
| X_36:bat[:int] := algebra.projection(X_26:bat[:oid], X_35:bat[:int]); |
| X_37:lng := aggr.count(X_26:bat[:oid]); |
| X_38:bat[:lng] := bat.single(X_25:lng); |
| X_39:bat[:lng] := mat.packIncrement(X_38:bat[:lng], 2:int); |
| X_40:bat[:lng] := bat.single(X_37:lng); |
| X_41:bat[:lng] := mat.packIncrement(X_39:bat[:lng], X_40:bat[:lng]); |
| X_42:lng := aggr.sum(X_41:bat[:lng]); |
| X_44:int := sql.resultSet(".%3":str, "%3":str, "bigint":str, 63:int, 0:int, 7:int, X_42:lng); |
| end user.main; |
+-----------------------------------------------------------------------------------------------------------+
27 tuples