F.74. sr_plan — сохранение отдельных планов параметризованных запросов для использования в будущем #
F.74.1. Обоснование #
Расширение sr_plan позволяет пользователям сохранять отдельные планы параметризованных запросов для использования в будущем вне зависимости от изменения параметров планировщика.
Модуль sr_plan действует подобно системе Oracle Outline. Используя его, вы можете жёстко зафиксировать план выполнения. Это бывает необходимо, если вы уверены, что планировщик не сможет выработать лучший план, или вы хотите использовать собственный. Обычно системные администраторы работают с запросами интерактивно и сохраняют лучшие планы для дальнейшего использования в случаях, когда время обработки запроса должно быть предсказуемым. Тогда приложение, работающее с этими запросами, будет использовать сохранённые планы.
F.74.2. Установка #
Модуль sr_plan включён в состав Postgres Pro Enterprise как стандартное расширение. Чтобы задействовать sr_plan, выполните следующие действия:
Измените файл конфигурации
postgresql.conf
следующим образом:shared_preload_libraries = 'sr_plan' sr_plan.enable = 'true'
Модуль sr_plan следует указывать последним в
shared_preload_libraries
, если планируется использовать его вместе с расширением pgpro_stats:shared_preload_libraries = 'pgpro_stats, sr_plan'
Перезагрузите сервер баз данных, чтобы изменения вступили в силу.
Примечание
Чтобы убедиться, что библиотека
sr_plan
установлена правильно, вы можете выполнить следующую команду:SHOW shared_preload_libraries;
Создайте расширение
sr_plan
, выполнив следующий запрос:CREATE EXTENSION sr_plan;
Расширение sr_plan использует кеш разделяемой памяти, который инициализируется только при запуске сервера, поэтому данная библиотека также должна предзагружаться при запуске. Расширение sr_plan следует создать в каждой базе данных.
F.74.3. Использование #
Рассмотрим типичный случай, когда есть запрос с низкой производительностью и выбран неудачный план (например, из-за недооценки избирательности используется соединение вложенным циклом вместо хеш-соединения) и есть представление, как исправить этот план (например, SET enable_nestloop = 'off'
). Модуль sr_plan позволяет заморозить планы для использования в будущем независимо от возможных изменений параметров планировщика. Сначала необходимо зарегистрировать запрос для работы под управлением sr_plan:
SELECT sr_register_query(query_string
,parameter_type
, ...);
Здесь query_string
— ваш запрос с параметрами $
(аналогично n
PREPARE
). Можно описать каждый тип параметра, используя необязательный аргумент функции statement_name
ASparameter_type
, или отказаться от явного определения типов параметров. В последнем случае Postgres Pro пытается определить тип каждого параметра из контекста. Эта функция возвращает уникальную пару queryid
и const_hash
. Обязательно сохраните уникальные queryid
и const_hash
до конца заморозки плана. Теперь sr_plan будет отслеживать выполнение запросов, соответствующих сохранённому шаблону параметризованного запроса. Для получения приемлемого плана запросов можно использовать любые методы. Затем выполните:
SELECT sr_plan_freeze(queryid
,const_hash
);
Здесь следует использовать значения queryid
и const_hash
, возвращаемые функцией sr_register_query
. Теперь sr_plan хранит последний использованный план запроса в хранилище файлов, общей памяти и локальном кеше.
Ниже показано, как использовать sr_plan.
Создайте таблицу:
CREATE TABLE a AS (SELECT * FROM generate_series(1,30) AS x); CREATE INDEX ON a(x); ANALYZE;
Зарегистрируйте запрос:
SELECT queryid, const_hash FROM sr_register_query('SELECT count(*) FROM a WHERE x = 1 OR (x > $2 AND x < $1) OR x = $1', 'int', 'int') queryid | const_hash ----------------------+------------ 5393873830515778388 | 15498345 (1 row)
Выполните запрос с определёнными значениями параметров:
SELECT count(*) FROM a WHERE x = 1 OR (x > 11 AND x < 22) OR x = 22;
Выполнив команду
EXPLAIN
, можно увидеть, что этот запрос отслеживается sr_plan:EXPLAIN SELECT count(*) FROM a WHERE x = 1 OR (x > 11 AND x < 22) OR x = 22; Custom Scan (SRScan) (actual rows=1 loops=1) Plan is: tracked Query ID: 5393873830515778388 Const hash: 15498345 -> Aggregate (actual rows=1 loops=1) -> Seq Scan on a (actual rows=10 loops=1) Filter: ((x = 1) OR ((x > $2) AND (x < $1)) OR (x = $1)) Rows Removed by Filter: 20
Отключите
SeqScan
и выполните команду снова:SET enable_seqscan = 'off'; Custom Scan (SRScan) (actual rows=1 loops=1) Plan is: tracked Query ID: 5393873830515778388 Const hash: 15498345 -> Aggregate (actual rows=1 loops=1) -> Index Only Scan using a_x_idx2 on a (actual rows=10 loops=1) Filter: ((x = 1) OR ((x > $2) AND (x < $1)) OR (x = $1)) Rows Removed by Filter: 20 Heap Fetches: 30 (5 rows)
Заморозьте план. Запрос будет обрабатываться как сканирование только индекса, даже если вы измените параметры планировщика:
SELECT sr_plan_freeze(5393873830515778388, 15498345); RESET enable_seqscan;
Заморозьте план полностью параметризованного запроса, используя параметр конфигурации sr_plan.auto_tracking:
SET sr_plan.auto_tracking = on; EXPLAIN SELECT count(*) FROM a WHERE x = 1 OR (x > 11 AND x < 22) OR x = 22; Custom Scan (SRScan) (cost=1.60..0.00 rows=1 width=8) Plan is: tracked Query ID: 5393873830515778388 Const hash: 0 -> Aggregate (cost=1.60..1.61 rows=1 width=8) -> Seq Scan on a (cost=0.00..1.60 rows=2 width=0) Filter: ((x = $1) OR ((x > $2) AND (x < $3)) OR (x = $4)) -- Укажите возвращённый 'Tracked plan ID': SELECT sr_plan_freeze(5393873830515778388, 0); RESET sr_plan.auto_tracking;
F.74.4. Представления #
F.74.4.1. Представление sr_plan_storage
#
Представление sr_plan_storage
содержит подробную информацию обо всех замороженных операторах. Столбцы представления показаны в Таблице F.139.
Таблица F.139. Столбцы sr_plan_storage
Имя | Тип | Описание |
---|---|---|
dbid | oid | Идентификатор базы данных, в которой выполнялся оператор |
queryid | bigint | Внутренний идентификатор запроса |
const_hash | bigint | Хеш непараметризованных констант |
valid | boolean | FALSE , если план был аннулирован при последнем использовании |
query_string | text | Запрос, зарегистрированный функцией sr_register_query |
paramtypes | regtype[] | Массив с типами параметров, использованными в запросе |
query | text | Внутреннее представление запроса |
plan | text | Внутреннее представление плана |
hintstr | text | Набор указаний, сформированный на основе замороженного плана |
F.74.4.2. Представление sr_plan_local_cache
#
Представление sr_plan_local_cache
содержит подробную информацию о зарегистрированных и замороженных операторах в локальном кеше. Столбцы представления показаны в Таблице F.140.
Таблица F.140. Столбцы sr_plan_local_cache
Имя | Тип | Описание |
---|---|---|
queryid | bigint | Внутренний идентификатор запроса |
const_hash | bigint | Хеш непараметризованных констант |
fs_is_frozen | boolean | TRUE , если оператор был заморожен |
fs_is_valid | boolean | TRUE , если оператор действителен |
ps_is_valid | boolean | TRUE , если оператор должен быть перепроверен |
query_string | text | Запрос, зарегистрированный функцией sr_register_query |
query | text | Внутреннее представление запроса |
paramtypes | regtype[] | Массив с типами параметров, использованными в запросе |
hintstr | text | Набор указаний, сформированный на основе замороженного плана |
F.74.5. Функции #
-
sr_register_query(
#query_string
text
) returns record Сохраняет запрос, описанный в
query_string
, в локальном кеше и возвращает уникальную паруqueryid
иconst_hash
.-
sr_unregister_query(
#queryid
bigint
const_hash
bigint
) returns bool Удаляет из локального кеша запрос, который был зарегистрирован, но не был заморожен. Если нет ошибок, возвращает true.
-
sr_plan_freeze(
#queryid
bigint
const_hash
bigint
plan_type
text
) returns bool Замораживает последний использованный план для оператора с указанными
queryid
,const_hash
иplan_type
. Допустимые значения необязательного аргументаplan_type
:serialized
иhintset
. Значениеserialized
показывает, что используется план запроса, основанный на сериализованном представлении. При использованииhintset
sr_plan использует план запроса на основе набора указаний, который формируется на этапе выполнения зарегистрированного запроса. Если аргументplan_type
опущен, по умолчанию используетсяserialized
план запроса. При отсутствии ошибок возвращает true.-
sr_plan_unfreeze(
#queryid
bigint
const_hash
bigint
) returns bool Удаляет план только из хранилища, но оставляет запрос в локальном кеше. Если нет ошибок, возвращает true.
-
sr_plan_remove(
#queryid
bigint
const_hash
bigint
) returns bool Удаляет замороженный оператор с указанными
queryid
иconst_hash
. Работает как функцииsr_plan_unfreeze
иsr_unregister_query
, вызываемые последовательно. Если нет ошибок, возвращает true.-
sr_plan_reset(
#dbid
oid
) returns bigint Удаляет все записи в хранилище sr_plan для указанной базы данных. Чтобы удалить данные, собранные sr_plan для текущей базы данных, не указывайте
dbid
. Чтобы сбросить данные для всех баз данных, установите для параметраdbid
значение NULL.-
sr_reload_frozen_plancache() returns bool
# Удаляет все замороженные планы и снова загружает их из хранилища. Также удаляет операторы, которые были зарегистрированы, но не заморожены.
-
sr_plan_fs_counter() returns table
# Возвращает количество использований каждого замороженного оператора и идентификатор базы данных, в которой этот оператор был зарегистрирован и использован.
-
sr_show_registered_query(
#queryid
bigint
const_hash
bigint
) returns table Возвращает зарегистрированный запрос с указанными
queryid
иconst_hash
, даже если он не заморожен, только для целей отладки. Работает, если запрос зарегистрирован в текущем обслуживающем процессе или заморожен в текущей базе данных.-
sr_set_plan_type(
#queryid
bigint
const_hash
bigint
plan_type
text
) returns bool Устанавливает тип плана запроса для замороженного оператора. Допустимые значения аргумента
plan_type
:serialized
иhintset
. Чтобы иметь возможность использовать план запроса типаhintset
, необходимо загрузить модуль pg_hint_plan. Если тип плана был успешно изменён, возвращает true.
F.74.6. Параметры конфигурации #
sr_plan.enable
(boolean
) #Позволяет sr_plan использовать замороженные планы. Значение по умолчанию —
off
. Изменить этот параметр могут только суперпользователи.sr_plan.max
(integer
) #Задаёт максимальное количество замороженных операторов, возвращаемых функцией
sr_plan_fs_counter()
. Значение по умолчанию — 5000. Этот параметр можно задать только при запуске сервера.sr_plan.max_items
(integer
) #Задаёт максимальное количество записей, с которым может работать sr_plan. Значение по умолчанию — 1000. Этот параметр можно задать только при запуске сервера.
sr_plan.auto_tracking
(boolean
) #Позволяет sr_plan автоматически нормализовать и регистрировать запросы, выполняемые с использованием команды
EXPLAIN
. Значение по умолчанию —off
. Изменить этот параметр могут только суперпользователи.sr_plan.auto_freeze
(boolean
) #Замораживает каждый запрос, обрабатываемый планировщиком. Используется только для целей отладки. Значение по умолчанию —
off
. Изменить этот параметр могут только суперпользователи.
F.74.7. Общие рекомендации #
Используйте явное приведение, чтобы sr_plan точно находил замороженный план для вашего запроса. Например, следующие операторы sr_plan воспринимает как разные:
SELECT * FROM a WHERE x = 1::integer SELECT * FROM a WHERE x = 1::bigint
Если в очереди обработчиков планировщика sr_plan не стоит последним, стандартный планировщик, вызываемый последним обработчиком в очереди, оптимизирует нагрузку (иногда значительно). Таким образом, если план уже заморожен, sr_plan удалит такой только что созданный план. Чтобы избежать ненужных издержек, sr_plan должен быть указан последним в списке библиотек shared_preload_libraries
.