Расширение sr_plan позволяет пользователям сохранять отдельные планы параметризованных запросов для использования в будущем вне зависимости от изменения параметров планировщика.
Модуль sr_plan действует подобно системе Oracle Outline. Используя его, вы можете жёстко зафиксировать план выполнения. Это бывает необходимо, если вы уверены, что планировщик не сможет выработать лучший план, или вы хотите использовать собственный. Обычно системные администраторы работают с запросами интерактивно и сохраняют лучшие планы для дальнейшего использования в случаях, когда время обработки запроса должно быть предсказуемым. Тогда приложение, работающее с этими запросами, будет использовать сохранённые планы.
Модуль sr_plan включён в состав Postgres Pro Enterprise как стандартное расширение. Чтобы задействовать sr_plan, выполните следующие действия:
Измените файл конфигурации postgresql.conf следующим образом:
shared_preload_libraries = 'sr_plan' sr_plan.enable = 'true'
Перезагрузите сервер баз данных, чтобы изменения вступили в силу.
Чтобы убедиться, что библиотека sr_plan установлена правильно, вы можете выполнить следующую команду:
SHOW shared_preload_libraries;
Создайте расширение sr_plan, выполнив следующий запрос:
CREATE EXTENSION sr_plan;
Расширение sr_plan использует кеш разделяемой памяти, который инициализируется только при запуске сервера, поэтому данная библиотека также должна предзагружаться при запуске. Расширение sr_plan следует создать в каждой базе данных.
Рассмотрим типичный случай, когда есть запрос с низкой производительностью и выбран неудачный план (например, из-за недооценки избирательности используется соединение вложенным циклом вместо хеш-соединения) и есть представление, как исправить этот план (например, SET enable_nestloop = 'off'). Модуль sr_plan позволяет заморозить планы для использования в будущем независимо от возможных изменений параметров планировщика. Сначала необходимо зарегистрировать запрос для работы под управлением sr_plan:
SELECT sr_register_query(query_string,parameter_type, ...);
Здесь query_string — ваш запрос с параметрами $ (аналогично nPREPARE ). Можно описать каждый тип параметра, используя необязательный аргумент функции statement_name ASparameter_type, или отказаться от явного определения типов параметров. В последнем случае Postgres Pro пытается определить тип каждого параметра из контекста. Эта функция возвращает уникальный идентификатор запроса srid и внутренний queryid. Обязательно запомните уникальный srid до конца заморозки плана. Теперь sr_plan будет отслеживать выполнение запросов, соответствующих сохранённому шаблону параметризованного запроса. Для получения приемлемого плана запросов можно использовать любые методы. Затем выполните:
SELECT sr_plan_freeze(srid);Здесь следует использовать идентификатор, возвращаемый функцией 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 srid FROM sr_register_query('SELECT count(*)
FROM a
WHERE x = 1 OR (x > $2 AND x < $1) OR x = $1', 'int', 'int');
srid
-------
1
(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) (cost=0.00..0.00 rows=0 width=0)
SR_PLAN: frozen plan
-> 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 < $1)) OR (x = $1))Отключите SeqScan и выполните команду снова:
SET enable_seqscan = 'off';
Custom Scan (SRScan) (cost=0.00..0.00 rows=0 width=0)
SR_PLAN: frozen plan
-> Aggregate (cost=12.89..12.90 rows=1 width=8)
-> Index Only Scan using a_x_idx on a (cost=0.14..12.89 rows=2 width=0)
Filter: ((x = 1) OR ((x > $2) AND (x < $1)) OR (x = $1))
(5 rows)Заморозьте план. Запрос будет обрабатываться как сканирование только индекса, даже если вы измените параметры планировщика:
SELECT sr_plan_freeze(1); RESET enable_seqscan;
sr_plan_storageПредставление sr_plan_storage содержит подробную информацию обо всех замороженных операторах. Столбцы представления показаны в Таблице F.119.
Таблица F.119. Столбцы sr_plan_storage
| Имя | Тип | Описание |
|---|---|---|
srid | int | Уникальный идентификатор оператора |
dbid | oid | Идентификатор базы данных, в которой выполнялся оператор |
valid | boolean | FALSE, если план был аннулирован при последнем использовании |
query_string | text | Запрос, зарегистрированный функцией sr_register_query |
queryid | bigint | Внутренний идентификатор запроса |
paramtypes | regtype[] | Массив с типами параметров, использованными в запросе |
query | text | Внутреннее представление запроса |
plan | text | Внутреннее представление плана |
sr_register_query(query_string text) returns record
Сохраняет запрос, описанный в query_string, в локальном кеше и возвращает уникальный srid и внутренний queryid.
sr_unregister_query(srid int) returns bool
Удаляет из локального кеша запрос, который был зарегистрирован, но не был заморожен. Если нет ошибок, возвращает true.
sr_plan_freeze(srid int) returns bool
Замораживает последний план, использованный для оператора с указанным идентификатором. Если нет ошибок, возвращает true.
sr_plan_unfreeze(srid int) returns bool
Удаляет план только из хранилища, но оставляет запрос в локальном кеше. Если нет ошибок, возвращает true.
sr_plan_remove(srid int) returns bool
Удаляет замороженный оператор с указанным идентификатором. Работает как функции 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(srid int) returns table
Возвращает зарегистрированный запрос с указанным идентификатором, даже если он не заморожен, только для целей отладки. Работает, если запрос зарегистрирован в текущем обслуживающем процессе или заморожен в текущей базе данных.
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_freeze (boolean)Замораживает каждый запрос, обрабатываемый планировщиком. Используется только для целей отладки. Значение по умолчанию — off. Изменить этот параметр могут только суперпользователи.
Используйте явное приведение, чтобы 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.