Plan Management Functions

pgxc_bind_plan(sql_hash text, outline_name text, apply_skew_hint boolean DEFAULT false, status text DEFAULT 'approved'::text)

Description: Associates the outline named outline_name with the SQL statement identified by sql_hash. Specifically, it binds the outline, which is identified by the primary keys outline_name and sql_hash in the SQL_OUTLINE system catalog, to the corresponding SQL statement.

• The parameter apply_skew_hint indicates whether to apply the skew hint in the outline. The default value is false, indicating that the skew hint in the outline is not applied.
• The parameter status indicates the binding status. This parameter is supported only by clusters of version 9.1.1.200 or later.

  The value can be preferred, approved, unapproved, or rejected. Plans are selected based on the status priority.

Only the database administrator or users with the gs_role_sql_management role permission can execute this function.

Return type: Boolean

Example:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

SELECT pgxc_bind_plan(sql_hash, outline_name) FROM outlines limit 1;
 pgxc_bind_plan
----------------
 t
(1 row)

SELECT pgxc_bind_plan('sql_be2995e824abb8b712b69fca4825b532', 'outline_e5214c8e3901da4d7d34166cb9cb84ee');
 pgxc_bind_plan
----------------
 t
(1 row)

SELECT pgxc_bind_plan(sql_hash, outline_name, true) FROM outlines WHERE outline ILIKE '%skew%' limit 1;
 pgxc_bind_plan
----------------
 t
(1 row)

SELECT pgxc_bind_plan('sql_be2995e824abb8b712b69fca4825b532', 'outline_e5214c8e3901da4d7d34166cb9cb84ee', true, 'preferred');
 pgxc_bind_plan
----------------
 t
(1 row)

pgxc_unbind_plan(sql_hash text, outline_name text DEFAULT NULL::text)

Description: Unbinds the plan from the statement corresponding to outline.

This parameter is supported only by clusters of version 9.1.1.200 or later.

• If outline_name is not specified, all outlines bound to the statement corresponding to sql_hash are unbound.
• If outline_name is specified, only the outline bound to the statement corresponding to sql_hash is unbound.

Only the database administrator or users with the gs_role_sql_management role are authorized to execute this function.

Return type: Boolean

Example:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17

SELECT pgxc_unbind_plan(sql_hash) FROM sql_plan_baseline;
 pgxc_unbind_plan 
------------------
 t
(1 row)

SELECT pgxc_unbind_plan('sql_be2995e824abb8b712b69fca4825b532');
 pgxc_unbind_plan
------------------
 t
(1 row)

SELECT pgxc_unbind_plan('sql_be2995e824abb8b712b69fca4825b532', 'outline_e5214c8e3901da4d7d34166cb9cb84ee');
 pgxc_unbind_plan
------------------
 t
(1 row)

pgxc_clean_plan(clean_period integer)

Description: Runs a manual cleanup task to remove outdated plans beyond the value of clean_period (unit: days). This function is supported only by clusters of version 9.1.1.200 or later. The cleanup removes:

• Unapproved baselines in pg_plan_baseline older than clean_period.
• Unbound outlines (that is, outlines that are not bound in pg_plan_baseline) in dbms_om.sql_outline older than clean_period.

Only the database administrator or users with the gs_role_sql_management role permission can execute this function.

Return type: Boolean

The plan management's background thread handles the plan cleanup task. It begins the clearing process only after completing other tasks, such as saving outlines. This can cause delays in the clearing operation.

Example:

Clear the bound outlines whose last_execute_time is two days ago and status is unapproved in pg_plan_baseline, and clear the unbound outlines whose create_time is two days ago in dbms_om.sql_outline.

1
2
3
4
5

SELECT pgxc_clean_plan(2);
 pgxc_unbind_plan 
------------------
 t
(1 row)

pgxc_create_auto_clean_plan_task(clean_period integer, clean_time timestamp without time zone, clean_interval interval)

Description: Creates an automatic plan cleanup task. The task regularly removes outdated pg_plan_baseline and dbms_om.sql_outline data based on the clean_period you set. You can define the first execution time (clean_time) and the interval (clean_interval). For details, see pgxc_clean_plan(clean_period integer). This parameter is supported only by clusters of version 9.1.1.200 or later.

Only the database administrator or users with the gs_role_sql_management role permission can execute this function.

Parameters:

• clean_period: specifies the time range for the task to be cleared, in days.
• clean_time: specifies the first execution time of the task.
• clean_interval: specifies the interval between two task executions.

Return type: Boolean

• Like pgxc_clean_plan(), the automatic plan clearing process might face delays.
• Each automatic plan clearing task works only within the database where its creation command was run. For cleaning plans across several databases, establish separate tasks by connecting to each one individually.
• Creating a second automated plan cleanup task in the same database triggers an error. Adjust the execution time, interval or range of an existing task using pgxc_alter_auto_clean_plan_task().

Example:

