G.4. pgpro_multiplan — сохранение планов выполнения запросов для последующего использования #

G.4.1. Описание #

Расширение pgpro_multiplan позволяет сохранять планы выполнения запросов и использовать их при последующем выполнении тех же запросов, что помогает избежать повторной оптимизации идентичных запросов. Можно также использовать это расширение для фиксации определённого плана выполнения, если план, выбранный планировщиком, по каким-то причинам не подходит. pgpro_multiplan работает подобно Oracle SQL Plan Management (управление SQL-планами).

G.4.2. Установка #

Расширение pgpro_multiplan предоставляется вместе с Postgres Pro Enterprise в виде отдельного пакета pgpro-multiplan-ent-18 (подробные инструкции по установке приведены в Главе 17). Чтобы включить pgpro_multiplan, выполните следующие действия:

  1. Добавьте имя библиотеки в переменную shared_preload_libraries в файле postgresql.conf:

    shared_preload_libraries = 'pgpro_multiplan'

    Обратите внимание, что имена библиотек в переменной shared_preload_libraries должны добавляться в определённом порядке. Совместимость pgpro_multiplan с другими расширениями описана в Подразделе G.4.8.

  2. Перезагрузите сервер баз данных, чтобы изменения вступили в силу.

    Чтобы убедиться, что библиотека pgpro_multiplan установлена правильно, вы можете выполнить следующую команду:

    SHOW shared_preload_libraries;
  3. Создайте расширение pgpro_multiplan, выполнив следующий запрос:

    CREATE EXTENSION pgpro_multiplan;

    Расширение pgpro_multiplan использует кеш разделяемой памяти, который инициализируется только при запуске сервера, поэтому данная библиотека также должна предзагружаться при запуске. Расширение pgpro_multiplan следует создать в каждой базе данных, где требуется управление запросами.

  4. Включите расширение pgpro_multiplan, которое выключено по умолчанию. Для этого укажите необходимые режимы в параметре pgpro_multiplan.mode. За дополнительной информацией обратитесь к разделу Поддерживаемые режимы и типы планов.

    Включить pgpro_multiplan можно одним из следующих способов:

    • Чтобы активировать расширение для всех сеансов, задайте параметр pgpro_multiplan.mode в файле postgresql.conf.

    • Чтобы активировать расширение в текущем сеансе, используйте следующую команду:

      SET pgpro_multiplan.mode = 'frozen';
  5. При необходимости переноса данных pgpro_multiplan с главного на резервный сервер при помощи физической репликации, на обоих серверах необходимо задать значение параметра pgpro_multiplan.wal_rw=on. Также убедитесь, что на обоих серверах установлена одинаковая версия pgpro_multiplan, иначе репликация может работать некорректно.

G.4.3. Поддерживаемые режимы и типы планов #

Расширение pgpro_multiplan предоставляет следующие типы планов:

  • Замороженные планы

    Зафиксированные планы, которым отдаётся приоритет при выполнении соответствующих запросов. Запрос может иметь только один замороженный план.

    pgpro_multiplan поддерживает следующие типы замороженных планов:

    • Сериализованные планы

      Сериализованные представления планов. Эти планы преобразуются в выполняемые планы при первом выполнении соответствующих запросов.

      Сериализованные планы остаются действительными до тех пор, пока не изменятся метаданные запроса (такие как структуры таблиц, индексы и так далее). Например, если таблица, задействованная в плане, пересоздаётся, то этот план становится недействительным и игнорируется. Сериализованные планы действительны только в текущей базе данных и не могут быть скопированы в другую, поскольку они зависят от идентификаторов объектов (OID). По этой причине использовать сериализованные планы для временных таблиц не имеет смысла.

    • Планы с наборами указаний

      Наборы указаний, которые формируются на основе соответствующих планов выполнения в момент заморозки. Набор указаний включает типы соединений, порядок соединений, методы доступа к данным и переменные окружения оптимизатора, значения которых отличаются от используемых по умолчанию. Эти указания соответствуют указаниям, которые поддерживаются расширением pg_hint_plan. Таким образом, для использования планов с наборами указаний необходимо включить расширение pg_hint_plan.

      Если найден соответствующий замороженный запрос, указания передаются pg_hint_plan для генерации выполняемого плана. Если расширение pg_hint_plan отключено, указания игнорируются и используется план, сформированный оптимизатором Postgres Pro. Планы с наборами указаний не зависят от идентификаторов объектов (OID) и остаются действительным при пересоздании таблиц, добавлении полей и других изменениях.

  • Планы с шаблонами

    Эти планы похожи на замороженные планы с наборами указаний, но используют шаблоны для имён таблиц. Если у запроса нет соответствующего замороженного плана, pgpro_multiplan пытается найти для этого запроса подходящий план с шаблонами.

    Эти планы тоже основаны на наборах указаний и требуют, чтобы расширение pg_hint_plan было включено. Однако такие планы могут применяться только для запросов с именами таблиц, которые соответствуют регулярному выражению POSIX, указанному в параметре pgpro_multiplan.wildcards. Значение pgpro_multiplan.wildcards замораживается вместе с соответствующим запросом. Если расширение pg_hint_plan отключено, указания игнорируются и используется план, сформированный оптимизатором Postgres Pro.

  • Базовые планы

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

    Как и замороженные планы с наборами указаний, базовые планы основаны на наборах указаний и требуют, чтобы расширение pg_hint_plan было включено. Если расширение pg_hint_plan отключено, указания игнорируются и используется план, сформированный оптимизатором Postgres Pro.

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

G.4.4. Идентификация планов #

В зависимости от типа планы идентифицируются следующими способами:

  • Замороженные планы (и сериализованные, и с наборами указаний) идентифицируются по комбинации sql_hash и const_hash.

    sql_hash вычисляется на основе дерева разбора без учёта параметров и констант. Псевдонимы полей и таблиц не игнорируются, поэтому одинаковые запросы с разными псевдонимами будут иметь разные значения sql_hash.

    const_hash вычисляется на основе всех констант, использующихся в запросе. Даже если значения констант совпадают, но типы данных различаются (например, 1 и '1'), значения хеша будут разными.

  • Планы с шаблонами идентифицируются по комбинации sql_hash и table_hash.

    sql_hash вычисляется на основе дерева разбора без учёта имён таблиц, параметров и констант.

    table_hash вычисляется на основе имён таблиц, которые не соответствуют регулярному выражению, указанному в параметре pgpro_multiplan.wildcards.

    const_hash для этих планов всегда равен 0.

  • Базовые планы идентифицируются по комбинации sql_hash и plan_hash.

    sql_hash вычисляется так же, как и для замороженных планов.

    plan_hash — это внутренний идентификатор плана.

    const_hash для этих планов всегда равен 0.

G.4.5. Заморозка планов #

Чтобы заморозить определённые планы выполнения запроса для дальнейшего использования, выполните следующие шаги:

  1. Используйте параметр pgpro_multiplan.mode, чтобы указать разделённый запятыми список с типами планов, которые будут создаваться. За подробной информацией о типах планов обратитесь к разделу Поддерживаемые режимы и типы планов.

    SET pgpro_multiplan.mode = 'frozen, wildcards, baseline, statistics';
  2. Зарегистрируйте запрос, для которого нужно зафиксировать план, одним из следующих способов:

    • Используйте функцию pgpro_multiplan_register_query, чтобы зарегистрировать запрос вручную.

      SELECT pgpro_multiplan_register_query(query_string, parameter_type, ...);

      Здесь query_string — запрос с параметрами $n (аналогично PREPARE statement_name AS). Можно описать каждый тип параметра, используя необязательный аргумент функции parameter_type, или отказаться от явного определения типов параметров. В последнем случае Postgres Pro пытается определить тип каждого параметра из контекста. Эта функция возвращает уникальную пару sql_hash и const_hash. После этого pgpro_multiplan будет отслеживать выполнение запросов, соответствующих сохранённому шаблону параметризованного запроса.

      -- Создайте таблицу 'a'
      CREATE TABLE a AS (SELECT * FROM generate_series(1,30) AS x);
      CREATE INDEX ON a(x);
      ANALYZE;
      
      -- Зарегистрируйте запрос
      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)

      Обратите внимание, что при регистрации подготовленного оператора номера и позиции параметров в параметризованном шаблоне запроса должны совпадать с номерами и позициями параметров в подготовленном операторе. В противном случае шаблон игнорируется.

    • Установите для параметра pgpro_multiplan.auto_tracking значение on, чтобы автоматически регистрировать запросы, выполняемые с помощью команд EXPLAIN и EXPLAIN EXECUTE.

      -- Включите pgpro_multiplan.auto_tracking
      SET pgpro_multiplan.auto_tracking = on;
      
      -- Выполните EXPLAIN для непараметризованного запроса
      
      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
        Plan 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))
      
      -- Отключите pgpro_multiplan.auto_tracking
      SET pgpro_multiplan.auto_tracking = off;
  3. Измените план выполнения запроса, если необходимо. Это можно сделать с помощью параметров конфигурации, указаний расширения pg_hint_plan, если оно включено, или других расширений, которые позволяют изменять план запроса, например aqo. За информацией о совместимости pgpro_multiplan с этими расширениями обратитесь к разделу Совместимость с другими расширениями.

  4. Заморозьте план выполнения запроса с помощью функции pgpro_multiplan_freeze. Для необязательного аргумента plan_type укажите значение serialized, hintset, template или baseline в зависимости от типа плана, который нужно создать. Если этот аргумент опущен, по умолчанию используется serialized.

    SELECT pgpro_multiplan_freeze('serialized');
      pgpro_multiplan_freeze
      ------------------------
      t
      (1 row)
  5. Если создаётся план с набором указаний, план с шаблоном или разрешённый план, включите расширение pg_hint_plan. За подробной информацией обратитесь к разделу Совместимость с другими расширениями.

  6. Если создаётся план с шаблоном, используйте параметр pgpro_multiplan.wildcards, чтобы указать регулярное выражение POSIX, используемое для проверки соответствия имён таблиц, задействованных в запросах.

G.4.5.1. Хранение и использование планов #

Все планы добавляются в глобальное хранилище pgpro_multiplan. Доступ к нему можно получить с помощью представления pgpro_multiplan_storage.

При запуске определённого запроса pgpro_multiplan ищет подходящий план в хранилище. За информацией о порядке поиска и применения планов обратитесь к разделу Поддерживаемые режимы и типы планов.

pgpro_multiplan также использует локальный кеш для замороженных планов, чтобы ускорить планирование и выполнение соответствующих запросов. Замороженный план добавляется в локальный кеш, если он был использован хотя бы один раз. Доступ к планам в локальном кеше можно получить с помощью представления pgpro_multiplan_local_cache. Размер локального кеша ограничивается параметром pgpro_multiplan.max_local_cache_size.

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

G.4.5.2. Пример добавления замороженного плана #

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

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

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

G.4.5.3. Пример добавления плана с шаблонами #

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

-- Создайте две таблицы и вставьте в них данные
CREATE TABLE data_2026(id INT, data1 INT, data2 INT);
CREATE TABLE data_2025(id INT, data1 INT, data2 INT);

INSERT INTO data_2026 SELECT g, g%10, g*100 FROM generate_series(1,10000) g;
INSERT INTO data_2025 SELECT g, g%10, g*100 FROM generate_series(1,10000) g;
INSERT 0 10000

-- Создайте индексы по этим таблицам
CREATE INDEX ON data_2026(id);
CREATE INDEX ON data_2026(data1);
CREATE INDEX ON data_2025(id);
CREATE INDEX ON data_2025(data1);

ANALYZE data_2026, data_2025;

-- pgpro_multiplan отключён
-- Выполните запрос, ссылающийся на таблицу data_2026, и проверьте его план выполнения
-- Планировщик выбирает Bitmap Heap Scan
EXPLAIN SELECT * FROM data_2026 WHERE id > 100 AND data1 = 5;
                                      QUERY PLAN
--------------------------------------------------------------------------------------
 Bitmap Heap Scan on data_2026  (cost=16.03..86.03 rows=990 width=12)
   Recheck Cond: (data1 = 5)
   Filter: (id > 100)
   ->  Bitmap Index Scan on data_2026_data1_idx  (cost=0.00..15.79 rows=1000 width=0)
         Index Cond: (data1 = 5)
(5 rows)

-- Выполните запрос, ссылающийся на таблицу data_2025. Также используется Bitmap Heap Scan
EXPLAIN SELECT * FROM data_2025 WHERE id > 200 AND data1 = 7;
                                      QUERY PLAN
--------------------------------------------------------------------------------------
 Bitmap Heap Scan on data_2025  (cost=16.03..86.03 rows=980 width=12)
   Recheck Cond: (data1 = 7)
   Filter: (id > 200)
   ->  Bitmap Index Scan on data_2025_data1_idx  (cost=0.00..15.79 rows=1000 width=0)
         Index Cond: (data1 = 7)
