SQL plan management-AnalyticDB(AnalyticDB)-阿里云帮助中心

AnalyticDB for PostgreSQL provides SQL plan management through the sr_plan extension. Use it to lock a specific execution plan to a query pattern, preventing plan instability caused by data volume changes or optimizer version updates.

With SQL plan management, you can:

Reduce repeated plan generation time for complex queries
Keep execution plans stable when data changes frequently
Share one registered plan across queries that use the same pattern but different constants

Prerequisites

Before you begin, ensure that you have:

An AnalyticDB for PostgreSQL instance in elastic storage mode running V6.3.9.0 or laterSubmit a ticket
(If upgrading) For instances running V6.3.8.9 or later, to install or upgrade extensions

For instructions on checking or updating your minor engine version, see View the minor engine version and Update the minor engine version.

How it works

AnalyticDB for PostgreSQL uses a cost-based optimizer that selects the minimum-cost execution plan for each SQL statement based on table statistics. While this works well in most cases, plans can shift significantly when:

Data volumes or distributions change sharply
The optimizer is updated

SQL plan management works by storing a SQL pattern (a parameterized version of a query) alongside its corresponding execution plan in the sr_plans table. When a matching query runs, the optimizer uses the stored plan directly instead of generating a new one.

A registered plan is inactive by default (enable = false). To use it, explicitly enable it. This gives you full control over when a pinned plan takes effect.

When to use this feature

Use SQL plan management proactively — before plan instability becomes a problem. Register plans for queries that are performance-sensitive and run frequently, so the optimizer cannot silently switch to a suboptimal plan after a data load or upgrade.

Use it reactively when a query has already regressed due to a plan change. Register and enable the previous known-good plan to restore performance while you investigate the root cause.

Limitations

SQL plan management applies per database. Install the sr_plan extension in each database where you want to use it.
If a table or index referenced by a plan is dropped, the plan is automatically deleted from sr_plans. SQL plan management cannot protect against irreversible structural changes such as dropping an index.

Manage the sr_plan extension

Install the extension

CREATE EXTENSION sr_plan;

Temporarily disable the extension in a session

To stop using stored plans in the current session without removing them:

SET sr_plan.enabled TO off;

Remove the extension

DROP EXTENSION sr_plan;

sr_plans table

Installing the sr_plan extension automatically creates the sr_plans table. Each row stores one SQL plan — a pattern paired with its execution plan.

Column	Data type	Description
`query_hash`	bigint	64-bit hash of the parameterized query. Identifies the SQL pattern.
`query_id`	int8	Reserved.
`plan_hash`	bigint	64-bit hash of the parameterized execution plan. Identifies the execution plan.
`enable`	bool	Whether the plan is active. Default: `false`. Valid values: `true`, `false`.
`query`	varchar	The query statement used to register the plan.
`plan`	bytea	Binary representation of the parameterized execution plan. Use `show_plan()` or `show_plan_node()` to inspect it.
`const_list`	bytea	Binary representation of the parameterized constants. Use `show_const_list()` to inspect it.
`reloids`	oid[]	OIDs of tables referenced by the plan. If a referenced table is dropped, this plan is deleted.
`index_reloids`	oid[]	OIDs of indexes referenced by the plan. If a referenced index is dropped, this plan is deleted.

GUC parameters

The following Grand Unified Configuration (GUC) parameters control SQL plan management behavior.

Parameter	Default	Description
`sr_plan.enabled`	`on`	Enables or disables SQL plan management globally. Valid values: `on`, `off`.
`sr_plan.log_usage`	`none`	Log level for the `sr_plan` extension. Valid values: `warning`, `notice`, `info`, `log`, `debug[1-5]`, `none`. Set to `none` to suppress all logs.
`sr_plan.write_mode`	`off`	When set to `on`, captures and stores the execution plan of every query that runs. Valid values: `on`, `off`.

Important

Do not set sr_plan.write_mode to on in normal operation. It captures the execution plan of every query that runs. To store a specific plan, use the sr_plans_register() function instead.

Register an SQL plan

bool sr_plans_register(<query>, <const_list>, <hint_str>)

Parameters

