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

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

-- Разрешите использование замороженных планов
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)

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

-- Создайте таблицу 'a'
CREATE TEMP TABLE a AS (SELECT * FROM generate_series(1,1000) AS x);
CREATE INDEX ON a(x);
ANALYZE;

-- Разрешите использование базовых планов
SET pgpro_multiplan.mode = 'baseline';

-- Зарегистрируйте запрос
SELECT sql_hash sql_hash, const_hash const_hash
  FROM pgpro_multiplan_register_query('SELECT count(*) FROM a WHERE x = 1 OR (x > 11 AND x < 22) OR x = 22');
        sql_hash        | const_hash
-----------------------+-------------
  -6037606140259443514 | 2413041345
(1 row)

-- Проверьте план выполнения запроса
EXPLAIN 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
    Hint string: BitmapScan("a" "a_x_idx")
    ->  Aggregate (actual rows=1 loops=1)
          ->  Bitmap Heap Scan on a (actual rows=12 loops=1)
(5 rows)

-- Добавьте план выполнения запроса в набор базовых планов
SELECT pgpro_multiplan_freeze('baseline');
  pgpro_multiplan_freeze
------------------------
  t
(1 row)

-- Теперь можно увидеть добавленный план с помощью соответствующего представления
SELECT * FROM pgpro_multiplan_storage \gx
-[ RECORD 1 ]-+---------------------------------------------------------------------
dbid          | 5
sql_hash      | -6037606140259443514
const_hash    | 0
plan_hash     | 487722818968417375
valid         | t
cost          | 36.785
sample_string | SELECT count(*) FROM a WHERE x = 1 OR (x > 11 AND x < 22) OR x = 22
query_string  | SELECT count(*) FROM a WHERE x = $1 OR (x > $2 AND x < $3) OR x = $4
paramtypes    |
query         | <>
plan_type     | baseline
plan          | <>
hintstr       | BitmapScan("a" "a_x_idx")
wildcards     |

-- План из набора базовых планов теперь применяется при выполнении соотвествующего запроса
SET enable_bitmapscan = FALSE;
EXPLAIN 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: baseline
    Hint string: BitmapScan("a" "a_x_idx")
    ->  Aggregate (actual rows=1 loops=1)
          ->  Bitmap Heap Scan on a (actual rows=12 loops=1)
(5 rows)

RESET enable_bitmapscan;

В примере ниже показано, как зарегистрировать подготовленный оператор с помощью функции pgpro_multiplan_register_query и заморозить план выполнения оператора.

-- Создайте таблицу 'a'
CREATE TABLE a AS (SELECT * FROM generate_series(1,30) AS x);
CREATE INDEX ON a(x);
ANALYZE;

-- Создайте подготовленный оператор
PREPARE stmt AS SELECT count(*) FROM a
WHERE x = $2 OR x < $1 OR x = 10;


