sr_plan — module for query plans restoring management in QHB

Description

The sr_plan module 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.

It 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 able to form a better plan.

Installation

A module for control of the query plan storage for QHB is available as package qhb-1.5.3-sr-plan.

Add QHB package repository and install the extension package for chosen platform from the download page.

To enable sr_plan, complete the following steps:

  1. Add the library name to the shared_preload_libraries variable in the qhb.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 Compatibility with Other Extensions.

  1. 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;
  1. 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.

  1. 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 qhb.conf file.

    • To activate sr_plan in the current session, use the following command:

SET sr_plan.enable TO true;
  1. 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.

Usage

If you want to save the query plan is necessary to set the variable:

SET sr_plan.write_mode = true;

Now plans for all subsequent queries will be stored in the table sr_plans. Don't forget that all queries will be stored including duplicates.

Make an example query:

SELECT query_hash FROM sr_plans WHERE query_hash=10;

Disable saving the plan for the query:

SET sr_plan.write_mode = false;

Enable it:

UPDATE sr_plans SET enable=true;

After that, the plan for the query will be taken from the sr_plans.

sr_plans table contains query_id columns which could be used to make joins with pg_stat_statements tables and views.

In addition sr_plan allows you to save a parameterized query plan. In this case, we have some constants in the query are not essential. For the parameters we use a special function _p (anyelement) example:

SELECT query_hash FROM sr_plans WHERE query_hash=1000+_p(10);

If we keep the plan for the query and enable it to be used also for the following queries:

SELECT query_hash FROM sr_plans WHERE query_hash=1000+_p(11);
SELECT query_hash FROM sr_plans WHERE query_hash=1000+_p(-5);

It is possible to see saved plans by using show_plan function. It requires knowing query hash which could be fetched from sr_plans table.

Examples:

Show enabled plan for query hash:

SELECT show_plan(1);
                  show_plan                   
----------------------------------------------
 ("Seq Scan on public.explain_test")
 ("  Output: test_attr1, test_attr2")
 ("  Filter: (explain_test.test_attr1 = 10)")
(3 rows)

Get second saved plan by using index parameter (ignores enable attribute):

SELECT show_plan(1, index := 2);
                  show_plan                   
----------------------------------------------
 ("Seq Scan on public.explain_test")
 ("  Output: test_attr1, test_attr2")
 ("  Filter: (explain_test.test_attr1 = 10)")
(3 rows)

Use another output format (supported formats are json, text, xml, yaml):

SELECT show_plan(1, format := 'json');
                      show_plan                       
------------------------------------------------------
 ("[                                                 +
   {                                                 +
     ""Plan"": {                                     +
       ""Node Type"": ""Seq Scan"",                  +
       ""Parallel Aware"": false,                    +
       ""Relation Name"": ""explain_test"",          +
       ""Schema"": ""public"",                       +
       ""Alias"": ""explain_test"",                  +
       ""Output"": [""test_attr1"", ""test_attr2""], +
       ""Filter"": ""(explain_test.test_attr1 = 10)""+
     }                                               +
   }                                                 +
 ]")
(1 row)

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

Registering a Query

There are two ways to register a query:

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

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

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. For information on compatibility of sr_plan with other extensions, see Section Compatibility with Other Extensions.

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 Frozen Plan Types.

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;

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 QHB optimizer is executed. Hint-set plans do not depend on object identifiers and remain valid when tables are recreated, fields are added, etc.

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 qhb.conf file in the specific order:

shared_preload_libraries = 'pg_hint_plan, sr_plan'

shared_preload_libraries = 'sr_plan, pg_stat_statements'

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.

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)

Views

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 3.

Table 3. sr_plan_storage Columns

Column TypeDescription
dbid oidID of the database where the statement is executed
sql_hash bigintInternal query ID
const_hash bigintHash of non-parameterized constants
valid booleanfalse if the plan was invalidated the last time it was used
query_string textQuery registered by the sr_register_query function
paramtypes regtype[]Array with parameter types used in the query
query textInternal representation of the query
plan textInternal representation of the plan
hintstr textSet of hints formed based on the frozen plan

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 4.

Table 4. sr_plan_local_cache Columns

Column TypeDescription
sql_hash bigintInternal query ID
const_hash bigintHash of non-parameterized constants
fs_is_frozen booleantrue if the statement is frozen
fs_is_valid booleantrue if the statement is valid
ps_is_valid booleantrue if the statement should be revalidated
query_string textQuery registered by the sr_register_query function
query textInternal representation of the query
paramtypes regtype[]Array with parameter types used in the query
hintstr textSet of hints formed based on the frozen plan

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 5.

Table 5. sr_captured_queries Columns

Column TypeDescription
dbid oidID of the database where the statement is executed
sql_hash bigintInternal query ID
queryid bigintStandard query ID
sample_string textQuery executed in the automatic query capture mode
query_string textParameterized query
constants textSet of constants in the query
prep_consts textSet of constants used to EXECUTE a prepared statement
hintstr textSet of hints formed based on the plan
explain_plan textPlan shown by the EXPLAIN command

Functions

Only superuser can call the functions listed below.

sr_register_query(query_string text) → record
sr_register_query(query_string text, VARIADIC regtype[]) → 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() → 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) → 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) → 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) → 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) → 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() → 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() → 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) → 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) → 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) → 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() → bigint

Removes all records from the sr_captured_queries view. The function returns the number of removed records.

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.max_consts_len (integer)
Sets the maximum length of a constant set in captured queries. The default value is 100. 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.

See Also

sr_plan documentation on Github

Copyright © KVANTOM LLC, 2019-2026