Skip to main content

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 variable optimizer, which can be modified using a SQL assignment, e.g. the minimal optimizer pipeline is set using:

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

Each pipeline consists of a sequence of MAL function calls that inspect and transform the plan for better execution. The preferred optimizer can be changed by either providing its name or the complete MAL function sequence.  For example the minimal pipe can be set also using:

sql> set optimizer='optimizer.inline();optimizer.remap();optimizer.deadcode();optimizer.multiplex();optimizer.garbageCollector();';

The final result of the optimizer steps becomes visible using the SQL EXPLAIN statement modifier. Alternatively, the SQL DEBUG statement modifier in combination with the 'o' command provides access to the intermediate optimizer results.

Several optimizer pipelines are pre-defined in the MonetDB sources. They represent ongoing development activities and experimental code. Their scope of applicability and/or stability  has not reached a satisfactory level to include it in the default pipeline. The predefined optimizer pipelines can be inspected as follows:

sql>select * from optimizers();

A user-defined optimizer pipeline is checked against the dependency information maintained in the optimizer library to ensure there are no conflicts and at least the pre-requisite optimizers are used.

The default SQL optimization pipe line contains the following steps:

inline Inline functions identified as such.
remap Locate hardwired multiplex operations.
costModel Inspects the SQL catalog for size information.
coercions Performs static type coercions.
evaluate Evaluate constant expressions.
emptybind Removes empty bindings
pushselect Push selections through e.g. joins.
aliases Remove alias assignments.
mitosis Horizontal fragmentation.
mergetable Expand horizontal fragmented plans.
deadcode Remove all code not leading to used results.
aliases Remove alias assignments.
constants Evaluate constant expressions.
commonterms Remove duplicate expressions.
projectionpath Searchs multiple joins and glues them together for better runtime optimization.
deadcode Remove all code not leading to used results.
reorder Reorder the execution order.
matpack Recombine partitions into a single BAT.
dataflow item Prepare code for multi-core execution
querylog Keep track of SQL query definitions and invocations.
multiplex Expand all remaining multiplex operations to iterators.
generator Implement for-loop generator as table producing function.
profiler Highlight the candidate lists in the code.
candidates Highlight the candidate lists in the code.
garbageCollector Injects calls to the garbage collector to free up space.