EXPLAIN PLAN

Updated on 2024-05-07 GMT+08:00

View PDF

Function

EXPLAIN PLAN saves information about an execution plan into the PLAN_TABLE table. Different from the EXPLAIN statement, EXPLAIN PLAN only saves plan information; it does not print information on the screen.

Syntax

    
       EXPLAIN PLAN
[ SET STATEMENT_ID = string ]
FOR statement ;

Parameter Description

PLAN: saves plan information into PLAN_TABLE. If information is stored successfully, "EXPLAIN SUCCESS" is returned.
STATEMENT_ID: tags each query. The tag information will be stored in PLAN_TABLE.
NOTE:

If SET STATEMENT_ID is not specified when the EXPLAIN PLAN statement is executed, STATEMENT_ID is left 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 lifecycle. Sessions are isolated from users, and therefore users can only view the data of 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:

Run the EXPLAIN PLAN statement.

NOTE:

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.

      
       
         
         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;

        

      
     

Query PLAN_TABLE.
```
SELECT * FROM PLAN_TABLE;
```
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.

     
          explain plan set statement_id = 'test remote query' for
  select
  current_user
  from 
  customer;

Query PLAN_TABLE.

     
        SELECT * FROM PLAN_TABLE;

Click to enlarge

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.

     
        set enable_stream_recursive = off;

Run the EXPLAIN PLAN statement.

     
        explain plan set statement_id = 'cte can not be push down'
for
with recursive rq as
(
  select id, name from  chinamap where id = 11
  union all
  select origin.id, rq.name || ' > ' || origin.name
  from rq join chinamap origin on origin.pid = rq.id
)
select id, name from rq order by 1;