EXPLAIN PLAN

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

1
2
3

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.
  

  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:

1. Run the EXPLAIN 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.

1
2
3
4
5

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

1

SELECT * FROM PLAN_TABLE;

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

1

set enable_stream_recursive = off;

 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  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;

1

SELECT * FROM PLAN_TABLE;