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.
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