EXPLAIN
The EXPLAIN
feature analyzes and displays how Firebolt’s query processing system runs your query. You can use this information to understand resource utilization, and identify opportunities to improve and optimize your query performance.
If you specify the ANALYZE
option for EXPLAIN
, Firebolt also collects detailed metrics about each operator during query runtime, including the amount of time spent on the operator, and how much data it processes.
Syntax
EXPLAIN [( <option_name> [<option_value>] [, ...] )] <select_statement>
Parameter | Description |
---|---|
option_name | The name of an option. See below for a list of all available options. |
option_value | The value of the option. If no value is specified, the default is TRUE . |
<select_statement> | Any select statement. |
Explain Options
You can augment the output of EXPLAIN
by specifying options. The following table lists all available options and their functionalities:
Option Name | Option Values | Description |
---|---|---|
LOGICAL | TRUE , FALSE | Returns the optimized logical query plan by default, unless otherwise specified. |
PHYSICAL | TRUE , FALSE | Returns an optimized physical query plan containing shuffle operators for queries on distributed engines, showing how work is distributed between nodes of an engine. |
ANALYZE | TRUE , FALSE | Returns an optimized physical query plan annotated with metrics from query execution. For more information about these metrics, see Example with ANALYZE. |
ALL | TRUE , FALSE | Returns all of the previous LOGICAL , PHYSICAL , and ANALYZE plans. Use the following sample syntax: EXPLAIN (ALL) <select statement> . |
STATISTICS | TRUE , FALSE | Returns an annotated query plan that includes estimates from Firebolt’s query optimizer. This option works with LOGICAL , PHYSICAL , ANALYZE , and ALL . Use the following sample syntax: EXPLAIN (STATISTICS) <select statement> . |
You may specify only one of the following options: LOGICAL
, PHYSICAL
, ANALYZE
, and ALL
. If you need to view several plans at once, use the ALL
option.
Example
The following example shows how to generate an EXPLAIN
statement for a SELECT
query on a table named lineitem
.
EXPLAIN
SELECT
l_shipdate,
l_linestatus,
l_orderkey,
AVG(l_discount)
FROM
lineitem
WHERE
l_returnflag = 'N'
AND l_shipdate > '1996-01-01'
GROUP BY
ALL
ORDER BY
1,2,3,4;
Returns:
[0] [Projection] lineitem.l_shipdate, lineitem.l_linestatus, lineitem.l_orderkey, avgOrNull(lineitem.l_discount)
\_[1] [Sort] OrderBy: [lineitem.l_shipdate Ascending Last, lineitem.l_linestatus Ascending Last, lineitem.l_orderkey Ascending Last, avgOrNull(lineitem.l_discount) Ascending Last]
\_[2] [Aggregate] GroupBy: [lineitem.l_orderkey, lineitem.l_linestatus, lineitem.l_shipdate] Aggregates: [avgOrNull(lineitem.l_discount)]
\_[3] [Projection] lineitem.l_orderkey, lineitem.l_discount, lineitem.l_linestatus, lineitem.l_shipdate
\_[4] [Predicate] equals(lineitem.l_returnflag, 'N'), greater(lineitem.l_shipdate, toPGDate('1996-01-01'))
\_[5] [StoredTable] Name: 'lineitem', used 5/16 column(s) FACT
The EXPLAIN
output shows the sequence of operations that Firebolt’s engine will use to run the query:
- Read the required columns from the
lineitem
table. - Filter the
lineitem
table by theWHERE
conditions. - Remove the columns that are no longer required.
- Perform the aggregation as specified by the
GROUP BY
clause. - Sort resulting rows in ascending order by all columns from the
ORDER BY
clause. - Bring the columns into the order specified in the
SELECT
clause.
Example with ANALYZE
The following code example runs the same query as the previous example on a multi-node engine using the (ALL)
option, which is the same as specifying (ALL TRUE)
or (LOGICAL, PHYSICAL, ANALYZE)
.
EXPLAIN (ALL)
SELECT
l_shipdate,
l_linestatus,
l_orderkey,
AVG(l_discount)
FROM
lineitem
WHERE
l_returnflag = 'N'
AND l_shipdate > '1996-01-01'
GROUP BY
ALL
ORDER BY
1,2,3,4;
Returns:
- Disclaimer
- The format of this result is subject to change.
The query in the previous example returns three columns explain_logical
, explain_physical
, and explain_analyze
, as shown in the following sections.
Each column can be toggled on or off using the corresponding options. For example, to display only explain_logical
and explain_analyze
, you can specify either (LOGICAL, ANALYZE)
or (ALL, PHYSICAL FALSE)
. With the ANALYZE
option enabled, this query took 4.7 seconds to run. Without the ANALYZE
option, the query does not run and should return the result almost immediately.
EXPLAIN (LOGICAL)
output
[0] [Projection] ref_2, ref_1, ref_0, ref_3
| [RowType]: date not null, text not null, bigint not null, double precision null
\_[1] [Sort] OrderBy: [ref_2 Ascending Last, ref_1 Ascending Last, ref_0 Ascending Last, ref_3 Ascending Last]
| [RowType]: bigint not null, text not null, date not null, double precision null
\_[2] [Aggregate] GroupBy: [ref_0, ref_2, ref_3] Aggregates: [avg2(ref_1)]
| [RowType]: bigint not null, text not null, date not null, double precision null
\_[3] [Projection] ref_0, ref_1, ref_3, ref_4
| [RowType]: bigint not null, double precision not null, text not null, date not null
\_[4] [Filter] (ref_2 = 'N'), (ref_4 > DATE '1996-01-01')
| [RowType]: bigint not null, double precision not null, text not null, text not null, date not null
\_[5] [StoredTable] Name: "lineitem", used 5/16 column(s) FACT, ref_0: "l_orderkey" ref_1: "l_discount" ref_2: "l_returnflag" ref_3: "l_linestatus" ref_4: "l_shipdate"
[RowType]: bigint not null, double precision not null, text not null, text not null, date not null
The explain_logical
column shows the optimized logical query plan of the SELECT
statement, identical to the plan in Example. Additionally, it shows the output row type for each operator. The variable ref_2
refers to the second input column from the operator below. For operators with multiple input operators such as the Join
operator, input columns are concatenated. For example, if the first input produces three columns, and the second input produces four columns, the first input’s columns are labeled ref_0
to ref_2
, while the second input’s columns are labeled ref_3
to ref-6
.
EXPLAIN (PHYSICAL)
output
[0] [MaybeCache]
| [RowType]: date not null, text not null, bigint not null, double precision null
\_[1] [Projection] ref_2, ref_1, ref_0, ref_3
| [RowType]: date not null, text not null, bigint not null, double precision null
\_[2] [SortMerge] OrderBy: [ref_2 Ascending Last, ref_1 Ascending Last, ref_0 Ascending Last, ref_3 Ascending Last]
| [RowType]: bigint not null, text not null, date not null, double precision null
\_[3] [Shuffle] Gather
| [RowType]: bigint not null, text not null, date not null, double precision null
| [Affinity]: many nodes
\_[4] [Sort] OrderBy: [ref_2 Ascending Last, ref_1 Ascending Last, ref_0 Ascending Last, ref_3 Ascending Last]
| [RowType]: bigint not null, text not null, date not null, double precision null
\_[5] [AggregateMerge] GroupBy: [ref_0, ref_1, ref_2] Aggregates: [avg2merge(ref_3)]
| [RowType]: bigint not null, text not null, date not null, double precision null
\_[6] [Shuffle] Hash by [ref_0, ref_1, ref_2]
| [RowType]: bigint not null, text not null, date not null, aggregatefunction(avg2ornull, double precision not null) not null
| [Affinity]: many nodes
\_[7] [AggregateState partial] GroupBy: [ref_0, ref_2, ref_3] Aggregates: [avg2(ref_1)]
| [RowType]: bigint not null, text not null, date not null, aggregatefunction(avg2ornull, double precision not null) not null
\_[8] [Projection] ref_0, ref_1, ref_3, ref_4
| [RowType]: bigint not null, double precision not null, text not null, date not null
\_[9] [Filter] (ref_2 = 'N'), (ref_4 > DATE '1996-01-01')
| [RowType]: bigint not null, double precision not null, text not null, text not null, date not null
\_[10] [StoredTable] Name: "lineitem", used 5/16 column(s) FACT, ref_0: "l_orderkey" ref_1: "l_discount" ref_2: "l_returnflag" ref_3: "l_linestatus" ref_4: "l_shipdate"
[RowType]: bigint not null, double precision not null, text not null, text not null, date not null
The explain_physical
column displays a detailed, optimized physical plan that includes Shuffle operators for distributed query execution, offering insights into how tasks are distributed across the engine’s nodes. A Shuffle
operator redistributes data across engine nodes. For example, scans of FACT
tables, like operator [9] [StoredTable]
in the previous example, and the operators following it are automatically distributed across all nodes of an engine. A Shuffle
operator of type Hash
indicates that the workload of the following operators is distributed across all nodes, while a Shuffle
operator of type Gather
consolidates data onto a single node of the engine. In the previous example, only the operator [1] [SortMerge]
runs on a single node, merging the sorted partial query results from all other nodes.
The MaybeCache
operator, at the top of the plan, caches its input in main memory on a best-effort basis for future runs of the same (sub-) query. It considers the entire plan leading to its input, as well as the state of the scanned tables. If the data in a table changes, the MaybeCache
operator will know not to read an outdated cached entry. It may also skip caching large results or prioritize caching results that offer greater time savings. The MaybeCache
operator may appear in different places of a plan, but in this query it caches the full query result. For more information about the MaybeCache
operator, see Subresult Reuse in Firebolt.
EXPLAIN (ANALYZE)
output
[0] [MaybeCache]
| [RowType]: date not null, text not null, bigint not null, double precision null
| [Execution Metrics]: output cardinality = 270958684, thread time = 7ms, cpu time = 4ms
\_[1] [Projection] ref_2, ref_1, ref_0, ref_3
| [RowType]: date not null, text not null, bigint not null, double precision null
| [Execution Metrics]: Optimized out
\_[2] [SortMerge] OrderBy: [ref_2 Ascending Last, ref_1 Ascending Last, ref_0 Ascending Last, ref_3 Ascending Last]
| [RowType]: bigint not null, text not null, date not null, double precision null
| [Execution Metrics]: output cardinality = 270958684, thread time = 27932ms, cpu time = 27692ms
\_[3] [Shuffle] Gather
| [RowType]: bigint not null, text not null, date not null, double precision null
| [Affinity]: many nodes
| [Execution Metrics]: output cardinality = 270958684, thread time = 1846ms, cpu time = 1779ms
\_[4] [Sort] OrderBy: [ref_2 Ascending Last, ref_1 Ascending Last, ref_0 Ascending Last, ref_3 Ascending Last]
| [RowType]: bigint not null, text not null, date not null, double precision null
| [Execution Metrics]: output cardinality = 270958684, thread time = 286199ms, cpu time = 285590ms
\_[5] [AggregateMerge] GroupBy: [ref_0, ref_1, ref_2] Aggregates: [avg2merge(ref_3)]
| [RowType]: bigint not null, text not null, date not null, double precision null
| [Execution Metrics]: output cardinality = 270958684, thread time = 90694ms, cpu time = 77308ms
\_[6] [Shuffle] Hash by [ref_0, ref_1, ref_2]
| [RowType]: bigint not null, text not null, date not null, aggregatefunction(avg2ornull, double precision not null) not null
| [Affinity]: many nodes
| [Execution Metrics]: output cardinality = 270958691, thread time = 27649ms, cpu time = 24247ms
\_[7] [AggregateState partial] GroupBy: [ref_0, ref_2, ref_3] Aggregates: [avg2(ref_1)]
| [RowType]: bigint not null, text not null, date not null, aggregatefunction(avg2ornull, double precision not null) not null
| [Execution Metrics]: output cardinality = 270958691, thread time = 191561ms, cpu time = 184761ms
\_[8] [Projection] ref_0, ref_1, ref_3, ref_4
| [RowType]: bigint not null, double precision not null, text not null, date not null
| [Execution Metrics]: Optimized out
\_[9] [Filter] (ref_2 = 'N'), (ref_4 > DATE '1996-01-01')
| [RowType]: bigint not null, double precision not null, text not null, text not null, date not null
| [Execution Metrics]: output cardinality = 275468571, thread time = 3578ms, cpu time = 3566ms
\_[10] [StoredTable] Name: "lineitem", used 5/16 column(s) FACT, ref_0: "l_orderkey" ref_1: "l_discount" ref_2: "l_returnflag" ref_3: "l_linestatus" ref_4: "l_shipdate"
[RowType]: bigint not null, double precision not null, text not null, text not null, date not null
[Execution Metrics]: output cardinality = 334006486, thread time = 29642ms, cpu time = 29608ms
The explain_analyze
column contains the same query plan as the explain_physical
column, but annotated with metrics collected during query execution. For each operator, it shows the number of rows it produced in output_cardinality
, and how much time was spent on that operator in thread_time
and cpu_time
. The thread_time
is the sum of the wall-clock time that threads spent working on the operator across all nodes, while cpu_time
is the total time those threads were scheduled on a CPU core. A significantly smaller cpu_time
compared to thread_time
suggests that the operator is either waiting on input or output operations or that the engine is handling multiple queries simultaneously.
Analyzing the Metrics
In the previous example, the cpu_time
is almost as high as the thread_time
on the StoredTable
node. This indicates that the data of the lineitem
table was in the SSD cache. On a cold query run, where data must be retrieved from an Amazon S3 bucket, thread_time
is considerably higher than cpu_time
for the same operator:
[10] [StoredTable] Name: "lineitem", used 5/16 column(s) FACT, ref_0: "l_orderkey" ref_1: "l_discount" ref_2: "l_returnflag" ref_3: "l_linestatus" ref_4: "l_shipdate"
[RowType]: bigint not null, double precision not null, text not null, text not null, date not null
[Execution Metrics]: output cardinality = 334006486, thread time = 29642ms, cpu time = 29608ms
Additionally, a significant amount of time is devoted to sorting the query results:
[2] [SortMerge] OrderBy: [ref_2 Ascending Last, ref_1 Ascending Last, ref_0 Ascending Last, ref_3 Ascending Last]
| [RowType]: bigint not null, text not null, date not null, double precision null
| [Execution Metrics]: output cardinality = 270958684, thread time = 27932ms, cpu time = 27692ms
\_[3] [Shuffle] Gather
| [RowType]: bigint not null, text not null, date not null, double precision null
| [Affinity]: many nodes
| [Execution Metrics]: output cardinality = 270958684, thread time = 1846ms, cpu time = 1779ms
\_[4] [Sort] OrderBy: [ref_2 Ascending Last, ref_1 Ascending Last, ref_0 Ascending Last, ref_3 Ascending Last]
| [RowType]: bigint not null, text not null, date not null, double precision null
| [Execution Metrics]: output cardinality = 270958684, thread time = 286199ms, cpu time = 285590ms
Removing the ORDER BY
can considerably speed up query runtimes. In this case, the total query time is nearly halved, dropping from 1 minute and 19 seconds to 47.3 seconds. If only the initial results in the specified sorting order are needed, introducing a LIMIT
operator can further enhance performance. Adding LIMIT 10000
to the query produces the following output:
[2] [SortMerge] OrderBy: [ref_2 Ascending Last, ref_1 Ascending Last, ref_0 Ascending Last, ref_3 Ascending Last] Limit: [10000]
| [RowType]: bigint not null, text not null, date not null, double precision null
| [Execution Metrics]: output cardinality = 10000, thread time = 1ms, cpu time = 1ms
\_[3] [Shuffle] Gather
| [RowType]: bigint not null, text not null, date not null, double precision null
| [Affinity]: many nodes
| [Execution Metrics]: output cardinality = 20000, thread time = 0ms, cpu time = 0ms
\_[4] [Sort] OrderBy: [ref_2 Ascending Last, ref_1 Ascending Last, ref_0 Ascending Last, ref_3 Ascending Last] Limit: [10000]
| [RowType]: bigint not null, text not null, date not null, double precision null
| [Execution Metrics]: output cardinality = 20000, thread time = 6403ms, cpu time = 6372ms
\_[5] [AggregateMerge] GroupBy: [ref_0, ref_1, ref_2] Aggregates: [avg2merge(ref_3)]
| [RowType]: bigint not null, text not null, date not null, double precision null
| [Execution Metrics]: output cardinality = 270958684, thread time = 83213ms, cpu time = 74525ms
The time spent in the Sort
operator is significantly reduced, nearly by an order of magnitude. The Sort
operator takes the LIMIT
clause directly into account and emits only the required minimum number of rows. This reduces the overall query time from 1 minute and 19 seconds to 40.3 seconds, which is an even greater improvement than removing the ORDER BY
clause, as less result data needs to be serialized for client return.
A secondary benefit is that the result is now small enough to be cached by the MaybeCache
operator. Running the query with LIMIT 10000
again produces the following output:
[0] [MaybeCache]
| [RowType]: date not null, text not null, bigint not null, double precision null
| [Execution Metrics]: output cardinality = 10000, thread time = 0ms, cpu time = 0ms
\_[1] [Projection] ref_2, ref_1, ref_0, ref_3
| [RowType]: date not null, text not null, bigint not null, double precision null
| [Execution Metrics]: Optimized out
\_[2] [SortMerge] OrderBy: [ref_2 Ascending Last, ref_1 Ascending Last, ref_0 Ascending Last, ref_3 Ascending Last] Limit: [10000]
| [RowType]: bigint not null, text not null, date not null, double precision null
| [Execution Metrics]: Nothing was executed
The query completes in under 10 ms, with each operator beneath MaybeCache
indicating that either Nothing was executed
or an output cardinality
of 0
. The MaybeCache
operator identified a cache entry for this physical plan, based on the unchanged state of the lineitem
table, and returned the cached result. As a result, all operators required to generate input for MaybeCache
were canceled and did not run.