EXPLAIN
command allows you to inspect the query plan at various stages in the query lifecycle.
- The
EXPLAIN
output after the logical optimization phase:EXPLAIN (logical) <query>
. - The
EXPLAIN
output after the physical optimization phase:EXPLAIN (physical) <query>
. - The
EXPLAIN
output annotated with execution statistics:EXPLAIN (analyze) <query>
.
You can retrieve the query plans as a JSON document.
This makes it easy to work with plans programmatically.
Just run
EXPLAIN (<options>, FORMAT JSON) <query>
.EXPLAIN
command, each line represents a single operator.
An operator is printed as a line that starts with [node_id] [OperatorType]
.
The [OperatorType]
is followed by node expressions specific to the operator type.
For example, in
store_sales.ss_item_sk, total_profit
expressions represent the expression retained after the projection.
Similarly, in
node_id
is unique within a single query plan, including subqueries.
However, the node_id
is not necessarily retained across the logical and physical plans for the same query.
Moreover, operations that are represented as a single node in the logical plan may be transformed into multiple nodes in the physical plan.
For example, the
store_sales
table.
The corresponding physical plan starts at
store_sales
table.
These include listing all tables, filtering out tables that don’t contain any relevant data, and reading the data from the remaining tablets.
You can learn more about tablet-level pruning in the Data Pruning and Storage section.
Note that despite the tree-shaped EXPLAIN
output, the query plan is not necessarily a tree.
For example, the plan of the following query references the target_items
CTE twice.
[5] [Filter]
node in the logical plan is referenced twice under the [3] [Join]
node.
The second reference is marked as a _Recurring Node --> [5]
line.