Set up an automated plan cleanup task that starts at 00:00:00, November 1, 2025. It will run daily and remove data older than 32 days.

1
2
3
4
5

SELECT pgxc_create_auto_clean_plan_task(32, '2025-11-1 00:00:00', '1 day');
 pgxc_create_auto_clean_plan_task
----------------------------------
 t
(1 row)

pgxc_alter_auto_clean_plan_task(clean_period integer, clean_time timestamp without time zone, clean_interval interval)

Description: Modifies the parameter settings of the automatic plan cleanup task. You only need to input updated values for the parameters you want to change and leave other fields as NULL to keep their current settings. Only the database administrator or users with the gs_role_sql_management role permission can execute this function.

This parameter is supported only by clusters of version 9.1.1.200 or later.

Return type: Boolean

Example:

Do not modify the parameters of the automatic plan cleanup task.

1
2
3
4
5
6
7

SELECT pgxc_alter_auto_clean_plan_task(NULL, NULL, NULL);
WARNING:  Nothing need to change
CONTEXT:  referenced column: pgxc_alter_auto_clean_plan_task
 pgxc_alter_auto_clean_plan_task
---------------------------------
 t
(1 row)

Set the automatic plan cleanup task's next run date to December 1, 2025, with a daily plus one-second interval.

1
2
3
4
5

SELECT pgxc_alter_auto_clean_plan_task(NULL, '2025-12-01', '1 day 1 second');
 pgxc_alter_auto_clean_plan_task
---------------------------------
 t
(1 row)

pgxc_drop_auto_clean_plan_task()

Description: Deletes automatic plan cleanup tasks that are no longer used. This parameter is supported only by clusters of version 9.1.1.200 or later.

Only the database administrator or users with the gs_role_sql_management role permission can execute this function.

Return type: Boolean

Example:

1
2
3
4
5

SELECT pgxc_drop_auto_clean_plan_task();
 pgxc_drop_auto_clean_plan_task
--------------------------------
 t
(1 row)

drop_outline(sql_hash text, outline_name text)

Description: This function deletes a specified outline from the dbms_om.sql_outline system catalog. Users not associated with an outline can be removed. This action is restricted to the database administrator or users endowed with the gs_role_sql_management role.

Return type: Boolean

Example:

 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
26
27
28
29

SELECT drop_outline(sql_hash, outline_name) FROM outlines;
WARNING:  The outline(specified by sql_hash[sql_3d937542c0d24c9c995f31cba327fa56] and outline_name[outline_413ac0e566ff20be320f5ef0774bf1eb]) is bind
CONTEXT:  referenced column: drop_outline
 drop_outline
--------------
 t
 t
 t
 t
 t
 t
 t
 t
 t
 t
 t
 f
 t
 t
 t
 t
 t
 t
 t
 t
 t
 t
 t
(23 rows)

pgxc_reset_planmgmt_hashtable()

Description: Releases the memory used by plan management to cache outlines on all CNs. Only the database administrator or users with the gs_role_sql_management role are authorized to execute this function.

Return type: Boolean

Example:

Query the memory usage of the outline saved by the plan management module on each CN.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

SELECT pg_catalog.pgxc_node_str(), contextname, level, parent, pg_size_pretty(totalsize) totalsize, pg_size_pretty(freesize) freesize, pg_size_pretty(usedsize) usedsize FROM PG_SHARED_MEMORY_DETAIL WHERE contextname ilike 'plan%' ORDER BY contextname;
 pgxc_node_str |        contextname         | level |     parent      | totalsize | freesize |  usedsize
---------------+----------------------------+-------+-----------------+-----------+----------+------------
 coordinator1  | plan management hash table |     2 | PlanmgmtContext | 80 KB     | 45 KB    | 35 KB
 coordinator1  | PlanmgmtContext            |     1 | ProcessMemory   | 96 KB     | 94 KB    | 2432 bytes
(2 rows)

SELECT pgxc_reset_planmgmt_hashtable();
 pgxc_reset_planmgmt_hashtable 
-------------------------------
 t
(1 row)

Once you've executed the pgxc_reset_planmgmt_hashtable() function, use this command to check the memory usage of the plan management module for storing outlines on each CN.

1
2
3
4
5
6

SELECT pg_catalog.pgxc_node_str(), contextname, level, parent, pg_size_pretty(totalsize) totalsize, pg_size_pretty(freesize) freesize, pg_size_pretty(usedsize) usedsize FROM PG_SHARED_MEMORY_DETAIL WHERE contextname ilike 'plan%' ORDER BY contextname;
 pgxc_node_str |        contextname         | level |     parent      | totalsize | freesize | usedsize
---------------+----------------------------+-------+-----------------+-----------+----------+----------
 coordinator1  | plan management hash table |     2 | PlanmgmtContext | 56 KB     | 33 KB    | 23 KB
 coordinator1  | PlanmgmtContext            |     1 | ProcessMemory   | 0 bytes   | 0 bytes  | 0 bytes
(2 rows)