EXPLAIN
Function
Shows the execution plan of a SQL statement.
The execution plan shows the scan methods used for the tables referenced by the SQL statement. If multiple tables are referenced, the execution plan also displays the JOIN algorithms employed.
The most critical part of the execution plan is the estimated execution cost of the statement, which represents the planner's estimation of the time required to execute the statement.
If the ANALYZE option is specified, the statement will be executed, and statistical data based on the actual runtime will be displayed, including the total time cost (in milliseconds) and the actual number of rows returned within each plan node. This is useful for assessing whether the planner's estimates align with reality.
Precautions
When the ANALYZE option is specified, the statement will be executed.
Syntax
- Display the execution plan of a SQL statement, supporting multiple options with no specific order required:
1
EXPLAIN [ ( option [, ...] ) ] statement;
Where the syntax for the option clause is:
1 2 3 4 5 6 7 8 9 10 11 12
ANALYZE [ boolean ] | ANALYSE [ boolean ] | VERBOSE [ boolean ] | COSTS [ boolean ] | CPU [ boolean ] | DETAIL [ boolean ] | NODES [ boolean ] | NUM_NODES [ boolean ] | BUFFERS [ boolean ] | TIMING [ boolean ] | PLAN [ boolean ] | FORMAT { TEXT | XML | JSON | YAML }
- Display the execution plan of a SQL statement, providing options in order:
1
EXPLAIN { [ { ANALYZE | ANALYSE } ] [ VERBOSE ] | PERFORMANCE } statement;
- Display the information necessary to reproduce the execution plan of a SQL statement, typically used for troubleshooting. The STATS option must be used independently:
1
EXPLAIN ( STATS [ boolean ] ) statement;
- Display detailed timing information for DDL statement execution steps (only supported in clusters of version 9.1.0 or later).
1
EXPLAIN PERFORMANCE statement;
Parameter Description
- statement
Specifies the SQL statement to analyze.
- ANALYZE boolean | ANALYSE boolean
Displays the actual runtime and other statistical data.
Range:
- TRUE (default): Displays the actual runtime and other statistical data.
- FALSE: Does not display.
- VERBOSE boolean
Displays additional information about the plan.
Range:
- TRUE (default): Displays additional information.
- FALSE: Does not display.
- COSTS boolean
Includes the estimated total cost for each plan node, along with the estimated number of rows and the width of each row.
Range:
- TRUE (default): Displays the estimated total cost and width.
- FALSE: Does not display.
- CPU boolean
Prints CPU usage information.
Range:
- TRUE (default): Displays the CPU usage.
- FALSE: Does not display.
- DETAIL boolean
Prints information on DNs.
Range:
- TRUE (default): Prints information on DNs.
- FALSE: Does not print.
- NODES boolean
Prints information about the node where the query is executed.
Range:
- TRUE (default): Prints information about the execution node.
- FALSE: Does not print.
- NUM_NODES boolean
Prints the number of nodes involved in execution.
Range:
- TRUE (default): Prints the number of DNs.
- FALSE: Does not print.
- BUFFERS boolean
Includes information about buffer usage.
Range:
- TRUE: Displays buffer usage.
- FALSE (default): Does not display.
- TIMING boolean
Includes the actual startup time and the time spent on the output node.
Range:
- TRUE (default): Displays startup time and time spent on the output node.
- FALSE: Does not display.
- PLAN
Whether to store the execution plan in plan_table. When this option is enabled, the execution plan is stored in PLAN_TABLE and not printed to the current screen. Thus, this option cannot be used simultaneously with others when enabled.
Range:
- ON (default): The execution plan is stored in plan_table and is not printed to the current screen. If the plan is successfully stored, EXPLAIN SUCCESS is returned.
- OFF: The execution plan is not stored but is printed to the current screen.
- FORMAT
Specifies the output format.
Range: TEXT, XML, JSON, and YAML.
Default Value: TEXT
- PERFORMANCE
When using this option, all relevant information during execution is printed.
- STATS boolean
Prints information required for reproducing the execution plan of a SQL statement, including the object definition, statistics, and configuration parameters. This information is typically used for troubleshooting.
Range:
- TRUE (default): Displays information required for reproducing the execution plan of a SQL statement.
- FALSE: Does not display.
Examples
Set explain_perf_mode to normal.
1
|
SET explain_perf_mode=normal; |
Display the execution plan of a simple table query.
1
|
EXPLAIN SELECT * FROM tpcds.customer_address_p1; |
Helpful Links
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.
For any further questions, feel free to contact us through the chatbot.
Chatbot