SELECT
queries are declarativeâthey donât prescribe the exact sequence of operations that the database will execute in order to answer them.
It is the job of the query planner to figure out this sequence in the form of an execution plan.
Query planning in Firebolt consists of a logical and a physical optimization phase.
Both phases follow a modular design by instantiating a set of rules and an optimization driver.
The driver instance receives an input query plan and transforms it by running through a sequence of pre-configured transformation passes.
Each rewrite pass contains a set of rewrite rules that are executed during the traversal of the query plan.
The rewrite pass keeps traversing the plan until the query plan reaches a fixed point, ie. when a full traversal ends without performing any modification to the query plan.
Rules are generally split into several categories:
- Desugaring rules remove aspects of the original query that are not directly supported by the Firebolt runtime.
Examples in this category are rules that de-correlate correlated sub-plans, as well the rule that lowers
GROUPING SETS
aggregations. - Normalization rules perform transformations that produce plans in canonical form that are easier to reason about.
Examples in this category are rules that merge adjacent query plan operators (such as a stack of
Filter
operators sitting on top of each other). - Heuristic-based rules perform rewrites that in general always result in a more efficient execution plan. Examples in this category are rules that push down filters and and rules that push down projections.
- Cost-based rules apply different transformations depending on a cost model which estimates the number of rows that will be processed by each operator. Examples in this category are rules that perform join reordering, as well as rules that push down aggregate operators through joins.
- Inspecting query plans.
Learn ho to use the
EXPLAIN
command in order to inspect query plans at various stages of the query lifecycle. - Cardinality estimation. Fireboltâs cost-based rules use a notion of cost based on the estimated number of output rows (that is, the output cardinality) of each sub-plan. Learn how these estimates are computed and how you can control them.
- User-guided mode. Per default, Fireboltâs optimizer makes all decisions for you. This mode of operation should be sufficient for most use cases. However, in mission-critical applications you might want to fine-tune and fix the execution plans of your queries. For such situations, Fireboltâs user-guided mode gives you fine-grained control over the order of operators such as joins and aggregations.