There are the following ways to register a query:

All registered queries are stored in the local cache that can be accessed using the pgpro_multiplan_local_cache view.

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

To freeze a modified query plan, use the pgpro_multiplan_freeze function. The optional plan_type parameter can be set to either serialized or hintset. The default value is serialized. For detailed information on types of frozen plans, see Supported Modes and Plan Types.

All frozen plans are stored in the pgpro_multiplan_storage view.

The example below illustrates how to use frozen plans.

-- 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 that you set a required mode for pgpro_multiplan
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 pgpro_multiplan.auto_capturing parameter allows capturing all executed queries. You can access all captured queries using the pgpro_multiplan_captured_queries view.

-- 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.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;
constants     | 1000, 900
prep_const    |
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;
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  | 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
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 following example illustrates the use of allowed plans.

-- Enable the auto_capturing parameter
SET pgpro_multiplan.mode = 'baseline'
SET pgpro_multiplan.auto_capturing = '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
--------------------------------------------------------------------------------
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
--------------------------------------------------------------------------------
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: baseline
  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: baseline
  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)

When you back up and restore plans, take into account the following limitations:

This section describes how to back up and restore frozen plans in different popular scenarios.

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';

-- 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.mode = 'frozen';
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.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)

The pgpro_multiplan_storage view provides detailed information about all plans. The columns of the view are shown in Table G.4.

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

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.

The pgpro_multiplan_stats view provides statistics about the plan usage. The columns of the view are shown in Table G.7.