The MAL code produced by MonetDB/SQL is massaged by a MAL-optimizer pipeline.
There are multiple optimizer pipelines predefined, which can be queried from
system view sys.optimizers (provided you have select privilege for this view).
-- list available optimizer pipeline names
sql>SELECT name, status FROM sys.optimizers;
+-----------------+--------+
| name | status |
+=================+========+
| minimal_pipe | stable |
| minimal_fast | stable |
| default_pipe | stable |
| default_fast | stable |
| no_mitosis_pipe | stable |
| sequential_pipe | stable |
| recursive_pipe | stable |
+-----------------+--------+
7 tuples
The two prominent optimizer pipelines are default_pipe and minimal_pipe.
The default_pipe uses the steps which have proven to be optimal in most circumstances.
It contains the mitosis-mergetable-reorder optimizers, aimed at large tables and improved access locality.
After login the default_pipe is the default optimizer,
unless a user has a different default optimizer set, see
CREATE USER command.
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 ambiguous output, by avoiding parallelism.
The recursive_pipe is used internally for recursive cte queries. It shouldn't be used by end-users.
The active optimizer pipeline is stored in the global session variable sys.optimizer.
It can be changed any time using a SQL SET sys.optimizer = '<name>' ,
e.g. the minimal_pipe optimizer pipeline is set using SET sys.optimizer = 'minimal_pipe';.
-- change optimizer pipeline
sql>SET sys.optimizer = 'minimal_pipe';
sql>SELECT sys.optimizer;
+--------------+
| %2 |
+==============+
| minimal_pipe |
+--------------+
Note When current schema is sys, you may omit the sys. prefix:
sql>SET schema sys;
sql>SELECT optimizer;
+--------------+
| %2 |
+==============+
| minimal_pipe |
+--------------+
sql>SET optimizer = 'default_fast';
sql>SELECT optimizer;
+--------------+
| %2 |
+==============+
| default_fast |
+--------------+
sql>SET optimizer = 'ceci_nest_pas_une_pipe';
-- it fails with Error: optimizer 'ceci_nest_pas_une_pipe' unknown
sql>SELECT optimizer;
+--------------+
| %2 |
+==============+
| default_fast |
+--------------+
This dynamic control over the used optimizer pipeline in a session allows optimisation of execution performance per SQL statement, which is quite powerful.
To see what the optimizer pipeline steps are for each optimizer use query:
sql>SELECT name, def FROM sys.optimizers;
+-----------------+--------------------------------------------------------------------------+
| name | def |
+=================+==========================================================================+
| minimal_pipe | optimizer.inline();optimizer.remap();optimizer.emptybind();optimizer.dea |
: : dcode();optimizer.for();optimizer.dict();optimizer.multiplex();optimizer :
: : .generator();optimizer.profiler();optimizer.garbageCollector(); :
| minimal_fast | optimizer.minimalfast(); |
| default_pipe | optimizer.inline();optimizer.remap();optimizer.costModel();optimizer.coe |
: : rcions();optimizer.aliases();optimizer.evaluate();optimizer.emptybind(); :
: : optimizer.deadcode();optimizer.pushselect();optimizer.aliases();optimize :
: : r.for();optimizer.dict();optimizer.mitosis();optimizer.mergetable();opti :
: : mizer.aliases();optimizer.constants();optimizer.commonTerms();optimizer. :
: : projectionpath();optimizer.deadcode();optimizer.matpack();optimizer.reor :
: : der();optimizer.dataflow();optimizer.querylog();optimizer.multiplex();op :
: : timizer.generator();optimizer.candidates();optimizer.deadcode();optimize :
: : r.postfix();optimizer.profiler();optimizer.garbageCollector(); :
| default_fast | optimizer.defaultfast(); |
| no_mitosis_pipe | optimizer.inline();optimizer.remap();optimizer.costModel();optimizer.coe |
: : rcions();optimizer.aliases();optimizer.evaluate();optimizer.emptybind(); :
: : optimizer.deadcode();optimizer.pushselect();optimizer.aliases();optimize :
: : r.for();optimizer.dict();optimizer.mergetable();optimizer.aliases();opti :
: : mizer.constants();optimizer.commonTerms();optimizer.projectionpath();opt :
: : imizer.deadcode();optimizer.matpack();optimizer.reorder();optimizer.data :
: : flow();optimizer.querylog();optimizer.multiplex();optimizer.generator(); :
: : optimizer.candidates();optimizer.deadcode();optimizer.postfix();optimize :
: : r.profiler();optimizer.garbageCollector(); :
| sequential_pipe | optimizer.inline();optimizer.remap();optimizer.costModel();optimizer.coe |
: : rcions();optimizer.aliases();optimizer.evaluate();optimizer.emptybind(); :
: : optimizer.deadcode();optimizer.pushselect();optimizer.aliases();optimize :
: : r.for();optimizer.dict();optimizer.mergetable();optimizer.aliases();opti :
: : mizer.constants();optimizer.commonTerms();optimizer.projectionpath();opt :
: : imizer.deadcode();optimizer.matpack();optimizer.reorder();optimizer.quer :
: : ylog();optimizer.multiplex();optimizer.generator();optimizer.candidates( :
: : );optimizer.deadcode();optimizer.postfix();optimizer.profiler();optimize :
: : r.garbageCollector(); :
| recursive_pipe | optimizer.inline();optimizer.remap();optimizer.costModel();optimizer.coe |
: : rcions();optimizer.aliases();optimizer.evaluate();optimizer.deadcode();o :
: : ptimizer.pushselect();optimizer.aliases();optimizer.for();optimizer.dict :
: : ();optimizer.mergetable();optimizer.aliases();optimizer.constants();opti :
: : mizer.projectionpath();optimizer.deadcode();optimizer.matpack();optimize :
: : r.querylog();optimizer.multiplex();optimizer.generator();optimizer.candi :
: : dates();optimizer.deadcode();optimizer.postfix();optimizer.profiler();op :
: : timizer.garbageCollector(); :
+-----------------+--------------------------------------------------------------------------+
7 tuples
More information on optimizer internals is available in MAL optimizers.