Parameter	Data type	Description
`query`	text	The query statement to register. Supports two modes — see below.
`const_list`	text	The constants to substitute into the query. Required in prepared mode. Default: `NULL`. Examples: `'11,12'`, `'''text'',1'`, `$$'text',1$$`.
`hint_str`	text	An optional hint string to inject without modifying the query text. Default: `''`. For hint syntax, see Use the hint feature.

Two registration modes

Use _p mode when you want to embed the parameterized constant directly in the query using the _p() function. Set const_list to NULL or ''.

Use prepared mode when you want to represent constants as $1, $2, and so on. Provide the actual constant values in const_list.

Prepared mode uses the Postgres query optimizer, not the Pivotal Optimizer (GPORCA).

_p mode example

SELECT sr_plans_register(
  'SELECT * FROM test_table, test_table2
   WHERE test_table.test_attr1 = test_table2.test_attr1
     AND test_table.test_attr2 = _p(11);',
  NULL,
  '/*+ Set(optimizer_enable_hashjoin off) */'
);

Prepared mode example

SELECT sr_plans_register(
  'SELECT * FROM test_table, test_table2
   WHERE test_table.test_attr1 = test_table2.test_attr1
     AND test_table.test_attr2 = $1;',
  '11',
  '/*+ MergeJoin(test_table test_table2) Leading((test_table test_table2)) */'
);

If the registered query doesn't use the stored plan, the constant's data type may not match. Run an EXPLAIN to check the actual type, then re-register with an explicit cast:

_p mode: rname = _p('ASIA'::bpchar)

Prepared mode: a = $1::int

Important

If sr_plans_register() fails and leaves internal prepared objects unreleased, call clean_sr_plans_register_prepare() to clear them.

Set sr_plan.log_usage to NOTICE before registering plans. The extension then prints a notice each time a stored plan is used, making it easy to confirm the plan is active.

SET sr_plan.log_usage = NOTICE;

Inspect an SQL plan

View the plan in text format

show_plan(<query_hash>, <plan_hash>, <format>)

Parameter	Data type	Description
`query_hash`	bigint	Hash of the parameterized query. Matches `query_hash` in `sr_plans`.
`plan_hash`	bigint	Hash of the execution plan. Matches `plan_hash` in `sr_plans`. Default: `NULL` (returns the first enabled plan).
`format`	text	Output format. Default: `'text'`. Valid values: `'text'`, `'xml'`, `'json'`, `'yaml'`.

Example

SELECT show_plan(-7846983602634689470, 1283098916874729409, 'text');

View the full plan structure

show_plan_node(<query_hash>, <plan_hash>, <pretty>)

Parameter	Data type	Description
`query_hash`	bigint	Hash of the parameterized query. Matches `query_hash` in `sr_plans`.
`plan_hash`	bigint	Hash of the execution plan. Default: `NULL` (returns the first enabled plan).
`pretty`	bool	Whether to apply text wrapping and indentation. Default: `true`.

Example

SELECT show_plan_node(-7846983602634689470, 1283098916874729409, true);

View parameterized constants

show_const_list(<query_hash>, <plan_hash>, <is_list>, <pretty>)

Parameter	Data type	Description
`query_hash`	bigint	Hash of the parameterized query. Matches `query_hash` in `sr_plans`.
`plan_hash`	bigint	Hash of the execution plan. Default: `NULL` (returns the first enabled plan).
`is_list`	bool	Whether to display the full operator list. Default: `true`.
`pretty`	bool	Whether to apply text wrapping and indentation. Default: `true`.

Example

SELECT show_const_list(-7846983602634689470, 1283098916874729409, true, false);

Enable an SQL plan

enable_sr_plans(<query_hash>, <plan_hash>)

Parameter	Data type	Description
`query_hash`	bigint	Hash of the parameterized query. Matches `query_hash` in `sr_plans`.
`plan_hash`	bigint	Hash of the execution plan. Default: `NULL` (enables all plans for the given `query_hash`).

Example

SELECT enable_sr_plans(-7846983602634689470, 1283098916874729409);

Disable an SQL plan

disable_sr_plans(<query_hash>, <plan_hash>)

Parameter	Data type	Description
`query_hash`	bigint	Hash of the parameterized query. Matches `query_hash` in `sr_plans`.
`plan_hash`	bigint	Hash of the execution plan. Default: `NULL` (disables all plans for the given `query_hash`).

Example

SELECT disable_sr_plans(-7846983602634689470, 1283098916874729409);

