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.
The sr_plan extension is a built-in extension included into Postgres Pro Enterprise. 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 F.76.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.
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.
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
$ parameters
(same as in nPREPARE
).
You can describe each parameter type with the optional
statement_name ASparameter_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 queryid 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 queryid, const_hash
FROM sr_register_query('SELECT count(*) FROM a
WHERE x = 1 OR (x > $2 AND x < $1) OR x = $1', 'int', 'int')
queryid | 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
Query ID: 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))
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 F.76.5.
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 F.76.4.
The below example illustrates the usage of sr_plan.
-- Register the query
SELECT queryid, const_hash
FROM sr_register_query('SELECT count(*) FROM a
WHERE x = 1 OR (x > $2 AND x < $1) OR x = $1', 'int', 'int')
queryid | 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
Query ID: 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;
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.
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'
A frozen query in the current database is identified by a combination
of queryid and const_hash.
queryid 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 queryid 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.
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 queryid, 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
Query ID: -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
Query ID: -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)
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 F.139.
Table F.139. sr_plan_storage Columns
| Name | Type | Description |
|---|---|---|
dbid | oid | ID of the database where the statement is executed |
queryid | 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 |
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 F.140.
Table F.140. sr_plan_local_cache Columns
| Name | Type | Description |
|---|---|---|
queryid | 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 |
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
queryid 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(queryid 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(queryid bigint, const_hash bigint) returns bool
#
Removes the frozen statement with the specified
queryid 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(queryid bigint, const_hash bigint) returns table
#
Returns the registered query with the specified queryid
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(queryid 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(queryid 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_plan.enable (boolean)
#
Enables sr_plan to use frozen plans.
The default value is off.
Only superusers can change this setting.
sr_plan.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.auto_freeze (boolean)
#
Forces to freeze each query processed by the planner.
Use it only for debugging purposes.
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.