Skip to main content

EXPLAIN SQL STMT

The intermediate mal code produced by the SQL compiler can be made visible using the explain statement modifier. It gives a detailed description of the actions taken to produce the answer. The example below illustrates what you can expect when a simple "select count(*) from tables" query starts with the explain modifier. The output strongly depends on the optimizer pipeline. The details of this program are better understood when you have read the MAL reference.

sql>select count(*) from tables;
+------+
| L41  |
+======+
|   93 |
+------+
1 tuple
clk: 1.993ms
sql>explain select count(*) from tables;
+--------------------------------------------------------------------------------------------------------------------+
| mal                                                                                                                |
+====================================================================================================================+
| function user.s6_2():void;                                                                                         |
|     X_1:void := querylog.define("explain select count(*) from tables;":str, "default_pipe":str, 30:int);           |
| barrier X_106:bit := language.dataflow();                                                                          |
|     X_38:bat[:lng] := bat.new(nil:lng);                                                                            |
|     X_4:int := sql.mvc();                                                                                          |
|     X_18:bat[:sht] := sql.bind(X_4:int, "sys":str, "_tables":str, "type":str, 0:int);                              |
|     C_5:bat[:oid] := sql.tid(X_4:int, "sys":str, "_tables":str);                                                   |
|     C_91:bat[:oid] := algebra.thetaselect(X_18:bat[:sht], C_5:bat[:oid], 2:sht, "!=":str);                         |
|     (X_21:bat[:oid], X_22:bat[:sht]) := sql.bind(X_4:int, "sys":str, "_tables":str, "type":str, 2:int);            |
|     C_92:bat[:oid] := algebra.thetaselect(X_22:bat[:sht], nil:bat[:oid], 2:sht, "!=":str);                         |
|     X_20:bat[:sht] := sql.bind(X_4:int, "sys":str, "_tables":str, "type":str, 1:int);                              |
|     C_94:bat[:oid] := algebra.thetaselect(X_20:bat[:sht], C_5:bat[:oid], 2:sht, "!=":str);                         |
|     C_27:bat[:oid] := sql.subdelta(C_91:bat[:oid], C_5:bat[:oid], X_21:bat[:oid], C_92:bat[:oid], C_94:bat[:oid]); |
|     X_31:lng := aggr.count(C_27:bat[:oid]);                                                                        |
|     X_37:bat[:lng] := sql.single(X_31:lng);                                                                        |
|     C_32:bat[:oid] := sql.tid(X_4:int, "tmp":str, "_tables":str);                                                  |
|     X_36:lng := aggr.count(C_32:bat[:oid]);                                                                        |
|     X_40:bat[:lng] := bat.append(X_38:bat[:lng], X_37:bat[:lng], true:bit);                                        |
|     X_42:bat[:lng] := bat.append(X_40:bat[:lng], X_36:lng, true:bit);                                              |
|     X_43:lng := aggr.sum(X_42:bat[:lng]);                                                                          |
|     language.pass(C_5:bat[:oid]);                                                                                  |
| exit X_106:bit;                                                                                                    |
|     sql.resultSet(".L41":str, "L41":str, "bigint":str, 64:int, 0:int, 7:int, X_43:lng);                            |
| end user.s6_2;                                                                                                     |
...
+--------------------------------------------------------------------------------------------------------------------+
50 tuples
clk: 3.942 ms

 

The SQL compiler maintains a cache of compiled (or prepared) queries. Each query is looked up in this cache based on an expression pattern match where the constants may take on different values. If it doesn't exist, the query is converted into a code block and stored in the module user.s0.

The call to the cached function is included in a wrapper function main, which is the only piece of code produced if the query is used more than once. The query cache disappears when the server is brought to a halt.

     +----------------------------+
     | function user.main():void; |
     |     mdb.start();           |
     |     user.s3_2();           |
     |     mdb.stop();            |
     | end main;                  |
     +----------------------------+