The MAL code produced by MonetDB/SQL is massaged by an optimizer pipeline.
There are multiple optimizer pipelines predefined, which can be queried from system table sys.optimizers.
The two prominent optimizer pipelines are default_pipe and minimal_pipe.
sql>select * from optimizers;
+-----------------+-------------------------------------------------------------+--------+
| name | def | status |
+=================+=============================================================+========+
| minimal_pipe | optimizer.inline();optimizer.remap();optimizer.emptybind(); | stable |
: : optimizer.deadcode();optimizer.for();optimizer.dict();optim : :
: : izer.multiplex();optimizer.generator();optimizer.profiler() : :
: : ;optimizer.garbageCollector(); : :
| minimal_fast | optimizer.minimalfast(); | stable |
| default_pipe | optimizer.inline();optimizer.remap();optimizer.costModel(); | stable |
: : optimizer.coercions();optimizer.aliases();optimizer.evaluat : :
: : e();optimizer.emptybind();optimizer.deadcode();optimizer.pu : :
: : shselect();optimizer.aliases();optimizer.for();optimizer.di : :
: : ct();optimizer.mitosis();optimizer.mergetable();optimizer.a : :
: : liases();optimizer.constants();optimizer.commonTerms();opti : :
: : mizer.projectionpath();optimizer.deadcode();optimizer.matpa : :
: : ck();optimizer.reorder();optimizer.dataflow();optimizer.que : :
: : rylog();optimizer.multiplex();optimizer.generator();optimiz : :
: : er.candidates();optimizer.deadcode();optimizer.postfix();op : :
: : timizer.profiler();optimizer.garbageCollector(); : :
| default_fast | optimizer.defaultfast(); | stable |
| no_mitosis_pipe | optimizer.inline();optimizer.remap();optimizer.costModel(); | stable |
: : optimizer.coercions();optimizer.aliases();optimizer.evaluat : :
: : e();optimizer.emptybind();optimizer.deadcode();optimizer.pu : :
: : shselect();optimizer.aliases();optimizer.mergetable();optim : :
: : izer.aliases();optimizer.constants();optimizer.commonTerms( : :
: : );optimizer.projectionpath();optimizer.deadcode();optimizer : :
: : .matpack();optimizer.reorder();optimizer.dataflow();optimiz : :
: : er.querylog();optimizer.multiplex();optimizer.generator();o : :
: : ptimizer.candidates();optimizer.deadcode();optimizer.postfi : :
: : x();optimizer.profiler();optimizer.garbageCollector(); : :
| sequential_pipe | optimizer.inline();optimizer.remap();optimizer.costModel(); | stable |
: : optimizer.coercions();optimizer.aliases();optimizer.evaluat : :
: : e();optimizer.emptybind();optimizer.deadcode();optimizer.pu : :
: : shselect();optimizer.aliases();optimizer.for();optimizer.di : :
: : ct();optimizer.mergetable();optimizer.aliases();optimizer.c : :
: : onstants();optimizer.commonTerms();optimizer.projectionpath : :
: : ();optimizer.deadcode();optimizer.matpack();optimizer.reord : :
: : er();optimizer.querylog();optimizer.multiplex();optimizer.g : :
: : enerator();optimizer.candidates();optimizer.deadcode();opti : :
: : mizer.postfix();optimizer.profiler();optimizer.garbageColle : :
: : ctor(); : :
+-----------------+-------------------------------------------------------------+--------+
6 tuples
The default_pipe uses the steps which have proven to be helpful in most circumstances.
It contains the mitosis-mergetable-reorder optimizers, aimed at large tables and improved access locality.
The minimal_pipe is the minimal optimizer pipeline necessary by the server to operate correctly.
It may speed up SQL processing for simple statements such as: INSERT INTO ... VALUES (...);
The minimal_fast and default_fast variants merely wrap the steps into a single function call.
The no_mitosis_pipe is identical to the default pipeline, except that optimizer mitosis is omitted.
It is used mainly to make some tests work deterministically, and
to check/debug whether "unexpected" problems are related to mitosis (and/or mergetable).
The sequential_pipe is identical to the default pipeline, except that optimizers mitosis and dataflow are omitted.
It is used mainly to make some tests work deterministically, i.e. avoid ambigious output, by avoiding parallelism.
More information on optimizer internals is available in MAL optimizers.
The default pipeline for users and sessions is default_pipe.
You can however specify a specific optimizer per user. See the
CREATE USER command.
The active optimizer pipeline is identified by the SQL global session variable optimizer.
It can be changed any time using a SQL SET assignment, e.g. the minimal optimizer pipeline is set using:
sql> select optimizer;
+--------------+
| %2 |
+==============+
| default_pipe |
+--------------+
sql> set optimizer='minimal_pipe';
sql> select optimizer;
+--------------+
| %2 |
+==============+
| minimal_pipe |
+--------------+
This MonetDB specific dynamic control over the used optimizer pipeline is very powerful.