Delete an SQL plan

delete_sr_plans(<query_hash>, <plan_hash>)

Parameter	Data type	Description
`query_hash`	bigint	Hash of the parameterized query. Matches `query_hash` in `sr_plans`.
`plan_hash`	bigint	Hash of the execution plan. Default: `NULL` (deletes all plans for the given `query_hash`).

Example

SELECT delete_sr_plans(-7846983602634689470, 1283098916874729409);

Modify an SQL plan

update_sr_plans(<query_hash>, <plan_node_string>, <plan_hash>)

Parameter	Data type	Description
`query_hash`	bigint	Hash of the parameterized query. Matches `query_hash` in `sr_plans`.
`plan_node_string`	text	The modified plan structure as a string. Use `show_plan_node()` to get the current plan structure.
`plan_hash`	bigint	Hash of the execution plan. Default: `NULL` (updates all plans for the given `query_hash`).

Important

This function validates that the string can be parsed into an executable plan, but does not verify that the new plan produces correct results for your query. Have a solid understanding of SQL plan data structures before using this function.

End-to-end example

This example walks through the full workflow: setting up test data, registering two SQL plans with different modes and hints, verifying each plan is applied, and cleaning up.

Step 1: Set up test tables

DROP TABLE IF EXISTS test_table;
DROP TABLE IF EXISTS test_table2;

CREATE TABLE test_table(test_attr1 int, test_attr2 int);
CREATE TABLE test_table2(test_attr1 int, test_attr2 int);

INSERT INTO test_table SELECT i, i + 1 FROM generate_series(1, 20000) i;
INSERT INTO test_table2 SELECT i, i + 1 FROM generate_series(1, 20000) i;

CREATE INDEX test_table_index1 ON test_table (test_attr1);
CREATE INDEX test_table_index2 ON test_table (test_attr2);

ANALYZE test_table;
ANALYZE test_table2;

Step 2: Enable plan usage logging

SET sr_plan.log_usage = NOTICE;

The extension prints a notice each time a stored plan is used. This makes it easy to confirm that registration succeeded.

Step 3: Check the default execution plan

EXPLAIN SELECT * FROM test_table, test_table2
WHERE test_table.test_attr1 = test_table2.test_attr1
  AND test_table.test_attr2 = 11;

Expected output:

QUERY PLAN
-----------------------------------------------------------------------------------------------
 Gather Motion 3:1  (slice1; segments: 3)  (cost=0.00..438.43 rows=1 width=16)
   ->  Hash Join  (cost=0.00..438.43 rows=1 width=16)
         Hash Cond: (test_table2.test_attr1 = test_table.test_attr1)
         ->  Seq Scan on test_table2  (cost=0.00..431.14 rows=6667 width=8)
         ->  Hash  (cost=6.00..6.00 rows=1 width=8)
               ->  Index Scan using test_table_index2 on test_table  (cost=0.00..6.00 rows=1 width=8)
                     Index Cond: (test_attr2 = 11)
 Optimizer: Pivotal Optimizer (GPORCA) version 3.86.0
(8 rows)

Step 4: Register two SQL plans

Plan 1 — _p mode with a hint to disable hash join

SELECT sr_plans_register(
  'SELECT * FROM test_table, test_table2
   WHERE test_table.test_attr1 = test_table2.test_attr1
     AND test_table.test_attr2 = _p(11);',
  NULL,
  '/*+ Set(optimizer_enable_hashjoin off) */'
);

Expected output:

NOTICE:  sr_plan: saved plan for /*+ Set(optimizer_enable_hashjoin off) */SELECT * FROM test_table, test_table2 WHERE test_table.test_attr1 = test_table2.test_attr1 and test_table.test_attr2 = _p(11);
CONTEXT:  SQL statement "..."
 sr_plans_register
-------------------
 t
(1 row)

Plan 2 — prepared mode with a merge join hint

Prepared mode uses the Postgres query optimizer, not the Pivotal Optimizer (GPORCA).

SELECT sr_plans_register(
  'SELECT * FROM test_table, test_table2
   WHERE test_table.test_attr1 = test_table2.test_attr1
     AND test_table.test_attr2 = $1;',
  '11',
  '/*+ MergeJoin(test_table test_table2) Leading((test_table test_table2)) */'
);

