Query Plans

Query Plans#

Models the structure of a query execution plan (QEP).

Query plans are constructed as a tree of operators. Each operator represents an entire query plan by itself. Hence, we use the QueryPlan to refer to the actual nodes in a hierarchical structure. Each node has a potentially large amount of metadata attached to it, e.g. regarding the table being scanned for scan nodes, the estimated cost of the operator or the actual cardinality of the result set. The different types of metadata are structured into three separate classes:

PlanParams contain all structural metadata about the operator, e.g. the table being scanned or the filter predicate.
PlanEstimates contain the optimizer’s view on the operator, e.g. the estimated cardinality and cost.
PlanMeasures contain the actual execution statistics of the operator, e.g. the actual cardinality and execution time.

Users are free to attach additional metadata to each of the containers to support there specific use-cases. However, these additional fields are typically not considered by the standard methods available on query plans. For example, if users store additional tables in the node, these are not considered in the tables method.

Each query plan can contain an arbitrary number of child nodes. This is true even for scans, to accomodate bitmap scans that combine an arbitrary amount of index lookups with a final scan. If just a single child is present, it can be set more expressively using the input_node property.

PostBOUND uses QEPs in two different ways: first, they can be used as the output of the optimization process (i.e. the optimization pipelines), being constructed by the different optimization stages. Second, they can also be extracted from an actual database system to encode the QEP that this system used to execute a specific query. This dichotomy leads to different granularities of query plans: actual database systems often have much more detailed QEPs. For example, Postgres represents a hash join as a hash join operator, whose inner child is a hash operator that constructs the hash table. The optimizer stages will typically not worry about such fine-grained details and simply demand a join to be executed as a hash join. To mitigate these issues, the query plans can be normalized by using the canonical method. This method removes all unnecessary details and only retains the join and scan operators.

When constructing a query plan, the metadata can be provided in two ways: either as instances of the corresponding metadata objects, or explicitly as keyword arguments to enable a more convenient usage. Notice however, that these two ways cannot be mixed: either all metadata of a specific type is provided as wrapper instance, or all metadata is provided as keyword arguments. Mixing is only allowed across different metadata types, e.g. providing the estimates as a PlanEstimates object and the measurements as keyword arguments.

In addition to the pre-defined metadata types, you can also add additional metadata as part of the kwargs. These will be added to the plan parameters (using the same mixing rules as the pre-defined types). Each query plan provides dict-like access to the plan parameters, estimates and measures, e.g. plan["custom"] = 42, plan.get("custom", default), or "custom" in plan.

Query plans provide rather extensive support methods to check their shape (e.g. is_linear() or is_bushy()), to aid with traversal (e.g. find_first_node() or find_all_nodes()) or to extract specific information (e.g. tables() or qerror()).

To convert between different optimization artifacts, a number of methods are available. For example, to_query_plan can be used to construct a query plan from a join order and a set of operators. Likewise, explode_query_plan converts the query plan back into join order, operators and parameters.

Query plans support len() (providing the plan depth without subplans) and iter() (providing all contained nodes including subplans).

Parameters:

