Returns the logical execution plan for the specified SQL statement.
An explain plan shows the operations (for example, table scans and joins) that Snowflake would perform to execute the query.
SYSTEM$EXPLAIN_PLAN_JSON , SYSTEM$EXPLAIN_JSON_TO_TEXT , EXPLAIN_JSON
EXPLAIN [ USING { TABULAR | JSON | TEXT } ] <statement>
Copy
Parameters¶statement
This is the SQL statement for which you want the explain plan.
USING output_format
This optional clause specifies the output format. The possible output formats are:
JSON: JSON output is easier to store in a table and query.
TABULAR: tabular output is generally more human-readable than JSON output.
TEXT: formatted text output is generally more human-readable than JSON output.
The default is TABULAR.
The output contains the following information:
Column
Description
step
Most queries contain a single step, but some are executed as multiple distinct steps. This column denotes to which step the operation belongs.
id
Unique identifier assigned to each operation in the query plan.
parentOperators
Array of identifiers for the operation’s parent nodes. In the query profile, a parent is shown above its child with a link connecting the two.
operation
Name of the operation, e.g. Result, Filter, TableScan, Join, etc.
objects
Name of the object referenced by a table scan operation, e.g. table, materialized view, or secure view.
alias
Alias of a referenced object, if the object has been given an alias in the query.
expressions
List of expressions relevant to the current operation such as filters, join predicates, projections, aggregations, etc.
totalPartitions
The total number of micro-partitions in the referenced database object.
assignedPartitions
The number of partitions from the referenced object that are left after compile-time pruning, i.e. the number of partitions that might be scanned by the query.
assignedBytes
The number of bytes contained in the assignedPartitions.
Usage notes¶EXPLAIN compiles the SQL statement, but does not execute it, so EXPLAIN does not require a running warehouse.
The EXPLAIN plan might differ depending on the size of the current warehouse. If you run EXPLAIN outside of a current warehouse, Snowflake constructs the EXPLAIN plan based on the capacity of an XSMALL warehouse.
Although EXPLAIN does not consume any compute credits, the compilation of the query does consume Cloud Service credits, just as other metadata operations do.
To post-process the output of this command, you can:
Use the RESULT_SCAN function, which treats the output as a table that can be queried.
Generate the output in JSON format and insert the JSON-formatted output into a table for analysis later. If you store the output in JSON format, you can use the function SYSTEM$EXPLAIN_JSON_TO_TEXT or EXPLAIN_JSON to convert the JSON to a more human readable format (either tabular or formatted text).
The assignedPartitions and assignedBytes values are upper bound estimates for query execution. Runtime optimizations such as join pruning can reduce the number of partitions and bytes scanned during query execution.
The EXPLAIN plan is the “logical” explain plan. It shows the operations that will be performed, and their logical relationship to each other. The actual execution order of the operations in the plan does not necessarily match the logical order shown by the plan.
If any of the database objects in the EXPLAIN statement are INFORMATION_SCHEMA objects, the statement fails with error EXPLAIN command has insufficient privilege on object <objName>.
This example shows the EXPLAIN output for a simple query against two small tables.
Create the tables:
CREATE TABLE Z1 (ID INTEGER); CREATE TABLE Z2 (ID INTEGER); CREATE TABLE Z3 (ID INTEGER);Copy
Generate the EXPLAIN plan in tabular format for the query:
EXPLAIN USING TABULAR SELECT Z1.ID, Z2.ID FROM Z1, Z2 WHERE Z2.ID = Z1.ID; +------+------+-----------------+-------------+------------------------------+-------+--------------------------+-----------------+--------------------+---------------+ | step | id | parentOperators | operation | objects | alias | expressions | partitionsTotal | partitionsAssigned | bytesAssigned | |------+------+-----------------+-------------+------------------------------+-------+--------------------------+-----------------+--------------------+---------------| | NULL | NULL | NULL | GlobalStats | NULL | NULL | NULL | 2 | 2 | 1024 | | 1 | 0 | NULL | Result | NULL | NULL | Z1.ID, Z2.ID | NULL | NULL | NULL | | 1 | 1 | [0] | InnerJoin | NULL | NULL | joinKey: (Z2.ID = Z1.ID) | NULL | NULL | NULL | | 1 | 2 | [1] | TableScan | TESTDB.TEMPORARY_DOC_TEST.Z2 | NULL | ID | 1 | 1 | 512 | | 1 | 3 | [1] | JoinFilter | NULL | NULL | joinKey: (Z2.ID = Z1.ID) | NULL | NULL | NULL | | 1 | 4 | [3] | TableScan | TESTDB.TEMPORARY_DOC_TEST.Z1 | NULL | ID | 1 | 1 | 512 | +------+------+-----------------+-------------+------------------------------+-------+--------------------------+-----------------+--------------------+---------------+Copy
Generate the EXPLAIN plan for the query as formatted text:
EXPLAIN USING TEXT SELECT Z1.ID, Z2.ID FROM Z1, Z2 WHERE Z2.ID = Z1.ID; +------------------------------------------------------------------------------------------------------------------------------------+ | content | |------------------------------------------------------------------------------------------------------------------------------------| | GlobalStats: | | partitionsTotal=2 | | partitionsAssigned=2 | | bytesAssigned=1024 | | Operations: | | 1:0 ->Result Z1.ID, Z2.ID | | 1:1 ->InnerJoin joinKey: (Z2.ID = Z1.ID) | | 1:2 ->TableScan TESTDB.TEMPORARY_DOC_TEST.Z2 ID {partitionsTotal=1, partitionsAssigned=1, bytesAssigned=512} | | 1:3 ->JoinFilter joinKey: (Z2.ID = Z1.ID) | | 1:4 ->TableScan TESTDB.TEMPORARY_DOC_TEST.Z1 ID {partitionsTotal=1, partitionsAssigned=1, bytesAssigned=512} | | | +------------------------------------------------------------------------------------------------------------------------------------+Copy
Generate the EXPLAIN plan for the query as JSON:
EXPLAIN USING JSON SELECT Z1.ID, Z2.ID FROM Z1, Z2 WHERE Z2.ID = Z1.ID; +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | content | |---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------| | {"GlobalStats":{"partitionsTotal":2,"partitionsAssigned":2,"bytesAssigned":1024},"Operations":[[{"id":0,"operation":"Result","expressions":["Z1.ID","Z2.ID"]},{"id":1,"parentOperators":[0],"operation":"InnerJoin","expressions":["joinKey: (Z2.ID = Z1.ID)"]},{"id":2,"parentOperators":[1],"operation":"TableScan","objects":["TESTDB.TEMPORARY_DOC_TEST.Z2"],"expressions":["ID"],"partitionsAssigned":1,"partitionsTotal":1,"bytesAssigned":512},{"id":3,"parentOperators":[1],"operation":"JoinFilter","expressions":["joinKey: (Z2.ID = Z1.ID)"]},{"id":4,"parentOperators":[3],"operation":"TableScan","objects":["TESTDB.TEMPORARY_DOC_TEST.Z1"],"expressions":["ID"],"partitionsAssigned":1,"partitionsTotal":1,"bytesAssigned":512}]]} | +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+Copy
RetroSearch is an open source project built by @garambo | Open a GitHub Issue
Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo
HTML:
3.2
| Encoding:
UTF-8
| Version:
0.7.4