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 and does not print information on the screen.

Syntax

EXPLAIN PLAN
[ SET STATEMENT_ID = name ]
FOR statement ;

Parameters

• name

  Specifies a query tag.

  Value range: a string.

• 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 the EXPLAIN PLAN statement does not contain SET STATEMENT_ID, 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 a database node.
• 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.

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.

   -- Create tables foo1 and foo2.
   openGauss=# CREATE TABLE foo1(f1 int, f2 text, f3 text[]);
   openGauss=# CREATE TABLE foo2(f1 int, f2 text, f3 text[]);
   
   -- Run EXPLAIN PLAN.
   openGauss=# 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;

2. Query PLAN_TABLE.

   openGauss=# SELECT * FROM plan_table;

3. Delete data from PLAN_TABLE.

   1 2 3

   openGauss=# DELETE FROM plan_table WHERE STATEMENT_ID = 'TPCH-Q4'; openGauss=# DROP TABLE foo1; openGauss=# DROP TABLE foo2;