Зарегистрировать запрос можно следующими способами:

Все зарегистрированные запросы хранятся в локальном кеше, к которому можно обращаться с помощью представления pgpro_multiplan_local_cache.

План выполнения запроса можно изменить при помощи переменных оптимизатора, указаний pg_hint_plan при включённом расширении или других расширений, например aqo. Информация о совместимости pgpro_multiplan с другими расширениями представлена в Подразделе G.3.7.

Для заморозки изменённого плана выполнения запроса используйте функцию pgpro_multiplan_freeze. Для необязательного параметра plan_type можно задать значение serialized, hintset или template. Значение по умолчанию — serialized. Более подробно типы замороженных планов описаны в Подразделе G.3.3.

Все замороженные планы хранятся в представлении pgpro_multiplan_storage.

Пример ниже показывает, как использовать замороженные планы.

-- План, который необходимо улучшить
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)

-- Установите необходимый режим для pgpro_multiplan
SET pgpro_multiplan.mode = 'frozen';

-- Зарегистрируйте запрос
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)

-- Измените план выполнения запроса
-- Запустите сканирование индекса, отключив последовательное сканирование
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)

-- Снова включите последовательное сканирование
RESET enable_seqscan;

-- Заморозьте план выполнения запроса
SELECT pgpro_multiplan_freeze();
 pgpro_multiplan_freeze
 ------------------------
  t
 (1 row)

-- Теперь замороженный план используется со сканированием индекса
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)

С помощью параметра pgpro_multiplan.auto_capturing можно включить захват всех выполняемых запросов. Получить доступ ко всем захваченным запросам можно с помощью представления pgpro_multiplan_captured_queries.

-- Создайте таблицу '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;

-- Включите параметр auto_capturing
SET pgpro_multiplan.mode = 'baseline'
SET pgpro_multiplan.auto_capturing = 'on';

-- Выполните запрос
SELECT count(*) FROM a t1, a t2 WHERE t1.x = t2.x AND t1.y <= 1000 AND t2.y > 900;
count
-------
100
(1 row)

-- Выполните запрос ещё раз с другими константами, чтобы получить другой план
SELECT count(*) FROM a t1, a t2 WHERE t1.x = t2.x AND t1.y <= 10 AND t2.y > 900;
count
-------
0
(1 row)

-- Теперь захваченные планы можно увидеть в соответствующем представлении
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                                                              +
              |

-- Отключите автоматический захват. Это не повлияет на планы, которые были захвачены ранее.
SET pgpro_multiplan.auto_capturing = 'off';

Чтобы одобрить любой план из представления pgpro_multiplan_captured_queries, используйте функцию pgpro_multiplan_captured_approve() с параметрами dbid, sql_hash и plan_hash.

-- Вручную одобрите план со сканированием по индексам
SELECT pgpro_multiplan_captured_approve(5, 6079808577596655075, 2719320099967191582);

pgpro_multiplan_captured_approve
----------------------------------
t
(1 row)

-- Или одобрите планы, выбранные из списка захваченных планов
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)

-- Одобренные планы автоматически удаляются из хранилища захваченных планов
SELECT count(*) FROM pgpro_multiplan_captured_queries;

count
-------
0
(1 row)

-- Одобренные планы можно увидеть в представлении pgpro_multiplan_storage
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     |

Ниже показан пример использования разрешённых планов.

-- Включите параметр auto_capturing
SET pgpro_multiplan.mode = 'baseline'
SET pgpro_multiplan.auto_capturing = 'on';

-- Выполните запрос
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)

-- Затем выполните запрос снова с другими константами
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)

-- Отключите автоматический захват
SET pgpro_multiplan.auto_capturing = 'off';

-- Одобрите все захваченные планы
SELECT pgpro_multiplan_captured_approve(dbid, sql_hash, plan_hash)
FROM pgpro_multiplan_captured_queries;

pgpro_multiplan_captured_approve
----------------------------------
t
t
(2 rows)

-- План не меняется, поскольку он входит в число разрешённых
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)

-- В обычной ситуации в этом плане выполнялось бы последовательное сканирование для обеих таблиц, но сейчас применяется самый дешёвый из набора разрешённых
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)

При создании копий замороженных планов и их восстановлении обратите внимание на следующие ограничения:

Этот раздел описывает как создать копии замороженных планов и восстановить их в разных популярных сценариях.

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

-- Подключитесь к исходному серверу
psql (17.4)
Type "help" for help.

-- В этом примере 1000 планов хранится в представлении pgpro_multiplan_storage
postgres=# select count(*) from pgpro_multiplan_storage;
  count
-------
  1000
(1 row)

-- Скопируйте замороженные планы из базы данных postgres в таблицу
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';

-- Выгрузите таблицу в файл архива
$ pg_dump --table storage_copy -Ft postgres > storage_copy.tar

-- Отключитесь от исходного сервера
-- Подключитесь к целевому серверу
./psql postgres
psql (16.8)
Type "help" for help.

-- Создайте расширение pgpro_multiplan и включите его
postgres=# create extension pgpro_multiplan;
CREATE EXTENSION
SET pgpro_multiplan.mode = 'frozen';
SET

-- Этот сервер не содержит замороженных планов
postgres=# select count(*) from pgpro_multiplan_storage;
 count
-------
     0
(1 row)

-- Переместите файл выгрузки с замороженными планами в целевую файловую систему

-- Восстановите таблицу с замороженными планами из файла выгрузки
$ pg_restore --dbname postgres -Ft storage_copy.tar

-- Восстановите замороженные планы из таблицы с помощью функции pgpro_multiplan_restore
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)

-- Функция восстановила 1000 замороженных планов. Результат показан в виде пар sql_hash и const_hash
-- Замороженные запросы были однотипными и отличались только константами, поэтому sql_hash одинаковый для всех планов

-- Удалите таблицу, используемую для восстановления планов
postgres=# DROP TABLE storage_copy;
DROP TABLE

-- Целевой сервер теперь тоже хранит 1000 замороженных планов
postgres=# select count(*) from pgpro_multiplan_storage;
 count
-------
  1000
(1 row)

-- Выключите pgpro_multiplan и выполните запрос
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)

-- Включите pgpro_multiplan и выполните тот же запрос ещё раз
-- Теперь используется один из восстановленных планов
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)

Представление pgpro_multiplan_storage содержит подробную информацию обо всех замороженных и разрешённых планах. Столбцы представления показаны в Таблице G.2.

Представление pgpro_multiplan_local_cache содержит подробную информацию о зарегистрированных и замороженных операторах в локальном кеше. Столбцы представления показаны в Таблице G.3.

Представление pgpro_multiplan_captured_queries содержит подробную информацию обо всех запросах, отслеживаемых в сеансах. Столбцы представления показаны в Таблице G.4.

Представление pgpro_multiplan_stats предоставляет статистику использования замороженных и разрешённых планов. Столбцы представления показаны в Таблице G.5.

Представление aqe_triggers содержит информацию об индивидуальных значениях триггеров перепланирования. Столбцы представления показаны в Таблице G.6.

Представление aqe_stats содержит сводную статистику о переоптимизациях с помощью перепланирования запросов в реальном времени. Это представление содержит одну строку для каждой комбинации идентификатора базы данных, запроса и плана выполнения. Столбцы представления показаны в Таблице G.7.