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:

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 $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 queryid and const_hash. Make sure to remember the queryid and const_hash 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(queryid, const_hash);

Here you must use the queryid and const_hash 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.

The example below shows how to freeze the plan of the fully parameterized query using the sr_plan.auto_tracking configuration parameter:

SET sr_plan.auto_tracking = on;

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))

-- Specify the returned 'Tracked plan ID' below:
SELECT sr_plan_freeze(5393873830515778388, 0);

RESET sr_plan.auto_tracking;
 

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.