G.4. pgpro_multiplan — сохранение планов выполнения запросов для последующего использования #
- G.4.1. Описание
- G.4.2. Установка
- G.4.3. Поддерживаемые режимы и типы планов
- G.4.4. Идентификация планов
- G.4.5. Заморозка планов
- G.4.6. Захват и одобрение планов
- G.4.7. Резервное копирование и восстановление планов
- G.4.8. Совместимость с другими расширениями
- G.4.9. Автоматическое приведение типов
- G.4.10. Статистика использования планов
- G.4.11. Интеграция с AQE
- G.4.12. Представления
- G.4.13. Функции
- G.4.14. Параметры конфигурации
- G.4.2. Установка
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, выполните следующие действия:
Добавьте имя библиотеки в переменную
shared_preload_librariesв файлеpostgresql.conf:shared_preload_libraries = 'pgpro_multiplan'
Обратите внимание, что имена библиотек в переменной
shared_preload_librariesдолжны добавляться в определённом порядке. Совместимость pgpro_multiplan с другими расширениями описана в Подразделе G.4.8.Перезагрузите сервер баз данных, чтобы изменения вступили в силу.
Чтобы убедиться, что библиотека
pgpro_multiplanустановлена правильно, вы можете выполнить следующую команду:SHOW shared_preload_libraries;
Создайте расширение pgpro_multiplan, выполнив следующий запрос:
CREATE EXTENSION pgpro_multiplan;
Расширение pgpro_multiplan использует кеш разделяемой памяти, который инициализируется только при запуске сервера, поэтому данная библиотека также должна предзагружаться при запуске. Расширение pgpro_multiplan следует создать в каждой базе данных, где требуется управление запросами.
Включите расширение pgpro_multiplan, которое выключено по умолчанию. Для этого укажите необходимые режимы в параметре pgpro_multiplan.mode. За дополнительной информацией обратитесь к разделу Поддерживаемые режимы и типы планов.
Включить pgpro_multiplan можно одним из следующих способов:
Чтобы активировать расширение для всех сеансов, задайте параметр
pgpro_multiplan.modeв файлеpostgresql.conf.Чтобы активировать расширение в текущем сеансе, используйте следующую команду:
SET pgpro_multiplan.mode = 'frozen';
При необходимости переноса данных 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. Заморозка планов #
Чтобы заморозить определённые планы выполнения запроса для дальнейшего использования, выполните следующие шаги:
Используйте параметр pgpro_multiplan.mode, чтобы указать разделённый запятыми список с типами планов, которые будут создаваться. За подробной информацией о типах планов обратитесь к разделу Поддерживаемые режимы и типы планов.
SET pgpro_multiplan.mode = 'frozen, wildcards, baseline, statistics';
Зарегистрируйте запрос, для которого нужно зафиксировать план, одним из следующих способов:
Используйте функцию pgpro_multiplan_register_query, чтобы зарегистрировать запрос вручную.
SELECT pgpro_multiplan_register_query(
query_string,parameter_type, ...);Здесь
query_string— запрос с параметрами$(аналогичноnPREPARE). Можно описать каждый тип параметра, используя необязательный аргумент функцииstatement_nameASparameter_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;
Измените план выполнения запроса, если необходимо. Это можно сделать с помощью параметров конфигурации, указаний расширения pg_hint_plan, если оно включено, или других расширений, которые позволяют изменять план запроса, например aqo. За информацией о совместимости pgpro_multiplan с этими расширениями обратитесь к разделу Совместимость с другими расширениями.
Заморозьте план выполнения запроса с помощью функции pgpro_multiplan_freeze. Для необязательного аргумента
plan_typeукажите значениеserialized,hintset,templateилиbaselineв зависимости от типа плана, который нужно создать. Если этот аргумент опущен, по умолчанию используетсяserialized.SELECT pgpro_multiplan_freeze('serialized'); pgpro_multiplan_freeze ------------------------ t (1 row)Если создаётся план с набором указаний, план с шаблоном или разрешённый план, включите расширение pg_hint_plan. За подробной информацией обратитесь к разделу Совместимость с другими расширениями.
Если создаётся план с шаблоном, используйте параметр 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. Захват и одобрение планов #
Планы можно также добавлять в набор базовых планов, выполняя следующие шаги:
Укажите для параметра pgpro_multiplan.mode значение
baseline.Включите расширение pg_hint_plan. За подробной информацией обратитесь к разделу Совместимость с другими расширениями.
Укажите для параметра pgpro_multiplan.auto_capturing значение
on, чтобы включить захват всех выполняемых запросов. Получить доступ к этим захваченным запросам можно в представлении pgpro_multiplan_captured_queries.Одобрите любой захваченный план с помощью функции pgpro_multiplan_captured_approve с указанными аргументами
dbid,sql_hashиplan_hash.Установите для параметра 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. Обновление версии сервера #
Выполните шаги ниже, чтобы сохранить планы при обновлении сервера со старой версии с несовместимым хранилищем данных.
Выполните резервное копирование планов до обновления.
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';
Обновите версию сервера.
Восстановите планы.
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, выполните следующие шаги:
Выполните резервное копирование планов из хранилища расширения 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';
Восстановите планы в базу данных с расширением pgpro_multiplan.
SELECT pgpro_multiplan_restore(query_string, NULL, hintstr, paramtypes, 'hintset', NULL, NULL) FROM storage_copy;
G.4.7.2.3. Перенос планов между экземплярами серверов #
Чтобы перенести планы между двумя серверами, выполните следующее:
Подключитесь к исходному серверу.
Выполните резервное копирование планов в таблицу.
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';
Используйте утилиту pg_dump, чтобы выгрузить таблицу в файл.
$ pg_dump --table storage_copy -Ft postgres > storage_copy.tar
Подключитесь к целевому серверу и базе данных.
Переместите созданный файл выгрузки в целевую файловую систему.
Используйте утилиту pg_restore, чтобы восстановить таблицу с планами из файла выгрузки.
$ pg_restore --dbname postgres -Ft storage_copy.tar
Восстановите планы.
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. Перенос планов из "песочницы" в обычное хранилище #
Чтобы перенести планы из "песочницы" в обычное хранилище, выполните следующие шаги:
Установите для параметра 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';
Установите для параметра
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, в список разрешённых планов.
Чтобы включить эту функциональность, выполните следующие шаги:
Установите для параметра aqe_enable значение
on, чтобы активировать AQE.Укажите один или несколько необходимых триггеров AQE с помощью параметров aqe_sql_execution_time_trigger, aqe_rows_underestimation_rate_trigger и aqe_backend_memory_used_trigger.
Укажите для параметра pgpro_multiplan.mode значение
baseline, чтобы включить использование разрешённых планов.Укажите для параметра pgpro_multiplan.aqe_mode значение
auto_approve_plans.
В следующем примере показано, как использовать управление планами в реальном времени.
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
| Имя | Тип | Описание |
|---|---|---|
dbid | oid | Идентификатор базы данных, в которой выполняется запрос |
sql_hash | bigint | Внутренний идентификатор запроса |
const_hash | bigint | Хеш непараметризованных констант для замороженных планов, 0 для планов с шаблонами и разрешённых планов |
plan_hash | bigint | Внутренний идентификатор разрешённого плана, 0 для замороженных планов и планов с шаблонами |
valid | boolean | FALSE, если план был аннулирован при последнем использовании |
cost | float | Стоимость разрешённого плана, 0 для замороженных планов и планов с шаблонами |
sample_string | text | Непараметризованный запрос с константами |
query_string | text | Параметризованный запрос |
paramtypes | regtype[] | Массив с типами параметров, использованными в запросе |
query | text | Внутреннее представление запроса |
plan | text | Внутреннее представление плана |
plan_type | text | Тип плана. Для замороженных планов: serialized или hintset. Для планов с шаблонами: template. Для разрешённых планов: baseline |
hintstr | text | Набор указаний, сформированный на основе плана |
wildcards | text | Регулярное выражение, используемое для плана с шаблонами (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_hash | bigint | Внутренний идентификатор запроса |
const_hash | bigint | Хеш непараметризованных констант |
fs_is_frozen | boolean | TRUE, если запрос заморожен |
fs_is_valid | boolean | TRUE, если замороженный оператор действителен |
ps_is_valid | boolean | TRUE, если соответствующий подготовленный оператор действителен |
query_string | text | Текст запроса |
query | text | Внутреннее представление запроса |
paramtypes | regtype[] | Массив с типами параметров, использованными в запросе |
hintstr | text | Набор указаний, сформированный на основе плана |
G.4.12.3. Представление pgpro_multiplan_captured_queries #
Представление pgpro_multiplan_captured_queries содержит подробную информацию обо всех запросах, отслеживаемых в сеансах. Столбцы представления показаны в Таблице G.6.
Таблица G.6. Столбцы pgpro_multiplan_captured_queries
| Имя | Тип | Описание |
|---|---|---|
dbid | oid | Идентификатор базы данных, в которой выполняется запрос |
sql_hash | bigint | Внутренний идентификатор запроса |
queryid | bigint | Стандартный идентификатор запроса |
plan_hash | bigint | Внутренний идентификатор плана |
planid | bigint | Идентификатор плана, совместимый с расширением pgpro_stats, если для параметра compute_plan_id установлено значение on. В противном случае 0 |
cost | float | Стоимость плана |
sample_string | text | Последний использованный непараметризованный запрос с константами |
query_string | text | Последний использованный параметризованный запрос |
hintstr | text | Набор указаний, сформированный на основе плана |
explain_plan | text | План, показанный командой EXPLAIN |
G.4.12.4. Представление pgpro_multiplan_stats #
Представление pgpro_multiplan_stats предоставляет статистику использования планов. Столбцы представления показаны в Таблице G.7.
Таблица G.7. Столбцы pgpro_multiplan_stats
| Имя | Тип | Описание |
|---|---|---|
dbid | oid | Идентификатор базы данных, в которой выполняется запрос |
sql_hash | bigint | Внутренний идентификатор запроса |
plan_hash | bigint | Внутренний идентификатор плана |
planid | bigint | Идентификатор плана, совместимый с расширением pgpro_stats, если для параметра compute_plan_id установлено значение on. В противном случае 0 |
counter | bigint | Количество использований плана |
G.4.12.5. Представление aqe_triggers #
Представление aqe_triggers содержит информацию об индивидуальных значениях триггеров AQE. Столбцы представления показаны в Таблице G.8.
Таблица G.8. Столбцы aqe_triggers
| Имя | Тип | Описание |
|---|---|---|
dbid | oid | Идентификатор базы данных, в которой выполняется запрос |
sql_hash | bigint | Внутренний идентификатор запроса |
query_string | text | Параметризованный запрос |
execution_time | int | Значение для триггера времени выполнения запроса, в миллисекундах. NULL, если используется глобальное значение триггера |
memory | int | Значение для триггера потребления памяти рабочим процессом. NULL, если используется глобальное значение триггера |
underestimation_rate | double | Коэффициент для триггера количества обработанных кортежей узлов. NULL, если используется глобальное значение триггера |
G.4.12.6. Представление aqe_stats #
Представление aqe_stats содержит сводную статистику о переоптимизациях AQE. Это представление содержит одну строку для каждой комбинации идентификатора базы данных, запроса и плана выполнения. Столбцы представления показаны в Таблице G.9.
Таблица G.9. Столбцы aqe_stats
| Имя | Тип | Описание |
|---|---|---|
dbid | oid | Идентификатор базы данных, в которой выполняется запрос |
sql_hash | bigint | Внутренний идентификатор запроса |
planid | bigint | Идентификатор плана выполнения запроса |
query | text | Внутреннее представление запроса |
last_updated | timestamp with time zone | Время последнего обновления статистики |
exec_num | bigint | Количество выполнений запроса |
min_attempts | integer | Минимальное количество переоптимизаций запроса |
max_attempts | integer | Максимальное количество переоптимизаций запроса |
total_attempts | integer | Общее количество переоптимизаций запроса |
reason_repeated_plan | bigint | Количество отключений AQE из-за генерации повторного плана выполнения |
reason_no_data | bigint | Количество отключений AQE из-за отсутствия новой информации, полученной во время выполнения |
reason_max_reruns | bigint | Количество отключений AQE из-за достижения максимального количества перезапусков |
reason_external | bigint | Количество раз, когда AQE было отключено расширением, например, pgpro_multiplan |
reruns_forced | bigint | Общее количество переоптимизаций, вызванных ручным триггером |
reruns_time | bigint | Общее количество переоптимизаций, вызванных триггером времени выполнения запроса |
reruns_underestimation | bigint | Общее количество переоптимизаций, вызванных триггером количества обработанных кортежей узлов |
reruns_memory | bigint | Общее количество переоптимизаций, вызванных триггером потребления памяти рабочим процессом |
min_planning_time | double precision | Минимальное время, затраченное на планирование, в миллисекундах |
max_planning_time | double precision | Максимальное время, затраченное на планирование, в миллисекундах |
mean_planning_time | double precision | Среднее время, затраченное на планирование, в миллисекундах |
stddev_planning_time | double precision | Стандартное отклонение времени, затраченного на планирование, в миллисекундах |
min_exec_time | double precision | Минимальное время, затраченное на выполнение запроса, в миллисекундах |
max_exec_time | double precision | Максимальное время, затраченное на выполнение запроса, в миллисекундах |
mean_exec_time | double precision | Среднее время, затраченное на выполнение запроса, в миллисекундах |
stddev_exec_time | double precision | Стандартное отклонение времени, затраченного на выполнение запроса, в миллисекундах |
G.4.13. Функции #
Вызывать нижеуказанные функции могут только суперпользователи.
-
pgpro_multiplan_register_query(query_stringtext) returns record
pgpro_multiplan_register_query(#query_stringtext,VARIADICregtype[]) returns record Регистрирует запрос, указанный аргументом
query_string. Возвращает уникальную паруsql_hashиconst_hash.-
pgpro_multiplan_unregister_query() returns bool# Удаляет зарегистрированный запрос. Если нет ошибок, возвращает
true.-
pgpro_multiplan_freeze(#plan_typetext) returns bool Фиксирует последний использованный для запроса план и сохраняет его в постоянном хранилище pgpro_multiplan, к которому можно получить доступ в представлении pgpro_multiplan_storage. Допустимые значения необязательного аргумента
plan_type:serialized(по умолчанию),hintset,templateиbaseline. Если план был успешно зафиксирован, возвращаетtrue. За подробной информацией о типах планов обратитесь к разделу Поддерживаемые режимы и типы планов. За подробной информацией об этой функции и примерами использования обратитесь к разделу Заморозка планов.-
pgpro_multiplan_unfreeze(#sql_hashbigint,const_hashbigint) returns bool Удаляет указанный план из постоянного хранилища, но оставляет запрос зарегистрированным. Если нет ошибок, возвращает
true.-
pgpro_multiplan_remove(#dbidoid,sql_hashbigint,const_hashbigint) returns bool Удаляет замороженный план, который идентифицируется по
sql_hashиconst_hash, из базы данных, определяемой аргументомdbid. Опуститеdbid, чтобы удалить план из текущей базы данных. Эта функция работает, как функции pgpro_multiplan_unfreeze и pgpro_multiplan_unregister_query, вызываемые последовательно. Если план был успешно удалён, возвращаетtrue.-
pgpro_multiplan_reset(#dbidoid) 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_hashbigint,const_hashbigint) returns table Возвращает зарегистрированный запрос с указанными параметрами
sql_hashиconst_hash, даже если он не заморожен, только для целей отладки. Работает, если запрос зарегистрирован в текущем обслуживающем процессе или заморожен в текущей базе данных.-
pgpro_multiplan_captured_approve(#dbidoid,sql_hashbigint,plan_hashbigint) returns bool Добавляет указанный план для захваченного запроса в набор базовых (разрешённых) планов и сохраняет его в представлении
pgpro_multiplan_storage. Если план был успешно добавлен, возвращаетtrue.-
pgpro_multiplan_remove_baseline(#dbidoid,sql_hashbigint,plan_hashbigint) returns bool Удаляет разрешённый план, который идентифицируется по
sql_hashиplan_hash, из базы данных, определяемой аргументомdbid. Опуститеdbid, чтобы удалить разрешённый план из текущей базы данных. Если план был успешно удалён, возвращаетtrue.-
pgpro_multiplan_remove_template(#dbidoid,sql_hashbigint,table_hashbigint) returns bool Удаляет план с шаблонами, который идентифицируется по
sql_hashиtable_hash, из базы данных, определяемой аргументомdbid. Опуститеdbid, чтобы удалить план из текущей базы данных. Если план был успешно удалён, возвращаетtrue.-
pgpro_multiplan_enable(#dbidoid,sql_hashbigint,const_hashbigint,enablebool) returns bool Включает или отключает замороженный план, который идентифицируется по
sql_hashиconst_hash. Аргументdbidопределяет идентификатор целевой базы данных. Опуститеdbid, чтобы использовать эту функцию для плана в текущей базе данных. Для последнего аргумента укажитеtrue, чтобы включить план, илиfalse, чтобы отключить его. Если состояние плана было успешно изменено, возвращаетtrue.-
pgpro_multiplan_enable_baseline(#dbidoid,sql_hashbigint,plan_hashbigint,enablebool) returns bool Включает или отключает разрешённый план, который идентифицируется по
sql_hashиplan_hash. Аргументdbidопределяет идентификатор целевой базы данных. Опуститеdbid, чтобы использовать эту функцию для плана в текущей базе данных. Для последнего аргумента укажитеtrue, чтобы включить план, илиfalse, чтобы отключить его. Если состояние плана было успешно изменено, возвращаетtrue.-
pgpro_multiplan_enable_template(#dbidoid,sql_hashbigint,table_hashbigint,enablebool) returns bool Включает или отключает план с шаблонами, который идентифицируется по
sql_hashиtable_hash. Аргументdbidопределяет идентификатор целевой базы данных. Опуститеdbid, чтобы использовать эту функцию для плана в текущей базе данных. Для последнего аргумента укажитеtrue, чтобы включить план, илиfalse, чтобы отключить его. Если состояние плана было успешно изменено, возвращаетtrue.-
pgpro_multiplan_set_plan_type(#sql_hashbigint,const_hashbigint,plan_typetext) returns bool Устанавливает тип плана запроса для замороженного оператора. Допустимые значения аргумента
plan_type:serializedиhintset. Чтобы иметь возможность использовать планы на основе указаний, необходимо загрузить расширение pg_hint_plan. Если тип плана был успешно изменён, возвращаетtrue.-
pgpro_multiplan_update_hintset(#dbidoid,sql_hashbigint,const_hashbigint,hintsettext) returns bool Заменяет сгенерированный набор указаний для замороженного плана типа
hintsetна указанный набор пользовательских указаний. План идентифицируется поsql_hashиconst_hash. Аргументdbidопределяет идентификатор целевой базы данных. Опуститеdbid, чтобы использовать эту функцию для плана в текущей базе данных. Аргументhintsetзадаёт строку с пользовательскими указаниями. Эта строка не должна задаваться в комментарии особого вида, используемого в pg_hint_plan, то есть она не должна начинаться с символов/*+и заканчиваться символами*/. Если набор указаний был успешно изменён, возвращаетtrue.-
pgpro_multiplan_update_template_hintset(#dbidoid,sql_hashbigint,table_hashbigint,hintsettext) returns bool Заменяет сгенерированный набор указаний для плана с шаблонами на указанный набор пользовательских указаний. План идентифицируется по
sql_hashиtable_hash. Аргументdbidопределяет идентификатор целевой базы данных. Опуститеdbid, чтобы использовать эту функцию для плана в текущей базе данных. Аргументhintsetзадаёт строку с пользовательскими указаниями. Эта строка не должна задаваться в комментарии особого вида, используемого в pg_hint_plan, то есть она не должна начинаться с символов/*+и заканчиваться символами*/. Если набор указаний был успешно изменён, возвращаетtrue.-
pgpro_multiplan_update_baseline_cost(#sql_hashbigint,plan_hashbigint) returns bool Обновляет стоимость указанного разрешённого плана в постоянном хранилище pgpro_multiplan. Если стоимость была успешно обновлена, возвращает
true.-
pgpro_multiplan_captured_clean() returns bigint# Удаляет все записи из представления pgpro_multiplan_captured_queries. Возвращает количество удалённых записей.
-
get_sql_hash(#query_stringtext) returns bigint Возвращает внутренний идентификатор (
sql_hash) для указанного запроса.-
set_aqe_trigger(trigger_nametext,trigger_valint,query_stringtext) returns bool
set_aqe_trigger(#trigger_nametext,trigger_valdouble precision,query_stringtext) 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(#dbidoid) returns bigint Удаляет все записи из представления
aqe_triggersдля указанной базы данных. Чтобы очистить представлениеaqe_triggersдля текущей базы данных, не указывайтеdbid. Чтобы удалить записи из представления для всех баз данных, установите для параметраdbidзначениеNULL. Возвращает количество удалённых записей.-
aqe_stats_reset(#dbidoid) returns bigint Удаляет все записи из представления
aqe_statsдля указанной базы данных. Чтобы очистить представлениеaqe_statsдля текущей базы данных, не указывайтеdbid. Чтобы удалить записи из представления для всех баз данных, установите для параметраdbidзначениеNULL. Возвращает количество удалённых записей.-
pgpro_multiplan_restore(#query_stringtext,sample_stringtext,hintstrtext,paramtypesregtype[],plan_typetext,wildcardstext,statusint) returns record Восстанавливает план для указанного запроса в текущую базу данных.
Эта функция имеет следующие аргументы:
query_string: запрос с параметрами$(аналогичноnPREPARE), для которого восстанавливается план на основе набора указаний.statement_nameASsample_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, в виде списка, разделённого запятыми. Доступны следующие значения:
auto_approve_plans: включает управление планами в реальном времени.individual_triggers: разрешает указывать индивидуальные значения триггеров AQE.statistics: включает сбор статистики 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
- G.4.2. Installation
- G.4.3. Supported Modes and Plan Types
- G.4.4. Plan Identification
- G.4.5. Freezing Plans
- G.4.6. Capturing and Approving Plans
- G.4.7. Plan Backup and Restore
- G.4.8. Compatibility with Other Extensions
- G.4.9. Automatic Type Casting
- G.4.10. Plan Usage Statistics
- G.4.11. Integration with AQE
- G.4.12. Views
- G.4.13. Functions
- G.4.14. Configuration Parameters
- G.4.2. Installation
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:
Add the library name to the
shared_preload_librariesvariable in thepostgresql.conffile:shared_preload_libraries = 'pgpro_multiplan'
Note that the library names in the
shared_preload_librariesvariable must be added in the specific order, for information on compatibility of pgpro_multiplan with other extensions, see Section G.4.8.Reload the database server for the changes to take effect.
To verify that the
pgpro_multiplanlibrary was installed correctly, you can run the following command:SHOW shared_preload_libraries;
Create the pgpro_multiplan extension using the following query:
CREATE EXTENSION pgpro_multiplan;
It is essential that the library is preloaded during server startup because pgpro_multiplan has a shared memory cache that can be initialized only during startup. The pgpro_multiplan extension should be created in each database where query management is required.
Enable the pgpro_multiplan extension, which is disabled by default. 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.modeparameter in thepostgresql.conffile.To activate the extension in the current session, use the following command:
SET pgpro_multiplan.mode = 'frozen';
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
onon 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.wildcardsvalue 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_hashandconst_hash.sql_hashis 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 differentsql_hashvalues.const_hashis computed from all constants used in the query. Constants with the same value but different data types, such as1and'1', produce different hash values.Template plans are identified by a combination of
sql_hashandtable_hash.sql_hashis computed from the parse tree while ignoring table names, parameters, and constants.table_hashis computed from table names that do not match the regular expression specified in the pgpro_multiplan.wildcards parameter.const_hashfor these plans is always equal to0.Baselines are identified by a combination of
sql_hashandplan_hash.sql_hashis computed in the same way as for frozen plans.plan_hashis an internal plan ID.const_hashfor these plans is always equal to0.
G.4.5. Freezing Plans #
To freeze certain query execution plans for future usage, perform the following steps:
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';
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_stringis your query with$parameters (same as innPREPARE). You can describe each parameter type with the optionalstatement_nameASparameter_typeargument 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 ofsql_hashandconst_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
onto register queries executed using theEXPLAINandEXPLAIN EXECUTEcommands 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;
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.
Freeze the query execution plan using the pgpro_multiplan_freeze function. Set the optional
plan_typeargument toserialized,hintset,template, orbaselinedepending on the type of the plan you want to create. If you omit this argument,serializedis used by default.SELECT pgpro_multiplan_freeze('serialized'); pgpro_multiplan_freeze ------------------------ t (1 row)If you create a hint-set, template, or allowed plan, enable the pg_hint_plan extension. For more information, see Compatibility with Other Extensions.
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:
Specify the
baselinevalue in the pgpro_multiplan.mode parameter.Enable the pg_hint_plan extension. For more information, see the Compatibility with Other Extensions section.
Set the pgpro_multiplan.auto_capturing parameter to
onto capture all the executed queries. These captured queries can be accessed in the pgpro_multiplan_captured_queries view.Approve any captured plan using the pgpro_multiplan_captured_approve function with the specified
dbid,sql_hash, andplan_hasharguments.Set the pgpro_multiplan.auto_capturing parameter to
offafter 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_namein 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.
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';
Upgrade the server.
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:
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';
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:
Connect to the source server.
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';
Use the pg_dump utility to dump the table to a file.
$ pg_dump --table storage_copy -Ft postgres > storage_copy.tar
Connect to the target server and database.
Move the created dump file to the target file system.
Use the pg_restore utility to restore the table with plans from the dump file.
$ pg_restore --dbname postgres -Ft storage_copy.tar
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:
Set the pgpro_multiplan.sandbox parameter to
onand 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';
Set the
pgpro_multiplan.sandboxparameter tooffand 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:
Set the aqe_enable parameter to
onto activate AQE.Specify one or more required AQE triggers using the aqe_sql_execution_time_trigger, aqe_rows_underestimation_rate_trigger, and aqe_backend_memory_used_trigger parameters.
Specify the
baselinevalue in the pgpro_multiplan.mode parameter to enable the use of allowed plans.Specify the
auto_approve_plansvalue in the pgpro_multiplan.aqe_mode parameter.
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:
Set the aqe_enable parameter to
onto activate AQE.Specify the
individual_triggersvalue in the pgpro_multiplan.aqe_mode parameter.Call the set_aqe_trigger function for each query whose triggers you want to override.
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:
Set the aqe_enable parameter to
onto activate AQE.Specify the
statisticsvalue in the pgpro_multiplan.aqe_mode parameter.Enable query ID computation using the compute_query_id parameter.
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
| Name | Type | Description |
|---|---|---|
dbid | oid | ID of the database where the query is executed |
sql_hash | bigint | Internal query ID |
const_hash | bigint | Hash of non-parameterized constants for frozen plans, 0 for template and allowed plans |
plan_hash | bigint | Internal ID of the allowed plan, 0 for frozen and template plans |
valid | boolean | FALSE if the plan was invalidated the last time it was used |
cost | float | Cost of the allowed plan, 0 for frozen and template plans |
sample_string | text | Non-parameterized query with constants |
query_string | text | Parameterized query |
paramtypes | regtype[] | Array with parameter types used in the query |
query | text | Internal representation of the query |
plan | text | Internal representation of the plan |
plan_type | text | Plan type. For frozen plans: serialized or hintset. For template plans: template. For allowed plans: baseline |
hintstr | text | Set of hints formed based on the plan |
wildcards | text | Regular 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
| Name | Type | Description |
|---|---|---|
sql_hash | bigint | Internal query ID |
const_hash | bigint | Hash of non-parameterized constants |
fs_is_frozen | boolean | TRUE if the query plan is frozen |
fs_is_valid | boolean | TRUE if the frozen statement is valid |
ps_is_valid | boolean | TRUE if the corresponding prepared statement is valid |
query_string | text | Query text |
query | text | Internal representation of the query |
paramtypes | regtype[] | Array with parameter types used in the query |
hintstr | text | Set of hints formed based on the 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
| Name | Type | Description |
|---|---|---|
dbid | oid | ID of the database where the query is executed |
sql_hash | bigint | Internal query ID |
queryid | bigint | Standard query ID |
plan_hash | bigint | Internal plan ID |
planid | bigint | Plan ID compatible with the pgpro_stats extension if the compute_plan_id parameter is set to on, otherwise 0 |
cost | float | Plan cost |
sample_string | text | Last used non-parameterized query with constants |
query_string | text | Last used parameterized query |
hintstr | text | Set of hints formed based on the plan |
explain_plan | text | Plan 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
| Name | Type | Description |
|---|---|---|
dbid | oid | ID of the database where the query is executed |
sql_hash | bigint | Internal query ID |
plan_hash | bigint | Internal plan ID |
planid | bigint | Plan ID compatible with the pgpro_stats extension if the compute_plan_id parameter is set to on, otherwise 0 |
counter | bigint | Number 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
| Name | Type | Description |
|---|---|---|
dbid | oid | ID of the database where the query is executed |
sql_hash | bigint | Internal query ID |
query_string | text | Parameterized query |
execution_time | int | Value for the query execution time trigger, in milliseconds. NULL if the global trigger value is used |
memory | int | Value for the backend memory consumption trigger. NULL if the global trigger value is used |
underestimation_rate | double | Factor 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
| Name | Type | Description |
|---|---|---|
dbid | oid | ID of the database where the query is executed |
sql_hash | bigint | Internal query ID |
planid | bigint | ID of the query execution plan |
query | text | Internal representation of the query |
last_updated | timestamp with time zone | Timestamp of the last statistics update |
exec_num | bigint | Number of query executions |
min_attempts | integer | Minimum number of times the query was reoptimized |
max_attempts | integer | Maximum number of times the query was reoptimized |
total_attempts | integer | Total number of times the query was reoptimized |
reason_repeated_plan | bigint | Number of times AQE was disabled because a repeated execution plan was generated |
reason_no_data | bigint | Number of times AQE was disabled because no new information was gathered during execution |
reason_max_reruns | bigint | Number of times AQE was disabled because the maximum number of reruns was reached |
reason_external | bigint | Number of times AQE was disabled by an extension, such as pgpro_multiplan |
reruns_forced | bigint | Total number of reoptimizations caused by the manual trigger |
reruns_time | bigint | Total number of reoptimizations caused by the query execution time trigger |
reruns_underestimation | bigint | Total number of reoptimizations caused by the processed number of node tuples trigger |
reruns_memory | bigint | Total number of reoptimizations caused by the backend memory trigger |
min_planning_time | double precision | Minimum time spent planning, in milliseconds |
max_planning_time | double precision | Maximum time spent planning, in milliseconds |
mean_planning_time | double precision | Mean time spent planning, in milliseconds |
stddev_planning_time | double precision | Population standard deviation of time spent planning, in milliseconds |
min_exec_time | double precision | Minimum time spent on query execution, in milliseconds |
max_exec_time | double precision | Maximum time spent on query execution, in milliseconds |
mean_exec_time | double precision | Mean time spent on query execution, in milliseconds |
stddev_exec_time | double precision | Population 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_stringtext) returns record
pgpro_multiplan_register_query(#query_stringtext,VARIADICregtype[]) returns record Registers the query specified by the
query_stringargument. Returns the unique pair ofsql_hashandconst_hash.-
pgpro_multiplan_unregister_query() returns bool# Removes the registered query. Returns
trueif there are no errors.-
pgpro_multiplan_freeze(#plan_typetext) 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_typeargument areserialized(default),hintset,template, andbaseline. Returnstrueif 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_hashbigint,const_hashbigint) returns bool Removes the specified plan from the permanent storage but keeps the query registered. Returns
trueif there are no errors.-
pgpro_multiplan_remove(#dbidoid,sql_hashbigint,const_hashbigint) returns bool Removes the frozen plan identified by the
sql_hashandconst_hasharguments from the database defined by thedbidargument. Omitdbidto remove the plan from the current database. This function operates as the pgpro_multiplan_unfreeze and pgpro_multiplan_unregister_query functions called sequentially. Returnstrueif the plan was removed successfully.-
pgpro_multiplan_reset(#dbidoid) returns bigint Removes all records from the
pgpro_multiplan_storageview for the specified database. Omitdbidto remove the data collected by pgpro_multiplan for the current database. Setdbidto 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_storageview. It also drops statements that have been registered but not frozen. Returnstrueif 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_hashbigint,const_hashbigint) returns table Returns the registered query with the specified
sql_hashandconst_hashparameters 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(#dbidoid,sql_hashbigint,plan_hashbigint) 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_storageview. Returnstrueif the plan was added successfully.-
pgpro_multiplan_remove_baseline(#dbidoid,sql_hashbigint,plan_hashbigint) returns bool Removes the allowed plan identified by the
sql_hashandplan_hasharguments from the database defined by thedbidargument. Omitdbidto remove the allowed plan from the current database. Returnstrueif the plan was removed successfully.-
pgpro_multiplan_remove_template(#dbidoid,sql_hashbigint,table_hashbigint) returns bool Removes the template plan identified by the
sql_hashandtable_hasharguments from the database defined by thedbidargument. Omitdbidto remove the template plan from the current database. Returnstrueif the plan was removed successfully.-
pgpro_multiplan_enable(#dbidoid,sql_hashbigint,const_hashbigint,enablebool) returns bool Enables or disables the frozen plan identified by the
sql_hashandconst_hasharguments. Thedbidargument defines the ID of a target database. Omitdbidto use the function for the plan in the current database. For the last argument, specifytrueto enable the plan orfalseto disable it. Returnstrueif the plan state was changed successfully.-
pgpro_multiplan_enable_baseline(#dbidoid,sql_hashbigint,plan_hashbigint,enablebool) returns bool Enables or disables the allowed plan identified by the
sql_hashandplan_hasharguments. Thedbidargument defines the ID of a target database. Omitdbidto use the function for the plan in the current database. For the last argument, specifytrueto enable the plan orfalseto disable it. Returnstrueif the plan state was changed successfully.-
pgpro_multiplan_enable_template(#dbidoid,sql_hashbigint,table_hashbigint,enablebool) returns bool Enables or disables the template plan identified by the
sql_hashandtable_hasharguments. Thedbidargument defines the ID of a target database. Omitdbidto use the function for the plan in the current database. For the last argument, specifytrueto enable the plan orfalseto disable it. Returnstrueif the plan state was changed successfully.-
pgpro_multiplan_set_plan_type(#sql_hashbigint,const_hashbigint,plan_typetext) returns bool Sets the type of the query plan for the frozen statement. The allowed values of the
plan_typeargument areserializedandhintset. To be able to use hint-set plans, the pg_hint_plan extension must be loaded. Returnstrueif the plan type was changed successfully.-
pgpro_multiplan_update_hintset(#dbidoid,sql_hashbigint,const_hashbigint,hintsettext) returns bool Replaces the generated hint set for the
hintsetfrozen plan with the specified set of custom hints. The plan is identified by thesql_hashandconst_hasharguments. Thedbidargument defines the ID of a target database. Omitdbidto use the function for the plan in the current database. Thehintsetargument 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*/. Returnstrueif the hint set was changed successfully.-
pgpro_multiplan_update_template_hintset(#dbidoid,sql_hashbigint,table_hashbigint,hintsettext) 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_hashandtable_hasharguments. Thedbidargument defines the ID of a target database. Omitdbidto use the function for the plan in the current database. Thehintsetargument 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*/. Returnstrueif the hint set was changed successfully.-
pgpro_multiplan_update_baseline_cost(#sql_hashbigint,plan_hashbigint) returns bool Updates the cost of the specified allowed plan in the permanent pgpro_multiplan storage. Returns
trueif 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_stringtext) returns bigint Returns the internal ID (
sql_hash) for the specified query.-
set_aqe_trigger(trigger_nametext,trigger_valint,query_stringtext) returns bool
set_aqe_trigger(#trigger_nametext,trigger_valdouble precision,query_stringtext) 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_triggersvalue 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:execution_time: The query execution time trigger. Corresponds to the aqe_sql_execution_time_trigger configuration parameter.memory: The backend memory consumption trigger. Corresponds to the aqe_backend_memory_used_trigger configuration parameter.underestimation_rate: The processed number of node tuples trigger. Corresponds to the aqe_rows_underestimation_rate_trigger configuration parameter.
trigger_val: The trigger value. Forexecution_timeandmemorytriggers, specify integer values. Forunderestimation_rate, you can define double precision values. To reset the individual trigger value, specifyNULLor negative value less than -1.query_string: The query text.
This function returns
trueif there are no errors.-
aqe_triggers_reset(#dbidoid) returns bigint Removes all records from the
aqe_triggersview for the specified database. To clean theaqe_triggersview for the current database, omitdbid. To remove records from this view for all databases, setdbidtoNULL. Returns the number of removed records.-
aqe_stats_reset(#dbidoid) returns bigint Removes all records from the
aqe_statsview for the specified database. To clean theaqe_statsview for the current database, omitdbid. To remove records from this view for all databases, setdbidtoNULL. Returns the number of removed records.-
pgpro_multiplan_restore(#query_stringtext,sample_stringtext,hintstrtext,paramtypesregtype[],plan_typetext,wildcardstext,statusint) returns record Restores the plan for the specified query into the current database.
This function has the following arguments:
query_string: The query with$parameters (same as innPREPARE) for which to restore the plan based on a set of hints.statement_nameASsample_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 thebaselineandtemplateplan types, this argument cannot be set to an empty string. For other plan types, if this argument is set toNULLor empty string, the standard plan is used.parameter_type: An array with parameter types used in the query. If this argument is set toNULL, parameter types should be determined automatically.plan_type: The plan type. Allowed values areserialized,hintset,baseline, andtemplate. For details about plan types, see Supported Modes and Plan Types.wildcards: The POSIX regular expression used only for thetemplateplan type to check matching of table names. For other plan types, this argument is ignored, and you can set it toNULL.status: This argument is reserved for future usage. Currently, always set it toNULLor0.
The function returns the unique pair of
sql_hashandconst_hashif the plan was restored successfully. Otherwise, it returnsNULL.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.modeparameter 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:
auto_approve_plans: Enables real-time plan management.individual_triggers: Enables you to specify individual AQE trigger values.statistics: Enables collecting AQE statistics.
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_modeparameter 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_statsview. 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
EXPLAINandEXPLAIN EXECUTEcommands automatically. The default value isoff. 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
offon the primary, no data is transferred from it to a standby. When set tooffon a standby, any data transferred from the primary is ignored. The default value isoff.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
onallows 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 isoff. 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
onon 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 isoff. 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
wildcardsvalue in the pgpro_multiplan.mode parameter. Thepgpro_multiplan.wildcardsvalue 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
EXPLAINoutput. The default value ison. 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
EXPLAINoutput. The default value isoff. 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.