Querying and query optimization
EXPLAIN
The
You may specify only one of the following options:
Returns:
The
Returns:
Disclaimer
: The format of this result is subject to change.
The query in the previous example returns three columns
The
The
The
Additionally, a significant amount of time is devoted to sorting the query results:
Removing the
The time spent in the
In the following output, each plan node is annotated with a
The possible sources are as follows:
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
| 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 ofEXPLAIN 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>. |
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 anEXPLAIN statement for a SELECT query on a table named lineitem.
EXPLAIN output shows the sequence of operations that Firebolt’s engine will use to run the query:
- Read the required columns from the
lineitemtable. - Filter the
lineitemtable by theWHEREconditions. - Remove the columns that are no longer required.
- Perform the aggregation as specified by the
GROUP BYclause. - Sort resulting rows in ascending order by all columns from the
ORDER BYclause. - Bring the columns into the order specified in the
SELECTclause.
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_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
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
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
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.
For TableFuncScan operators that read from tables with primary indexes, additional metrics may be displayed:
granulesshows the output granules (not pruned) out of the total number of granules that were considered.
granules: 52257/52257 indicates that no granules were pruned by the primary index, meaning all 52,257 granules were scanned.
Analyzing the Metrics
In the previous example, thecpu_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:
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:
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:
Example with STATISTICS
The following code example uses theSTATISTICS option to display the logical and physical query plan for the SELECT statement, annotated with estimated statistics from Firebolt’s query optimizer:
[Logical Profile], indicating the source of the estimation for that node.
| Source | Description |
|---|---|
hardcoded | This node received hard-coded defaults because no additional information is available, a scenario that typically occurs only with external tables. |
estimated | This node’s profile was computed from its child nodes’ profiles, based on assumptions about the data that may not always hold true. These assumptions can introduce inaccuracies, which may be further amplified. |
metadata | This node’s profile was constructed from available metadata, ensuring that the information is either precise or highly accurate. |
history | This node’s profile was obtained from a previously recorded run, and is accurate unless data has changed since the recording. |
learned | This node’s profile was predicted using machine learning. |