Postgres Pro Enterprise : Documentation: 16: G.4. sr_plan — save a specific plan of a parameterized query for future usage : Postgres Professional

G.4. sr_plan — save a specific plan of a parameterized query for future usage
Prev	Up	Appendix G. Postgres Pro Modules and Extensions Shipped as Individual Packages	Home	Next

G.4. sr_plan — save a specific plan of a parameterized query for future usage #

G.4.1. Description
G.4.2. Installation
G.4.3. Usage
G.4.4. Frozen Plan Types
G.4.5. Compatibility with Other Extensions
G.4.6. Frozen Query Identification
G.4.7. Automatic Type Casting
G.4.8. Views
G.4.9. Functions
G.4.10. Configuration Parameters

G.4.1. Description #

sr_plan allows the user to save query execution plans and utilize these plans for subsequent executions of the same queries, thereby avoiding repeated optimization of identical queries.

sr_plan looks like Oracle Outline system. It can be used to lock the execution plan. It could help if you do not trust the planner.

G.4.2. Installation #

The sr_plan extension is provided with Postgres Pro Enterprise as a separate pre-built package sr-plan-ent-16 (for the detailed installation instructions, see Chapter 17). To enable sr_plan, complete the following steps:

Add the library name to the shared_preload_libraries variable in the postgresql.conf file:
```
shared_preload_libraries = 'sr_plan'
```
Note that the library names in the shared_preload_libraries variable must be added in the specific order, for information on compatibility of sr_plan with other extensions, see Section G.4.5.
Reload the database server for the changes to take effect.
To verify that the sr_plan library was installed correctly, you can run the following command:
```
SHOW shared_preload_libraries;
```
Create the sr_plan extension using the following query:
```
CREATE EXTENSION sr_plan;
```
It is essential that the library is preloaded during server startup because sr_plan has a shared memory cache that can be initialized only during startup. The sr_plan extension should be created in each database where query management is required.
Enable the sr_plan extension, which is disabled by default, in one of the following ways:
- To enable sr_plan for all backends, set sr_plan.enable = true in the postgresql.conf file.
- To activate sr_plan in the current session, use the following command:
```
SET sr_plan.enable TO true;
```
If you want to transfer sr_plan data from the primary to a standby using physical replication, set the sr_plan.wal_rw parameter to on on both servers. In this case, ensure that the same sr_plan versions are installed on both primary and standby, otherwise correct replication workflow is not guaranteed.

G.4.3. Usage #

sr_plan allows you to freeze plans for future usage. Freezing involves three stages:

Registering the query for which you want to freeze the plan.
Modifying the query execution plan.
Freezing the query execution plan.

G.4.3.1. Registering a Query #

There are two ways to register a query:

Using the sr_register_query() function:
```
SELECT sr_register_query(query_string, parameter_type, ...);
```
Here query_string is your query with $n parameters (same as in PREPARE statement_name AS). You can describe each parameter type with the optional parameter_type argument of the function or choose not to define parameter types explicitly. In the latter case, Postgres Pro attempts to determine each parameter type from the context. This function returns the unique pair of sql_hash and const_hash. Now sr_plan will track executions of queries that fit the saved parameterized query template.
```
-- Create table 'a'
CREATE TABLE a AS (SELECT * FROM generate_series(1,30) AS x);
CREATE INDEX ON a(x);
ANALYZE;

-- Register the query
SELECT sql_hash, const_hash
FROM sr_register_query('SELECT count(*) FROM a
WHERE x = 1 OR (x > $2 AND x < $1) OR x = $1', 'int', 'int')
       sql_hash       | const_hash
----------------------+------------
  5393873830515778388 |  15498345
(1 row)
```

Using the sr_plan.auto_tracking parameter:

-- Set sr_plan.auto_tracking to on
SET sr_plan.auto_tracking = on;

-- Execute EXPLAIN for a non-parameterized query

EXPLAIN SELECT count(*) FROM a WHERE x = 1 OR (x > 11 AND x < 22) OR x = 22;

Custom Scan (SRScan)  (cost=1.60..0.00 rows=1 width=8)
  Plan is: tracked
  SQL hash: 5393873830515778388
  Const hash: 0
  ->  Aggregate  (cost=1.60..1.61 rows=1 width=8)
        ->  Seq Scan on a  (cost=0.00..1.60 rows=2 width=0)
              Filter: ((x = $1) OR ((x > $2) AND (x < $3)) OR (x = $4))