node_type (str | PhysicalOperator) – The name of the operator. If this is supplied as a physical operator, the name is inferred from it.
operator (Optional[PhysicalOperator], optional) – The actual operator that is used to compute the result set. This can be empty if there is no specific operator corresponding to the current node (e.g. for transient hash tables).
children (Optional[QueryPlan | Iterable[QueryPlan]], optional) – The input nodes of the current operator. For nodes without an input (e.g. most scans), this can simply be None or an empty list. Nodes with exactly one input node (e.g. most aggregations) can supply their input either directly as a plan object, or as a singleton list. Nodes with two input nodes (e.g. joins) should supply them as an ordered iterable with the outer child first.
plan_params (Optional[PlanParams], optional) – Structural metadata (e.g. parallel workers or accessed indexes) of the operator. If this is provided, no other plan parameters can be supplied as keyword arguments, including kwargs.
subplan (Optional[Subplan], optional) – A subquery that has to be executed as part of this node. If this is provided, no other subplan components can be supplied as keyword arguments.
estimates (Optional[PlanEstimates], optional) – The optimizer’s view on the operator (e.g. estimated cardinality and cost). If this is provided, no other estimates can be supplied as keyword arguments.
measures (Optional[PlanMeasures], optional) – The actual execution statistics of the operator (e.g. actual cardinality and execution time). If this is provided, no other measures can be supplied as keyword arguments.
base_table (Optional[TableReference], optional) – The table that is being scanned. This is only relevant for scan nodes and should be None for all other nodes. If this argument is used, no other plan parameters can be supplied in the plan_params argument.
filter_predicate (Optional[AbstractPredicate], optional) – An arbitrary predicate to restrict the allowed tuples in the output of a relation. This should be mostly used for join nodes and scans. If this argument is used, no other plan parameters can be supplied in the plan_params argument.
parallel_workers (Optional[int], optional) – The number of parallel workers that should be used to execute the operator. If this argument is used, no other plan parameters can be supplied in the plan_params argument.
index (Optional[str], optional) – The name of the index that should be used to scan the table. This is mostly relevant for scan nodes and should be None for all other nodes. If this argument is used, no other plan parameters can be supplied in the plan_params argument.
lookup_key (Optional[SqlExpression], optional) – The expression that is used to lookup tuples in some indexing structure. For scans, this could actually be the physical index. For intermediate operators such as hash tables or memoize nodes, this could be the expression that is used to build the table or to structure the memo. If this argument is used, no other plan parameters can be supplied in the plan_params argument.
sort_keys (Optional[Sequence[SortKey]], optional) – How the tuples in a the output of a relation are sorted. Absence of a specific sort order can be indicated either through an empty list or by setting this parameter to None. In this case, tuples are assumed to be in some random order. If this argument is used, no other plan parameters can be supplied in the plan_params argument.
estimated_cardinality (Cardinality, optional) – The estimated number of tuples that are produced by the operator. If no estimate is available, NaN can be used. If this argument is used, no other estimates can be supplied in the estimates argument.
estimated_cost (Cost, optional) – The approximate amount of abstract “work” that needs to be done to compute the result set of the operator. If no estimate is available, NaN can be used. If this argument is used, no other estimates can be supplied in the estimates argument.
actual_cardinality (Cardinality, optional) – The actual number of tuples that are produced by the operator. If no measurement is available, NaN can be used. If this argument is used, no other measures can be supplied in the measures argument.
execution_time (float, optional) – The total time (in seconds) that was spent to compute the result set of the operator. If no measurement is available, NaN can be used. If this argument is used, no other measures can be supplied in the measures argument.
cache_hits (Optional[int], optional) – The number of page reads that were satisfied by the shared buffer. If no measurement is available, None can be used. If this argument is used, no other measures can be supplied in the measures argument.
cache_misses (Optional[int], optional) – The number of page reads that had to be delegated to the disk and could not be satisfied by the shared buffer. If no measurement is available, None can be used. If this argument is used, no other measures can be supplied in the measures argument.
subplan_root (Optional[QueryPlan], optional) – The root operator of the subplan. If this argument is used, no other subplan components can be supplied in the subplan argument.
subplan_target_name (str, optional) – The name of the target table that the subplan should produce. If this argument is used, no other subplan components can be supplied in the subplan argument.
**kwargs – Additional metadata that should be attached to the plan parameters. If this is used, no other plan parameters can be supplied in the plan_params argument.

See also

to_query_plan, explode_query_plan, OptimizerInterface.query_plan, OptimizationPipeline.query_execution_plan

property node_type: str#: Get the name of the operator.

property operator: ScanOperator | JoinOperator | IntermediateOperator | None#

Get the actual operator that is used to compute the result set.

For transient operators (e.g. hash tables), this can be None.

property input_node: QueryPlan | None#

Get the input node of the current operator.

For nodes without an input (e.g. most scans), or nodes with multiple inputs (e.g. joins), this is None.

property children: Sequence[QueryPlan]#

Get the input nodes of the current operator.

For nodes without an input (e.g. most scans), this is an empty list. For nodes with exactly one input (e.g. most aggregations), this is a singleton list. For nodes with two input nodes (e.g. joins), this is an ordered iterable with the outer child first.

property outer_child: QueryPlan | None#

Get the outer input of the current operator.

For nodes that do not have exactly two inputs, this is None.

property inner_child: QueryPlan | None#

Get the inner input of the current operator.

For nodes that do not have exactly two inputs, this is None.

property params: PlanParams#: Get the structural metadata of the operator.

property base_table: TableReference | None#

Get the table that is being scanned. For non-scan nodes, this will probably is None.