Updated on 2025-09-18 GMT+08:00

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

ANALYZE | ANALYSE