(5 rows)

-- Включите pgpro_multiplan и разрешите использование планов с шаблонами
SET pgpro_multiplan.mode = 'wildcards';
SET pgpro_multiplan.show_hint_string = true;

-- Установите шаблон для всех таблиц data_*
SET pgpro_multiplan.wildcards = 'data_.*';

-- Зарегистрируйте запрос, ссылающийся на таблицу data_2026
SELECT pgpro_multiplan_register_query(
    'SELECT * FROM data_2026 WHERE id > $1 AND data1 = $2',
    'int', 'int'
);
 pgpro_multiplan_register_query
--------------------------------
 (-6003069785000534667,0)
(1 row)

-- Отключите Bitmap Scan
SET enable_bitmapscan = 'off';

-- Выполните запрос, ссылающийся на таблицу data_2026, с параметрами
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF)
SELECT * FROM data_2026 WHERE id > 5000 AND data1 = 3;
                                      QUERY PLAN
---------------------------------------------------------------------------------------
 Custom Scan (MultiplanScan) (actual rows=500.00 loops=1)
   Plan is: tracked
   Hint string: Set(enable_bitmapscan "off") IndexScan("data_2026" "data_2026_id_idx")
   SQL hash: -6003069785000534667
   Const hash: 0
   Plan hash: 0
   Buffers: shared hit=28 read=16
   ->  Index Scan using data_2026_id_idx on data_2026 (actual rows=500.00 loops=1)
         Index Cond: (id > $1)
         Filter: (data1 = $2)
         Rows Removed by Filter: 4500
         Index Searches: 1
         Buffers: shared hit=28 read=16
 Planning:
   Buffers: shared hit=3
 Planning Time: 0.821 ms
 Execution Time: 3.469 ms
(17 rows)

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

-- Включите Bitmap Scan
RESET enable_bitmapscan;

-- Выполните первые два запроса из этого примера ещё раз
-- Из-за использования шаблона pgpro_multiplan теперь выбирает Index Scan
-- для обеих таблиц, хотя план был заморожен только для data_2026
EXPLAIN SELECT * FROM data_2026 WHERE id > 100 AND data1 = 5;
                                           QUERY PLAN
------------------------------------------------------------------------------------------------
 Custom Scan (MultiplanScan)  (cost=0.29..246.16 rows=990 width=12)
   Plan is: frozen, template
   Hint string: Set(enable_bitmapscan "off") IndexScan("data_2026")
   SQL hash: -311628753062991878
   Const hash: 2654435769
   Plan hash: 0
   ->  Index Scan using data_2026_data1_idx on data_2026  (cost=0.29..246.16 rows=990 width=12)
         Index Cond: (data1 = 5)
         Filter: (id > 100)
(9 rows)

EXPLAIN SELECT * FROM data_2025 WHERE id > 200 AND data1 = 7;
                                           QUERY PLAN
------------------------------------------------------------------------------------------------
 Custom Scan (MultiplanScan)  (cost=0.29..246.16 rows=980 width=12)
   Plan is: frozen, template
   Hint string: Set(enable_bitmapscan "off") IndexScan("data_2025")
   SQL hash: -311628753062991878
   Const hash: 2654435769
   Plan hash: 0
   ->  Index Scan using data_2025_data1_idx on data_2025  (cost=0.29..246.16 rows=980 width=12)
         Index Cond: (data1 = 7)
         Filter: (id > 200)
(9 rows)

-- Проверьте информацию в хранилище pgpro_multiplan
SELECT plan_type, hintstr, wildcards, sample_string
FROM pgpro_multiplan_storage
WHERE plan_type = 'template';
 plan_type |                           hintstr                            | wildcards |                     sample_string                      
-----------+--------------------------------------------------------------+-----------+--------------------------------------------------------
 template  | Set(enable_bitmapscan "off") IndexScan("pgpro_multiplan_0")  | data_.*   | SELECT * FROM data_2026 WHERE id > 5000 AND data1 = 3;
(1 row)

G.4.5.4. Пример добавления набора базовых планов #

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

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

G.4.5.5. Примеры заморозки планов для подготовленных операторов #

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

G.4.6. Захват и одобрение планов #

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

  1. Укажите для параметра pgpro_multiplan.mode значение baseline.

  2. Включите расширение pg_hint_plan. За подробной информацией обратитесь к разделу Совместимость с другими расширениями.

  3. Укажите для параметра pgpro_multiplan.auto_capturing значение on, чтобы включить захват всех выполняемых запросов. Получить доступ к этим захваченным запросам можно в представлении pgpro_multiplan_captured_queries.

  4. Одобрите любой захваченный план с помощью функции pgpro_multiplan_captured_approve с указанными аргументами dbid, sql_hash и plan_hash.

  5. Установите для параметра pgpro_multiplan.auto_capturing значение off после завершения захвата.

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

-- Разрешите использование базовых планов и включите автоматический захват
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;
hint_str      | Leading(("t1" "t2" )) HashJoin("t1" "t2")  IndexScan("t2" "a_y_idx") SeqScan("t1")
explain_plan  | Aggregate  (cost=36.77..36.78 rows=1 width=8)
              |   Output: count(*)
              |   ->  Hash Join  (cost=11.28..36.52 rows=100 width=0)
              |         Hash Cond: (t1.x = t2.x)
              |         ->  Seq Scan on public.a t1  (cost=0.00..20.50 rows=1000 width=4)
              |               Output: t1.x, t1.y
              |               Filter: (t1.y <= 1000)
              |         ->  Hash  (cost=10.03..10.03 rows=100 width=4)
              |               Output: t2.x
              |               Buckets: 1024  Batches: 1  Memory Usage: 12kB
              |               ->  Index Scan using a_y_idx on public.a t2  (cost=0.28..10.03 rows=100 width=4)
              |                     Output: t2.x
              |                     Index Cond: (t2.y > 900)
              | Query Identifier: -8984284243102644350
              |
-[ RECORD 2 ]-+-----------------------------------------------------------------------------------------------------
dbid          | 5
sql_hash      | 6079808577596655075
plan_hash     | 2719320099967191582
queryid       | -8984284243102644350
cost          | 18.997500000000002
sample_string | SELECT count(*) FROM a t1, a t2 WHERE t1.x = t2.x AND t1.y <= 10 AND t2.y > 900;
query_string  | SELECT count(*) FROM a t1, a t2 WHERE t1.x = t2.x AND t1.y <= $1 AND t2.y > $2;
hint_str      | Leading(("t2" "t1" )) HashJoin("t1" "t2")  IndexScan("t2" "a_y_idx") IndexScan("t1" "a_y_idx")
explain_plan  | Aggregate  (cost=18.99..19.00 rows=1 width=8)
              |   Output: count(*)
              |   ->  Hash Join  (cost=8.85..18.98 rows=1 width=0)
              |         Hash Cond: (t2.x = t1.x)
              |         ->  Index Scan using a_y_idx on public.a t2  (cost=0.28..10.03 rows=100 width=4)
              |               Output: t2.x, t2.y
              |               Index Cond: (t2.y > 900)
              |         ->  Hash  (cost=8.45..8.45 rows=10 width=4)
              |               Output: t1.x
              |               Buckets: 1024  Batches: 1  Memory Usage: 9kB
              |               ->  Index Scan using a_y_idx on public.a t1  (cost=0.28..8.45 rows=10 width=4)
              |                     Output: t1.x
              |                     Index Cond: (t1.y <= 10)
              | Query Identifier: -8984284243102644350
              |

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

-- Вручную одобрите план со сканированием индекса
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     |

G.4.7. Резервное копирование и восстановление планов #

Расширение pgpro_multiplan позволяет создавать резервные копии необходимых планов и затем восстанавливать эти планы в текущую базу данных. Это может быть полезно для переноса планов между базами данных или экземплярами серверов.

Чтобы создать резервную копию планов из определённой базы данных, используйте представление pgpro_multiplan_storage следующим образом:

CREATE TABLE storage_copy AS SELECT s.*
FROM pgpro_multiplan_storage s
JOIN pg_database d ON s.dbid = d.oid
WHERE d.datname = 'db_name';

Чтобы восстановить планы из резервной копии, вызовите функцию pgpro_multiplan_restore, например, следующим образом:

SELECT s.query_string, res.sql_hash IS NOT NULL AS success
FROM storage_copy s,
LATERAL pgpro_multiplan_restore(s.query_string, s.sample_string, s.hintstr, s.paramtypes, s.plan_type, s.wildcards, NULL) res;

Примечание

Планы могут быть восстановлены только при работающем расширении pg_hint_plan, см. раздел Совместимость с другими расширениями.

G.4.7.1. Особенности и ограничения #

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

  • Планы всегда восстанавливаются в текущую базу данных. Чтобы восстановить планы в другую базу данных, сначала подключитесь к ней. Таким образом, рекомендуется создавать резервные копии планов только из одной базы данных, как для db_name в примерах в этой секции. Если нужно перенести планы для нескольких баз данных, создайте для них отдельные резервные копии, подключитесь последовательно к каждой целевой базе данных и восстановите планы из соответствующих резервных копий.

  • Если копия планов создаётся из нескольких баз данных, и эти базы данных содержат разные планы для одинаковых запросов, восстанавливается только первый конфликтующий план.

  • Можно восстановить только планы для допустимых запросов, что означает, что все отношения, используемые в запросе, должны существовать в текущей базе данных.

G.4.7.2. Сценарии использования #

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

G.4.7.2.1. Обновление версии сервера #

Выполните шаги ниже, чтобы сохранить планы при обновлении сервера со старой версии с несовместимым хранилищем данных.

  1. Выполните резервное копирование планов до обновления.

    CREATE TABLE storage_copy AS SELECT s.*
    FROM pgpro_multiplan_storage s
    JOIN pg_database d ON s.dbid = d.oid
    WHERE d.datname = 'db_name';
  2. Обновите версию сервера.

  3. Восстановите планы.

    SELECT pgpro_multiplan_restore(query_string, NULL, hintstr, paramtypes, 'hintset', NULL, NULL)
    FROM storage_copy;
G.4.7.2.2. Миграция с sr_plan #

Чтобы мигрировать планы из устаревшего расширения sr_plan в pgpro_multiplan, выполните следующие шаги:

  1. Выполните резервное копирование планов из хранилища расширения sr_plan.

    CREATE TABLE storage_copy AS SELECT s.*
    FROM sr_plan_storage s
    JOIN pg_database d ON s.dbid = d.oid
    WHERE d.datname = 'db_name';
  2. Восстановите планы в базу данных с расширением pgpro_multiplan.

    SELECT pgpro_multiplan_restore(query_string, NULL, hintstr, paramtypes, 'hintset', NULL, NULL)
    FROM storage_copy;
G.4.7.2.3. Перенос планов между экземплярами серверов #

Чтобы перенести планы между двумя серверами, выполните следующее:

  1. Подключитесь к исходному серверу.

  2. Выполните резервное копирование планов в таблицу.

      CREATE TABLE storage_copy AS SELECT s.*
      FROM pgpro_multiplan_storage s
      JOIN pg_database d ON s.dbid = d.oid
      WHERE d.datname = 'db_name';
      
  3. Используйте утилиту pg_dump, чтобы выгрузить таблицу в файл.

    $ pg_dump --table storage_copy -Ft postgres > storage_copy.tar
  4. Подключитесь к целевому серверу и базе данных.

  5. Переместите созданный файл выгрузки в целевую файловую систему.

  6. Используйте утилиту pg_restore, чтобы восстановить таблицу с планами из файла выгрузки.

    $ pg_restore --dbname postgres -Ft storage_copy.tar
  7. Восстановите планы.

    SELECT pgpro_multiplan_restore(query_string, sample_string, hintstr, paramtypes, plan_type, wildcards, NULL)
    FROM storage_copy;
    
    DROP TABLE storage_copy;
G.4.7.2.4. Перенос планов из "песочницы" в обычное хранилище #

Чтобы перенести планы из "песочницы" в обычное хранилище, выполните следующие шаги:

  1. Установите для параметра pgpro_multiplan.sandbox значение on и выполните резервное копирование планов из "песочницы".

    SET pgpro_multiplan.sandbox = ON;
    
    CREATE TABLE storage_copy AS SELECT s.*
    FROM pgpro_multiplan_storage s
    JOIN pg_database d ON s.dbid = d.oid
    WHERE d.datname = 'db_name';
  2. Установите для параметра pgpro_multiplan.sandbox значение off и восстановите планы в обычное хранилище.

    SET pgpro_multiplan.sandbox = OFF;
    
    SELECT pgpro_multiplan_restore(query_string, sample_string, hintstr, paramtypes, plan_type, wildcards, NULL)
    FROM storage_copy;
