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:
- 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;
- 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.
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; |
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.
For any further questions, feel free to contact us through the chatbot.
Chatbot