pgpro_multiplan allows you to save query execution plans and utilize these plans for subsequent executions of the same queries, thereby avoiding repeated optimization of identical queries. It can also be used to lock a specific execution plan if a plan chosen by the planner does not suit your needs for some reasons.
pgpro_multiplan works similar to Oracle SQL Plan Management.
The pgpro_multiplan extension is provided with Postgres Pro Enterprise
as a separate pre-built package pgpro-multiplan-ent-15
(for the detailed installation instructions, see Chapter 17).
To enable
pgpro_multiplan, complete the following steps:
Add the library name to the
shared_preload_libraries variable in the
postgresql.conf file:
shared_preload_libraries = 'pgpro_multiplan'
Note that the library names in the
shared_preload_libraries variable must be
added in the specific order, for information on compatibility of
pgpro_multiplan with other extensions, see
Section G.3.8.
Reload the database server for the changes to take effect.
To verify that the pgpro_multiplan library
was installed correctly, you can run the following command:
SHOW shared_preload_libraries;
Create the pgpro_multiplan extension using the following query:
CREATE EXTENSION pgpro_multiplan;
It is essential that the library is preloaded during server startup because pgpro_multiplan has a shared memory cache that can be initialized only during startup. The pgpro_multiplan extension should be created in each database where query management is required.
Enable the pgpro_multiplan extension, which is disabled by default. To do this, specify the required modes in the pgpro_multiplan.mode parameter. For more information, see Supported Modes and Plan Types.
You can enable pgpro_multiplan in one of the following ways:
To activate the extension for all sessions, set the
pgpro_multiplan.mode parameter in the
postgresql.conf file.
To activate the extension in the current session, use the following command:
SET pgpro_multiplan.mode = 'frozen';
If you want to transfer pgpro_multiplan data
from the primary to a standby using physical replication, set
the pgpro_multiplan.wal_rw
parameter to on on both servers. In this case,
ensure that the same pgpro_multiplan versions
are installed on both primary and standby, otherwise correct
replication workflow is not guaranteed.
The pgpro_multiplan extension provides the following types of plans:
Frozen plans
Locked plans that take precedence when executing the corresponding queries. A query can have one frozen plan only.
pgpro_multiplan supports the following types of frozen plans:
Serialized plans
Serialized representations of plans. These plans are converted into executable plans when the corresponding queries are executed for the first time.
Serialized plans remain valid as long as metadata of the corresponding queries remain unchanged (such as table structures, indexes, and so on). For example, if a table referenced in the plan is recreated, the plan becomes invalid and is ignored. Serialized plans are only valid within the current database and cannot be copied to another database as they depend on OIDs. For this reason, using serialized plans for temporary tables is impractical.
Hint-set plans
Sets of hints that are formed based on the corresponding execution plans at the time of freezing. The set of hints includes join types, join orders, data access methods, and optimizer environment variables differing from default values. These hints correspond to those provided by the pg_hint_plan extension. Therefore, to use hint-set plans, the pg_hint_plan extension must be enabled.
When the corresponding frozen query is matched, the hints are passed to pg_hint_plan to generate 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.
Template plans
Plans that are similar to hint-set frozen plans but use a template for table names. If a query does not have a corresponding frozen plan, pgpro_multiplan tries to search the matching template plan for this query.
These plans are also based on sets of hints and require the
pg_hint_plan extension to be enabled. However,
these plans can be applied only to queries with table names that match
the POSIX regular expression specified in the
pgpro_multiplan.wildcards
parameter. The pgpro_multiplan.wildcards value is
frozen along with the corresponding query. If the
pg_hint_plan extension is not active, the
hints are ignored, and the plan generated by the
Postgres Pro optimizer is executed.
Baselines
Sets of allowed plans that can be used to execute queries if there are no corresponding frozen or template plans.
Like hint-set frozen plans, baselines rely on sets of hints and require the pg_hint_plan extension to be enabled. If pg_hint_plan is not active, the hints are ignored, and the plan generated by the Postgres Pro optimizer is executed.
Use the pgpro_multiplan.mode configuration parameter to specify a comma-separated list of enabled modes and plan types. By default, this parameter is set to an empty string, and pgpro_multiplan is disabled.
Plans are identified in the following ways depending on the plan type:
Frozen plans (both serialized and hint-set) are identified by a
combination of sql_hash and const_hash.
sql_hash is computed from the parse tree while ignoring
parameters and constants. Field and table aliases are not ignored.
Therefore, the same queries with different aliases have different
sql_hash values.
const_hash is computed from all constants used
in the query. Constants with the same value but different data types,
such as 1 and '1', produce different
hash values.
Template plans are identified by a combination of sql_hash
and table_hash.
sql_hash is computed from the parse tree while ignoring
table names, parameters, and constants.
table_hash is computed from table names that do not
match the regular expression specified in the
pgpro_multiplan.wildcards
parameter.
const_hash for these plans is always equal to
0.
Baselines are identified by a combination of sql_hash
and plan_hash.
sql_hash is computed in the same way as for frozen
plans.
plan_hash is an internal plan ID.
const_hash for these plans is always equal to
0.
To freeze certain query execution plans for future usage, perform the following steps:
Use the pgpro_multiplan.mode parameter to specify a comma-separated list with types of plans that you want to create. For detailed information about plan types, see Supported Modes and Plan Types.
SET pgpro_multiplan.mode = 'frozen, wildcards, baseline, statistics';
Register the query for which you want to fix the plan in one of the following ways:
Use the pgpro_multiplan_register_query function to register the query manually.
SELECT pgpro_multiplan_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 sql_hash and
const_hash. Now pgpro_multiplan
will track execution 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 pgpro_multiplan_register_query('SELECT count(*) FROM a
WHERE x = 1 OR (x > $2 AND x < $1) OR x = $1', 'int', 'int');
sql_hash | const_hash
-----------------------+-------------
-6037606140259443514 | 2413041345
(1 row)
Note that when registering a prepared statement, numbers and positions of parameters in the parameterized query template must match numbers and positions of parameters in the statement. Otherwise, the template is ignored.
Set the pgpro_multiplan.auto_tracking parameter to
on to register queries executed using the
EXPLAIN and EXPLAIN EXECUTE
commands automatically.
-- Enable pgpro_multiplan.auto_tracking
SET pgpro_multiplan.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 (MultiplanScan) (cost=1.60..0.00 rows=1 width=8)
Plan is: tracked
SQL hash: 5393873830515778388
Const hash: 0
Plan 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))
-- Disable pgpro_multiplan.auto_tracking
SET pgpro_multiplan.auto_tracking = off;
Modify the query execution plan, if necessary. You can do this using configuration parameters, pg_hint_plan hints if this extension is enabled, or other extensions that allow changing the query plan, such as aqo. For information on compatibility of pgpro_multiplan with these extensions, see the Compatibility with Other Extensions section.
Freeze the query execution plan using the
pgpro_multiplan_freeze
function. Set the optional plan_type argument to
serialized, hintset,
template, or baseline depending on
the type of the plan you want to create. If you omit this argument,
serialized is used by default.
SELECT pgpro_multiplan_freeze('serialized');
pgpro_multiplan_freeze
------------------------
t
(1 row)
If you create a hint-set, template, or allowed plan, enable the pg_hint_plan extension. For more information, see Compatibility with Other Extensions.
If you create a template plan, use the pgpro_multiplan.wildcards parameter to specify a POSIX regular expression used to check matching of table names referenced in queries.
All fixed plans then can be accessed in the pgpro_multiplan_storage view.
The example below illustrates how to add a frozen plan.
-- Execute the query and check its plan
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF)
SELECT count(*) FROM a
WHERE x = 1 OR (x > 11 AND x < 22) OR x = 22;
QUERY PLAN
-------------------------------------------------------------------------
Aggregate (actual rows=1 loops=1)
-> Seq Scan on a (actual rows=12 loops=1)
Filter: ((x = 1) OR ((x > 11) AND (x < 22)) OR (x = 22))
Rows Removed by Filter: 18
Planning Time: 0.179 ms
Execution Time: 0.069 ms
(6 rows)
-- Allow the use of frozen plans
SET pgpro_multiplan.mode = 'frozen';
-- Register the query
SELECT sql_hash, const_hash
FROM pgpro_multiplan_register_query('SELECT count(*) FROM a
WHERE x = 1 OR (x > $2 AND x < $1) OR x = $1', 'int', 'int');
sql_hash | const_hash
----------------------+------------
-6037606140259443514 | 2413041345
(1 row)
-- Modify the query execution plan
-- Force index scan by disabling sequential scan
SET enable_seqscan = 'off';
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF)
SELECT count(*) FROM a
WHERE x = 1 OR (x > 11 AND x < 22) OR x = 22;
QUERY PLAN
----------------------------------------------------------------------------
Custom Scan (MultiplanScan) (actual rows=1 loops=1)
Plan is: tracked
SQL hash: -6037606140259443514
Const hash: 2413041345
Plan hash: 0
-> Aggregate (actual rows=1 loops=1)
-> Index Only Scan using a_x_idx on a (actual rows=12 loops=1)
Filter: ((x = 1) OR ((x > $2) AND (x < $1)) OR (x = $1))
Rows Removed by Filter: 18
Heap Fetches: 30
Planning Time: 0.235 ms
Execution Time: 0.099 ms
(12 rows)
-- Restore the seqscan ability
RESET enable_seqscan;
-- Freeze the query execution plan
SELECT pgpro_multiplan_freeze();
pgpro_multiplan_freeze
------------------------
t
(1 row)
-- The frozen plan with indexscan is now used
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF)
SELECT count(*) FROM a
WHERE x = 1 OR (x > 11 AND x < 22) OR x = 22;
QUERY PLAN
----------------------------------------------------------------------------
Custom Scan (MultiplanScan) (actual rows=1 loops=1)
Plan is: frozen, serialized
SQL hash: -6037606140259443514
Const hash: 2413041345
Plan hash: 0
-> Aggregate (actual rows=1 loops=1)
-> Index Only Scan using a_x_idx on a (actual rows=12 loops=1)
Filter: ((x = 1) OR ((x > $2) AND (x < $1)) OR (x = $1))
Rows Removed by Filter: 18
Heap Fetches: 30
Planning Time: 0.063 ms
Execution Time: 0.119 ms
(12 rows)
The example below demonstrates how to create a baseline.
-- Create table 'a'
CREATE TEMP TABLE a AS (SELECT * FROM generate_series(1,1000) AS x);
CREATE INDEX ON a(x);
ANALYZE;
-- Allow the use of baselines
SET pgpro_multiplan.mode = 'baseline';
-- Register the query
SELECT sql_hash sql_hash, const_hash const_hash
FROM pgpro_multiplan_register_query('SELECT count(*) FROM a WHERE x = 1 OR (x > 11 AND x < 22) OR x = 22');
sql_hash | const_hash
-----------------------+-------------
-6037606140259443514 | 2413041345
(1 row)
-- Check the query execution plan
EXPLAIN SELECT count(*) FROM a WHERE x = 1 OR (x > 11 AND x < 22) OR x = 22;
QUERY PLAN
------------------------------------------------------------
Custom Scan (MultiplanScan) (actual rows=1 loops=1)
Plan is: tracked
Hint string: BitmapScan("a" "a_x_idx")
-> Aggregate (actual rows=1 loops=1)
-> Bitmap Heap Scan on a (actual rows=12 loops=1)
(5 rows)
-- Add the query execution plan to the baseline
SELECT pgpro_multiplan_freeze('baseline');
pgpro_multiplan_freeze
------------------------
t
(1 row)
-- Now you can see the added plan using the corresponding view
SELECT * FROM pgpro_multiplan_storage \gx
-[ RECORD 1 ]-+---------------------------------------------------------------------
dbid | 5
sql_hash | -6037606140259443514
const_hash | 0
plan_hash | 487722818968417375
valid | t
cost | 36.785
sample_string | SELECT count(*) FROM a WHERE x = 1 OR (x > 11 AND x < 22) OR x = 22
query_string | SELECT count(*) FROM a WHERE x = $1 OR (x > $2 AND x < $3) OR x = $4
paramtypes |
query | <>
plan_type | baseline
plan | <>
hintstr | BitmapScan("a" "a_x_idx")
wildcards |
-- This plan from the baseline is now applied when executing the corresponding query
SET enable_bitmapscan = FALSE;
EXPLAIN SELECT count(*) FROM a WHERE x = 1 OR (x > 11 AND x < 22) OR x = 22;
QUERY PLAN
------------------------------------------------------------
Custom Scan (MultiplanScan) (actual rows=1 loops=1)
Plan is: baseline
Hint string: BitmapScan("a" "a_x_idx")
-> Aggregate (actual rows=1 loops=1)
-> Bitmap Heap Scan on a (actual rows=12 loops=1)
(5 rows)
RESET enable_bitmapscan;
The example below illustrates how to register a prepared statement using the pgpro_multiplan_register_query function and freeze the statement execution plan.
-- Create table 'a'
CREATE TABLE a AS (SELECT * FROM generate_series(1,30) AS x);
CREATE INDEX ON a(x);
ANALYZE;
-- Create a prepared statement
PREPARE stmt AS SELECT count(*) FROM a
WHERE x = $2 OR x < $1 OR x = 10;
-- Register the statement. Note that numbers and positions of parameters $1 and $2
-- in the parameterized query template match numbers and positions of parameters
-- in the prepared statement
SELECT sql_hash, const_hash
FROM pgpro_multiplan_register_query('SELECT count(*) FROM a
WHERE x = $2 OR x < $1 OR x = $3');
-- Execute the prepared statement with parameters
EXPLAIN (COSTS OFF, TIMING OFF) EXECUTE stmt(10, 1);
QUERY PLAN
----------------------------------------------------------
Custom Scan (MultiplanScan)
Plan is: tracked
SQL hash: -1476198425806211485
Const hash: 0
Plan hash: 0
-> Aggregate
-> Seq Scan on a
Filter: ((x = $2) OR (x < $1) OR (x = $3))
(8 rows)
-- Freeze the statement execution plan
SELECT pgpro_multiplan_freeze();
-- Execute the statement once again. The frozen plan is now used.
EXPLAIN (COSTS OFF, TIMING OFF) EXECUTE stmt(10, 1);
QUERY PLAN
----------------------------------------------------------
Custom Scan (MultiplanScan)
Plan is: frozen, serialized
SQL hash: -1476198425806211485
Const hash: 0
Plan hash: 0
-> Aggregate
-> Seq Scan on a
Filter: ((x = $2) OR (x < $1) OR (x = $3))
(8 rows)
The following example demonstrates how to automatically register an executed prepared statement.
-- Create a prepared statement:
PREPARE stmt AS SELECT count(*) FROM a
WHERE x = $2 OR x < $1 OR x = 10;
-- Enable automatic tracking of executed statements
SET pgpro_multiplan.auto_tracking = on;
-- Execute the prepared statement with parameters
EXPLAIN (COSTS OFF, TIMING OFF) EXECUTE stmt(10, 1);
QUERY PLAN
----------------------------------------------------------
Custom Scan (MultiplanScan)
Plan is: tracked
SQL hash: -1476198425806211485
Const hash: 0
Plan hash: 0
-> Aggregate
-> Seq Scan on a
Filter: ((x = $2) OR (x < $1) OR (x = $3))
(8 rows)
-- Disable automatic tracking
SET pgpro_multiplan.auto_tracking = off;
-- Freeze the statement execution plan
SELECT pgpro_multiplan_freeze();
You can also add plans to a baseline by performing the following steps:
Specify the baseline value in the
pgpro_multiplan.mode parameter.
Enable the pg_hint_plan extension. For more information, see the Compatibility with Other Extensions section.
Set the pgpro_multiplan.auto_capturing parameter to
on to capture all the executed queries. These
captured queries can be accessed in the
pgpro_multiplan_captured_queries
view.
Approve any captured plan using the
pgpro_multiplan_captured_approve
function with the specified dbid, sql_hash,
and plan_hash arguments.
Set the pgpro_multiplan.auto_capturing parameter to
off after completing the capture.
-- Create table 'a'
CREATE TABLE a AS SELECT x, x AS y FROM generate_series(1,1000) x;
CREATE INDEX ON a(x);
CREATE INDEX ON a(y);
ANALYZE;
-- Enable the use of baselines and automatic capture
SET pgpro_multiplan.mode = 'baseline'
SET pgpro_multiplan.auto_capturing = 'on';
-- Execute the query
SELECT count(*) FROM a t1, a t2 WHERE t1.x = t2.x AND t1.y <= 1000 AND t2.y > 900;
count
-------
100
(1 row)
-- Execute it again with different constants to get a different plan
SELECT count(*) FROM a t1, a t2 WHERE t1.x = t2.x AND t1.y <= 10 AND t2.y > 900;
count
-------
0
(1 row)
-- Now you can see the captured plans using the corresponding view
SELECT * FROM pgpro_multiplan_captured_queries \gx
dbid | 5
sql_hash | 6079808577596655075
plan_hash | -487722818968417375
queryid | -8984284243102644350
cost | 36.785
sample_string | SELECT count(*) FROM a t1, a t2 WHERE t1.x = t2.x AND t1.y <= 1000 AND t2.y > 900;
query_string | SELECT count(*) FROM a t1, a t2 WHERE t1.x = t2.x AND t1.y <= $1 AND t2.y > $2;
hint_str | Leading(("t1" "t2" )) HashJoin("t1" "t2") IndexScan("t2" "a_y_idx") SeqScan("t1")
explain_plan | Aggregate (cost=36.77..36.78 rows=1 width=8)
| Output: count(*)
| -> Hash Join (cost=11.28..36.52 rows=100 width=0)
| Hash Cond: (t1.x = t2.x)
| -> Seq Scan on public.a t1 (cost=0.00..20.50 rows=1000 width=4)
| Output: t1.x, t1.y
| Filter: (t1.y <= 1000)
| -> Hash (cost=10.03..10.03 rows=100 width=4)
| Output: t2.x
| Buckets: 1024 Batches: 1 Memory Usage: 12kB
| -> Index Scan using a_y_idx on public.a t2 (cost=0.28..10.03 rows=100 width=4)
| Output: t2.x
| Index Cond: (t2.y > 900)
| Query Identifier: -8984284243102644350
|
-[ RECORD 2 ]-+-----------------------------------------------------------------------------------------------------
dbid | 5
sql_hash | 6079808577596655075
plan_hash | 2719320099967191582
queryid | -8984284243102644350
cost | 18.997500000000002
sample_string | SELECT count(*) FROM a t1, a t2 WHERE t1.x = t2.x AND t1.y <= 10 AND t2.y > 900;
query_string | SELECT count(*) FROM a t1, a t2 WHERE t1.x = t2.x AND t1.y <= $1 AND t2.y > $2;
hint_str | Leading(("t2" "t1" )) HashJoin("t1" "t2") IndexScan("t2" "a_y_idx") IndexScan("t1" "a_y_idx")
explain_plan | Aggregate (cost=18.99..19.00 rows=1 width=8)
| Output: count(*)
| -> Hash Join (cost=8.85..18.98 rows=1 width=0)
| Hash Cond: (t2.x = t1.x)
| -> Index Scan using a_y_idx on public.a t2 (cost=0.28..10.03 rows=100 width=4)
| Output: t2.x, t2.y
| Index Cond: (t2.y > 900)
| -> Hash (cost=8.45..8.45 rows=10 width=4)
| Output: t1.x
| Buckets: 1024 Batches: 1 Memory Usage: 9kB
| -> Index Scan using a_y_idx on public.a t1 (cost=0.28..8.45 rows=10 width=4)
| Output: t1.x
| Index Cond: (t1.y <= 10)
| Query Identifier: -8984284243102644350
|
-- Disable the automatic capturing. This will not affect previously captured plans.
SET pgpro_multiplan.auto_capturing = 'off';
-- Manually approve the plan with index scans
SELECT pgpro_multiplan_captured_approve(5, 6079808577596655075, 2719320099967191582);
pgpro_multiplan_captured_approve
----------------------------------
t
(1 row)
-- Or approve plans selected from the captured list
SELECT pgpro_multiplan_captured_approve(dbid, sql_hash, plan_hash)
FROM pgpro_multiplan_captured_queries
WHERE query_string like '%SELECT % FROM a t1, a t2%';
pgpro_multiplan_captured_approve
----------------------------------
t
(1 row)
-- Approved plans are automatically removed from the captured queries storage
SELECT count(*) FROM pgpro_multiplan_captured_queries;
count
-------
0
(1 row)
-- Approved plans are shown in the pgpro_multiplan_storage view
SELECT * FROM pgpro_multiplan_storage \gx
-[ RECORD 1 ]-+------------------------------------------------------------------------------------------------
dbid | 5
sql_hash | 6079808577596655075
const_hash | 0
plan_hash | -487722818968417375
valid | t
cost | 36.785
sample_string | SELECT count(*) FROM a t1, a t2 WHERE t1.x = t2.x AND t1.y <= 1000 AND t2.y > 900;
query_string | SELECT count(*) FROM a t1, a t2 WHERE t1.x = t2.x AND t1.y <= $1 AND t2.y > $2;
paramtypes |
query | <>
plan | <>
plan_type | baseline
hintstr | Leading(("t1" "t2" )) HashJoin("t1" "t2") IndexScan("t2" "a_y_idx") SeqScan("t1")
wildcards |
-[ RECORD 2 ]-+------------------------------------------------------------------------------------------------
dbid | 5
sql_hash | 6079808577596655075
const_hash | 0
plan_hash | 2719320099967191582
valid | t
cost | 18.997500000000002
sample_string | SELECT count(*) FROM a t1, a t2 WHERE t1.x = t2.x AND t1.y <= 10 AND t2.y > 900;
query_string | SELECT count(*) FROM a t1, a t2 WHERE t1.x = t2.x AND t1.y <= $1 AND t2.y > $2;
paramtypes |
query | <>
plan | <>
plan_type | baseline
hintstr | Leading(("t2" "t1" )) HashJoin("t1" "t2") IndexScan("t2" "a_y_idx") IndexScan("t1" "a_y_idx")
wildcards |
The pgpro_multiplan extension allows you to create a backup of necessary plans and then restore these plans into the current database. This can be useful to transfer plans between databases or server instances.
To back up plans from a specific database, use the pgpro_multiplan_storage view as follows:
CREATE TABLE storage_copy AS SELECT s.* FROM pgpro_multiplan_storage s JOIN pg_database d ON s.dbid = d.oid WHERE d.datname = 'db_name';
To restore plans from a backup, call the pgpro_multiplan_restore function, for instance, as follows:
SELECT s.query_string, res.sql_hash IS NOT NULL AS success FROM storage_copy s, LATERAL pgpro_multiplan_restore(s.query_string, s.sample_string, s.hintstr, s.paramtypes, s.plan_type, s.wildcards, NULL) res;
Plans can be restored only when the pg_hint_plan extension is active, see the Compatibility with Other Extensions section.
When you back up and restore plans, take into account the following considerations and limitations:
Plans are always restored into the current database. To restore plans
into another database, connect to it first. Thus, it is recommended
to create a backup with plans only from one database as for
db_name in the examples in this section. If you
need to transfer plans for multiple databases, create separate backups
for them, connect to each target database sequentially one by one, and
restore the corresponding plans from backups.
If you back up plans from multiple databases and those databases contain different plans for identical queries, only the first conflicting plan is restored.
Only plans for valid queries can be restored, which means that all relations used in the query must exist in the current database.
This section describes how to back up and restore plans in different popular scenarios.
Follow the steps below to save plans when upgrading a server from an older version that has an incompatible data storage.
Back up plans before upgrade.
CREATE TABLE storage_copy AS SELECT s.* FROM pgpro_multiplan_storage s JOIN pg_database d ON s.dbid = d.oid WHERE d.datname = 'db_name';
Upgrade the server.
Restore plans.
SELECT pgpro_multiplan_restore(query_string, NULL, hintstr, paramtypes, 'hintset', NULL, NULL) FROM storage_copy;
To migrate plans from the deprecated sr_plan extension to pgpro_multiplan, perform the following steps:
Back up plans from the storage of the sr_plan extension.
CREATE TABLE storage_copy AS SELECT s.* FROM sr_plan_storage s JOIN pg_database d ON s.dbid = d.oid WHERE d.datname = 'db_name';
Restore plans in a database with the pgpro_multiplan extension.
SELECT pgpro_multiplan_restore(query_string, NULL, hintstr, paramtypes, 'hintset', NULL, NULL) FROM storage_copy;
To transfer plans between two servers, do the following:
Connect to the source server.
Back up plans to a table.
CREATE TABLE storage_copy AS SELECT s.* FROM pgpro_multiplan_storage s JOIN pg_database d ON s.dbid = d.oid WHERE d.datname = 'db_name';
Use the pg_dump utility to dump the table to a file.
$ pg_dump --table storage_copy -Ft postgres > storage_copy.tar
Connect to the target server and database.
Move the created dump file to the target file system.
Use the pg_restore utility to restore the table with plans from the dump file.
$ pg_restore --dbname postgres -Ft storage_copy.tar
Restore plans.
SELECT pgpro_multiplan_restore(query_string, sample_string, hintstr, paramtypes, plan_type, wildcards, NULL) FROM storage_copy; DROP TABLE storage_copy;
To transfer plans from the sandbox to the regular storage, complete the following steps:
Set the pgpro_multiplan.sandbox
parameter to on and back up plans from
the sandbox.
SET pgpro_multiplan.sandbox = ON; CREATE TABLE storage_copy AS SELECT s.* FROM pgpro_multiplan_storage s JOIN pg_database d ON s.dbid = d.oid WHERE d.datname = 'db_name';
Set the pgpro_multiplan.sandbox parameter
to off and restore plans into the
regular storage.
SET pgpro_multiplan.sandbox = OFF; SELECT pgpro_multiplan_restore(query_string, sample_string, hintstr, paramtypes, plan_type, wildcards, NULL) FROM storage_copy;
To transfer plans from one database to another, connect to the target database and restore plans as shown below.
SELECT pgpro_multiplan_restore(s.query_string, s.sample_string, s.hintstr, s.paramtypes, s.plan_type, s.wildcards, NULL) FROM pgpro_multiplan_storage s JOIN pg_database d ON s.dbid = d.oid WHERE d.datname = 'db_name';
Here db_name is the name of the database from
which you want to transfer plans.
This example demonstrates how to transfer plans from one server instance to another.
-- Connect to the source server
psql (17.4)
Type "help" for help.
-- In this example, 1000 frozen plans are stored in the pgpro_multiplan_storage view
postgres=# select count(*) from pgpro_multiplan_storage;
count
-------
1000
(1 row)
-- Copy plans from the postgres database to a table
postgres=# CREATE TABLE storage_copy AS SELECT s.*
FROM pgpro_multiplan_storage s
JOIN pg_database d ON s.dbid = d.oid
WHERE d.datname = 'postgres';
-- Dump the table to an archive file
$ pg_dump --table storage_copy -Ft postgres > storage_copy.tar
-- Close the connection to the source server
-- Connect to the target server
./psql postgres
psql (16.8)
Type "help" for help.
-- Create the pgpro_multiplan extension and enable it
postgres=# create extension pgpro_multiplan;
CREATE EXTENSION
SET pgpro_multiplan.mode = 'frozen';
SET
-- This server does not contain plans
postgres=# select count(*) from pgpro_multiplan_storage;
count
-------
0
(1 row)
-- Move the dump file with frozen plans to the target file system
-- Restore the table with frozen plans from the dump
$ pg_restore --dbname postgres -Ft storage_copy.tar
-- Restore frozen plans from the table using the pgpro_multiplan_restore function
postgres=# SELECT pgpro_multiplan_restore(query_string, sample_string, hintstr, paramtypes, 'serialized', NULL, NULL)
FROM storage_copy;
pgpro_multiplan_restore
----------------------------------
(8436876698844323073,871432885)
(8436876698844323073,573678316)
(8436876698844323073,1999378082)
(8436876698844323073,1681603536)
(8436876698844323073,3959620774)
...
(8436876698844323073,1263226437)
(8436876698844323073,4053700861)
(8436876698844323073,2418458596)
(8436876698844323073,413896030)
(1000 rows)
-- The function restores 1000 frozen plans. The result is shown as pairs of sql_hash and const_hash
-- Frozen queries were identical and differed only in constants, so sql_hash is the same for all plans
-- Drop the table used to restore plans
postgres=# DROP TABLE storage_copy;
DROP TABLE
-- The target server now also stores 1000 frozen plans
postgres=# select count(*) from pgpro_multiplan_storage;
count
-------
1000
(1 row)
-- Disable pgpro_multiplan and run the query
postgres=# SET pgpro_multiplan.mode = '';
SET
postgres=# EXPLAIN (COSTS OFF) SELECT * FROM a WHERE x > 10;
QUERY PLAN
--------------------
Seq Scan on a
Filter: (x > 10)
(2 rows)
-- Enable pgpro_multiplan and run the same query once again
-- One of the restored plans is now used
postgres=# SET pgpro_multiplan.mode = 'frozen';
SET
postgres=# EXPLAIN (COSTS OFF) SELECT * FROM a WHERE x > 10;
QUERY PLAN
-------------------------------------
Custom Scan (MultiplanScan)
Plan is: frozen, serialized
SQL hash: 8436876698844323073
Const hash: 2295408638
Plan hash: 0
-> Index Scan using a_x_idx on a
Index Cond: (x > 10)
(7 rows)
To ensure compatibility of pgpro_multiplan 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: pgpro_multiplan must be loaded after pg_hint_plan.
shared_preload_libraries = 'pg_hint_plan, pgpro_multiplan'
aqo: pgpro_multiplan must be loaded before aqo.
shared_preload_libraries = 'pgpro_multiplan, aqo'
pgpro_stats: pgpro_multiplan must be loaded after pgpro_stats.
shared_preload_libraries = 'pgpro_stats, pgpro_multiplan'
pgpro_multiplan automatically attempts to cast the types of constants involved in the query to match the parameter types of the query for which a plan is frozen. If type casting is not possible, the plan is ignored.
SELECT sql_hash, const_hash
FROM pgpro_multiplan_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 (MultiplanScan) (cost=1.38..1.39 rows=1 width=8)
Plan is: tracked
SQL hash: -5166001356546372387
Const hash: 0
Plan 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 (MultiplanScan) (cost=1.38..1.39 rows=1 width=8)
Plan is: tracked
SQL hash: -5166001356546372387
Const hash: 0
Plan 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)
To collect statistics about the plan usage, specify the
statistics value in the
pgpro_multiplan.mode parameter.
These statistics can be accessed using the
pgpro_multiplan_stats
view. The pgpro_multiplan.max_stats
parameter specifies the maximum number of collected statistics values. When
this limit is reached, further statistics are discarded. If a plan has
changed, the statistics of the plan usage are reset and recalculated
using new plan identifiers.
For more detailed planning and execution statistics, you can use the pgpro_stats extension (see the Compatibility with Other Extensions section). These statistics can be accessed using the pgpro_stats_statements view.
You can join information from the pgpro_multiplan_stats
and pgpro_stats_statements views by the
planid field.
The following example demonstrates how to collect and view statistics. It uses the query and frozen plan from the Frozen Plan Example.
-- Enable collecting statistics
SET pgpro_multiplan.mode = 'frozen, statistics';
-- Execute the query
SELECT count(*) FROM a
WHERE x = 1 OR (x > 11 AND x < 22) OR x = 22;
count
-------
12
(1 row)
-- Now you can see statistics of the plan usage
SELECT * FROM pgpro_multiplan_stats;
dbid | sql_hash | const_hash | plan_hash | planid | counter
------+---------------------+------------+-----------+---------------------+---------
5 | 6062491547151210914 | 2413041345 | 0 | 3549961214127427294 | 1
(1 row)
pgpro_multiplan_storage View
The pgpro_multiplan_storage view provides detailed
information about all plans. The columns of the view are
shown in Table G.4.
Table G.4. pgpro_multiplan_storage Columns
| Name | Type | Description |
|---|---|---|
dbid | oid | ID of the database where the query is executed |
sql_hash | bigint | Internal query ID |
const_hash | bigint | Hash of non-parameterized constants for frozen plans, 0 for template and allowed plans |
plan_hash | bigint | Internal ID of the allowed plan, 0 for frozen and template plans |
valid | boolean | FALSE if the plan was invalidated the last time it was used |
cost | float | Cost of the allowed plan, 0 for frozen and template plans |
sample_string | text | Non-parameterized query with constants |
query_string | text | Parameterized query |
paramtypes | regtype[] | Array with parameter types used in the query |
query | text | Internal representation of the query |
plan | text | Internal representation of the plan |
plan_type | text | Plan type. For frozen plans: serialized or hintset.
For template plans: template. For allowed plans: baseline |
hintstr | text | Set of hints formed based on the plan |
wildcards | text | Regular expression used for the template plan,
NULL for frozen and allowed plans |
pgpro_multiplan_local_cache View
The pgpro_multiplan_local_cache view provides detailed
information about frozen plans in the local cache. A frozen plan is added to
the local cache if it was used at least once. The size of the local cache is
limited by the pgpro_multiplan.max_local_cache_size parameter.
The columns of the view are shown in
Table G.5.
Table G.5. pgpro_multiplan_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 query plan is frozen |
fs_is_valid | boolean | TRUE if the frozen statement is valid |
ps_is_valid | boolean | TRUE if the corresponding prepared statement is valid |
query_string | text | Query text |
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 plan |
pgpro_multiplan_captured_queries View
The pgpro_multiplan_captured_queries view provides detailed
information about all queries captured in sessions. The columns of the view
are shown in Table G.6.
Table G.6. pgpro_multiplan_captured_queries Columns
| Name | Type | Description |
|---|---|---|
dbid | oid | ID of the database where the query is executed |
sql_hash | bigint | Internal query ID |
queryid | bigint | Standard query ID |
plan_hash | bigint | Internal plan ID |
planid | bigint | Plan ID compatible with the pgpro_stats extension |
cost | float | Plan cost |
sample_string | text | Last used non-parameterized query with constants |
query_string | text | Last used parameterized query |
hintstr | text | Set of hints formed based on the plan |
explain_plan | text | Plan shown by the EXPLAIN command |
pgpro_multiplan_stats View
The pgpro_multiplan_stats view provides
statistics about the plan usage. The columns of the view
are shown in Table G.7.
Table G.7. pgpro_multiplan_stats Columns
| Name | Type | Description |
|---|---|---|
dbid | oid | ID of the database where the query is executed |
sql_hash | bigint | Internal query ID |
plan_hash | bigint | Internal plan ID |
planid | bigint | Plan ID compatible with the pgpro_stats extension |
counter | bigint | Number of times the plan was used |
Only superusers can call the functions listed below.
pgpro_multiplan_register_query(query_string text) returns record
pgpro_multiplan_register_query(query_string text, VARIADIC regtype[]) returns record
Registers the query specified by the query_string
argument. Returns the unique pair of sql_hash and
const_hash.
pgpro_multiplan_unregister_query() returns bool
Removes the registered query. Returns true if there
are no errors.
pgpro_multiplan_freeze(plan_type text) returns bool
Fixes the last used plan for the query and saves it to the permanent
pgpro_multiplan storage that can be accessed
in the pgpro_multiplan_storage
view. The allowed values of the optional plan_type
argument are serialized (default), hintset,
template, and baseline. Returns
true if the plan was fixed successfully. For details
about plan types, see Supported
Modes and Plan Types. For more information about this function
and examples, refer to
Freezing Plans.
pgpro_multiplan_unfreeze(sql_hash bigint, const_hash bigint) returns bool
Removes the specified plan from the permanent storage but keeps the query
registered. Returns true if there are no errors.
pgpro_multiplan_remove(dbid oid, sql_hash bigint, const_hash bigint) returns bool
Removes the frozen plan identified by the sql_hash
and const_hash arguments from the database defined
by the dbid argument. Omit dbid
to remove the plan from the current database. This function operates
as the pgpro_multiplan_unfreeze
and pgpro_multiplan_unregister_query
functions called sequentially. Returns true if the
plan was removed successfully.
pgpro_multiplan_reset(dbid oid) returns bigint
Removes all records from the pgpro_multiplan_storage
view for the specified database. Omit dbid
to remove the data collected by pgpro_multiplan
for the current database. Set dbid to
NULL to reset data for all databases. Returns the number of removed records.
pgpro_multiplan_reload_frozen_plancache() returns bool
Drops all frozen plans and reloads them in the
pgpro_multiplan_storage view.
It also drops statements that have been registered but not frozen.
Returns true if there are no errors.
pgpro_multiplan_stats() returns table
Returns statistics of the plan usage from the pgpro_multiplan_stats view.
pgpro_multiplan_registered_query(sql_hash bigint, const_hash bigint) returns table
Returns the registered query with the specified sql_hash
and const_hash parameters 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.
pgpro_multiplan_captured_approve(dbid oid, sql_hash bigint, plan_hash bigint) returns bool
Adds the specified plan for the captured query to a baseline
(a set of allowed plans) and saves it to the
pgpro_multiplan_storage view.
Returns true if the plan was added successfully.
pgpro_multiplan_remove_baseline(dbid oid, sql_hash bigint, plan_hash bigint) returns bool
Removes the allowed plan identified by the sql_hash
and plan_hash arguments from the database defined
by the dbid argument. Omit dbid
to remove the allowed plan from the current database. Returns
true if the plan was removed successfully.
pgpro_multiplan_remove_template(dbid oid, sql_hash bigint, table_hash bigint) returns bool
Removes the template plan identified by the sql_hash
and table_hash arguments from the database
defined by the dbid argument. Omit
dbid to remove the template plan from the
current database. Returns true if the plan was
removed successfully.
pgpro_multiplan_enable(dbid oid, sql_hash bigint, const_hash bigint, enable bool) returns bool
Enables or disables the frozen plan identified by the sql_hash
and const_hash arguments. The dbid
argument defines the ID of a target database. Omit dbid
to use the function for the plan in the current database. For the last
argument, specify true to enable the plan or
false to disable it. Returns true
if the plan state was changed successfully.
pgpro_multiplan_enable_baseline(dbid oid, sql_hash bigint, plan_hash bigint, enable bool) returns bool
Enables or disables the allowed plan identified by the sql_hash
and plan_hash arguments. The dbid
argument defines the ID of a target database. Omit dbid
to use the function for the plan in the current database. For the last
argument, specify true to enable the plan or
false to disable it. Returns true
if the plan state was changed successfully.
pgpro_multiplan_enable_template(dbid oid, sql_hash bigint, table_hash bigint, enable bool) returns bool
Enables or disables the template plan identified by the sql_hash
and table_hash arguments. The dbid
argument defines the ID of a target database. Omit dbid
to use the function for the plan in the current database. For the last
argument, specify true to enable the plan or
false to disable it. Returns true
if the plan state was changed successfully.
pgpro_multiplan_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 hint-set plans, the
pg_hint_plan extension
must be loaded. Returns true if the plan type was
changed successfully.
pgpro_multiplan_update_hintset(dbid oid, sql_hash bigint, const_hash bigint, hintset text) returns bool
Replaces the generated hint set for the hintset
frozen plan with the specified set of custom hints. The plan is
identified by the sql_hash and
const_hash arguments. The dbid
argument defines the ID of a target database. Omit dbid
to use the function for the plan in the current database. The
hintset argument specifies a string with custom
hints. This string should not be specified in the special comment used in
pg_hint_plan, that is it
should not start with /*+ and end with */.
Returns true if the hint set was changed successfully.
pgpro_multiplan_update_template_hintset(dbid oid, sql_hash bigint, table_hash bigint, hintset text) returns bool
Replaces the generated hint set for the template plan with the
specified set of custom hints. The plan is identified by the
sql_hash and table_hash
arguments. The dbid argument defines the ID of
a target database. Omit dbid to use the function
for the plan in the current database. The hintset
argument specifies a string with custom hints. This string should not
be specified in the special comment used in
pg_hint_plan, that is it
should not start with /*+ and end with */.
Returns true if the hint set was changed successfully.
pgpro_multiplan_update_baseline_cost(sql_hash bigint, plan_hash bigint) returns bool
Updates the cost of the specified allowed plan in the permanent
pgpro_multiplan storage. Returns
true if the cost was updated successfully.
pgpro_multiplan_captured_clean() returns bigint
Removes all records from the pgpro_multiplan_captured_queries view. Returns the number of removed records.
get_sql_hash(query_string text) returns bigint
Returns the internal ID (sql_hash) for the
specified query.
pgpro_multiplan_restore(query_string text, sample_string text, hintstr text, paramtypes regtype[], plan_type text, wildcards text, status int) returns record
Restores the plan for the specified query into the current database.
This function has the following arguments:
query_string: The query with
$ parameters
(same as in nPREPARE
) for which
to restore the plan based on a set of hints.
statement_name AS
sample_string: The non-parameterized query
with constants.
hintstr: A set of hints that describe the
desired plan. These hints should be supported by the
pg_hint_plan extension. For
the baseline and template
plan types, this argument cannot be set to an empty string. For
other plan types, if this argument is set to NULL
or empty string, the standard plan is used.
parameter_type: An array with parameter
types used in the query. If this argument is set to
NULL, parameter types should be determined
automatically.
plan_type: The plan type. Allowed values
are serialized, hintset,
baseline, and template.
For details about plan types, see
Supported Modes and Plan Types.
wildcards: The POSIX regular
expression used only for the template plan type
to check matching of table names. For other plan types, this argument
is ignored, and you can set it to NULL.
status: This argument is reserved for future
usage. Currently, always set it to NULL or
0.
The function returns the unique pair of sql_hash
and const_hash if the plan was restored
successfully. Otherwise, it returns NULL.
For more information about this function and use cases, refer to Plan Backup and Restore.
pgpro_multiplan.mode (string)
A comma-separated list of enabled pgpro_multiplan modes. The following values are available:
frozen: Enables the use of frozen plans (both
serialized and hint-set).
wildcards: Enables the use of template plans.
baseline: Enables the use of allowed plans.
statistics: Enables collecting statistics about
the plan usage. These statistics are stored in the
pgpro_multiplan_stats
view. This value can be specified only together with one or more
plan types.
SET pgpro_multiplan.mode = 'frozen, wildcards, baseline, statistics';
For details about plan types, see Supported Modes and Plan Types. For more information about collecting statistics, refer to Statistics.
By default, the pgpro_multiplan.mode parameter is
set to an empty string, which means that pgpro_multiplan
is disabled. Only superusers can change this setting.
pgpro_multiplan.max_stats (integer)
Sets the maximum number of statistics values that can be stored in the
pgpro_multiplan_stats view. Further
statistics will be discarded. The default value is 5000. This
parameter can only be set at server start.
pgpro_multiplan.max_items (integer)
Sets the maximum number of entries pgpro_multiplan can operate with. The default value is 100. This parameter can only be set at server start.
pgpro_multiplan.auto_tracking (boolean)
Enables pgpro_multiplan to register
queries executed using the EXPLAIN and
EXPLAIN EXECUTE commands automatically. The default
value is off. Only superusers can change this setting.
pgpro_multiplan.max_local_cache_size (integer)
Sets the maximum size of the local cache, in kilobytes.
The default value is 0, which means no limit.
Only superusers can change this setting.
pgpro_multiplan.wal_rw (boolean)
Enables physical replication of pgpro_multiplan
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.
pgpro_multiplan.auto_capturing (boolean)
Enables the automatic query capture in pgpro_multiplan.
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
pgpro_multiplan_captured_queries
view. Also, all plans for each query are shown. Information about
executed queries is stored until the server restart. The default value
is off. Only superusers can change this setting.
pgpro_multiplan.max_captured_items (integer)
Sets the maximum number of queries pgpro_multiplan can capture. The default value is 1000. This parameter can only be set at server start.
pgpro_multiplan.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,
pgpro_multiplan 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 pgpro_multiplan
cache. The default value is off. Only superusers
can change this setting.
pgpro_multiplan.wildcards (string)
A POSIX regular expression used for template plans
to check matching of table names referenced in queries. To enable the
use of template plans, specify the wildcards
value in the pgpro_multiplan.mode
parameter. The pgpro_multiplan.wildcards
value is frozen along with the corresponding query. The default value
is .* that means matching for any table name.
pgpro_multiplan.show_explain_details (boolean)
Enables displaying plan
identifiers in the EXPLAIN output. The default value is
on. Only superusers can change this setting.
pgpro_multiplan.show_hint_string (boolean)
Enables displaying sets of hints, which are formed based on plans, in
the EXPLAIN output. The default value is
off. Only superusers can change this setting.