G.4.7.2.5. Перенос планов между базами данных #

Чтобы перенести планы из одной базы данных в другую, подключитесь к целевой базе данных и восстановите планы, как показано ниже.

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

Здесь db_name — это имя базы данных, из которой вы хотите перенести планы.

G.4.7.2.6. Пример переноса планов #

Этот пример показывает, как перенести планы с одного экземпляра сервера на другой.

-- Подключитесь к исходному серверу
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)

G.4.8. Совместимость с другими расширениями #

Для обеспечения совместимости pgpro_multiplan с другими расширениями необходимо в файле postgresql.conf в переменной shared_preload_libraries указать имена библиотек в определённом порядке:

  • pg_hint_plan: расширение pgpro_multiplan необходимо загрузить после pg_hint_plan.

    shared_preload_libraries = 'pg_hint_plan, pgpro_multiplan'
  • aqo: расширение pgpro_multiplan необходимо загружать до aqo.

    shared_preload_libraries = 'pgpro_multiplan, aqo'
  • pgpro_stats: расширение pgpro_multiplan необходимо загружать после pgpro_stats.

    shared_preload_libraries = 'pgpro_stats, pgpro_multiplan'

G.4.9. Автоматическое приведение типов #

pgpro_multiplan пытается автоматически приводить типы констант из запроса к типам параметров запроса, для которого был заморожен план. Если привести типы невозможно, план игнорируется.

SELECT sql_hash, const_hash
FROM pgpro_multiplan_register_query('SELECT count(*) FROM a
WHERE x = $1', 'int');

-- Приведение типов возможно
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)

-- Приведение типов возможно
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)

-- Приведение типов невозможно
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.4.10. Статистика использования планов #

Чтобы собирать статистику об использовании планов, укажите для параметра pgpro_multiplan.mode значение statistics. Доступ к этой статистике можно получить через представление pgpro_multiplan_stats. Параметр pgpro_multiplan.max_stats задаёт максимальное количество собираемых статистических значений. При достижении этого ограничения дальнейшая статистика игнорируется. Если план изменился, статистика использования этого плана сбрасывается и пересчитывается с новыми идентификаторами плана.

Для получения более детальной статистики планирования и выполнения запросов можно использовать расширение pgpro_stats (см. секцию Совместимость с другими расширениями). Доступ к этой статистике можно получить с помощью представления pgpro_stats_statements.

Можно объединить информацию из представлений pgpro_multiplan_stats и pgpro_stats_statements по полю planid. Чтобы включить вычисление идентификаторов планов в ядре, установите для параметра конфигурации compute_plan_id значение on. В противном случае для этого поля задаётся значение 0.

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

-- Включите сбор статистики
SET pgpro_multiplan.mode = 'frozen, statistics';

-- Включите вычисление идентификаторов планов в ядре
SET compute_plan_id = on;

-- Выполните запрос
SELECT count(*) FROM a
WHERE x = 1 OR (x > 11 AND x < 22) OR x = 22;

 count
-------
    12
(1 row)

-- Теперь можно посмотреть статистику использования плана
SELECT * FROM pgpro_multiplan_stats;

 dbid |      sql_hash       | const_hash | plan_hash |       planid        | counter
------+---------------------+------------+-----------+---------------------+---------
    5 | 6062491547151210914 | 2413041345 |         0 | 3549961214127427294 |       1
(1 row)

G.4.11. Интеграция с AQE #

Расширение pgpro_multiplan может работать совместно с адаптивным выполнением запросов (adaptive query execution, AQE), предоставляя более гибкие возможности для управления планами выполнения запросов.

AQE пытается переоптимизировать запрос, если во время выполнения запроса срабатывает определённый триггер, указывающий на неоптимальность плана. Чтобы включить AQE, используйте параметр конфигурации aqe_enable.

Параметр конфигурации pgpro_multiplan.aqe_mode задаёт функции, связанные с AQE, в виде списка, разделённого запятыми.

G.4.11.1. Управление планами в реальном времени #

При управлении планами в реальном времени 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)

G.4.11.2. Индивидуальные значения триггеров #

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

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

  • Установите для параметра aqe_enable значение on, чтобы активировать AQE.

  • Укажите для параметра pgpro_multiplan.aqe_mode значение individual_triggers.

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

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

G.4.11.3. Статистика AQE #

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

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

  • Установите для параметра aqe_enable значение on, чтобы активировать AQE.

  • Укажите для параметра pgpro_multiplan.aqe_mode значение statistics.

  • Включите вычисление идентификаторов запросов с помощью параметра compute_query_id.

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

G.4.12. Представления #

G.4.12.1. Представление pgpro_multiplan_storage #

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

Таблица G.4. Столбцы pgpro_multiplan_storage

ИмяТипОписание
dbidoidИдентификатор базы данных, в которой выполняется запрос
sql_hashbigintВнутренний идентификатор запроса
const_hashbigintХеш непараметризованных констант для замороженных планов, 0 для планов с шаблонами и разрешённых планов
plan_hashbigintВнутренний идентификатор разрешённого плана, 0 для замороженных планов и планов с шаблонами
validbooleanFALSE, если план был аннулирован при последнем использовании
costfloatСтоимость разрешённого плана, 0 для замороженных планов и планов с шаблонами
sample_stringtextНепараметризованный запрос с константами
query_stringtextПараметризованный запрос
paramtypesregtype[]Массив с типами параметров, использованными в запросе
querytextВнутреннее представление запроса
plantextВнутреннее представление плана
plan_typetextТип плана. Для замороженных планов: serialized или hintset. Для планов с шаблонами: template. Для разрешённых планов: baseline
hintstrtextНабор указаний, сформированный на основе плана
wildcardstextРегулярное выражение, используемое для плана с шаблонами (template), NULL для замороженных и разрешённых планов

G.4.12.2. Представление pgpro_multiplan_local_cache #

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

Таблица G.5. Столбцы pgpro_multiplan_local_cache

ИмяТипОписание
sql_hashbigintВнутренний идентификатор запроса
const_hashbigintХеш непараметризованных констант
fs_is_frozenbooleanTRUE, если запрос заморожен
fs_is_validbooleanTRUE, если замороженный оператор действителен
ps_is_validbooleanTRUE, если соответствующий подготовленный оператор действителен
query_stringtextТекст запроса
querytextВнутреннее представление запроса
paramtypesregtype[]Массив с типами параметров, использованными в запросе
hintstrtextНабор указаний, сформированный на основе плана

G.4.12.3. Представление pgpro_multiplan_captured_queries #

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

Таблица G.6. Столбцы pgpro_multiplan_captured_queries

ИмяТипОписание
dbidoidИдентификатор базы данных, в которой выполняется запрос
sql_hashbigintВнутренний идентификатор запроса
queryidbigintСтандартный идентификатор запроса
plan_hashbigintВнутренний идентификатор плана
planidbigintИдентификатор плана, совместимый с расширением pgpro_stats, если для параметра compute_plan_id установлено значение on. В противном случае 0
costfloatСтоимость плана
sample_stringtextПоследний использованный непараметризованный запрос с константами
query_stringtextПоследний использованный параметризованный запрос
hintstrtextНабор указаний, сформированный на основе плана
explain_plantextПлан, показанный командой EXPLAIN

G.4.12.4. Представление pgpro_multiplan_stats #

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

Таблица G.7. Столбцы pgpro_multiplan_stats

ИмяТипОписание
dbidoidИдентификатор базы данных, в которой выполняется запрос
sql_hashbigintВнутренний идентификатор запроса
plan_hashbigintВнутренний идентификатор плана
planidbigintИдентификатор плана, совместимый с расширением pgpro_stats, если для параметра compute_plan_id установлено значение on. В противном случае 0
counterbigintКоличество использований плана

G.4.12.5. Представление aqe_triggers #

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

Таблица G.8. Столбцы aqe_triggers

ИмяТипОписание
dbidoidИдентификатор базы данных, в которой выполняется запрос
sql_hashbigintВнутренний идентификатор запроса
query_stringtextПараметризованный запрос
execution_timeintЗначение для триггера времени выполнения запроса, в миллисекундах. NULL, если используется глобальное значение триггера
memoryintЗначение для триггера потребления памяти рабочим процессом. NULL, если используется глобальное значение триггера
underestimation_ratedoubleКоэффициент для триггера количества обработанных кортежей узлов. NULL, если используется глобальное значение триггера

G.4.12.6. Представление aqe_stats #

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

Таблица G.9. Столбцы aqe_stats

ИмяТипОписание
dbidoidИдентификатор базы данных, в которой выполняется запрос
sql_hashbigintВнутренний идентификатор запроса
planidbigintИдентификатор плана выполнения запроса
querytextВнутреннее представление запроса
last_updatedtimestamp with time zoneВремя последнего обновления статистики
exec_numbigintКоличество выполнений запроса
min_attemptsintegerМинимальное количество переоптимизаций запроса
max_attemptsintegerМаксимальное количество переоптимизаций запроса
total_attemptsintegerОбщее количество переоптимизаций запроса
reason_repeated_planbigintКоличество отключений AQE из-за генерации повторного плана выполнения
reason_no_databigintКоличество отключений AQE из-за отсутствия новой информации, полученной во время выполнения
reason_max_rerunsbigintКоличество отключений AQE из-за достижения максимального количества перезапусков
reason_externalbigintКоличество раз, когда AQE было отключено расширением, например, pgpro_multiplan
reruns_forcedbigintОбщее количество переоптимизаций, вызванных ручным триггером
reruns_timebigintОбщее количество переоптимизаций, вызванных триггером времени выполнения запроса
reruns_underestimationbigintОбщее количество переоптимизаций, вызванных триггером количества обработанных кортежей узлов
reruns_memorybigintОбщее количество переоптимизаций, вызванных триггером потребления памяти рабочим процессом
min_planning_timedouble precisionМинимальное время, затраченное на планирование, в миллисекундах
max_planning_timedouble precisionМаксимальное время, затраченное на планирование, в миллисекундах
mean_planning_timedouble precisionСреднее время, затраченное на планирование, в миллисекундах
stddev_planning_timedouble precisionСтандартное отклонение времени, затраченного на планирование, в миллисекундах
min_exec_timedouble precisionМинимальное время, затраченное на выполнение запроса, в миллисекундах
max_exec_timedouble precisionМаксимальное время, затраченное на выполнение запроса, в миллисекундах
mean_exec_timedouble precisionСреднее время, затраченное на выполнение запроса, в миллисекундах
stddev_exec_timedouble precisionСтандартное отклонение времени, затраченного на выполнение запроса, в миллисекундах

G.4.13. Функции #

Вызывать нижеуказанные функции могут только суперпользователи.

pgpro_multiplan_register_query(query_string text) returns record
pgpro_multiplan_register_query(query_string text, VARIADIC regtype[]) returns record #

Регистрирует запрос, указанный аргументом query_string. Возвращает уникальную пару sql_hash и const_hash.

pgpro_multiplan_unregister_query() returns bool #

Удаляет зарегистрированный запрос. Если нет ошибок, возвращает true.

pgpro_multiplan_freeze(plan_type text) returns bool #

Фиксирует последний использованный для запроса план и сохраняет его в постоянном хранилище pgpro_multiplan, к которому можно получить доступ в представлении pgpro_multiplan_storage. Допустимые значения необязательного аргумента plan_type: serialized (по умолчанию), hintset, template и baseline. Если план был успешно зафиксирован, возвращает true. За подробной информацией о типах планов обратитесь к разделу Поддерживаемые режимы и типы планов. За подробной информацией об этой функции и примерами использования обратитесь к разделу Заморозка планов.

pgpro_multiplan_unfreeze(sql_hash bigint, const_hash bigint) returns bool #

Удаляет указанный план из постоянного хранилища, но оставляет запрос зарегистрированным. Если нет ошибок, возвращает true.

pgpro_multiplan_remove(dbid oid, sql_hash bigint, const_hash bigint) returns bool #

Удаляет замороженный план, который идентифицируется по sql_hash и const_hash, из базы данных, определяемой аргументом dbid. Опустите dbid, чтобы удалить план из текущей базы данных. Эта функция работает, как функции pgpro_multiplan_unfreeze и pgpro_multiplan_unregister_query, вызываемые последовательно. Если план был успешно удалён, возвращает true.

pgpro_multiplan_reset(dbid oid) returns bigint #

Удаляет все записи в представлении pgpro_multiplan_storage для указанной базы данных. Чтобы удалить данные, собранные pgpro_multiplan для текущей базы данных, не указывайте dbid. Чтобы сбросить данные для всех баз данных, установите для параметра dbid значение NULL. Возвращает количество удалённых записей.

pgpro_multiplan_reload_frozen_plancache() returns bool #

