Postgres Pro Enterprise : Documentation: 17: G.2. pgpro_multiplan — save a specific plan of a parameterized query for future usage : Postgres Professional

G.2. pgpro_multiplan — save a specific plan of a parameterized query for future usage
Prev	Up	Appendix G. Postgres Pro Modules and Extensions Shipped as Individual Packages	Home	Next

G.2. pgpro_multiplan — save a specific plan of a parameterized query for future usage #

G.2.1. Description
G.2.2. Installation
G.2.3. Usage
G.2.4. Frozen Plan Types
G.2.5. Compatibility with Other Extensions
G.2.6. Frozen Query Identification
G.2.7. Automatic Type Casting
G.2.8. Views
G.2.9. Functions
G.2.10. Configuration Parameters

G.2.1. Description #

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.

G.2.2. Installation #

The pgpro_multiplan extension is provided with Postgres Pro Enterprise as a separate pre-built package pgpro-multiplan-ent-17 (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.2.5.
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.

G.2.3. Usage #

There are two ways to use pgpro_multiplan: either with frozen plans or with allowed plans.

G.2.3.1. Frozen 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.

G.2.3.1.1. Registering a Query #

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 $n parameters (same as in PREPARE statement_name AS). You can describe each parameter type with the optional parameter_type argument of the function or choose not to define parameter types explicitly. In the latter case, 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
  ->  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))

G.2.3.1.2. Modifying the Query Execution Plan #

A query execution plan can be modified using optimizer variables, pg_hint_plan hints if the extension is enabled, or other extensions that allow changing the query plan, such as aqo. For information on compatibility of pgpro_multiplan with other extensions, see Section G.2.5.

G.2.3.1.3. Freezing the Query Execution Plan #

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

G.2.3.1.4. Frozen Plan Example #

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
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;
SET enable_seqscan = 'off';

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

G.2.3.2. Allowed Plans #

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.

To add the plan created by the standard planner to the set of allowed plans, follow these steps:

Capture the plan
Approve the plan

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.

Note

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.

G.2.3.2.1. Capturing a Plan #

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

G.2.3.2.2. Approving a Plan #

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         | <>
hintstr      | Leading(("t1" "t2" )) HashJoin("t1" "t2")  IndexScan("t2" "a_y_idx") SeqScan("t1")
-[ 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         | <>
hintstr      | Leading(("t2" "t1" )) HashJoin("t1" "t2")  IndexScan("t2" "a_y_idx") IndexScan("t1" "a_y_idx")

G.2.3.2.3. Allowed Plan Example #

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)

G.2.4. Frozen Plan Types #

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.

G.2.5. Compatibility with Other Extensions #

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'

G.2.6. Frozen Query Identification #

A frozen query in the current database is identified by a combination of sql_hash and const_hash.

sql_hash is a hash generated based on the parse tree, ignoring parameters and constants. Field and table aliases are not ignored. Therefore, the same query with different aliases will have different sql_hash values.

const_hash is a hash generated based on all constants involved in the query. Constants with the same value but different types, such as 1 and '1', will produce different hash values.

G.2.7. Automatic Type Casting #

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)

G.2.8. Views #

G.2.8.1. The `pgpro_multiplan_storage` View #

The pgpro_multiplan_storage view provides detailed information about all frozen statements. 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
`valid`	`boolean`	`FALSE` if the plan was invalidated the last time it was used
`query_string`	`text`	Query registered by the `pgpro_multiplan_register_query` function
`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: `serialized`, `hintset`, or `template`
`hintstr`	`text`	Set of hints formed based on the frozen plan
`wildcards`	`text`	Wildcards used for the `template` plan, NULL for other plan types

G.2.8.2. The `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

G.2.8.3. The `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
`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

G.2.8.4. The `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

G.2.9. Functions #

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.

G.2.10. Configuration Parameters #

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

Prev	Up	Next
G.1. pgpro_anonymizer — mask or replace sensitive data	Home	G.3. pgpro_pwr — workload reports

epub pdf