G.4.3.2. Modifying the Query Execution Plan #

A query execution plan can be modified using optimizer variables, pg_hint_plan hints if the extension is enabled, or other extensions that allow changing the query plan, such as aqo. For information on compatibility of sr_plan with other extensions, see Section G.4.5.

G.4.3.3. Freezing the Query Execution Plan #

To freeze a modified query plan, use the sr_plan_freeze function. The optional parameter plan_type can be set to either serialized or hintset. The default value is serialized. For detailed information on types of frozen plans, see Section G.4.4.

G.4.3.4. Usage Example #

The below example illustrates the usage of sr_plan.

-- Register the query
SELECT sql_hash, const_hash
FROM sr_register_query('SELECT count(*) FROM a
WHERE x = 1 OR (x > $2 AND x < $1) OR x = $1', 'int', 'int')
       sql_hash       | const_hash
----------------------+------------
  5393873830515778388 |  15498345
(1 row)

-- Modify the query execution plan
SET enable_seqscan = 'off';

Custom Scan (SRScan) (actual rows=1 loops=1)
  Plan is: tracked
  SQL hash: 5393873830515778388
  Const hash: 15498345
  ->  Aggregate (actual rows=1 loops=1)
        ->  Index Only Scan using a_x_idx2 on a (actual rows=10 loops=1)
              Filter: ((x = 1) OR ((x > $2) AND (x < $1)) OR (x = $1))
              Rows Removed by Filter: 20
              Heap Fetches: 30
(5 rows)

-- Freeze the query execution plan
SELECT sr_plan_freeze();
RESET enable_seqscan;

G.4.4. Frozen Plan Types #

There are two types of frozen plans: serialized plans and hint-set plans.

A serialized plan is a serialized representation of the plan. This plan is transformed into an executable plan upon the first match of the corresponding frozen query. The serialized plan remains valid as long as the query metadata (table structures, indexes, etc.) remain unchanged. For example, if a table present in the frozen plan is recreated, the frozen plan becomes invalid and is ignored. The serialized plan is only valid within the current database and cannot be copied to another, as it depends on OIDs. For this reason, using a serialized plan for temporary tables is impractical.
A hintset plan is a set of hints that are formed based on the execution plan at the time of freezing. The set of hints consists of optimizer environment variables differing from default values, join types, join orders, and data access methods. These hints correspond to those supported by the pg_hint_plan extension. To use hint-set plans, pg_hint_plan must be enabled. The set of hints is passed to the pg_hint_plan planner upon the first match of the corresponding frozen query, and pg_hint_plan generates the executable plan. If the pg_hint_plan extension is not active, the hints are ignored, and the plan generated by the Postgres Pro optimizer is executed. Hint-set plans do not depend on object identifiers and remain valid when tables are recreated, fields are added, etc.

G.4.5. Compatibility with Other Extensions #

To ensure compatibility of sr_plan with other enabled extensions, specify the library names in the shared_preload_libraries variable in the postgresql.conf file in the specific order:

pg_hint_plan: sr_plan must be loaded after pg_hint_plan.
```
shared_preload_libraries = 'pg_hint_plan, sr_plan'
```

aqo: sr_plan must be loaded before aqo.

shared_preload_libraries = 'sr_plan, aqo'

pgpro_stats: sr_plan must be loaded after pgpro_stats.

shared_preload_libraries = 'pgpro_stats, sr_plan'

G.4.6. Frozen Query Identification #

A frozen query in the current database is identified by a combination of sql_hash and const_hash.

sql_hash is a hash generated based on the parse tree, ignoring parameters and constants. Field and table aliases are not ignored. Therefore, the same query with different aliases will have different sql_hash values.

const_hash is a hash generated based on all constants involved in the query. Constants with the same value but different types, such as 1 and '1', will produce different hash values.

G.4.7. Automatic Type Casting #

sr_plan automatically attempts to cast the types of constants involved in the query to match the parameter types of the frozen query. If type casting is not possible, the frozen plan is ignored.

SELECT sql_hash, const_hash
FROM sr_register_query('SELECT count(*) FROM a
WHERE x = $1', 'int');

-- Type casting is possible
EXPLAIN SELECT count(*) FROM a WHERE x = '1';
                     QUERY PLAN
-------------------------------------------------------------
Custom Scan (SRScan)  (cost=1.38..0.00 rows=1 width=8)
  Plan is: tracked
  SQL hash: -5166001356546372387
  Const hash: 0
  ->  Aggregate  (cost=1.38..1.39 rows=1 width=8)
        ->  Seq Scan on a  (cost=0.00..1.38 rows=1 width=0)
              Filter: (x = $1)

-- Type casting is possible
EXPLAIN SELECT count(*) FROM a WHERE x = 1::bigint;
                     QUERY PLAN
-------------------------------------------------------------
Custom Scan (SRScan)  (cost=1.38..0.00 rows=1 width=8)
  Plan is: tracked
  SQL hash: -5166001356546372387
  Const hash: 0
  ->  Aggregate  (cost=1.38..1.39 rows=1 width=8)
        ->  Seq Scan on a  (cost=0.00..1.38 rows=1 width=0)
              Filter: (x = $1)

-- Type casting is impossible
EXPLAIN SELECT count(*) FROM a WHERE x = 1111111111111;
                  QUERY PLAN
-------------------------------------------------------
 Aggregate  (cost=1.38..1.39 rows=1 width=8)
   ->  Seq Scan on a  (cost=0.00..1.38 rows=1 width=0)
         Filter: (x = '1111111111111'::bigint)

G.4.8. Views #

G.4.8.1. The `sr_plan_storage` View #

The sr_plan_storage view provides detailed information about all frozen statements. The columns of the view are shown in Table G.84.

Table G.84. sr_plan_storage Columns

Name	Type	Description
`dbid`	`oid`	ID of the database where the statement is executed
`sql_hash`	`bigint`	Internal query ID
`const_hash`	`bigint`	Hash of non-parameterized constants
`valid`	`boolean`	`FALSE` if the plan was invalidated the last time it was used
`query_string`	`text`	Query registered by the `sr_register_query` function
`paramtypes`	`regtype[]`	Array with parameter types used in the query
`query`	`text`	Internal representation of the query
`plan`	`text`	Internal representation of the plan
`hintstr`	`text`	Set of hints formed based on the frozen plan

G.4.8.2. The `sr_plan_local_cache` View #

The sr_plan_local_cache view provides detailed information about registered and frozen statements in the local cache. The columns of the view are shown in Table G.85.

Table G.85. sr_plan_local_cache Columns

Name	Type	Description
`sql_hash`	`bigint`	Internal query ID
`const_hash`	`bigint`	Hash of non-parameterized constants
`fs_is_frozen`	`boolean`	`TRUE` if the statement is frozen
`fs_is_valid`	`boolean`	`TRUE` if the statement is valid
`ps_is_valid`	`boolean`	`TRUE` if the statement should be revalidated
`query_string`	`text`	Query registered by the `sr_register_query` function
`query`	`text`	Internal representation of the query
`paramtypes`	`regtype[]`	Array with parameter types used in the query
`hintstr`	`text`	Set of hints formed based on the frozen plan

G.4.8.3. The `sr_captured_queries` View #

The sr_captured_queries view provides detailed information about all queries captured in sessions. The columns of the view are shown in Table G.86.

Table G.86. sr_captured_queries Columns

Name	Type	Description
`dbid`	`oid`	ID of the database where the statement is executed
`sql_hash`	`bigint`	Internal query ID
`queryid`	`bigint`	Standard query ID
`sample_string`	`text`	Query executed in the automatic query capture mode
`query_string`	`text`	Parameterized query
`constants`	`text`	Set of constants in the query
`prep_consts`	`text`	Set of constants used to `EXECUTE` a prepared statement
`hintstr`	`text`	Set of hints formed based on the plan
`explain_plan`	`text`	Plan shown by the `EXPLAIN` command

G.4.9. Functions #

Only superuser can call the functions listed below.

sr_register_query(query_string text) returns record sr_register_query(query_string text, VARIADIC regtype[]) returns record #: Saves the query described in the query_string in the local cache and returns the unique pair of sql_hash and const_hash.
sr_unregister_query() returns bool #: Removes the query that was registered but not frozen from the local cache. Returns true if there are no errors.
sr_plan_freeze(plan_type text) returns bool #: Freezes the last used plan for the statement. The allowed values of the plan_type optional argument are serialized and hintset. The serialized value means that the query plan based on the serialized representation is used. With hintset, sr_plan uses the query plan based on the set of hints, which is formed at the stage of registered query execution. If the plan_type argument is omitted, the serialized query plan is used by default. Returns true if there are no errors.
sr_plan_unfreeze(sql_hash bigint, const_hash bigint) returns bool #: Removes the plan only from the storage and keeps the query registered in the local cache. Returns true if there are no errors.
sr_plan_remove(sql_hash bigint, const_hash bigint) returns bool #: Removes the frozen statement with the specified sql_hash and const_hash. Operates as sr_plan_unfreeze and sr_unregister_query called sequentially. Returns true if there are no errors.
sr_plan_reset(dbid oid) returns bigint #: Removes all records in the sr_plan storage for the specified database. Omit dbid to remove the data collected by sr_plan for the current database. Set dbid to NULL to reset data for all databases.
sr_reload_frozen_plancache() returns bool #: Drops all frozen plans and reloads them from the storage. It also drops statements that have been registered but not frozen.
sr_plan_fs_counter() returns table #: Returns the number of times each frozen statement was used and the ID of the database where the statement was registered and used.
sr_show_registered_query(sql_hash bigint, const_hash bigint) returns table #: Returns the registered query with the specified sql_hash and const_hash even if it is not frozen, for debugging purposes only. This works if the query is registered in the current backend or frozen in the current database.
sr_set_plan_type(sql_hash bigint, const_hash bigint, plan_type text) returns bool #: Sets the type of the query plan for the frozen statement. The allowed values of the plan_type argument are serialized and hintset. To be able to use the query plan of the hintset type, the pg_hint_plan module must be loaded. Returns true if the plan type has been changed successfully.
sr_plan_hintset_update(sql_hash bigint, const_hash bigint, hintset text) returns bool #: Allows to change the generated hint set with the set of custom hints. Custom hint-set string should not be enclosed in the special form of comment, as in pg_hint_plan, i.e. it should not start with /*+ and end with */. Returns true if the hint-set plan was changed successfully.
sr_captured_clean() returns bigint #: Removes all records from the sr_captured_queries view. The function returns the number of removed records.

G.4.10. Configuration Parameters #

sr_plan.enable (boolean) #: Enables sr_plan to use frozen plans. The default value is off. Only superusers can change this setting.
sr_plan.fs_ctr_max (integer) #: Sets the maximum number of frozen statements returned by the sr_plan_fs_counter() function. The default value is 5000. This parameter can only be set at server start.
sr_plan.max_items (integer) #: Sets the maximum number of entries sr_plan can operate with. The default value is 100. This parameter can only be set at server start.
sr_plan.auto_tracking (boolean) #: Enables sr_plan to normalize and register queries executed using the EXPLAIN command automatically. The default value is off. Only superusers can change this setting.
sr_plan.max_local_cache_size (integer) #: Sets the maximum size of local cache, in kB. The default value is zero, which means no limit. Only superusers can change this setting.
sr_plan.wal_rw (boolean) #: Enables physical replication of sr_plan data. When set to off on the primary, no data is transferred from it to a standby. When set to off on a standby, any data transferred from the primary is ignored. The default value is off.This parameter can only be set at server start.
sr_plan.auto_capturing (boolean) #: Enables the automatic query capture in sr_plan. Setting this configuration parameter to on allows you to see the queries with constants in the text form as well as parameterized queries in the sr_captured_queries view. Information about executed queries is stored until the server restart. The default value is off. Only superusers can change this setting.
sr_plan.max_captured_items (integer) #: Sets the maximum number of queries sr_plan can capture. The default value is 1000. This parameter can only be set at server start.
sr_plan.sandbox (boolean) #: Enables reserving a separate area in shared memory to be used by a primary or standby node, which allows testing and analyzing queries with the existing data set without affecting the node operation. If set to on on the standby, sr_plan freezes plans only on this node and stores them in the “sandbox”, an alternative plan storage. If enabled on the primary, the extension uses the separate shared memory area that is not replicated to the standby. Changing the parameter value resets the sr_plan cache. The default value is off. Only superusers can change this setting.

Prev	Up	Next
G.3. pgpro_stats — a means for tracking planning and execution statistics of all SQL statements executed by a server	Home	Appendix H. Third-Party Modules and Extensions Shipped as Individual Packages

epub pdf