3.2.5 Optimizer Control

The code produced by MonetDB/SQL is massaged by several code optimizers to arrive at the best possible plan for evaluation. However, for development purposes and the rare case that more control is needed, the SQL session variable optimizer can be set to a list of optimizers to identify the steps needed.

     sql>set optimizer='costModel,coercions,emptySet';
     sql>select optimizer;
     +------------------------------+
     | single_value                 |
     +==============================+
     | costModel,coercions,emptySet |
     +------------------------------+
     sql>set optimizer='default_pipe'; -- to return to default setting

The default SQL optimization pipe line is defined as:

inline	Inline functions identified as such.
remap	Locate hardwired multiplex operations.
evaluate	Evaluate constant expressions
costModel	Inspects the SQL catalog for size information.
coercions	Performs static coercions.
emptySet	Removes all empty set expressions
aliases	Remove alias assignments.
mergetables	Manage horizontal partitions
commonterms	Hunts for common terms and retains one only.
accumulators	Re-uses BATs to hold the result of an arithmetic expression.
joinPath	Searchs multiple joins and glues them together for better optimization.
deadcode	Remove all code not leading to used results.
reduce	Reduces the stack space for faster calls.
garbageCollector	Injects calls to the garbage collector to free up space.
dataflow	item Prepare code for multi-core execution
multiplex	Expand all remaining multiplex operations to iterators.

During SQL initialization, the 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 final result of the optimizer steps become visible using the explain statement modifier. Alternatively, the debug statement modifier in combination with the 'o' command provides access to the intermediate optimizer results.

Several optimizer pipelines are defined in the MonetDB configuration file. They represent ongoing development activities and experimental code. Or, the stability of an optimizer has not reached a satisfactory level. The pipe lines defined in the current releae are illustrated below.

default_pipe The approved pipeline used on a daily basis in all SQL applications. The default pipe line contains as of Feb2010 mitosis-mergetable-reorder, aimed at large tables and improved access locality.

nov2009_pipe The default pipeline used in the November 2009 release.

replication_pipe Extend the default_pipe with replication functionality.

recycler_pipe Extend the default_pipe with the recycler cache functionality.

cracker_pipe Extend the default_pipe with the database cracking functionality.

octopus_pipe Extend the default_pipe with distributed processing functionality.