Optimizer Pipelines

The code produced by MonetDB/SQL is massaged by an optimizer pipeline. The pipeline to be used is identified by the SQL global session variable optimizer, which can be modified using a SQL assignment, e.g. the minimal optimizer pipeline is set using:

sql> select optimizer;
+--------------+
| single value |
+==============+
| default_pipe |
+--------------+
sql> set optimizer='minimal_pipe';
sql> select optimizer;
+--------------+
| single value |
+==============+
| minimal_pipe |
+--------------+

The MonetDB optimizer is a linear sequence of MAL transformations. A complete overview is given in MonetDB Internals.

The two prominent optimizer pipelines are minimal_pipe and default_pipe. The former describes the optimizer steps needed for any SQL query to arrive at a valid MAL execution plan.

The default_pipe enumerates the steps which have proven to be helpful in most circumstances.

The _fast variantes merely wrap the steps into a single function call.

The no_mitosis_pipe is used to test SQL queries without dynamic partitioning. ??

The sequential_pipe runs the program without parallelism.

The predefined optimizer pipelines can be called for:

sql>select * from optimizers;
+-----------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+
| name            | def                                                                                                                                                                                                               | status |
+=================+===================================================================================================================================================================================================================+========+
| minimal_pipe    | optimizer.inline();optimizer.remap();optimizer.bincopyfrom();optimizer.deadcode();optimizer.multiplex();optimizer.generator();optimizer.garbageCollector();                                                       | stable |
| minimal_fast    | optimizer.minimalfast()                                                                                                                                                                                           | stable |
| default_pipe    | optimizer.inline();optimizer.remap();optimizer.costModel();optimizer.coercions();optimizer.aliases();optimizer.evaluate();optimizer.emptybind();optimizer.deadcode();optimizer.pushselect();optimizer.aliases();o | stable |
:                 : ptimizer.mitosis();optimizer.mergetable();optimizer.bincopyfrom();optimizer.aliases();optimizer.constants();optimizer.commonTerms();optimizer.projectionpath();optimizer.deadcode();optimizer.reorder();optimizer :        :
:                 : .matpack();optimizer.dataflow();optimizer.querylog();optimizer.multiplex();optimizer.generator();optimizer.profiler();optimizer.candidates();optimizer.deadcode();optimizer.postfix();optimizer.wlc();optimizer.g :        :
:                 : arbageCollector();                                                                                                                                                                                                :        :
| default_fast    | optimizer.defaultfast()                                                                                                                                                                                           | stable |
| no_mitosis_pipe | optimizer.inline();optimizer.remap();optimizer.costModel();optimizer.coercions();optimizer.aliases();optimizer.evaluate();optimizer.emptybind();optimizer.deadcode();optimizer.pushselect();optimizer.aliases();o | stable |
:                 : ptimizer.mergetable();optimizer.bincopyfrom();optimizer.aliases();optimizer.constants();optimizer.commonTerms();optimizer.projectionpath();optimizer.deadcode();optimizer.reorder();optimizer.matpack();optimizer :        :
:                 : .dataflow();optimizer.querylog();optimizer.multiplex();optimizer.generator();optimizer.profiler();optimizer.candidates();optimizer.deadcode();optimizer.postfix();optimizer.wlc();optimizer.garbageCollector();   :        :
| sequential_pipe | optimizer.inline();optimizer.remap();optimizer.costModel();optimizer.coercions();optimizer.aliases();optimizer.evaluate();optimizer.emptybind();optimizer.deadcode();optimizer.pushselect();optimizer.aliases();o | stable |
:                 : ptimizer.mergetable();optimizer.bincopyfrom();optimizer.aliases();optimizer.constants();optimizer.commonTerms();optimizer.projectionpath();optimizer.deadcode();optimizer.reorder();optimizer.matpack();optimizer :        :
:                 : .querylog();optimizer.multiplex();optimizer.generator();optimizer.profiler();optimizer.candidates();optimizer.deadcode();optimizer.postfix();optimizer.wlc();optimizer.garbageCollector();                        :        :
+-----------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------