sr_plan allows the user to save a specific plan of a parameterized query for future usage regardless of how planner parameters may change.
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 or you can form a better plan. Typically, a DBA would play with queries interactively, save their plans, and enable use of saved plans for the queries where predictable response time is essential. Then the application that uses these queries would use saved plans.
The sr_plan extension is a built-in extension included into Postgres Pro Enterprise. To enable sr_plan, complete the following steps:
Modify the postgresql.conf file as follows:
shared_preload_libraries = 'sr_plan' sr_plan.enable = 'true'
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.
In a typical case, you have a poorly-performing query with the bad plan
choice (e.g. nested loop join instead of a hash join caused
by selectivity underestimation), and you have an idea how to fix
this plan (e.g. SET enable_nestloop = 'off').
sr_plan allows you to freeze plans
for future usage regardless of how planner parameters may change.
First, you have to register the query under the control
of sr_plan:
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 query ID srid and the internal
queryid. Make sure to remember the unique
srid until the end of the plan freezing. Now
sr_plan will track executions of queries
that fit the saved parameterized query template. You can use any
techniques to get an acceptable query plan. After that, execute:
SELECT sr_plan_freeze(srid);
Here you must use the ID returned by the sr_register_query
function. Now sr_plan stores the
last used query plan in the file storage, shared memory, and local cache.
The below example illustrates the usage of sr_plan.
Create a table:
CREATE TABLE a AS (SELECT * FROM generate_series(1,30) AS x); CREATE INDEX ON a(x); ANALYZE;
Register the query:
SELECT srid FROM sr_register_query('SELECT count(*)
FROM a
WHERE x = 1 OR (x > $2 AND x < $1) OR x = $1', 'int', 'int');
srid
-------
1
(1 row)
Execute the query with specific parameter values:
SELECT count(*) FROM a WHERE x = 1 OR (x > 11 AND x < 22) OR x = 22;
With EXPLAIN, you can see that this query
is under the control of sr_plan:
EXPLAIN SELECT count(*) FROM a WHERE x = 1 OR (x > 11 AND x < 22) OR x = 22;
Custom Scan (SRScan) (cost=0.00..0.00 rows=0 width=0)
SR_PLAN: frozen plan
-> 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 < $1)) OR (x = $1))
Disable SeqScan and try again:
SET enable_seqscan = 'off';
Custom Scan (SRScan) (cost=0.00..0.00 rows=0 width=0)
SR_PLAN: frozen plan
-> Aggregate (cost=12.89..12.90 rows=1 width=8)
-> Index Only Scan using a_x_idx on a (cost=0.14..12.89 rows=2 width=0)
Filter: ((x = 1) OR ((x > $2) AND (x < $1)) OR (x = $1))
(5 rows)
Freeze the plan. The query will be handled as an index-only scan even if you change the planner parameters:
SELECT sr_plan_freeze(1); RESET enable_seqscan;
sr_plan_storage View
sr_plan_storage view provides detailed information
about all frozen statements. The columns of the view are shown in
Table F.119.
Table F.119. sr_plan_storage Columns
| Name | Type | Description |
|---|---|---|
srid | int | Unique ID of the statement |
dbid | oid | ID of the database where the statement is executed |
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 |
queryid | bigint | Internal query ID |
paramtypes | regtype[] | Array with parameter types used in the query |
query | text | Internal representation of the query |
plan | text | Internal representation of the plan |
sr_register_query(query_string text) returns record
Saves the query described in the query_string
in the local cache and returns the unique srid
and internal queryid.
sr_unregister_query(srid int) 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(srid int) returns bool
Freezes the last used plan for the statement with the specified ID. Returns true if there are no errors.
sr_plan_unfreeze(srid int) 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(srid int) returns bool
Removes the frozen statement with the specified ID. 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(srid int) returns table
Returns the registered query with the specified ID even if it isn't frozen, for debugging purposes only. This works if the query is registered in the current backend or frozen in the current database.
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 1000. This parameter can only be set at server start.
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.
Use explicit cast to make sure that sr_plan finds a frozen plan for your query. For example, the following statements are recognized by sr_plan as different ones:
SELECT * FROM a WHERE x = 1::integer SELECT * FROM a WHERE x = 1::bigint
If sr_plan is not the last hook in
the queue of planner hooks, the standard planner called by
the last hook in the queue would optimize workload
(sometimes greatly). So if the plan is already frozen,
sr_plan would drop such a newly created plan.
To avoid this unnecessary overhead, sr_plan
should be the last in the list of libraries
shared_preload_libraries.