Удаляет все замороженные планы и снова загружает их в представление pgpro_multiplan_storage. Также удаляет операторы, которые были зарегистрированы, но не заморожены. Если нет ошибок, возвращает true.

pgpro_multiplan_stats() returns table #

Возвращает статистику использования планов из представления pgpro_multiplan_stats.

pgpro_multiplan_registered_query(sql_hash bigint, const_hash bigint) returns table #

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

pgpro_multiplan_captured_approve(dbid oid, sql_hash bigint, plan_hash bigint) returns bool #

Добавляет указанный план для захваченного запроса в набор базовых (разрешённых) планов и сохраняет его в представлении pgpro_multiplan_storage. Если план был успешно добавлен, возвращает true.

pgpro_multiplan_remove_baseline(dbid oid, sql_hash bigint, plan_hash bigint) returns bool #

Удаляет разрешённый план, который идентифицируется по sql_hash и plan_hash, из базы данных, определяемой аргументом dbid. Опустите dbid, чтобы удалить разрешённый план из текущей базы данных. Если план был успешно удалён, возвращает true.

pgpro_multiplan_remove_template(dbid oid, sql_hash bigint, table_hash bigint) returns bool #

Удаляет план с шаблонами, который идентифицируется по sql_hash и table_hash, из базы данных, определяемой аргументом dbid. Опустите dbid, чтобы удалить план из текущей базы данных. Если план был успешно удалён, возвращает true.

pgpro_multiplan_enable(dbid oid, sql_hash bigint, const_hash bigint, enable bool) returns bool #

Включает или отключает замороженный план, который идентифицируется по sql_hash и const_hash. Аргумент dbid определяет идентификатор целевой базы данных. Опустите dbid, чтобы использовать эту функцию для плана в текущей базе данных. Для последнего аргумента укажите true, чтобы включить план, или false, чтобы отключить его. Если состояние плана было успешно изменено, возвращает true.

pgpro_multiplan_enable_baseline(dbid oid, sql_hash bigint, plan_hash bigint, enable bool) returns bool #

Включает или отключает разрешённый план, который идентифицируется по sql_hash и plan_hash. Аргумент dbid определяет идентификатор целевой базы данных. Опустите dbid, чтобы использовать эту функцию для плана в текущей базе данных. Для последнего аргумента укажите true, чтобы включить план, или false, чтобы отключить его. Если состояние плана было успешно изменено, возвращает true.

pgpro_multiplan_enable_template(dbid oid, sql_hash bigint, table_hash bigint, enable bool) returns bool #

Включает или отключает план с шаблонами, который идентифицируется по sql_hash и table_hash. Аргумент dbid определяет идентификатор целевой базы данных. Опустите dbid, чтобы использовать эту функцию для плана в текущей базе данных. Для последнего аргумента укажите true, чтобы включить план, или false, чтобы отключить его. Если состояние плана было успешно изменено, возвращает true.

pgpro_multiplan_set_plan_type(sql_hash bigint, const_hash bigint, plan_type text) returns bool #

Устанавливает тип плана запроса для замороженного оператора. Допустимые значения аргумента plan_type: serialized и hintset. Чтобы иметь возможность использовать планы на основе указаний, необходимо загрузить расширение pg_hint_plan. Если тип плана был успешно изменён, возвращает true.

pgpro_multiplan_update_hintset(dbid oid, sql_hash bigint, const_hash bigint, hintset text) returns bool #

Заменяет сгенерированный набор указаний для замороженного плана типа hintset на указанный набор пользовательских указаний. План идентифицируется по sql_hash и const_hash. Аргумент dbid определяет идентификатор целевой базы данных. Опустите dbid, чтобы использовать эту функцию для плана в текущей базе данных. Аргумент hintset задаёт строку с пользовательскими указаниями. Эта строка не должна задаваться в комментарии особого вида, используемого в pg_hint_plan, то есть она не должна начинаться с символов /*+ и заканчиваться символами */. Если набор указаний был успешно изменён, возвращает true.

pgpro_multiplan_update_template_hintset(dbid oid, sql_hash bigint, table_hash bigint, hintset text) returns bool #

Заменяет сгенерированный набор указаний для плана с шаблонами на указанный набор пользовательских указаний. План идентифицируется по sql_hash и table_hash. Аргумент dbid определяет идентификатор целевой базы данных. Опустите dbid, чтобы использовать эту функцию для плана в текущей базе данных. Аргумент hintset задаёт строку с пользовательскими указаниями. Эта строка не должна задаваться в комментарии особого вида, используемого в pg_hint_plan, то есть она не должна начинаться с символов /*+ и заканчиваться символами */. Если набор указаний был успешно изменён, возвращает true.

pgpro_multiplan_update_baseline_cost(sql_hash bigint, plan_hash bigint) returns bool #

Обновляет стоимость указанного разрешённого плана в постоянном хранилище pgpro_multiplan. Если стоимость была успешно обновлена, возвращает true.

pgpro_multiplan_captured_clean() returns bigint #

Удаляет все записи из представления pgpro_multiplan_captured_queries. Возвращает количество удалённых записей.

get_sql_hash(query_string text) returns bigint #

Возвращает внутренний идентификатор (sql_hash) для указанного запроса.

set_aqe_trigger(trigger_name text, trigger_val int, query_string text) returns bool
set_aqe_trigger(trigger_name text, trigger_val double precision, query_string text) returns bool #

Задаёт или сбрасывает индивидуальное значение триггера AQE для указанного запроса. Индивидуальное значение триггера переопределяет глобальное значение триггера, указанное в параметре конфигурации AQE. Чтобы эта функциональность работала, значение individual_triggers должно быть указано в параметре pgpro_multiplan.aqe_mode.

Эта функция имеет следующие аргументы:

  • trigger_name: имя триггера. Разрешены следующие значения:

    • execution_time: триггер времени выполнения запроса. Соответствует параметру конфигурации aqe_sql_execution_time_trigger.

    • memory: триггер потребления памяти рабочим процессом. Соответствует параметру конфигурации aqe_backend_memory_used_trigger.

    • underestimation_rate: триггер количества обработанных кортежей узлов. Соответствует параметру конфигурации aqe_rows_underestimation_rate_trigger.

  • trigger_val: значение триггера. Для триггеров execution_time и memory указывайте целочисленные значения. Для underestimation_rate можно указать значения с двойной точностью. Чтобы сбросить индивидуальное значение триггера, передайте в качестве значения NULL или отрицательное число меньше -1.

  • query_string: текст запроса.

Если нет ошибок, эта функция возвращает true.

aqe_triggers_reset(dbid oid) returns bigint #

Удаляет все записи из представления aqe_triggers для указанной базы данных. Чтобы очистить представление aqe_triggers для текущей базы данных, не указывайте dbid. Чтобы удалить записи из представления для всех баз данных, установите для параметра dbid значение NULL. Возвращает количество удалённых записей.

aqe_stats_reset(dbid oid) returns bigint #

Удаляет все записи из представления aqe_stats для указанной базы данных. Чтобы очистить представление aqe_stats для текущей базы данных, не указывайте dbid. Чтобы удалить записи из представления для всех баз данных, установите для параметра dbid значение NULL. Возвращает количество удалённых записей.

pgpro_multiplan_restore(query_string text, sample_string text, hintstr text, paramtypes regtype[], plan_type text, wildcards text, status int) returns record #

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

Эта функция имеет следующие аргументы:

  • query_string: запрос с параметрами $n (аналогично PREPARE statement_name AS), для которого восстанавливается план на основе набора указаний.

  • sample_string: непараметризованный запрос с константами.

  • hintstr: набор указаний, которые описывают нужный план. Эти указания должны поддерживаться расширением pg_hint_plan. Для планов с типами baseline и template этот аргумент не может представлять собой пустую строку. Для остальных типов планов, если для этого аргумента указано значение NULL или пустая строка, будет использоваться стандартный план.

  • parameter_type: массив с типами параметров, используемых в запросе. Если для аргумента указано значение NULL, типы параметров должны быть определены автоматически.

  • plan_type: тип плана. Допустимые значения: serialized, hintset, baseline и template. За дополнительной информацией о типах планов обратитесь к разделу Поддерживаемые режимы и типы планов.

  • wildcards: регулярное выражение POSIX, используемое только для планов с типом template, чтобы проверять соответствия имён таблиц. Для остальных типов планов этот аргумент игнорируется, и можно для него указывать значение NULL.

  • status: этот аргумент зарезервирован для использования в будущем. На текущий момент всегда устанавливайте для него значение NULL или 0.

Функция возвращает уникальную пару sql_hash и const_hash, если план был успешно восстановлен. В противном случае, она возвращает NULL.

За подробной информацией об этой функции и сценариях использования обратитесь к разделу Резервное копирование и восстановление планов.

G.4.14. Параметры конфигурации #

pgpro_multiplan.mode (string) #

Список включённых режимов pgpro_multiplan, разделённых запятой. Доступны следующие значения:

  • frozen: разрешает использование замороженных планов (и сериализованных, и с наборами указаний).

  • wildcards: разрешает использование планов с шаблонами.

  • baseline: разрешает использование базовых (разрешённых) планов.

  • statistics: разрешает сбор статистики использования планов. Эта статистика хранится в представлении pgpro_multiplan_stats. Это значение параметра может быть указано только совместно с одним или несколькими типами планов.

SET pgpro_multiplan.mode = 'frozen, wildcards, baseline, statistics';

Подробнее о типах планов можно узнать в разделе Поддерживаемые режимы и типы планов. За информацией о сборе статистики обратитесь к разделу Статистика.

По умолчанию для параметра pgpro_multiplan.mode задана пустая строка, означающая, что все режимы планов выключены. Изменить этот параметр могут только суперпользователи.

pgpro_multiplan.aqe_mode (string) #

Включённые функции, связанные с AQE, в виде списка, разделённого запятыми. Доступны следующие значения:

SET pgpro_multiplan.aqe_mode = 'auto_approve_plans, individual_triggers, statistics';

Чтобы эта функциональность работала, AQE должно быть включено с помощью параметра конфигурации aqe_enable.

По умолчанию для параметра pgpro_multiplan.aqe_mode указана пустая строка. Изменить этот параметр могут только суперпользователи.

pgpro_multiplan.max_stats (integer) #

Задаёт максимальное количество статистических значений, которые могут храниться в представлении pgpro_multiplan_stats. Дальнейшая статистика будет игнорироваться. Значение по умолчанию — 5000. Этот параметр можно задать только при запуске сервера.

pgpro_multiplan.max_items (integer) #

Задаёт максимальное количество записей, с которым может работать pgpro_multiplan. Значение по умолчанию — 100. Этот параметр можно задать только при запуске сервера.

pgpro_multiplan.auto_tracking (boolean) #

Позволяет pgpro_multiplan автоматически регистрировать запросы, выполняемые с помощью команд EXPLAIN и EXPLAIN EXECUTE. Значение по умолчанию — off. Изменить этот параметр могут только суперпользователи.

pgpro_multiplan.max_local_cache_size (integer) #

Задаёт максимальный размер локального кеша, в килобайтах. Значение по умолчанию — 0, что означает отсутствие ограничения. Изменить этот параметр могут только суперпользователи.

pgpro_multiplan.wal_rw (boolean) #

Включает физическую репликацию данных pgpro_multiplan. При значении off на главном сервере данные на резервный сервер не передаются. При значении off на резервном сервере любые данные, передаваемые с главного сервера, игнорируются. Значение по умолчанию — off. Этот параметр можно задать только при запуске сервера.

pgpro_multiplan.auto_capturing (boolean) #

Включает автоматическое отслеживание запросов в pgpro_multiplan. Если для этого параметра конфигурации установить значение on, в представлении pgpro_multiplan_captured_queries можно будет увидеть запросы с константами в текстовой форме и параметризованные запросы. Также будут видны все планы для каждого запроса. Информация о выполненных запросах хранится до перезапуска сервера. Значение по умолчанию — off. Изменить этот параметр могут только суперпользователи.

pgpro_multiplan.max_captured_items (integer) #

Задаёт максимальное количество запросов, которые может отслеживать pgpro_multiplan. Значение по умолчанию — 1000. Этот параметр можно задать только при запуске сервера.

pgpro_multiplan.sandbox (boolean) #

Включает резервирование отдельных зон разделяемой памяти для ведущего и резервного узла, что позволяет тестировать и анализировать запросы с существующим набором данных без влияния на работу узла. Если на резервном узле установлено значение on, pgpro_multiplan замораживает планы выполнения запросов только на этом узле и хранит их в альтернативном хранилище планов — «песочнице». Если параметр включён на ведущем узле, расширение использует отдельную зону разделяемой памяти, данные которой не реплицируются на резервные узлы. При изменении значения параметра сбрасывается кеш pgpro_multiplan. Значение по умолчанию — off. Изменить этот параметр могут только суперпользователи.

