Help Center > > Developer Guide> SQL Reference> SQL Syntax> EXPLAIN PLAN

EXPLAIN PLAN

Updated at: Jul 15, 2020 GMT+08:00

Function

You can run the EXPLAIN PLAN statement to save the information about an execution plan to the PLAN_TABLE table. Different from the EXPLAIN statement, EXPLAIN PLAN only stores plan information and does not print it on the screen.

Syntax

1
2
3
EXPLAIN PLAN
[ SET STATEMENT_ID = string ]
FOR statement ;

Parameter Description

  • PLAN

    Stores plan information in PLAN_TABLE. If the storing is successful, EXPLAIN SUCCESS is returned.

  • STATEMENT_ID
    Tags a query. The tag information will be stored in PLAN_TABLE.

    If the EXPLAIN PLAN statement does not contain SET STATEMENT_ID, the value of STATEMENT_ID is empty by default. In addition, the value of STATEMENT_ID cannot exceed 30 bytes. Otherwise, an error will be reported.

Precautions

  • EXPLAIN PLAN cannot be executed on DNs.
  • Plan information cannot be collected for SQL statements that failed to be executed.
  • Data in PLAN_TABLE is in a session-level life cycle. Sessions are isolated from users and thereby users can view data of only the current session and current user.
  • PLAN_TABLE cannot be joined with GDS foreign tables.
  • For a query that cannot be pushed down, object information cannot be collected and only such information as REMOTE_QUERY and CTE can be collected. For details, see Example 2.

Example 1

You can perform the following steps to collect execution plans of SQL statements by running EXPLAIN PLAN:

  1. Run the EXPLAN PLAN statement.

    After the EXPLAIN PLAN statement is executed, plan information is automatically stored in PLAN_TABLE. INSERT, UPDATE, and ANALYZE cannot be performed on PLAN_TABLE.

    For details about PLAN_TABLE, see PLAN_TABLE.

     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    explain plan set statement_id='TPCH-Q4' for
    select
    o_orderpriority,
    count(*) as order_count
    from
    orders
    where
    o_orderdate >= '1993-07-01'::date
    and o_orderdate < '1993-07-01'::date + interval '3 month'
    and exists (
    select
    *
    from
    lineitem
    where
    l_orderkey = o_orderkey
    and l_commitdate < l_receiptdate
    )
    group by
    o_orderpriority
    order by
    o_orderpriority;
    

  2. Query PLAN_TABLE.

    SELECT * FROM PLAN_TABLE;

  3. Delete data from PLAN_TABLE.

    1
    DELETE FROM PLAN_TABLE WHERE xxx;
    

Example 2

For a query that cannot be pushed down, only such information as REMOTE_QUERY and CTE can be collected from PLAN_TABLE after EXPLAIN PLAN is executed.

Scenario 1: The optimizer generates a plan for pushing down statements. In this case, only REMOTE_QUERY can be collected.
1
2
3
4
5
  explain plan set statement_id = 'test remote query' for
  select
  current_user
  from 
  customer;
Query PLAN_TABLE.
1
SELECT * FROM PLAN_TABLE;

Scenario 2: For a query with WITH RECURSIVE that cannot be pushed down, only CTE can be collected.

Disable enable_stream_recursive so that the query cannot be pushed down.
1
set enable_stream_recursive = off;
Run the EXPLAIN PLAN SQL statement.
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
explain plan set statement_id = 'cte can not be push down'
for
with recursive rq as
(
  select id, name from t where id = 11
  union all
  select origin.id, rq.name || ' > ' || origin.name
  from rq join t origin on origin.pid = rq.id
)
select id, name from rq order by 1;
Query PLAN_TABLE.
1
SELECT * FROM PLAN_TABLE;

Did you find this page helpful?

Submit successfully!

Thank you for your feedback. Your feedback helps make our documentation better.

Failed to submit the feedback. Please try again later.

Which of the following issues have you encountered?







Please complete at least one feedback item.

Content most length 200 character

Content is empty.

OK Cancel