EXPLAIN Statement

The query execution plan produced by the SQL compiler can be made visible using the explain statement modifier. It is a MonetDB Assembly Language program snippet, the internal language understood by the MonetDB interpreter. The example below illustrates what you can expect when a simple SELECT count(*) FROM sys.tables query starts with the EXPLAIN modifier. The details of this code snippet are better understood when you have read the MAL reference.

Example

sql>SELECT count(*) FROM sys.tables;
+------+
| %10  |
+======+
|  125 |
+------+
1 tuple
sql>EXPLAIN SELECT count(*) FROM sys.tables;
+----------------------------------------------------------------------------------------------------------------+
| mal                                                                                                            |
+================================================================================================================+
| function user.main():void;                                                                                     |
|     X_1:void := querylog.define("explain select count(*) from sys.tables\n;":str, "default_pipe":str, 27:int); |
| barrier X_103:bit := language.dataflow();                                                                      |
|     X_4:int := sql.mvc();                                                                                      |
|     C_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_89:bat[:oid] := algebra.thetaselect(X_15:bat[:sht], C_5:bat[:oid], 2:sht, "!=":str);                     |
|     C_90:bat[:oid] := algebra.thetaselect(X_18:bat[:sht], nil:bat[:oid], 2:sht, "!=":str);                     |
|     C_22:bat[:oid] := sql.subdelta(C_89:bat[:oid], C_5:bat[:oid], X_17:bat[:oid], C_90:bat[:oid]);             |
|     X_26:lng := aggr.count(C_22:bat[:oid]);                                                                    |
|     C_27:bat[:oid] := sql.tid(X_4:int, "tmp":str, "_tables":str);                                              |
|     X_34:lng := aggr.count(C_27:bat[:oid]);                                                                    |
|     X_35:bat[:lng] := bat.single(X_26:lng);                                                                    |
|     X_36:bat[:lng] := bat.new(nil:lng);                                                                        |
|     X_38:bat[:lng] := bat.append(X_36:bat[:lng], X_35:bat[:lng], true:bit);                                    |
|     X_40:bat[:lng] := bat.append(X_38:bat[:lng], X_34:lng, true:bit);                                          |
|     X_41:lng := aggr.sum(X_40:bat[:lng]);                                                                      |
|     X_105:void := language.pass(C_5:bat[:oid]);                                                                |
| exit X_103:bit;                                                                                                |
|     X_43:int := sql.resultSet(".%10":str, "%10":str, "bigint":str, 64:int, 0:int, 7:int, X_41:lng);            |
| end user.main;                                                                                                 |                                                                                                  |
...
+----------------------------------------------------------------------------------------------------------------+
50 tuples