pgpro_multiplan.wildcards (string) #

Регулярное выражение POSIX, которое используется для планов с шаблонами для проверки соответствия имён таблиц, задействованных в запросах. Чтобы разрешить использование планов с шаблонами, укажите для параметра pgpro_multiplan.mode значение wildcards. Значение pgpro_multiplan.wildcards замораживается вместе с соответствующим запросом. Значение по умолчанию — .*, которое означает соответствие любому имени таблицы.

pgpro_multiplan.show_explain_details (boolean) #

Разрешает отображение идентификаторов планов в выводе команды EXPLAIN. Значение по умолчанию — on. Изменить этот параметр могут только суперпользователи.

pgpro_multiplan.show_hint_string (boolean) #

Разрешает отображение наборов указаний, сформированных на основе планов, в выводе команды EXPLAIN. Значение по умолчанию — off. Изменить этот параметр могут только суперпользователи.

pgpro_multiplan.aqe_max_items (integer) #

Задаёт максимальное количество хранимых индивидуальных значений триггеров AQE. Значение по умолчанию — 100. Этот параметр можно задать только при запуске сервера.

pgpro_multiplan.aqe_max_stats (integer) #

Задаёт максимальное количество собираемых статистических значений AQE. Дальнейшие статистические значения игнорируются. Значение по умолчанию — 5000. Этот параметр можно задать только при запуске сервера.

G.4. pgpro_multiplan — save query execution plans for future usage #

G.4.1. Description #

The pgpro_multiplan extension allows you to save query execution plans and use them for subsequent executions of the same queries, thereby avoiding repeated optimization of identical queries. It can also be used to lock a specific execution plan if a plan chosen by the planner does not suit your needs for some reasons. pgpro_multiplan works similar to Oracle SQL Plan Management.

G.4.2. Installation #

The pgpro_multiplan extension is provided with Postgres Pro Enterprise as a separate pre-built package pgpro-multiplan-ent-18 (for the detailed installation instructions, see Chapter 17). To enable pgpro_multiplan, complete the following steps:

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

  2. 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;
    
  3. 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.

  4. Enable the pgpro_multiplan extension, which is disabled by default. To do this, specify the required modes in the pgpro_multiplan.mode parameter. For more information, see Supported Modes and Plan Types.

    You can enable pgpro_multiplan in one of the following ways:

    • To activate the extension for all sessions, set the pgpro_multiplan.mode parameter in the postgresql.conf file.

    • To activate the extension in the current session, use the following command:

      SET pgpro_multiplan.mode = 'frozen';
      

  5. 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.4.3. Supported Modes and Plan Types #

The pgpro_multiplan extension provides the following types of plans:

  • Frozen plans

    Locked plans that take precedence when executing the corresponding queries. A query can have one frozen plan only.

    pgpro_multiplan supports the following types of frozen plans:

    • Serialized plans

      Serialized representations of plans. These plans are converted into executable plans when the corresponding queries are executed for the first time.

      Serialized plans remain valid as long as metadata of the corresponding queries remain unchanged (such as table structures, indexes, and so on). For example, if a table referenced in the plan is recreated, the plan becomes invalid and is ignored. Serialized plans are only valid within the current database and cannot be copied to another database as they depend on OIDs. For this reason, using serialized plans for temporary tables is impractical.

    • Hint-set plans

      Sets of hints that are formed based on the corresponding execution plans at the time of freezing. The set of hints includes join types, join orders, data access methods, and optimizer environment variables differing from default values. These hints correspond to those provided by the pg_hint_plan extension. Therefore, to use hint-set plans, the pg_hint_plan extension must be enabled.

      When the corresponding frozen query is matched, the hints are passed to pg_hint_plan to generate 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.

  • Template plans

    Plans that are similar to hint-set frozen plans but use a template for table names. If a query does not have a corresponding frozen plan, pgpro_multiplan tries to search the matching template plan for this query.

    These plans are also based on sets of hints and require the pg_hint_plan extension to be enabled. However, these plans can be applied only to queries with table names that match the POSIX regular expression specified in the pgpro_multiplan.wildcards parameter. The pgpro_multiplan.wildcards value is frozen along with the corresponding query. If the pg_hint_plan extension is not active, the hints are ignored, and the plan generated by the Postgres Pro optimizer is executed.

  • Baselines

    Sets of allowed plans that can be used to execute queries if there are no corresponding frozen or template plans.

    Like hint-set frozen plans, baselines rely on sets of hints and require the pg_hint_plan extension to be enabled. If pg_hint_plan is not active, the hints are ignored, and the plan generated by the Postgres Pro optimizer is executed.

Use the pgpro_multiplan.mode configuration parameter to specify a comma-separated list of enabled modes and plan types. By default, this parameter is set to an empty string, which means that all plan modes are disabled.

G.4.4. Plan Identification #

Plans are identified in the following ways depending on the plan type:

  • Frozen plans (both serialized and hint-set) are identified by a combination of sql_hash and const_hash.

    sql_hash is computed from the parse tree while ignoring parameters and constants. Field and table aliases are not ignored. Therefore, the same queries with different aliases have different sql_hash values.

    const_hash is computed from all constants used in the query. Constants with the same value but different data types, such as 1 and '1', produce different hash values.

  • Template plans are identified by a combination of sql_hash and table_hash.

    sql_hash is computed from the parse tree while ignoring table names, parameters, and constants.

    table_hash is computed from table names that do not match the regular expression specified in the pgpro_multiplan.wildcards parameter.

    const_hash for these plans is always equal to 0.

  • Baselines are identified by a combination of sql_hash and plan_hash.

    sql_hash is computed in the same way as for frozen plans.

    plan_hash is an internal plan ID.

    const_hash for these plans is always equal to 0.

G.4.5. Freezing Plans #

To freeze certain query execution plans for future usage, perform the following steps:

  1. Use the pgpro_multiplan.mode parameter to specify a comma-separated list with types of plans that you want to create. For detailed information about plan types, see Supported Modes and Plan Types.

    SET pgpro_multiplan.mode = 'frozen, wildcards, baseline, statistics';
    
  2. Register the query for which you want to fix the plan in one of the following ways:

    • Use the pgpro_multiplan_register_query function to register the query manually.

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

      Note that when registering a prepared statement, numbers and positions of parameters in the parameterized query template must match numbers and positions of parameters in the statement. Otherwise, the template is ignored.

    • Set the pgpro_multiplan.auto_tracking parameter to on to register queries executed using the EXPLAIN and EXPLAIN EXECUTE commands automatically.

      -- Enable pgpro_multiplan.auto_tracking
      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
        Plan 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))
      
      -- Disable pgpro_multiplan.auto_tracking
      SET pgpro_multiplan.auto_tracking = off;
      
  3. Modify the query execution plan, if necessary. You can do this using configuration parameters, pg_hint_plan hints if this extension is enabled, or other extensions that allow changing the query plan, such as aqo. For information on compatibility of pgpro_multiplan with these extensions, see the Compatibility with Other Extensions section.

  4. Freeze the query execution plan using the pgpro_multiplan_freeze function. Set the optional plan_type argument to serialized, hintset, template, or baseline depending on the type of the plan you want to create. If you omit this argument, serialized is used by default.

    SELECT pgpro_multiplan_freeze('serialized');
      pgpro_multiplan_freeze
      ------------------------
      t
      (1 row)
    
  5. If you create a hint-set, template, or allowed plan, enable the pg_hint_plan extension. For more information, see Compatibility with Other Extensions.

  6. If you create a template plan, use the pgpro_multiplan.wildcards parameter to specify a POSIX regular expression used to check matching of table names referenced in queries.

G.4.5.1. Storing and Using Plans #

All plans are added to the global pgpro_multiplan storage. You can access it using the pgpro_multiplan_storage view.

When running a certain query, pgpro_multiplan searches for an appropriate plan in the storage. For information about the order of searching and applying plans, refer to Supported Modes and Plan Types.

pgpro_multiplan also uses the local cache for frozen plans to speed up planning and execution of the corresponding queries. A frozen plan is added to the local cache if it was used at least once. You can access plans in the local cache using the pgpro_multiplan_local_cache view. The size of the local cache is limited by the pgpro_multiplan.max_local_cache_size parameter.

Note that when starting a new session, its local plan cache is empty. So, when executing a query for the first time, its planning and execution time may be longer than for subsequent executions where the frozen plan will be used from the local cache.

G.4.5.2. Example of Adding a Frozen Plan #

The example below illustrates how to add a frozen plan.

-- Create table 'a'
CREATE TEMP TABLE a AS (SELECT * FROM generate_series(1,1000) AS x);
CREATE INDEX ON a(x);
ANALYZE;

-- Execute the query and check its plan
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)

-- Allow the use of frozen plans
SET pgpro_multiplan.mode = 'frozen';

-- Register the query
SELECT sql_hash, const_hash
FROM pgpro_multiplan_register_query('SELECT count(*) FROM a
WHERE x = 1 OR (x > $2 AND x < $1) OR x = $1', 'int', 'int');
        sql_hash       | const_hash
----------------------+------------
  -6037606140259443514 | 2413041345
(1 row)

-- Modify the query execution plan
-- Force index scan by disabling sequential scan
SET enable_seqscan = 'off';
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF)
SELECT count(*) FROM a
WHERE x = 1 OR (x > 11 AND x < 22) OR x = 22;

                                    QUERY PLAN
----------------------------------------------------------------------------
Custom Scan (MultiplanScan) (actual rows=1 loops=1)
    Plan is: tracked
    SQL hash: -6037606140259443514
    Const hash: 2413041345
    Plan hash: 0
    ->  Aggregate (actual rows=1 loops=1)
          ->  Index Only Scan using a_x_idx on a (actual rows=12 loops=1)
                Filter: ((x = 1) OR ((x > $2) AND (x < $1)) OR (x = $1))
                Rows Removed by Filter: 18
                Heap Fetches: 30
Planning Time: 0.235 ms
Execution Time: 0.099 ms
(12 rows)

-- Enable sequential scan
RESET enable_seqscan;

-- Freeze the query execution plan
SELECT pgpro_multiplan_freeze();
  pgpro_multiplan_freeze
  ------------------------
  t
  (1 row)

-- The frozen plan with index scan 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.4.5.3. Example of Adding a Template Plan #

The example below shows how to create and use a template plan.

-- Create two tables and insert data into them
CREATE TABLE data_2026(id INT, data1 INT, data2 INT);
CREATE TABLE data_2025(id INT, data1 INT, data2 INT);

INSERT INTO data_2026 SELECT g, g%10, g*100 FROM generate_series(1,10000) g;
INSERT INTO data_2025 SELECT g, g%10, g*100 FROM generate_series(1,10000) g;
INSERT 0 10000

-- Create indexes on these tables
CREATE INDEX ON data_2026(id);
CREATE INDEX ON data_2026(data1);
CREATE INDEX ON data_2025(id);
CREATE INDEX ON data_2025(data1);

ANALYZE data_2026, data_2025;

-- pgpro_multiplan is disabled
-- Execute a query referencing the data_2026 table and check its execution plan
-- The planner chooses Bitmap Heap Scan
EXPLAIN SELECT * FROM data_2026 WHERE id > 100 AND data1 = 5;
                                      QUERY PLAN
--------------------------------------------------------------------------------------
 Bitmap Heap Scan on data_2026  (cost=16.03..86.03 rows=990 width=12)
   Recheck Cond: (data1 = 5)
   Filter: (id > 100)
   ->  Bitmap Index Scan on data_2026_data1_idx  (cost=0.00..15.79 rows=1000 width=0)
         Index Cond: (data1 = 5)
(5 rows)

-- Execute a query referencing the data_2025 table. Bitmap Heap Scan is also used
EXPLAIN SELECT * FROM data_2025 WHERE id > 200 AND data1 = 7;
                                      QUERY PLAN
--------------------------------------------------------------------------------------
 Bitmap Heap Scan on data_2025  (cost=16.03..86.03 rows=980 width=12)
   Recheck Cond: (data1 = 7)
   Filter: (id > 200)
   ->  Bitmap Index Scan on data_2025_data1_idx  (cost=0.00..15.79 rows=1000 width=0)
         Index Cond: (data1 = 7)
(5 rows)

-- Enable pgpro_multiplan and allow the use of template plans
SET pgpro_multiplan.mode = 'wildcards';
SET pgpro_multiplan.show_hint_string = true;

-- Set a wildcard for all data_* tables
SET pgpro_multiplan.wildcards = 'data_.*';

-- Register a query referencing the data_2026 table
SELECT pgpro_multiplan_register_query(
    'SELECT * FROM data_2026 WHERE id > $1 AND data1 = $2',
    'int', 'int'
);
 pgpro_multiplan_register_query