-- Зарегистрируйте оператор. Обратите внимание, что номера и позиции параметров $1 и $2
-- в параметризованном шаблоне запроса совпадают с номерами и позициями параметров
-- в подготовленном операторе
SELECT sql_hash, const_hash
FROM pgpro_multiplan_register_query('SELECT count(*) FROM a
WHERE x = $2 OR x < $1 OR x = $3');

-- Выполните подготовленный оператор с параметрами
EXPLAIN (COSTS OFF, TIMING OFF) EXECUTE stmt(10, 1);
                        QUERY PLAN
----------------------------------------------------------
 Custom Scan (MultiplanScan)
   Plan is: tracked
   SQL hash: -1476198425806211485
   Const hash: 0
   Plan hash: 0
   ->  Aggregate
         ->  Seq Scan on a
               Filter: ((x = $2) OR (x < $1) OR (x = $3))
(8 rows)

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

-- Выполните оператор ещё раз. Используется замороженный план
EXPLAIN (COSTS OFF, TIMING OFF) EXECUTE stmt(10, 1);
                        QUERY PLAN
----------------------------------------------------------
 Custom Scan (MultiplanScan)
   Plan is: frozen, serialized
   SQL hash: -1476198425806211485
   Const hash: 0
   Plan hash: 0
   ->  Aggregate
         ->  Seq Scan on a
               Filter: ((x = $2) OR (x < $1) OR (x = $3))
(8 rows)

В следующем примере показано, как автоматически зарегистрировать выполняемый подготовленный оператор.

-- Создайте подготовленный оператор
PREPARE stmt AS SELECT count(*) FROM a
WHERE x = $2 OR x < $1 OR x = 10;

-- Включите автоматическую регистрацию выполняемых операторов
SET pgpro_multiplan.auto_tracking = on;

-- Выполните подготовленный оператор с параметрами
EXPLAIN (COSTS OFF, TIMING OFF) EXECUTE stmt(10, 1);
                        QUERY PLAN
----------------------------------------------------------
 Custom Scan (MultiplanScan)
   Plan is: tracked
   SQL hash: -1476198425806211485
   Const hash: 0
   Plan hash: 0
   ->  Aggregate
         ->  Seq Scan on a
               Filter: ((x = $2) OR (x < $1) OR (x = $3))
(8 rows)

-- Отключите автоматическую регистрацию
SET pgpro_multiplan.auto_tracking = off;

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

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

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

SELECT pgpro_multiplan_restore(s.query_string, s.sample_string, s.hintstr, s.paramtypes, s.plan_type, s.wildcards, NULL)
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, sample_string, hintstr, paramtypes, 'serialized', NULL, NULL)
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 автоматически добавляет планы, созданные с помощью AQE, в список разрешённых планов.

Чтобы включить эту функциональность, выполните следующие шаги:

В следующем примере показано, как использовать управление планами в реальном времени.

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

-- Включите AQE и триггер количества обработанных кортежей узлов
SET aqe_enable = 'on';
SET aqe_rows_underestimation_rate_trigger = 2;

-- Настройте режимы pgpro_multiplan
SET pgpro_multiplan.mode = 'baseline';
SET pgpro_multiplan.aqe_mode = 'auto_approve_plans';

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

-- Запрос был переоптимизирован с помощь AQE в соответствии с триггером
-- План выполнения запроса был сохранён в список базовых (разрешённых) планов

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

-- Выключите AQE
RESET aqe_enable;
RESET aqe_rows_underestimation_rate_trigger;

-- Выполните тот же запрос и посмотрите, что используется ранее сохранённый план
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)

Расширение pgpro_multiplan разрешает переопределять и настраивать глобальные значения триггеров AQE> для отдельных запросов. Глобальные триггеры указываются в параметрах конфигурации aqe_sql_execution_time_trigger, aqe_rows_underestimation_rate_trigger и aqe_backend_memory_used_trigger.

Чтобы использовать эту функциональность, выполните следующие шаги:

Все индивидуальные значения триггеров показаны в представлении aqe_triggers. Параметр pgpro_multiplan.aqe_max_items указывает максимальное количество хранимых значений триггеров.

pgpro_multiplan может собирать статистику AQE для всех выражений, которые рассматриваются для переоптимизации. Эта статистика хранится в разделяемой памяти до выключения сервера и не реплицируется.

Выполните следующие шаги для включения сбора статистики:

Все статистические значения показаны в представлении aqe_stats. Параметр pgpro_multiplan.aqe_max_stats указывает максимальное количество хранимых статистических значений. Дальнейшие статистические значения игнорируются.

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

Представление pgpro_multiplan_local_cache содержит подробную информацию о замороженных планах в локальном кеше. Замороженный план добавляется в локальный кеш, если он был использован хотя бы один раз. Размер локального кеша ограничивается параметром pgpro_multiplan.max_local_cache_size. Столбцы представления показаны в Таблице G.5.

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

Представление pgpro_multiplan_stats предоставляет статистику использования планов. Столбцы представления показаны в Таблице G.7.

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

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