Updated on 2025-03-13 GMT+08:00

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:

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

  2. Query PLAN_TABLE.

    gaussdb=# SELECT * FROM plan_table;

  3. 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.

Scenario 1: The optimizer generates a plan for pushing down statements. In this case, only REMOTE_QUERY can be collected.
 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;
Query PLAN_TABLE.
1
gaussdb=# SELECT * FROM plan_table;

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

Disable enable_stream_recursive so that the query cannot be pushed down.
1
gaussdb=# SET enable_stream_recursive = off;
Run the EXPLAIN PLAN statement.
 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;