--------------------------------
 (-6003069785000534667,0)
(1 row)

-- Disable Bitmap Scan
SET enable_bitmapscan = 'off';

-- Execute the query referencing the data_2026 table with parameters
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF)
SELECT * FROM data_2026 WHERE id > 5000 AND data1 = 3;
                                      QUERY PLAN
---------------------------------------------------------------------------------------
 Custom Scan (MultiplanScan) (actual rows=500.00 loops=1)
   Plan is: tracked
   Hint string: Set(enable_bitmapscan "off") IndexScan("data_2026" "data_2026_id_idx")
   SQL hash: -6003069785000534667
   Const hash: 0
   Plan hash: 0
   Buffers: shared hit=28 read=16
   ->  Index Scan using data_2026_id_idx on data_2026 (actual rows=500.00 loops=1)
         Index Cond: (id > $1)
         Filter: (data1 = $2)
         Rows Removed by Filter: 4500
         Index Searches: 1
         Buffers: shared hit=28 read=16
 Planning:
   Buffers: shared hit=3
 Planning Time: 0.821 ms
 Execution Time: 3.469 ms
(17 rows)

-- Freeze the query execution plan of the template type
SELECT pgpro_multiplan_freeze('template');
 pgpro_multiplan_freeze
------------------------
 t
(1 row)

-- Enable Bitmap Scan
RESET enable_bitmapscan;

-- Execute the first two queries from this example once again
-- Due to the use of a wildcard, pgpro_multiplan now chooses Index Scan
-- for both tables even though the plan is frozen only for data_2026
EXPLAIN SELECT * FROM data_2026 WHERE id > 100 AND data1 = 5;
                                           QUERY PLAN
------------------------------------------------------------------------------------------------
 Custom Scan (MultiplanScan)  (cost=0.29..246.16 rows=990 width=12)
   Plan is: frozen, template
   Hint string: Set(enable_bitmapscan "off") IndexScan("data_2026")
   SQL hash: -311628753062991878
   Const hash: 2654435769
   Plan hash: 0
   ->  Index Scan using data_2026_data1_idx on data_2026  (cost=0.29..246.16 rows=990 width=12)
         Index Cond: (data1 = 5)
         Filter: (id > 100)
(9 rows)

EXPLAIN SELECT * FROM data_2025 WHERE id > 200 AND data1 = 7;
                                           QUERY PLAN
------------------------------------------------------------------------------------------------
 Custom Scan (MultiplanScan)  (cost=0.29..246.16 rows=980 width=12)
   Plan is: frozen, template
   Hint string: Set(enable_bitmapscan "off") IndexScan("data_2025")
   SQL hash: -311628753062991878
   Const hash: 2654435769
   Plan hash: 0
   ->  Index Scan using data_2025_data1_idx on data_2025  (cost=0.29..246.16 rows=980 width=12)
         Index Cond: (data1 = 7)
         Filter: (id > 200)
(9 rows)

-- Check plan information in the pgpro_multiplan storage
SELECT plan_type, hintstr, wildcards, sample_string
FROM pgpro_multiplan_storage
WHERE plan_type = 'template';
 plan_type |                           hintstr                            | wildcards |                     sample_string                      
-----------+--------------------------------------------------------------+-----------+--------------------------------------------------------
 template  | Set(enable_bitmapscan "off") IndexScan("pgpro_multiplan_0")  | data_.*   | SELECT * FROM data_2026 WHERE id > 5000 AND data1 = 3;
(1 row)

G.4.5.4. Example of Adding a Baseline #

The example below shows how to create a baseline.

-- Create table 'a'
CREATE TEMP TABLE a AS (SELECT * FROM generate_series(1,1000) AS x);
CREATE INDEX ON a(x);
ANALYZE;

-- Allow the use of baselines
SET pgpro_multiplan.mode = 'baseline';

-- Register the query
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)

-- Check the query execution plan
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)

-- Add the query execution plan to the baseline
SELECT pgpro_multiplan_freeze('baseline');
  pgpro_multiplan_freeze
------------------------
  t
(1 row)

-- Now you can see the added plan using the corresponding view
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     |

-- This plan from the baseline is now applied when executing the corresponding query
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;

G.4.5.5. Examples of Freezing Plans for Prepared Statements #

The example below illustrates how to register a prepared statement using the pgpro_multiplan_register_query function and freeze the statement execution plan.

-- Create table 'a'
CREATE TABLE a AS (SELECT * FROM generate_series(1,30) AS x);
CREATE INDEX ON a(x);
ANALYZE;

-- Create a prepared statement
PREPARE stmt AS SELECT count(*) FROM a
WHERE x = $2 OR x < $1 OR x = 10;


-- Register the statement. Note that numbers and positions of parameters $1 and $2
-- in the parameterized query template match numbers and positions of parameters
-- in the prepared statement
SELECT sql_hash, const_hash
FROM pgpro_multiplan_register_query('SELECT count(*) FROM a
WHERE x = $2 OR x < $1 OR x = $3');

-- Execute the prepared statement with parameters
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)

-- Freeze the statement execution plan
SELECT pgpro_multiplan_freeze();

-- Execute the statement once again. The frozen plan is now used.
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)

The following example shows how to automatically register an executed prepared statement.

-- Create a prepared statement:
PREPARE stmt AS SELECT count(*) FROM a
WHERE x = $2 OR x < $1 OR x = 10;

-- Enable automatic tracking of executed statements
SET pgpro_multiplan.auto_tracking = on;

-- Execute the prepared statement with parameters
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)

-- Disable automatic tracking
SET pgpro_multiplan.auto_tracking = off;

-- Freeze the statement execution plan
SELECT pgpro_multiplan_freeze();

G.4.6. Capturing and Approving Plans #

You can also add plans to a baseline by performing the following steps:

  1. Specify the baseline value in the pgpro_multiplan.mode parameter.

  2. Enable the pg_hint_plan extension. For more information, see the Compatibility with Other Extensions section.

  3. Set the pgpro_multiplan.auto_capturing parameter to on to capture all the executed queries. These captured queries can be accessed in the pgpro_multiplan_captured_queries view.

  4. Approve any captured plan using the pgpro_multiplan_captured_approve function with the specified dbid, sql_hash, and plan_hash arguments.

  5. Set the pgpro_multiplan.auto_capturing parameter to off after completing the capture.

-- 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 use of baselines and automatic capture
SET pgpro_multiplan.mode = 'baseline'
SET pgpro_multiplan.auto_capturing = 'on';

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

-- Execute it again with different constants to get a different plan
SELECT count(*) FROM a t1, a t2 WHERE t1.x = t2.x AND t1.y <= 10 AND t2.y > 900;
count
-------
0
(1 row)

-- Now you can see the captured plans using the corresponding view
SELECT * FROM pgpro_multiplan_captured_queries \gx

dbid          | 5
sql_hash      | 6079808577596655075
plan_hash     | -487722818968417375
queryid       | -8984284243102644350
cost          | 36.785
sample_string | SELECT count(*) FROM a t1, a t2 WHERE t1.x = t2.x AND t1.y <= 1000 AND t2.y > 900;
query_string  | SELECT count(*) FROM a t1, a t2 WHERE t1.x = t2.x AND t1.y <= $1 AND t2.y > $2;
hint_str      | Leading(("t1" "t2" )) HashJoin("t1" "t2")  IndexScan("t2" "a_y_idx") SeqScan("t1")
explain_plan  | Aggregate  (cost=36.77..36.78 rows=1 width=8)
              |   Output: count(*)
              |   ->  Hash Join  (cost=11.28..36.52 rows=100 width=0)
              |         Hash Cond: (t1.x = t2.x)
              |         ->  Seq Scan on public.a t1  (cost=0.00..20.50 rows=1000 width=4)
              |               Output: t1.x, t1.y
              |               Filter: (t1.y <= 1000)
              |         ->  Hash  (cost=10.03..10.03 rows=100 width=4)
              |               Output: t2.x
              |               Buckets: 1024  Batches: 1  Memory Usage: 12kB
              |               ->  Index Scan using a_y_idx on public.a t2  (cost=0.28..10.03 rows=100 width=4)
              |                     Output: t2.x
              |                     Index Cond: (t2.y > 900)
              | Query Identifier: -8984284243102644350
              |
-[ RECORD 2 ]-+-----------------------------------------------------------------------------------------------------
dbid          | 5
sql_hash      | 6079808577596655075
plan_hash     | 2719320099967191582
queryid       | -8984284243102644350
cost          | 18.997500000000002
sample_string | SELECT count(*) FROM a t1, a t2 WHERE t1.x = t2.x AND t1.y <= 10 AND t2.y > 900;
query_string  | SELECT count(*) FROM a t1, a t2 WHERE t1.x = t2.x AND t1.y <= $1 AND t2.y > $2;
hint_str      | Leading(("t2" "t1" )) HashJoin("t1" "t2")  IndexScan("t2" "a_y_idx") IndexScan("t1" "a_y_idx")
explain_plan  | Aggregate  (cost=18.99..19.00 rows=1 width=8)
              |   Output: count(*)
              |   ->  Hash Join  (cost=8.85..18.98 rows=1 width=0)
              |         Hash Cond: (t2.x = t1.x)
              |         ->  Index Scan using a_y_idx on public.a t2  (cost=0.28..10.03 rows=100 width=4)
              |               Output: t2.x, t2.y
              |               Index Cond: (t2.y > 900)
              |         ->  Hash  (cost=8.45..8.45 rows=10 width=4)
              |               Output: t1.x
              |               Buckets: 1024  Batches: 1  Memory Usage: 9kB
              |               ->  Index Scan using a_y_idx on public.a t1  (cost=0.28..8.45 rows=10 width=4)
              |                     Output: t1.x
              |                     Index Cond: (t1.y <= 10)
              | Query Identifier: -8984284243102644350
              |

-- Disable the automatic capturing. This will not affect previously captured plans.
SET pgpro_multiplan.auto_capturing = 'off';

-- Manually approve the plan with index scans
SELECT pgpro_multiplan_captured_approve(5, 6079808577596655075, 2719320099967191582);

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

-- Or approve plans selected from the captured list
SELECT pgpro_multiplan_captured_approve(dbid, sql_hash, plan_hash)
FROM pgpro_multiplan_captured_queries
WHERE query_string like '%SELECT % FROM a t1, a t2%';

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

-- Approved plans are automatically removed from the captured queries storage
SELECT count(*) FROM pgpro_multiplan_captured_queries;

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

-- Approved plans are shown in the pgpro_multiplan_storage view
SELECT * FROM pgpro_multiplan_storage \gx
-[ RECORD 1 ]-+------------------------------------------------------------------------------------------------
dbid          | 5
sql_hash      | 6079808577596655075
const_hash    | 0
plan_hash     | -487722818968417375
valid         | t
cost          | 36.785
sample_string | SELECT count(*) FROM a t1, a t2 WHERE t1.x = t2.x AND t1.y <= 1000 AND t2.y > 900;
query_string  | SELECT count(*) FROM a t1, a t2 WHERE t1.x = t2.x AND t1.y <= $1 AND t2.y > $2;
paramtypes    |
query         | <>
plan          | <>
plan_type     | baseline
hintstr       | Leading(("t1" "t2" )) HashJoin("t1" "t2")  IndexScan("t2" "a_y_idx") SeqScan("t1")
wildcards     |
-[ RECORD 2 ]-+------------------------------------------------------------------------------------------------
dbid          | 5
sql_hash      | 6079808577596655075
const_hash    | 0
plan_hash     | 2719320099967191582
valid         | t
cost          | 18.997500000000002
sample_string | SELECT count(*) FROM a t1, a t2 WHERE t1.x = t2.x AND t1.y <= 10 AND t2.y > 900;
query_string  | SELECT count(*) FROM a t1, a t2 WHERE t1.x = t2.x AND t1.y <= $1 AND t2.y > $2;
paramtypes    |
query         | <>
plan          | <>
plan_type     | baseline
hintstr       | Leading(("t2" "t1" )) HashJoin("t1" "t2")  IndexScan("t2" "a_y_idx") IndexScan("t1" "a_y_idx")
wildcards     |

G.4.7. Plan Backup and Restore #

The pgpro_multiplan extension allows you to create a backup of necessary plans and then restore these plans into the current database. This can be useful to transfer plans between databases or server instances.

To back up plans from a specific database, use the pgpro_multiplan_storage view as follows:

CREATE TABLE storage_copy AS SELECT s.*
FROM pgpro_multiplan_storage s
JOIN pg_database d ON s.dbid = d.oid
WHERE d.datname = 'db_name';

To restore plans from a backup, call the pgpro_multiplan_restore function, for instance, as follows:

SELECT s.query_string, res.sql_hash IS NOT NULL AS success
FROM storage_copy s,
LATERAL pgpro_multiplan_restore(s.query_string, s.sample_string, s.hintstr, s.paramtypes, s.plan_type, s.wildcards, NULL) res;

Note

Plans can be restored only when the pg_hint_plan extension is active, see the Compatibility with Other Extensions section.

G.4.7.1. Considerations and Limitations #

When you back up and restore plans, take into account the following considerations and limitations:

  • Plans are always restored into the current database. To restore plans into another database, connect to it first. Thus, it is recommended to create a backup with plans only from one database as for db_name in the examples in this section. If you need to transfer plans for multiple databases, create separate backups for them, connect to each target database sequentially one by one, and restore the corresponding plans from backups.

  • If you back up plans from multiple databases and those databases contain different plans for identical queries, only the first conflicting plan is restored.

  • Only plans for valid queries can be restored, which means that all relations used in the query must exist in the current database.

G.4.7.2. Use Cases #

This section describes how to back up and restore plans in different popular scenarios.

G.4.7.2.1. Upgrading a Server Version #

Follow the steps below to save plans when upgrading a server from an older version that has an incompatible data storage.

  1. Back up plans before upgrade.

    CREATE TABLE storage_copy AS SELECT s.*
    FROM pgpro_multiplan_storage s
    JOIN pg_database d ON s.dbid = d.oid
    WHERE d.datname = 'db_name';
    
  2. Upgrade the server.

  3. Restore plans.

    SELECT pgpro_multiplan_restore(query_string, NULL, hintstr, paramtypes, 'hintset', NULL, NULL)
    FROM storage_copy;
    
G.4.7.2.2. Migrating from sr_plan #

To migrate plans from the deprecated sr_plan extension to pgpro_multiplan, perform the following steps:

  1. Back up plans from the storage of the sr_plan extension.

    CREATE TABLE storage_copy AS SELECT s.*
    FROM sr_plan_storage s
    JOIN pg_database d ON s.dbid = d.oid
    WHERE d.datname = 'db_name';
    
  2. Restore plans in a database with the pgpro_multiplan extension.

    SELECT pgpro_multiplan_restore(query_string, NULL, hintstr, paramtypes, 'hintset', NULL, NULL)
    FROM storage_copy;
    
G.4.7.2.3. Transferring Plans Between Server Instances #

To transfer plans between two servers, do the following:

  1. Connect to the source server.

  2. Back up plans to a table.

      CREATE TABLE storage_copy AS SELECT s.*
      FROM pgpro_multiplan_storage s
      JOIN pg_database d ON s.dbid = d.oid
      WHERE d.datname = 'db_name';
      
  3. Use the pg_dump utility to dump the table to a file.

    $ pg_dump --table storage_copy -Ft postgres > storage_copy.tar
    
  4. Connect to the target server and database.

  5. Move the created dump file to the target file system.

  6. Use the pg_restore utility to restore the table with plans from the dump file.

    $ pg_restore --dbname postgres -Ft storage_copy.tar
    
  7. Restore plans.

    SELECT pgpro_multiplan_restore(query_string, sample_string, hintstr, paramtypes, plan_type, wildcards, NULL)
    FROM storage_copy;
    
    DROP TABLE storage_copy;
    
G.4.7.2.4. Transferring Plans From the Sandbox to the Regular Storage #

To transfer plans from the sandbox to the regular storage, complete the following steps:

  1. Set the pgpro_multiplan.sandbox parameter to on and back up plans from the sandbox.

    SET pgpro_multiplan.sandbox = ON;
    
    CREATE TABLE storage_copy AS SELECT s.*
    FROM pgpro_multiplan_storage s
    JOIN pg_database d ON s.dbid = d.oid
    WHERE d.datname = 'db_name';
    
  2. Set the pgpro_multiplan.sandbox parameter to off and restore plans into the regular storage.

    SET pgpro_multiplan.sandbox = OFF;
    
    SELECT pgpro_multiplan_restore(query_string, sample_string, hintstr, paramtypes, plan_type, wildcards, NULL)
    FROM storage_copy;
    
G.4.7.2.5. Transferring Plans Between Databases #

To transfer plans from one database to another, connect to the target database and restore plans as shown below.

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

Here db_name is the name of the database from which you want to transfer plans.

G.4.7.2.6. Example of Transferring Plans #

This example shows how to transfer plans from one server instance to another.

-- Connect to the source server
psql (17.4)
Type "help" for help.

-- In this example, 1000 frozen plans are stored in the pgpro_multiplan_storage view
postgres=# select count(*) from pgpro_multiplan_storage;
  count
-------
  1000
(1 row)

-- Copy plans from the postgres database to a table
postgres=# CREATE TABLE storage_copy AS SELECT s.*
FROM pgpro_multiplan_storage s
JOIN pg_database d ON s.dbid = d.oid
WHERE d.datname = 'postgres';

-- Dump the table to an archive file
$ pg_dump --table storage_copy -Ft postgres > storage_copy.tar

-- Close the connection to the source server
-- Connect to the target server
./psql postgres
psql (16.8)
Type "help" for help.

-- Create the pgpro_multiplan extension and enable it
postgres=# create extension pgpro_multiplan;
CREATE EXTENSION
SET pgpro_multiplan.mode = 'frozen';
SET

-- This server does not contain plans
postgres=# select count(*) from pgpro_multiplan_storage;
 count
-------
     0
(1 row)

-- Move the dump file with frozen plans to the target file system

-- Restore the table with frozen plans from the dump
$ pg_restore --dbname postgres -Ft storage_copy.tar

-- Restore frozen plans from the table using the pgpro_multiplan_restore function
postgres=# SELECT pgpro_multiplan_restore(query_string, 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)

-- The function restores 1000 frozen plans. The result is shown as pairs of sql_hash and const_hash
-- Frozen queries were identical and differed only in constants, so sql_hash is the same for all plans

-- Drop the table used to restore plans
postgres=# DROP TABLE storage_copy;
DROP TABLE

-- The target server now also stores 1000 frozen plans
postgres=# select count(*) from pgpro_multiplan_storage;
 count
-------
  1000
(1 row)

-- Disable pgpro_multiplan and run the query
postgres=# SET pgpro_multiplan.mode = '';

SET
postgres=# EXPLAIN (COSTS OFF) SELECT * FROM a WHERE x > 10;
     QUERY PLAN
--------------------
 Seq Scan on a
   Filter: (x > 10)
(2 rows)

-- Enable pgpro_multiplan and run the same query once again
-- One of the restored plans is now used
postgres=# SET pgpro_multiplan.mode = 'frozen';
SET
postgres=# EXPLAIN (COSTS OFF) SELECT * FROM a WHERE x > 10;
             QUERY PLAN
-------------------------------------
 Custom Scan (MultiplanScan)
   Plan is: frozen, serialized
   SQL hash: 8436876698844323073
   Const hash: 2295408638
   Plan hash: 0
   ->  Index Scan using a_x_idx on a
         Index Cond: (x > 10)
(7 rows)

G.4.8. 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.4.9. Automatic Type Casting #

pgpro_multiplan automatically attempts to cast the types of constants involved in the query to match the parameter types of the query for which a plan is frozen. If type casting is not possible, the 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.4.10. Plan Usage Statistics #

To collect statistics about the plan usage, specify the statistics value in the pgpro_multiplan.mode parameter. These statistics can be accessed using the pgpro_multiplan_stats view. The pgpro_multiplan.max_stats parameter specifies the maximum number of collected statistics values. When this limit is reached, further statistics are discarded. If a plan has changed, the statistics of the plan usage are reset and recalculated using new plan identifiers.

To get more detailed planning and execution statistics, you can use the pgpro_stats extension (see the Compatibility with Other Extensions section). These statistics can be accessed using the pgpro_stats_statements view.

You can join information from the pgpro_multiplan_stats and pgpro_stats_statements views by the planid field. To enable in-core computation of plan identifiers, set the compute_plan_id configuration parameter to on. Otherwise, the values of this field are set to 0.

The following example shows how to collect and view statistics. It uses the query and frozen plan from the Frozen Plan Example.

-- Enable collecting statistics
SET pgpro_multiplan.mode = 'frozen, statistics';

-- Enable in-core computation of plan identifiers
SET compute_plan_id = on;

-- Execute the query
SELECT count(*) FROM a
WHERE x = 1 OR (x > 11 AND x < 22) OR x = 22;

 count
-------
    12
(1 row)

-- Now you can see statistics of the plan usage
SELECT * FROM pgpro_multiplan_stats;

 dbid |      sql_hash       | const_hash | plan_hash |       planid        | counter
------+---------------------+------------+-----------+---------------------+---------
    5 | 6062491547151210914 | 2413041345 |         0 | 3549961214127427294 |       1
(1 row)

G.4.11. Integration with AQE #

The pgpro_multiplan extension can work in conjunction with adaptive query execution (AQE) providing more flexible features for managing query execution plans.

AQE attempts to reoptimize a query if a specific trigger fires during query execution indicating that it is non-optimal. To enable AQE, use the aqe_enable configuration parameter.

The pgpro_multiplan.aqe_mode configuration parameter specifies a comma-separated list of enabled AQE-related features.

G.4.11.1. Real-Time Plan Management #

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

To enable this functionality, do the following:

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

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

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

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

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

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

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

-- Disable AQE
RESET aqe_enable;
RESET aqe_rows_underestimation_rate_trigger;

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

G.4.11.2. Individual Trigger Values #

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

To use this functionality, do the following:

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

G.4.11.3. AQE Statistics #

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

Perform the following steps to enable statistics collection:

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

G.4.12. Views #

G.4.12.1. The pgpro_multiplan_storage View #

The pgpro_multiplan_storage view provides detailed information about all plans. The columns of the view are shown in Table G.4.

Table G.4. pgpro_multiplan_storage Columns

NameTypeDescription
dbidoidID of the database where the query is executed
sql_hashbigintInternal query ID
const_hashbigintHash of non-parameterized constants for frozen plans, 0 for template and allowed plans
plan_hashbigintInternal ID of the allowed plan, 0 for frozen and template plans
validbooleanFALSE if the plan was invalidated the last time it was used
costfloatCost of the allowed plan, 0 for frozen and template plans
sample_stringtextNon-parameterized query with constants
query_stringtextParameterized query
paramtypesregtype[]Array with parameter types used in the query
querytextInternal representation of the query
plantextInternal representation of the plan
plan_typetextPlan type. For frozen plans: serialized or hintset. For template plans: template. For allowed plans: baseline
hintstrtextSet of hints formed based on the plan
wildcardstextRegular expression used for the template plan, NULL for frozen and allowed plans

G.4.12.2. The pgpro_multiplan_local_cache View #

The pgpro_multiplan_local_cache view provides detailed information about frozen plans in the local cache of the current session. A frozen plan is added to the local cache if it was used at least once. The size of the local cache is limited by the pgpro_multiplan.max_local_cache_size parameter. The columns of the view are shown in Table G.5.

Table G.5. pgpro_multiplan_local_cache Columns

NameTypeDescription
sql_hashbigintInternal query ID
const_hashbigintHash of non-parameterized constants
fs_is_frozenbooleanTRUE if the query plan is frozen
fs_is_validbooleanTRUE if the frozen statement is valid
ps_is_validbooleanTRUE if the corresponding prepared statement is valid
query_stringtextQuery text
querytextInternal representation of the query
paramtypesregtype[]Array with parameter types used in the query
hintstrtextSet of hints formed based on the plan

G.4.12.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.6.

Table G.6. pgpro_multiplan_captured_queries Columns

NameTypeDescription
dbidoidID of the database where the query is executed
sql_hashbigintInternal query ID
queryidbigintStandard query ID
plan_hashbigintInternal plan ID
planidbigintPlan ID compatible with the pgpro_stats extension if the compute_plan_id parameter is set to on, otherwise 0
costfloatPlan cost
sample_stringtextLast used non-parameterized query with constants
query_stringtextLast used parameterized query
hintstrtextSet of hints formed based on the plan
explain_plantextPlan shown by the EXPLAIN command

G.4.12.4. The pgpro_multiplan_stats View #

The pgpro_multiplan_stats view provides statistics about the plan usage. The columns of the view are shown in Table G.7.

Table G.7. pgpro_multiplan_stats Columns

NameTypeDescription
dbidoidID of the database where the query is executed
sql_hashbigintInternal query ID
plan_hashbigintInternal plan ID
planidbigintPlan ID compatible with the pgpro_stats extension if the compute_plan_id parameter is set to on, otherwise 0
counterbigintNumber of times the plan was used

G.4.12.5. The aqe_triggers View #

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

Table G.8. aqe_triggers Columns