Expected output:

NOTICE:  sr_plan: saved plan for select * from test_table, test_table2 where test_table.test_attr1 = test_table2.test_attr1 and test_table.test_attr2 = $1;
CONTEXT:  SQL statement "..."
 sr_plans_register
-------------------
 t
(1 row)

Step 5: Inspect stored plans

SELECT * FROM sr_plans;

Run \x in psql first to display results vertically.

Step 6: Verify each plan

Both plans share the same query_hash (-7846983602634689470) because they match the same SQL pattern. They differ by plan_hash.

Verify plan 1 (nested loop, no hash join)

-- Enable plan 1
SELECT enable_sr_plans(-7846983602634689470, 1283098916874729409);

-- Run EXPLAIN with a different constant — the stored plan should apply
EXPLAIN SELECT * FROM test_table, test_table2
WHERE test_table.test_attr1 = test_table2.test_attr1
  AND test_table.test_attr2 = 12;

The NOTICE confirms the cached plan was used:

NOTICE:  sr_plan: cached plan was used for query: explain SELECT * FROM test_table ...
                                             QUERY PLAN
-----------------------------------------------------------------------------------------------------
 Gather Motion 3:1  (slice1; segments: 3)  (cost=0.00..120434.24 rows=8000 width=16)
   ->  Nested Loop  (cost=0.00..120433.77 rows=2667 width=16)
         Join Filter: true
         ->  Seq Scan on test_table2  (cost=0.00..431.14 rows=6667 width=8)
         ->  Index Scan using test_table_index1 on test_table  (cost=0.00..120002.35 rows=1 width=8)
               Index Cond: (test_attr1 = test_table2.test_attr1)
               Filter: (test_attr2 = 12)
 Optimizer: Pivotal Optimizer (GPORCA) version 3.86.0
(8 rows)

-- Disable plan 1
SELECT disable_sr_plans(-7846983602634689470, 1283098916874729409);

Verify plan 2 (merge join)

-- Enable plan 2
SELECT enable_sr_plans(-7846983602634689470, 8380868479165711144);

-- Run EXPLAIN with a different constant
EXPLAIN SELECT * FROM test_table, test_table2
WHERE test_table.test_attr1 = test_table2.test_attr1
  AND test_table.test_attr2 = 13;

The NOTICE confirms the cached plan was used:

NOTICE:  sr_plan: cached plan was used for query: explain SELECT * FROM test_table ...
                                              QUERY PLAN
------------------------------------------------------------------------------------------------------
 Gather Motion 3:1  (slice1; segments: 3)  (cost=1660.96..1760.98 rows=15 width=16)
   ->  Merge Join  (cost=1660.96..1760.98 rows=5 width=16)
         Merge Cond: (test_table.test_attr1 = test_table2.test_attr1)
         ->  Sort  (cost=8.19..8.20 rows=1 width=8)
               Sort Key: test_table.test_attr1
               ->  Index Scan using test_table_index2 on test_table  (cost=0.16..8.18 rows=1 width=8)
                     Index Cond: (test_attr2 = 13)
         ->  Sort  (cost=1652.77..1702.77 rows=6667 width=8)
               Sort Key: test_table2.test_attr1
               ->  Seq Scan on test_table2  (cost=0.00..224.00 rows=6667 width=8)
 Optimizer: Postgres query optimizer
(11 rows)

-- Disable plan 2
SELECT disable_sr_plans(-7846983602634689470, 8380868479165711144);

Step 7: Clean up

SELECT delete_sr_plans(-7846983602634689470, 1283098916874729409);
SELECT delete_sr_plans(-7846983602634689470, 8380868479165711144);

Prerequisites

How it works

Limitations

Manage the sr_plan extension

Install the extension

Temporarily disable the extension in a session

Remove the extension

sr_plans table

GUC parameters

Register an SQL plan

Inspect an SQL plan

View the plan in text format

View the full plan structure

View parameterized constants

Enable an SQL plan

Disable an SQL plan

Delete an SQL plan

Modify an SQL plan

End-to-end example

Step 1: Set up test tables

Step 2: Enable plan usage logging

Step 3: Check the default execution plan

Step 4: Register two SQL plans

Step 5: Inspect stored plans

Step 6: Verify each plan

Step 7: Clean up

What's next