Inspecting query plans - Firebolt Documentation

In order to tune your query, you will often have to get the execution plan for that query, and interpret plan differences as you make modifications. The 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>.

The following code snippet illustrates the different plans on a simple query.

select
  ss_item_sk,
  sum(ss_net_profit) as total_profit
from
  store_sales
where
  ss_sold_date_sk between 2451666 and 2451696
group by
  ss_item_sk
having 
  sum(ss_net_profit) > 0
order by
  total_profit desc;

[0] [Projection] store_sales.ss_item_sk, total_profit
 \_[1] [Sort] OrderBy: [total_profit Descending First]
    \_[2] [Filter] (total_profit > 0.00)
       \_[3] [Aggregate] GroupBy: [store_sales.ss_item_sk] Aggregates: [total_profit: sum(store_sales.ss_net_profit)]
          \_[4] [Projection] store_sales.ss_item_sk, store_sales.ss_net_profit
             \_[5] [Filter] (store_sales.ss_sold_date_sk >= 2451666), (store_sales.ss_sold_date_sk <= 2451696)
                \_[6] [StoredTable] Name: "store_sales"

[0] [Projection] store_sales.ss_item_sk, total_profit
 \_[1] [Sort] OrderBy: [total_profit Descending First]
    \_[2] [Filter] (total_profit > 0.00)
       \_[3] [Aggregate] GroupBy: [store_sales.ss_item_sk] Aggregates: [total_profit: sum(store_sales.ss_net_profit)]
          \_[4] [Projection] store_sales.ss_item_sk, store_sales.ss_net_profit
             \_[5] [Filter] (store_sales.ss_sold_date_sk >= 2451666), (store_sales.ss_sold_date_sk <= 2451696)
                \_[6] [TableFuncScan] store_sales.ss_sold_date_sk: $0.ss_sold_date_sk, store_sales.ss_item_sk: $0.ss_item_sk, store_sales.ss_net_profit: $0.ss_net_profit
                  |   $0 = read_tablets(table_name => store_sales, tablet)
                   \_[7] [Projection] tablet
                      \_[8] [Filter] ((max_ss_sold_date_sk < 2451666) IS DISTINCT FROM TRUE), ((min_ss_sold_date_sk > 2451696) IS DISTINCT FROM TRUE)
                         \_[9] [TableFuncScan] tablet: $0.tablet, min_ss_sold_date_sk: $0.min_ss_sold_date_sk, max_ss_sold_date_sk: $0.max_ss_sold_date_sk
                           |   $0 = list_tablets(table_name => store_sales)
                            \_[10] [Projection]
                               \_[11] [SystemOneTable]

[0] [Projection] store_sales.ss_item_sk, total_profit
|   [Execution Metrics]: Optimized out
 \_[1] [Sort] OrderBy: [total_profit Descending First]
   |   [Execution Metrics]: output cardinality = 1527, thread time = 0ms, cpu time = 0ms
    \_[2] [Filter] (total_profit > 0.00)
      |   [Execution Metrics]: output cardinality = 1527, thread time = 0ms, cpu time = 0ms
       \_[3] [Aggregate] GroupBy: [store_sales.ss_item_sk] Aggregates: [total_profit: sum(store_sales.ss_net_profit)]
         |   [Execution Metrics]: output cardinality = 8476, thread time = 2ms, cpu time = 2ms
          \_[4] [Projection] store_sales.ss_item_sk, store_sales.ss_net_profit
            |   [Execution Metrics]: Optimized out
             \_[5] [Filter] (store_sales.ss_sold_date_sk >= 2451666), (store_sales.ss_sold_date_sk <= 2451696)
               |   [Execution Metrics]: output cardinality = 27253, thread time = 0ms, cpu time = 0ms
                \_[6] [TableFuncScan] store_sales.ss_sold_date_sk: $0.ss_sold_date_sk, store_sales.ss_item_sk: $0.ss_item_sk, store_sales.ss_net_profit: $0.ss_net_profit
                  |   $0 = read_tablets(table_name => store_sales, tablet)
                  |   [Execution Metrics]: output cardinality = 27253, thread time = 67ms, cpu time = 67ms
                   \_[7] [Projection] tablet
                     |   [Execution Metrics]: Optimized out
                      \_[8] [Filter] ((max_ss_sold_date_sk < 2451666) IS DISTINCT FROM TRUE), ((min_ss_sold_date_sk > 2451696) IS DISTINCT FROM TRUE)
                        |   [Execution Metrics]: output cardinality = 1, thread time = 0ms, cpu time = 0ms
                         \_[9] [TableFuncScan] tablet: $0.tablet, min_ss_sold_date_sk: $0.min_ss_sold_date_sk, max_ss_sold_date_sk: $0.max_ss_sold_date_sk
                           |   $0 = list_tablets(table_name => store_sales)
                           |   [Execution Metrics]: output cardinality = 1, thread time = 0ms, cpu time = 0ms
                            \_[10] [Projection]
                              |   [Execution Metrics]: output cardinality = 0, thread time = 0ms, cpu time = 0ms
                               \_[11] [SystemOneTable]
                                     [Execution Metrics]: Nothing was executed

