pgpro_multiplan 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.
pgpro_multiplan 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 pgpro_multiplan extension is provided with Postgres Pro Enterprise
as a separate pre-built package pgpro-multiplan-ent-16
(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.6.
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, in one of the following ways:
To enable pgpro_multiplan for all backends,
set pgpro_multiplan.enable = true in the
postgresql.conf file.
To activate pgpro_multiplan in the current session, use the following command:
SET pgpro_multiplan.enable TO true;
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.
There are two ways to use pgpro_multiplan: either with frozen plans or with allowed plans.
pgpro_multiplan 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 pgpro_multiplan_register_query() function:
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 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 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)
Using the pgpro_multiplan.auto_tracking parameter:
-- Set pgpro_multiplan.auto_tracking to on
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))
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 pgpro_multiplan with other extensions, see Section G.3.6.
To freeze a modified query plan, use the pgpro_multiplan_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 G.3.4.
The below example illustrates the usage of the frozen plan.
-- A plan that needs to be improved
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)
-- Make sure pgpro_multiplan is enabled
SET pgpro_multiplan.enable = 'on';
-- 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)
If there is no frozen plan for the given query, the pgpro_multiplan extension can apply a plan from the set of allowed plans created by the standard planner or real-time query replanning.
To add plans produced by real-time query replanning to the list of
allowed plans automatically, enable real-time query replanning and set
the pgpro_multiplan.aqe_plans_auto_approve parameter
to on.
To add the plan created by the standard planner to the set of allowed plans, follow these steps:
For subsequent queries, the created plan is applied without modification if it is in the set of allowed plans. If there is no such plan, the cheapest plan from the set of allowed plans is used.
Allowed plans can be used only when pg_hint_plan extension is active, see Frozen Plan Types and Compatibility with Other Extensions sections. Allowed plans are not used if automatic capturing is enabled. Do not forget to disable the pgpro_multiplan.auto_capturing parameter after completing the capture.
The pgpro_multiplan.auto_capturing parameter allows capturing all executed queries.
-- 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 auto_capturing parameter
SET pgpro_multiplan.auto_capturing = 'on';
SET pgpro_multiplan.enable = '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;
constants | 1000, 900
prep_const |
hint_str | Leading(("t1" "t2" )) HashJoin("t1" "t2") IndexScan("t2" "a_y_idx") SeqScan("t1")
explain_plan | Custom Scan (MultiplanScan) (cost=36.77..36.78 rows=1 width=8) +
| Output: (count(*)) +
| Plan is: tracked +
| SQL hash: 6079808577596655075 +
| Const hash: 0 +
| Plan hash: -487722818968417375 +
| Parameters: 0 +
| -> 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;
constants | 10, 900
prep_const |
hint_str | Leading(("t2" "t1" )) HashJoin("t1" "t2") IndexScan("t2" "a_y_idx") IndexScan("t1" "a_y_idx")
explain_plan | Custom Scan (MultiplanScan) (cost=18.99..19.00 rows=1 width=8) +
| Output: (count(*)) +
| Plan is: tracked +
| SQL hash: 6079808577596655075 +
| Const hash: 0 +
| Plan hash: 2719320099967191582 +
| Parameters: 0 +
| -> 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';
You can approve any plan from the
pgpro_multiplan_captured_queries view by
using the pgpro_multiplan_captured_approve()
function with the specified dbid, sql_hash,
and plan_hash parameters.
-- 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
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 | hintset
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
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 | hintset
hintstr | Leading(("t2" "t1" )) HashJoin("t1" "t2") IndexScan("t2" "a_y_idx") IndexScan("t1" "a_y_idx")
wildcards |
The following example illustrates the use of allowed plans.
-- Enable the auto_capturing parameter
SET pgpro_multiplan.auto_capturing = 'on';
SET pgpro_multiplan.enable = 'on';
-- Execute the query
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF)
SELECT count(*) FROM a t1, a t2 WHERE t1.x = t2.x AND t1.y <= 1000 AND t2.y > 900;
QUERY PLAN
--------------------------------------------------------------------------------
Custom Scan (MultiplanScan) (actual rows=1 loops=1)
Plan is: tracked
SQL hash: 6079808577596655075
Const hash: 0
Plan hash: -487722818968417375
-> Aggregate (actual rows=1 loops=1)
-> Hash Join (actual rows=100 loops=1)
Hash Cond: (t1.x = t2.x)
-> Seq Scan on a t1 (actual rows=1000 loops=1)
Filter: (y <= 1000)
-> Hash (actual rows=100 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 12kB
-> Index Scan using a_y_idx on a t2 (actual rows=100 loops=1)
Index Cond: (y > 900)
Planning Time: 0.543 ms
Execution Time: 0.688 ms
(16 rows)
-- And execute it again with different constants
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF)
SELECT count(*) FROM a t1, a t2 WHERE t1.x = t2.x AND t1.y <= 10 AND t2.y > 900;
QUERY PLAN
--------------------------------------------------------------------------------
Custom Scan (MultiplanScan) (actual rows=1 loops=1)
Plan is: tracked
SQL hash: 6079808577596655075
Const hash: 0
Plan hash: 2719320099967191582
-> Aggregate (actual rows=1 loops=1)
-> Hash Join (actual rows=0 loops=1)
Hash Cond: (t2.x = t1.x)
-> Index Scan using a_y_idx on a t2 (actual rows=100 loops=1)
Index Cond: (y > 900)
-> Hash (actual rows=10 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Index Scan using a_y_idx on a t1 (actual rows=10 loops=1)
Index Cond: (y <= 10)
Planning Time: 0.495 ms
Execution Time: 0.252 ms
(16 rows)
-- Disable the automatic capturing
SET pgpro_multiplan.auto_capturing = 'off';
-- Approve all captured plans
SELECT pgpro_multiplan_captured_approve(dbid, sql_hash, plan_hash)
FROM pgpro_multiplan_captured_queries;
pgpro_multiplan_captured_approve
----------------------------------
t
t
(2 rows)
-- The plan does not change because it is one of the allowed ones
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF)
SELECT count(*) FROM a t1, a t2 WHERE t1.x = t2.x AND t1.y <= 1000 AND t2.y > 900;
QUERY PLAN
--------------------------------------------------------------------------------
Custom Scan (MultiplanScan) (actual rows=1 loops=1)
Plan is: frozen, hintset
SQL hash: 6079808577596655075
Const hash: 0
Plan hash: -487722818968417375
-> Aggregate (actual rows=1 loops=1)
-> Hash Join (actual rows=100 loops=1)
Hash Cond: (t1.x = t2.x)
-> Seq Scan on a t1 (actual rows=1000 loops=1)
Filter: (y <= 1000)
-> Hash (actual rows=100 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 12kB
-> Index Scan using a_y_idx on a t2 (actual rows=100 loops=1)
Index Cond: (y > 900)
Planning Time: 0.426 ms
Execution Time: 0.519 ms
(16 rows)
-- This plan would normally perform seqscan on both tables, but is currently the cheapest of the allowed set
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF)
SELECT count(*) FROM a t1, a t2 WHERE t1.x = t2.x AND t1.y <= 1000 AND t2.y > 0;
QUERY PLAN
--------------------------------------------------------------------------------
Custom Scan (MultiplanScan) (actual rows=1 loops=1)
Plan is: frozen, hintset
SQL hash: 6079808577596655075
Const hash: 0
Plan hash: 2719320099967191582
-> Aggregate (actual rows=1 loops=1)
-> Hash Join (actual rows=1000 loops=1)
Hash Cond: (t2.x = t1.x)
-> Index Scan using a_y_idx on a t2 (actual rows=1000 loops=1)
Index Cond: (y > $2)
-> Hash (actual rows=1000 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 44kB
-> Index Scan using a_y_idx on a t1 (actual rows=1000 loops=1)
Index Cond: (y <= $1)
Planning Time: 2.473 ms
Execution Time: 1.859 ms
(16 rows)
There are three types of frozen plans: serialized plans, hint-set plans, and template 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.
Currently the allowed plans can have only the
hintset type.
A template plan is a special case of a
hintset plan. If the same POSIX
regular expressions are used for mapping the table names in queries
and the frozen query, the template plan is used.
The pgpro_multiplan extension allows you to create a backup of frozen 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 frozen plans from a specific database, use the
pgpro_multiplan_storage view like this:
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 frozen plans from a backup, call the
pgpro_multiplan_restore()
function:
SELECT s.query_string, res.sql_hash IS NOT NULL AS success FROM storage_copy s, LATERAL pgpro_multiplan_restore(s.query_string, s.hintstr, s.paramtypes, s.plan_type) res;
Plans can be restored only when the pg_hint_plan extension is active, see the Compatibility with Other Extensions section.
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 required 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.
When you back up and restore plans, take into account the following limitations:
Only frozen plans can be restored.
Frozen plans of the template type cannot be
restored. Only serialized and
hintset plans are supported.
If you back up plans from multiple databases and those databases contain different frozen plans for identical queries, only the first conflicting plan will be 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 frozen plans in different popular scenarios.
Follow the steps below to save frozen plans when upgrading a server from an older version that has an incompatible data storage.
Back up frozen 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 frozen plans.
SELECT pgpro_multiplan_restore(query_string, hintstr, paramtypes, plan_type) FROM storage_copy;
To transfer frozen plans between two servers, do the following:
Connect to the source server.
Back up frozen 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 connect to the required database.
Move the created dump file to the target file system.
Use the pg_restore utility to restore the table with frozen plans from the dump file.
$ pg_restore --dbname postgres -Ft storage_copy.tar
Restore frozen plans.
SELECT pgpro_multiplan_restore(query_string, hintstr, paramtypes, plan_type) FROM storage_copy; DROP TABLE storage_copy;
To transfer frozen plans from the sandbox to the regular storage, complete the following steps:
Set the pgpro_multiplan.sandbox
parameter to on and back up frozen 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 frozen plans into the
regular storage.
SET pgpro_multiplan.sandbox = OFF; SELECT pgpro_multiplan_restore(query_string, hintstr, paramtypes, plan_type) FROM storage_copy;
To transfer frozen 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.hintstr, s.paramtypes, s.plan_type) 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 plans are stored in the pgpro_multiplan_storage view
postgres=# select count(*) from pgpro_multiplan_storage;
count
-------
1000
(1 row)
-- Copy frozen 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.enable TO true;
SET
-- This server does not contain frozen 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, hintstr, paramtypes, plan_type)
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.enable = OFF;
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.enable = ON;
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'
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.
pgpro_multiplan 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 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)
If real-time query replanning is enabled, it attempts to reoptimize queries when replanning triggers fire during query execution. Global trigger values are specified in the replan_query_execution_time, replan_overrun_limit, and replan_memory_limit configuration parameters.
The pgpro_multiplan extension allows you to
override and adjust trigger values for individual
queries using the
set_aqe_trigger()
function. All individual trigger values are shown in the
aqe_triggers
view.
The pgpro_multiplan extension can collect
cumulative statistics for all statements considered feasible for
real-time query replanning
reoptimization. To enable this feature, set the
pgpro_multiplan.aqe_collect_stats parameter to
on. These statistics are stored in shared memory until a
server shutdown and can be accessed using the
aqe_stats
view. The statistics are not replicated. The
pgpro_multiplan.aqe_max_stats parameter specifies the
maximum number of collected statistics values, further statistics will be
discarded.
pgpro_multiplan_storage View #
The pgpro_multiplan_storage view provides detailed
information about all frozen and allowed plans. The columns of the view are
shown in Table G.2.
Table G.2. pgpro_multiplan_storage Columns
| Name | Type | Description |
|---|---|---|
dbid | oid | ID of the database where the statement is executed |
sql_hash | bigint | Internal query ID |
const_hash | bigint | Hash of non-parameterized constants |
plan_hash | bigint | Internal ID of the allowed plan, 0 for frozen 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 plans |
query_string | text | Query for which the plan was frozen or approved |
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, hintset,
or template. For allowed plans: hintset |
hintstr | text | Set of hints formed based on the plan |
wildcards | text | Wildcards used for the template frozen plan,
NULL for other plan types |
pgpro_multiplan_local_cache View #
The pgpro_multiplan_local_cache view provides detailed
information about registered and frozen statements in the local cache.
The columns of the view are shown in
Table G.3.
Table G.3. 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 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 pgpro_multiplan_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 |
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.4.
Table G.4. pgpro_multiplan_captured_queries Columns
| Name | Type | Description |
|---|---|---|
dbid | oid | ID of the database where the statement is executed |
sql_hash | bigint | Internal query ID |
queryid | bigint | Standard query ID |
plan_hash | bigint | Internal plan ID |
cost | float | Plan cost |
sample_string | text | Query executed in the automatic query capture mode |
query_string | text | Parameterized query |
constants | text | Set of constants in the query |
prep_consts | text | Set of constants used to EXECUTE a prepared statement |
hintstr | text | Set of hints formed based on the plan |
explain_plan | text | Plan shown by the EXPLAIN command |
pgpro_multiplan_fs_counter View #
The pgpro_multiplan_fs_counter view provides
information about frozen statements. The columns of the view
are shown in Table G.5.
Table G.5. pgpro_multiplan_fs_counter Columns
| Name | Type | Description |
|---|---|---|
dbid | oid | ID of the database where the statement is executed |
sql_hash | bigint | Internal query ID |
plan_hash | bigint | Internal plan ID |
usage_numb | text | Frozen statement usage counter |
aqe_triggers View #
The aqe_triggers view provides information about
individual replanning
trigger values. The columns of the view are shown in
Table G.6.
Table G.6. aqe_triggers Columns
| Name | Type | Description |
|---|---|---|
dbid | oid | ID of the database where the statement is executed |
sql_hash | bigint | Internal query ID |
execution_time | int | Value for the query execution time trigger, in milliseconds. NULL if the global trigger value is used |
memory | int | Value for the backend memory consumption trigger. NULL if the global trigger value is used |
underestimation_rate | double | Factor for the processed number of node tuples trigger. NULL if the global trigger value is used |
aqe_stats View #
The aqe_stats view provides cumulative
statistics about
real-time query replanning
reoptimizations. This view stores one row per each combination of the
database ID, query, and execution plan. The columns of the view are shown
in Table G.7.
Table G.7. aqe_stats Columns
| Name | Type | Description |
|---|---|---|
dbid | oid | ID of the database where the statement is executed |
sql_hash | bigint | Internal query ID |
planid | bigint | ID of the query execution plan |
query | text | Internal representation of the query |
last_updated | timestamp with time zone | Timestamp of the last statistics update |
exec_num | bigint | Number of query executions |
min_attempts | integer | Minimum number of times the query was reoptimized |
max_attempts | integer | Maximum number of times the query was reoptimized |
total_attempts | integer | Total number of times the query was reoptimized |
reason_repeated_plan | bigint | Number of times real-time query replanning was disabled because a repeated execution plan was generated |
reason_no_data | bigint | Number of times real-time query replanning was disabled because no new information was gathered during execution |
reason_max_reruns | bigint | Number of times real-time query replanning was disabled because the maximum number of reruns was reached |
reason_external | bigint | Number of times real-time query replanning was disabled by an extension, such as pgpro_multiplan |
reruns_forced | bigint | Total number of reoptimizations caused by the manual trigger |
reruns_time | bigint | Total number of reoptimizations caused by the query execution time trigger |
reruns_underestimation | bigint | Total number of reoptimizations caused by the processed number of node tuples trigger |
reruns_memory | bigint | Total number of reoptimizations caused by the backend memory trigger |
min_planning_time | double precision | Minimum time spent planning, in milliseconds |
max_planning_time | double precision | Maximum time spent planning, in milliseconds |
mean_planning_time | double precision | Mean time spent planning, in milliseconds |
stddev_planning_time | double precision | Population standard deviation of time spent planning, in milliseconds |
min_exec_time | double precision | Minimum time spent on query execution, in milliseconds |
max_exec_time | double precision | Maximum time spent on query execution, in milliseconds |
mean_exec_time | double precision | Mean time spent on query execution, in milliseconds |
stddev_exec_time | double precision | Population standard deviation of time spent on query execution, in milliseconds |
Only superuser 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
#
Saves the query described in the query_string
in the local cache and returns the unique pair of
sql_hash and const_hash.
pgpro_multiplan_unregister_query() returns bool
#Removes the query that was registered but not frozen from the local cache. Returns true if there are no errors.
pgpro_multiplan_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, hintset, and
template. The serialized value
means that the query plan based on the serialized representation is
used. With hintset, pgpro_multiplan
uses the query plan based on the set of hints, which is formed at the
stage of registered query execution. With template,
pgpro_multiplan creates a template plan,
which can be applied to the queries with table names matching
the regular expressions in the
pgpro_multiplan.wildcards
configuration parameter. The content of pgpro_multiplan.wildcards
is frozen along with the query for template plans.
If the plan_type argument is omitted,
the serialized query plan is used by default.
Returns true if there are no errors.
pgpro_multiplan_unfreeze(sql_hash 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.
pgpro_multiplan_remove(sql_hash bigint, const_hash bigint) returns bool
#
Removes the frozen statement with the specified
sql_hash and const_hash. Operates
as pgpro_multiplan_unfreeze and
pgpro_multiplan_unregister_query called sequentially. Returns
true if there are no errors.
pgpro_multiplan_reset(dbid oid) returns bigint
#
Removes all records in the pgpro_multiplan storage
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.
pgpro_multiplan_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.
pgpro_multiplan_fs_counter() returns table
#
Returns plan_hash of the frozen plan, the number of
times each frozen statement was used, and the ID of the database where
the statement was registered and used. If the frozen plan changed,
the statistics of frozen statements usage is reset and
recalculated using the new plan_hash.
pgpro_multiplan_registered_query(sql_hash bigint, const_hash bigint) returns 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.
pgpro_multiplan_captured_approve(dbid oid, sql_hash bigint, plan_hash bigint) returns bool
#Moves the captured query to the permanent pgpro_multiplan storage. Returns true if the query has been moved 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 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.
pgpro_multiplan_hintset_update(sql_hash 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.
pgpro_multiplan_captured_clean() returns bigint
#Removes all records from the pgpro_multiplan_captured_queries view. The function returns the number of removed records.
get_sql_hash(query_string text) returns bigint
#
Returns the internal ID (sql_hash) for the
specified query.
set_aqe_trigger(sql_hash bigint, trigger_name text, trigger_val int) returns bool
set_aqe_trigger(sql_hash bigint, trigger_name text, trigger_val double precision) returns bool
#
Sets or resets the individual value of the
replanning
trigger for the specified query. The individual trigger value
overrides the global trigger value specified in the configuration
parameter. The allowed values of the trigger_name
argument are execution_time, memory,
or underestimation_rate. For
execution_time and memory triggers,
specify integer values. For underestimation_rate,
you can define double precision values. To reset the individual
trigger value, pass NULL or negative value less
than -1.
aqe_triggers_reset(dbid oid) returns bigint
#
Removes all records from the aqe_triggers
view for the specified database. To clean the
aqe_triggers view for the current database,
omit dbid. To remove records from this view
for all databases, set dbid to
NULL. Returns the number of removed records.
aqe_stats_reset(dbid oid) returns bigint
#
Removes all records from the aqe_stats
view for the specified database. To clean the
aqe_stats view for the current database,
omit dbid. To remove records from this view
for all databases, set dbid to
NULL. Returns the number of removed records.
pgpro_multiplan_restore(query_string text, hintstr texttext, paramtypes regtype[], plan_type text) returns record
#Restores the frozen 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 frozen plan based on a set of hints.
statement_name AS
hintstr: A set of hints supported by the
pg_hint_plan extension. If
this argument is set to NULL or empty
string, the standard plan will be 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 and hintset.
The template plan is not supported.
The function returns the unique pair of sql_hash
and const_hash if the plan was restored
successfully. Otherwise, it returns NULL.
pgpro_multiplan.enable (boolean)
#
Enables pgpro_multiplan to use frozen plans.
The default value is off.
Only superusers can change this setting.
pgpro_multiplan.fs_ctr_max (integer)
#
Sets the maximum number of frozen statements returned by the
pgpro_multiplan_fs_counter() function.
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 normalize and register
queries executed using the EXPLAIN command
automatically. The default value is off. Only
superusers can change this setting.
pgpro_multiplan.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.
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 comma-separated list of POSIX regular expressions
that serves as a template for checking table names contained in a
query. Wildcards used for table name mapping are stored in the plans
frozen as the template plans. The default value is
.* that matches anything. Regular expressions are
applied from left to right. For example, in
^t[[:digit:]]$,^t.*,.* the first regular expression
checked is ^t[[:digit:]]$, the next is
^t.*, and the last is .*.
pgpro_multiplan.aqe_plans_auto_approve (boolean)
#
Enables pgpro_multiplan to add plans
produced by real-time
query replanning to the list of
allowed plans
automatically. The default value is off. Only
superusers can change this setting. Ensure that real-time query
replanning is enabled using the replan_enable
configuration parameter.
pgpro_multiplan.aqe_max_items (integer)
#
Sets the maximum number of replanning trigger values that can be
stored in the aqe_triggers view. The
default value is 100. This parameter can only be set at server start.
pgpro_multiplan.aqe_collect_stats (integer)
#
Enables pgpro_multiplan to collect
real-time query replanning statistics for all statements considered
feasible for reoptimization. These statistics are stored in shared
memory until a server shutdown and can be accessed using the
aqe_stats view. The statistics are not
replicated. For this feature to work, query ID computation should be
enabled using the compute_query_id configuration
parameter. The default value is off. Only
superusers can change this setting.
pgpro_multiplan.aqe_max_stats (integer)
#
Sets the maximum number of real-time query replanning statistics that
can be stored in the aqe_stats view. Further
statistics will be discarded. The default value is 5000. This
parameter can only be set at server start.