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

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

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)

Real-time plan management adds plans produced by AQE to the list of allowed plans automatically.

To enable this functionality, do the following:

The following example demonstrates how to use real-time plan management.

INSERT INTO a SELECT x, x AS y FROM generate_series(1001,2000) x;

-- Enable AQE and its node tuples underestimation trigger
SET aqe_enable = 'on';
SET aqe_rows_underestimation_rate_trigger = 2;

-- Configure pgpro_multiplan modes
SET pgpro_multiplan.mode = 'baseline';
SET pgpro_multiplan.aqe_mode = 'auto_approve_plans';

-- Execute a query
SELECT count(*) FROM a t1, a t2 WHERE t1.x = t2.x AND t1.y <= 2000 AND t2.y > 900;
  count
-------
  1100
(1 row)

-- The query was reoptimized by AQE according to its trigger
-- The query execution plan was saved to a baseline (a list of allowed plans)

-- Now you can see the plan using the corresponding view
SELECT * FROM pgpro_multiplan_storage \gx
-[ RECORD 1 ]-+------------------------------------------------------------------------------------
dbid          | 16384
sql_hash      | 771426262442742808
const_hash    | 0
plan_hash     | -487722818968417375
valid         | t
cost          | 75.78750000000001
sample_string | SELECT count(*) FROM a t1, a t2 WHERE t1.x = t2.x AND t1.y <= 2000 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_type     | baseline
plan          | <>
hintstr       | Leading(("t1" "t2" )) HashJoin("t1" "t2")  IndexScan("t2" "a_y_idx") SeqScan("t1")
wildcards     |

-- Disable AQE
RESET aqe_enable;
RESET aqe_rows_underestimation_rate_trigger;

-- Execute the same query and see that the previously saved plan is used
explain analyze
SELECT count(*) FROM a t1, a t2 WHERE t1.x = t2.x AND t1.y <= 2000 AND t2.y > 900;
                                                                 QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------
 Custom Scan (MultiplanScan)  (cost=52.03..52.04 rows=1 width=8) (actual time=2.148..2.150 rows=1 loops=1)
   Plan is: baseline
   SQL hash: 771426262442742808
   Const hash: 0
   Plan hash: -487722818968417375
   ->  Aggregate  (cost=52.03..52.04 rows=1 width=8) (actual time=2.148..2.149 rows=1 loops=1)
         ->  Hash Join  (cost=13.78..51.65 rows=150 width=0) (actual time=1.390..2.051 rows=1100 loops=1)
               Hash Cond: (t1.x = t2.x)
               ->  Seq Scan on a t1  (cost=0.00..30.75 rows=1500 width=4) (actual time=0.028..0.597 rows=2000 loops=1)
                     Filter: (y <= 2000)
               ->  Hash  (cost=11.90..11.90 rows=150 width=4) (actual time=0.993..0.993 rows=1100 loops=1)
                     Buckets: 2048 (originally 1024)  Batches: 1 (originally 1)  Memory Usage: 55kB
                     ->  Index Scan using a_y_idx on a t2  (cost=0.28..11.90 rows=150 width=4) (actual time=0.026..0.695 rows=1100 loops=1)
                           Index Cond: (y > 900)
 Planning Time: 0.460 ms
 Execution Time: 2.194 ms
(16 rows)

The pgpro_multiplan extension allows you to override and adjust global AQE trigger values for individual queries. Global triggers are specified in the aqe_sql_execution_time_trigger, aqe_rows_underestimation_rate_trigger, and aqe_backend_memory_used_trigger configuration parameters.

To use this functionality, do the following:

All individual trigger values are shown in the aqe_triggers view. The pgpro_multiplan.aqe_max_items parameter specifies the maximum number of stored trigger values.

pgpro_multiplan can collect AQE statistics for all statements considered feasible for reoptimization. These statistics are stored in shared memory until a server shutdown and are not replicated.

Perform the following steps to enable statistics collection:

All statistics values are shown in the aqe_stats view. The pgpro_multiplan.aqe_max_stats parameter specifies the maximum number of collected statistics values. Further statistics are discarded.

Take into account the following specifics and limitations when using global prepared statements:

The pgpro_multiplan_storage view provides detailed information about all frozen and allowed 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 usage of frozen and allowed plans. The columns of the view are shown in Table G.7.

The aqe_triggers view provides information about individual AQE trigger values. The columns of the view are shown in Table G.8.

The aqe_stats view provides cumulative statistics about AQE 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.9.

The pgpro_global_prepared_statements view provides detailed information about all global prepared statements. The columns of the view are shown in Table G.10.