The query plans are printed as a tree structure, with the root node at the top left and the leaves at the bottom right. For binary operators (such as joins) the left child of a node is the first input side of the operator, and the right child is the second input side. If no other per-operator information is requested in the 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

[0] [Projection] store_sales.ss_item_sk, total_profit

the store_sales.ss_item_sk, total_profit expressions represent the expression retained after the projection. Similarly, in

[5] [Filter] (store_sales.ss_sold_date_sk >= 2451666), (store_sales.ss_sold_date_sk <= 2451696)

the two comma-seprated expressions represent the filter conditions that need to hold simultaneously in order to retain the row in the filter output. The 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

[6] [StoredTable] Name: "store_sales"

operator in the logical plan represents the logical operation of reading the store_sales table. The corresponding physical plan starts at

[6] [TableFuncScan] store_sales.ss_sold_date_sk: ...

and represents the sequence of physical operations that the runtime will perform when reading the 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.

with target_items as (
  select * from item where i_category_id = 'Music'
)
select
  *
from
  target_items l, target_items r
where
  l.i_brand_id = r.i_brand_id and l.i_wholesale_cost < r.i_wholesale_cost;

[0] [Projection] item.i_item_sk, item.i_item_id, item.i_rec_start_date, item.i_rec_end_date, item.i_item_desc, item.i_current_price, item.i_wholesale_cost, item.i_brand_id, item.i_brand, item.i_class_id, item.i_class, fb_cast_0: cast('Music' as bigint), item.i_category, item.i_manufact_id, item.i_manufact, item.i_size, item.i_formulation, item.i_color, item.i_units, item.i_container, item.i_manager_id, item.i_product_name, item.i_item_sk, item.i_item_id, item.i_rec_start_date, item.i_rec_end_date, item.i_item_desc, item.i_current_price, item.i_wholesale_cost, item.i_brand_id, item.i_brand, item.i_class_id, item.i_class, fb_cast_1: cast('Music' as bigint), item.i_category, item.i_manufact_id, item.i_manufact, item.i_size, item.i_formulation, item.i_color, item.i_units, item.i_container, item.i_manager_id, item.i_product_name
 \_[1] [Filter] (item.i_wholesale_cost < item.i_wholesale_cost)
    \_[2] [Projection] item.i_item_sk, item.i_item_id, item.i_rec_start_date, item.i_rec_end_date, item.i_item_desc, item.i_current_price, item.i_wholesale_cost, item.i_brand_id, item.i_brand, item.i_class_id, item.i_class, item.i_category, item.i_manufact_id, item.i_manufact, item.i_size, item.i_formulation, item.i_color, item.i_units, item.i_container, item.i_manager_id, item.i_product_name, item.i_item_sk, item.i_item_id, item.i_rec_start_date, item.i_rec_end_date, item.i_item_desc, item.i_current_price, item.i_wholesale_cost, item.i_brand, item.i_class_id, item.i_class, item.i_category, item.i_manufact_id, item.i_manufact, item.i_size, item.i_formulation, item.i_color, item.i_units, item.i_container, item.i_manager_id, item.i_product_name
       \_[3] [Join] Mode: Inner [(item.i_brand_id = item.i_brand_id)]
          \_[4] [Projection] item.i_item_sk, item.i_item_id, item.i_rec_start_date, item.i_rec_end_date, item.i_item_desc, item.i_current_price, item.i_wholesale_cost, item.i_brand_id, item.i_brand, item.i_class_id, item.i_class, item.i_category, item.i_manufact_id, item.i_manufact, item.i_size, item.i_formulation, item.i_color, item.i_units, item.i_container, item.i_manager_id, item.i_product_name
          |  \_[5] [Filter] (item.i_category_id = cast('Music' as bigint))
          |     \_[6] [StoredTable] Name: "item"
          \_[7] [Projection] item.i_item_sk, item.i_item_id, item.i_rec_start_date, item.i_rec_end_date, item.i_item_desc, item.i_current_price, item.i_wholesale_cost, item.i_brand_id, item.i_brand, item.i_class_id, item.i_class, item.i_category, item.i_manufact_id, item.i_manufact, item.i_size, item.i_formulation, item.i_color, item.i_units, item.i_container, item.i_manager_id, item.i_product_name
             \_Recurring Node --> [5]

The [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.