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-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.
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
----------------------+------------
5393873830515778388 | 15498345
(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))
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.
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.
The below example illustrates the usage of pgpro_multiplan.
-- 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
----------------------+------------
5393873830515778388 | 15498345
(1 row)
-- Modify the query execution plan
SET enable_seqscan = 'off';
Custom Scan (MultiplanScan) (actual rows=1 loops=1)
Plan is: tracked
SQL hash: 5393873830515778388
Const hash: 15498345
-> Aggregate (actual rows=1 loops=1)
-> Index Only Scan using a_x_idx2 on a (actual rows=10 loops=1)
Filter: ((x = 1) OR ((x > $2) AND (x < $1)) OR (x = $1))
Rows Removed by Filter: 20
Heap Fetches: 30
(5 rows)
-- Freeze the query execution plan
SELECT pgpro_multiplan_freeze();
RESET enable_seqscan;
There are two types of frozen plans: serialized plans and hint-set 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.
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..0.00 rows=1 width=8)
Plan is: tracked
SQL hash: -5166001356546372387
Const 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..0.00 rows=1 width=8)
Plan is: tracked
SQL hash: -5166001356546372387
Const 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)
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 |
hintstr | text | Set of hints formed based on the frozen plan |
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 |
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 |
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 and hintset.
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. 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_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.
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. 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.