EXPLAIN PLAN
Description
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 = name ] FOR statement ; |
Parameters
- EXPLAIN PLAN
PLAN saves plan information into PLAN_TABLE. If information is stored successfully, "EXPLAIN SUCCESS" is returned.
- STATEMENT_ID
STATEMENT_ID tags each query. The tag information will be stored in PLAN_TABLE.
- name
Specifies a query tag.
Value range: a string.
If STATEMENT_ID is not set 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 EXPLAIN PLAN.
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
-- Create tables foo1 and foo2. gaussdb=# CREATE TABLE foo1(f1 int, f2 text, f3 text[]); gaussdb=# CREATE TABLE foo2(f1 int, f2 text, f3 text[]); -- Run EXPLAIN PLAN. gaussdb=# EXPLAIN PLAN SET STATEMENT_ID = 'TPCH-Q4' FOR SELECT f1, count(*) FROM foo1 WHERE f1 > 1 AND f1 < 3 AND EXISTS (SELECT * FROM foo2) GROUP BY f1;
- Query PLAN_TABLE.
gaussdb=# SELECT * FROM plan_table;
- Delete data from PLAN_TABLE.
1 2 3
gaussdb=# DELETE FROM plan_table WHERE STATEMENT_ID = 'TPCH-Q4'; gaussdb=# DROP TABLE foo1; gaussdb=# DROP TABLE foo2;
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 6 7 8 9 10 11 |
-- Create tables pt_t1 and pg_t2. gaussdb=# CREATE TABLE pt_t1(a integer, b int, c int)WITH(autovacuum_enabled = off) DISTRIBUTE hash(c); gaussdb=# CREATE TABLE pt_t1(a int, b int, c int)WITH(autovacuum_enabled = off) DISTRIBUTE hash(c); gaussdb=# EXPLAIN PLAN SET statement_id = 'test remote query' FOR SELECT current_user FROM pt_t1, pt_t2; -- Query PLAN_TABLE. gaussdb=# SELECT * FROM plan_table; -- Drop tables pt_t1 and pg_t2. gaussdb=# DROP TABLE pt_t1; gaussdb=# DROP TABLE pg_t2; |
1
|
gaussdb=# SELECT * FROM plan_table; |
Scenario 2: For a query with WITH RECURSIVE that cannot be pushed down, only CTE can be collected.
1
|
gaussdb=# SET enable_stream_recursive = off; |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
-- Create the chinamap table. gaussdb=# CREATE TABLE chinamap ( id integer, pid integer, name text ) DISTRIBUTE BY hash(id); -- Plan collected by plan_table. gaussdb=# 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; -- Query PLAN_TABLE. gaussdb=# SELECT * FROM plan_table; -- Drop the chinamap table. gaussdb=# DROP TABLE chinamap; |
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