EXPLAIN PLAN

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

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.

  Note

  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. Import TPC-H sample data.

2. Run the EXPLAN 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 the PLAN_TABLE system view.

   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;
   

3. Query PLAN_TABLE.

   SELECT * FROM PLAN_TABLE;
   

   

4. Delete data from PLAN_TABLE.

   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.

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;

last updated: 2024-09-07 00:39