NameTypeDescription
dbidoidID of the database where the query is executed
sql_hashbigintInternal query ID
query_stringtextParameterized query
execution_timeintValue for the query execution time trigger, in milliseconds. NULL if the global trigger value is used
memoryintValue for the backend memory consumption trigger. NULL if the global trigger value is used
underestimation_ratedoubleFactor for the processed number of node tuples trigger. NULL if the global trigger value is used

G.4.12.6. The aqe_stats View #

The aqe_stats view provides cumulative statistics about AQE reoptimizations. This view stores one row per each combination of the database ID, query, and execution plan. The columns of the view are shown in Table G.9.

Table G.9. aqe_stats Columns

NameTypeDescription
dbidoidID of the database where the query is executed
sql_hashbigintInternal query ID
planidbigintID of the query execution plan
querytextInternal representation of the query
last_updatedtimestamp with time zoneTimestamp of the last statistics update
exec_numbigintNumber of query executions
min_attemptsintegerMinimum number of times the query was reoptimized
max_attemptsintegerMaximum number of times the query was reoptimized
total_attemptsintegerTotal number of times the query was reoptimized
reason_repeated_planbigintNumber of times AQE was disabled because a repeated execution plan was generated
reason_no_databigintNumber of times AQE was disabled because no new information was gathered during execution
reason_max_rerunsbigintNumber of times AQE was disabled because the maximum number of reruns was reached
reason_externalbigintNumber of times AQE was disabled by an extension, such as pgpro_multiplan
reruns_forcedbigintTotal number of reoptimizations caused by the manual trigger
reruns_timebigintTotal number of reoptimizations caused by the query execution time trigger
reruns_underestimationbigintTotal number of reoptimizations caused by the processed number of node tuples trigger
reruns_memorybigintTotal number of reoptimizations caused by the backend memory trigger
min_planning_timedouble precisionMinimum time spent planning, in milliseconds
max_planning_timedouble precisionMaximum time spent planning, in milliseconds
mean_planning_timedouble precisionMean time spent planning, in milliseconds
stddev_planning_timedouble precisionPopulation standard deviation of time spent planning, in milliseconds
min_exec_timedouble precisionMinimum time spent on query execution, in milliseconds
max_exec_timedouble precisionMaximum time spent on query execution, in milliseconds
mean_exec_timedouble precisionMean time spent on query execution, in milliseconds
stddev_exec_timedouble precisionPopulation standard deviation of time spent on query execution, in milliseconds

G.4.13. Functions #

Only superusers 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 #

Registers the query specified by the query_string argument. Returns the unique pair of sql_hash and const_hash.

pgpro_multiplan_unregister_query() returns bool #

Removes the registered query. Returns true if there are no errors.

pgpro_multiplan_freeze(plan_type text) returns bool #

Fixes the last used plan for the query and saves it to the permanent pgpro_multiplan storage that can be accessed in the pgpro_multiplan_storage view. The allowed values of the optional plan_type argument are serialized (default), hintset, template, and baseline. Returns true if the plan was fixed successfully. For details about plan types, see Supported Modes and Plan Types. For more information about this function and examples, refer to Freezing Plans.

pgpro_multiplan_unfreeze(sql_hash bigint, const_hash bigint) returns bool #

Removes the specified plan from the permanent storage but keeps the query registered. Returns true if there are no errors.

pgpro_multiplan_remove(dbid oid, sql_hash bigint, const_hash bigint) returns bool #

Removes the frozen plan identified by the sql_hash and const_hash arguments from the database defined by the dbid argument. Omit dbid to remove the plan from the current database. This function operates as the pgpro_multiplan_unfreeze and pgpro_multiplan_unregister_query functions called sequentially. Returns true if the plan was removed successfully.

pgpro_multiplan_reset(dbid oid) returns bigint #

Removes all records from the pgpro_multiplan_storage view 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. Returns the number of removed records.

pgpro_multiplan_reload_frozen_plancache() returns bool #

Drops all frozen plans and reloads them in the pgpro_multiplan_storage view. It also drops statements that have been registered but not frozen. Returns true if there are no errors.

pgpro_multiplan_stats() returns table #

Returns statistics of the plan usage from the pgpro_multiplan_stats view.

pgpro_multiplan_registered_query(sql_hash bigint, const_hash bigint) returns table #

Returns the registered query with the specified sql_hash and const_hash parameters 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 #

Adds the specified plan for the captured query to a baseline (a set of allowed plans) and saves it to the pgpro_multiplan_storage view. Returns true if the plan was added successfully.

pgpro_multiplan_remove_baseline(dbid oid, sql_hash bigint, plan_hash bigint) returns bool #

Removes the allowed plan identified by the sql_hash and plan_hash arguments from the database defined by the dbid argument. Omit dbid to remove the allowed plan from the current database. Returns true if the plan was removed successfully.

pgpro_multiplan_remove_template(dbid oid, sql_hash bigint, table_hash bigint) returns bool #

Removes the template plan identified by the sql_hash and table_hash arguments from the database defined by the dbid argument. Omit dbid to remove the template plan from the current database. Returns true if the plan was removed successfully.

pgpro_multiplan_enable(dbid oid, sql_hash bigint, const_hash bigint, enable bool) returns bool #

Enables or disables the frozen plan identified by the sql_hash and const_hash arguments. The dbid argument defines the ID of a target database. Omit dbid to use the function for the plan in the current database. For the last argument, specify true to enable the plan or false to disable it. Returns true if the plan state was changed successfully.

pgpro_multiplan_enable_baseline(dbid oid, sql_hash bigint, plan_hash bigint, enable bool) returns bool #

Enables or disables the allowed plan identified by the sql_hash and plan_hash arguments. The dbid argument defines the ID of a target database. Omit dbid to use the function for the plan in the current database. For the last argument, specify true to enable the plan or false to disable it. Returns true if the plan state was changed successfully.

pgpro_multiplan_enable_template(dbid oid, sql_hash bigint, table_hash bigint, enable bool) returns bool #

Enables or disables the template plan identified by the sql_hash and table_hash arguments. The dbid argument defines the ID of a target database. Omit dbid to use the function for the plan in the current database. For the last argument, specify true to enable the plan or false to disable it. Returns true if the plan state was changed 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 hint-set plans, the pg_hint_plan extension must be loaded. Returns true if the plan type was changed successfully.

pgpro_multiplan_update_hintset(dbid oid, sql_hash bigint, const_hash bigint, hintset text) returns bool #

Replaces the generated hint set for the hintset frozen plan with the specified set of custom hints. The plan is identified by the sql_hash and const_hash arguments. The dbid argument defines the ID of a target database. Omit dbid to use the function for the plan in the current database. The hintset argument specifies a string with custom hints. This string should not be specified in the special comment used in pg_hint_plan, that is it should not start with /*+ and end with */. Returns true if the hint set was changed successfully.

pgpro_multiplan_update_template_hintset(dbid oid, sql_hash bigint, table_hash bigint, hintset text) returns bool #

Replaces the generated hint set for the template plan with the specified set of custom hints. The plan is identified by the sql_hash and table_hash arguments. The dbid argument defines the ID of a target database. Omit dbid to use the function for the plan in the current database. The hintset argument specifies a string with custom hints. This string should not be specified in the special comment used in pg_hint_plan, that is it should not start with /*+ and end with */. Returns true if the hint set was changed successfully.

pgpro_multiplan_update_baseline_cost(sql_hash bigint, plan_hash bigint) returns bool #

Updates the cost of the specified allowed plan in the permanent pgpro_multiplan storage. Returns true if the cost was updated successfully.

pgpro_multiplan_captured_clean() returns bigint #

Removes all records from the pgpro_multiplan_captured_queries view. Returns the number of removed records.

get_sql_hash(query_string text) returns bigint #

Returns the internal ID (sql_hash) for the specified query.

set_aqe_trigger(trigger_name text, trigger_val int, query_string text) returns bool
set_aqe_trigger(trigger_name text, trigger_val double precision, query_string text) returns bool #

Sets or resets the individual value of the AQE trigger for the specified query. The individual trigger value overrides the global trigger value specified in the AQE configuration parameter. For this feature to work, ensure that the individual_triggers value is specified in the pgpro_multiplan.aqe_mode parameter.

This function has the following arguments:

  • trigger_name: The trigger name. The following values are allowed:

  • trigger_val: The trigger value. For execution_time and memory triggers, specify integer values. For underestimation_rate, you can define double precision values. To reset the individual trigger value, specify NULL or negative value less than -1.

  • query_string: The query text.

This function returns true if there are no errors.

aqe_triggers_reset(dbid oid) returns bigint #

Removes all records from the aqe_triggers view for the specified database. To clean the aqe_triggers view for the current database, omit dbid. To remove records from this view for all databases, set dbid to NULL. Returns the number of removed records.

aqe_stats_reset(dbid oid) returns bigint #

Removes all records from the aqe_stats view for the specified database. To clean the aqe_stats view for the current database, omit dbid. To remove records from this view for all databases, set dbid to NULL. Returns the number of removed records.

pgpro_multiplan_restore(query_string text, sample_string text, hintstr text, paramtypes regtype[], plan_type text, wildcards text, status int) returns record #

Restores the plan for the specified query into the current database.

This function has the following arguments:

  • query_string: The query with $n parameters (same as in PREPARE statement_name AS) for which to restore the plan based on a set of hints.

  • sample_string: The non-parameterized query with constants.

  • hintstr: A set of hints that describe the desired plan. These hints should be supported by the pg_hint_plan extension. For the baseline and template plan types, this argument cannot be set to an empty string. For other plan types, if this argument is set to NULL or empty string, the standard plan is used.

  • parameter_type: An array with parameter types used in the query. If this argument is set to NULL, parameter types should be determined automatically.

  • plan_type: The plan type. Allowed values are serialized, hintset, baseline, and template. For details about plan types, see Supported Modes and Plan Types.

  • wildcards: The POSIX regular expression used only for the template plan type to check matching of table names. For other plan types, this argument is ignored, and you can set it to NULL.

  • status: This argument is reserved for future usage. Currently, always set it to NULL or 0.

The function returns the unique pair of sql_hash and const_hash if the plan was restored successfully. Otherwise, it returns NULL.

For more information about this function and use cases, refer to Plan Backup and Restore.

G.4.14. Configuration Parameters #

pgpro_multiplan.mode (string) #

A comma-separated list of enabled pgpro_multiplan modes. The following values are available:

  • frozen: Enables the use of frozen plans (both serialized and hint-set).

  • wildcards: Enables the use of template plans.

  • baseline: Enables the use of allowed plans.

  • statistics: Enables collecting statistics about the plan usage. These statistics are stored in the pgpro_multiplan_stats view. This value can be specified only together with one or more plan types.

SET pgpro_multiplan.mode = 'frozen, wildcards, baseline, statistics';

For details about plan types, see Supported Modes and Plan Types. For more information about collecting statistics, refer to Statistics.

By default, the pgpro_multiplan.mode parameter is set to an empty string, which means that all plan modes are disabled. Only superusers can change this setting.

pgpro_multiplan.aqe_mode (string) #

A comma-separated list of enabled AQE-related features. The following values are available:

SET pgpro_multiplan.aqe_mode = 'auto_approve_plans, individual_triggers, statistics';

For these features to work, ensure that AQE is enabled using the aqe_enable configuration parameter.

By default, the pgpro_multiplan.aqe_mode parameter is set to an empty string. Only superusers can change this setting.

pgpro_multiplan.max_stats (integer) #

Sets the maximum number of statistics values that can be stored in the pgpro_multiplan_stats view. Further statistics will be discarded. 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 register queries executed using the EXPLAIN and EXPLAIN EXECUTE commands automatically. The default value is off. Only superusers can change this setting.

pgpro_multiplan.max_local_cache_size (integer) #

Sets the maximum size of the local cache, in kilobytes. The default value is 0, 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 POSIX regular expression used for template plans to check matching of table names referenced in queries. To enable the use of template plans, specify the wildcards value in the pgpro_multiplan.mode parameter. The pgpro_multiplan.wildcards value is frozen along with the corresponding query. The default value is .* that means matching for any table name.

pgpro_multiplan.show_explain_details (boolean) #

Enables displaying plan identifiers in the EXPLAIN output. The default value is on. Only superusers can change this setting.

pgpro_multiplan.show_hint_string (boolean) #

Enables displaying sets of hints, which are formed based on plans, in the EXPLAIN output. The default value is off. Only superusers can change this setting.

pgpro_multiplan.aqe_max_items (integer) #

Sets the maximum number of stored individual AQE trigger values. The default value is 100. This parameter can only be set at server start.

pgpro_multiplan.aqe_max_stats (integer) #

Sets the maximum number of collected AQE statistics values. Further statistics are discarded. The default value is 5000. This parameter can only be set at server start.

FAQ