Производительность
Сохранение планов запросов
16
Авторские права
© Postgres Professional, 2023–2026
Авторы: Алексей Береснев, Илья Баштанов, Павел Толмачев, Игорь
Гнатюк
Фото: Олег Бартунов (монастырь Пху и пик Бхрикути, Непал)
Использование материалов курса
Некоммерческое использование материалов курса (презентации,
демонстрации) разрешается без ограничений. Коммерческое
использование возможно только с письменного разрешения компании
Postgres Professional. Запрещается внесение изменений в материалы
курса.
Обратная связь
Отзывы, замечания и предложения направляйте по адресу:
edu@postgrespro.ru
Отказ от ответственности
Компания Postgres Professional не несет никакой ответственности за
любые повреждения и убытки, включая потерю дохода, нанесенные
прямым или непрямым, специальным или случайным использованием
материалов курса. Компания Postgres Professional не предоставляет
каких-либо гарантий на материалы курса. Материалы курса
предоставляются на основе принципа «как есть» и компания Postgres
Professional не обязана предоставлять сопровождение, поддержку,
обновления, расширения и изменения.
2
Темы
Модуль pgpro_multiplan
Замороженные планы
Песочница
Планы с шаблонами
Базовые планы
Совместная работа с перепланированием
Расширение pgpro_multiplan позволяет «заморозить» определенный
план выполнения запроса (или привязать план к целой группе
запросов) и при дальнейшем исполнении запроса использовать этот
план вместо того, который построил бы планировщик. Стабилизация
некоторых планов полезна, например, при обновлении сервера
(в новых версиях планировщик в среднем работает лучше, но планы
небольшой части запросов могут измениться и в худшую сторону),
или для оптимизации отдельного запроса, когда не удается иными
средствами убедить планировщик построить нужный план.
Замороженные планы сохраняются в файлы в отдельном каталоге,
а работа с ними происходит в разделяемой памяти. Таким образом,
в отличие от обычного локального кеша разобранных запросов и их
планов, кеш замороженных планов — общий для всех сеансов.
Планы хранятся в файлах, а не в таблицах БД, чтобы обеспечить
совместимость расширения с системой 1С. Обратной стороной такого
решения является то, что замороженные планы не передаются
с мастера на реплику автоматически: если это необходимо, требуется
включить параметр pgpro_multiplan.wal_rw на обоих серверах.
До версии Postgres Pro Enterprise 16 включительно в продукте
присутствует расширение sr_plan с аналогичным функционалом.
Начиная с версии 15, оно считается устаревшим и вместо него
рекомендуется использовать расширение pgpro_multiplan.
3
Модуль pgpro_multiplan
Стабилизация планов выполнения запросов
заморозка плана для конкретного запроса или группы запросов
разделяемый кеш замороженных планов
планы сохраняются в файлах сервера,
для репликации: pgpro_multiplan.wal_rw
Заменяет устаревшее расширение sr_plan
План может сохраняться в двух вариантах.
Сериализованный план — это дерево плана, представленное в виде
текстовой строки.
Сериализованный план действителен только в текущей базе данных,
он зависит от идентификаторов объектов, структуры таблиц и индексов.
Например, если пересоздать используемую в плане таблицу (даже
с тем же именем), то сохраненный план станет недействительным.
Преимущество сериализованных планов состоит в том, что запрос при
выполнении не требуется планировать.
Набор указаний — хранится как указания расширения pg_hint_plan.
Для этого требуется, чтобы было установлено и само расширение
pg_hint_plan, иначе указания будут проигнорированы. В таком случае
план не зависит от идентификаторов объектов. Запрос при выполнении
приходится планировать, но указания подсказывают планировщику,
какой именно план требуется получить.
4
Типы планов
Сериализованный — serialized
хранится внутреннее представление плана
зависит от системного каталога: OID, структуры таблиц и т. п.
действует только в текущей базе данных
планирование не требуется
Набор указаний — hintset
хранятся указания расширения pg_hint_plan
запрос планируется с учетом указаний
Первый из режимов работы расширения — заморозка плана — состоит
из трех этапов: регистрации запроса, построения плана и собственно
заморозки.
Зарегистрировать запрос можно одним из двух способов:
●
функцией pgpro_multiplan_register_query, в которую необходимо
передать текст запроса (можно в параметризованном виде) и список
типов параметров (можно доверить расширению автоматически
определить типы);
●
с помощью автоматической регистрации запросов (параметр
pgpro_multiplan.auto_tracking, по умолчанию выключен).
При этом запрос идентифицируется двумя хеш-кодами:
●
sql_hash — вычисляется по дереву разбора без учета параметров
и констант (тот же хеш-код используется, например, в расширении
pg_stat_statements; требуется включенный compute_query_id);
●
const_hash — вычисляется по типам и значениям констант.
Далее запрос планируется, при этом допускается влиять на план
любыми доступными способами: параметрами оптимизатора,
с помощью расширений AQO или pg_hint_plan и т. п.
На последнем шаге необходимо заморозить последний выполненный
план с помощью функции pgpro_multiplan_freeze. После этого при
выполнении зарегистрированного запроса будет обеспечиваться
применение замороженного плана. Замороженный план будет
применяться только к точно такому же запросу, для которого
замораживался план.
5
pgpro_multiplan_storage
Заморозка плана
можно
влиять
регистрация запроса
заморозка плана (serialize или hintset)
SELECT *
FROM bookings
WHERE book_ref > '800000'
sql hash: 457033572132829198
const hash: 1320965230
Sort
Sort Key
-> Seq Scan
sql: 457033572132829198
const: 1320965230
Sort
Sort Key
-> Seq Scan
планирование
типы и значения
констант
дерево разбора
без параметров
и констант
Расширение pgpro_multiplan позволяет задействовать для хранения
замороженных планов песочницу: отдельную, независимую от
основного хранилища область разделяемой памяти и каталог на диске.
Переключением на альтернативное хранилище планов управляет
параметр pgpro_multiplan.sandbox (по умолчанию он выключен). Менять
значение этого параметра могут только суперпользователи.
И к основному хранилищу, и к песочнице существует только один общий
интерфейс доступа — представление pgpro_multiplan_storage.
План, сохраненный в песочнице, можно перенести в основное
хранилище. Для этого необходимо сохранить данные из представления
pgpro_multiplan_storage в отдельную таблицу, затем отключить
песочницу и восстановить данные в основное хранилище, используя
функцию pgpro_multiplan_restore.
Данные из песочницы не реплицируются на ведомый сервер.
7
Песочница
Альтернативное хранилище планов
pgpro_multiplan.sandbox = off
необходимы права суперпользователя
Поддерживается перенос планов в основное хранилище
ручное копирование из представления pgpro_multiplan_storage
данные не реплицируются из песочницы на ведомый сервер
План с шаблонами (template) хранится в виде набора указаний, как и
план с указаниями (hintset). Отличие состоит в том, что такой план
применяется ко всех запросам, совпадающим с исходным с точностью
до имен таблиц.
Говоря точнее, запрос идентифицируется двумя хеш-кодами:
●
sql_hash — построен по дереву разбора,
●
table_hash — построен по именам таблиц, не соответствующим
регулярному выражению из параметра pgpro_multiplan.wildcards.
Константы (const_hash) не учитываются. Значение параметра
pgpro_multiplan.wildcards сохраняется вместе с замороженным планом
(table_hash не сохраняется, а вычисляется при необходимости).
План с шаблонами позволяет применять один и тот же план к запросам
с одинаковой структурой, но разными названиями таблиц (например,
запросы к временным таблицам, генерируемые системой 1С).
Такой план применяется, только если не нашлось точного соответствия
(плана с типом serialized или hintset).
9
Планы с шаблонами
pgpro_multiplan_storage
можно
влиять
регистрация запроса
заморозка плана (template)
SELECT *
FROM bookings
WHERE book_ref > '800000'
sql hash: 457033572132829198
table hash: 289967942
Sort
Sort Key
-> Seq Scan
sql: 457033572132829198
wildcards: ^t[0-9]\_bookings
Sort
Sort Key
-> Seq Scan
планирование
дерево разбора
без параметров
и констант
таблицы, не
соответствующие
wildcards
Базовые планы применяют, когда для одного запроса может
существовать несколько удачных планов. Если планировщик выбрал
один из базовых планов, запрос выполняется с этим планом.
В противном случае расширение принудительно устанавливает для
запроса самый дешевый из существующих базовых планов. Базовые
планы рассматриваются, только если не нашлось замороженного плана
(с типом serialized, hintset или template).
Базовые планы идентифицируются двумя хеш-кодами:
●
sql_hashr— вычисляется так же, как и для замороженных планов;
●
plan_hashr— внутренний идентификатор плана.
Константы не учитываются, а const_hashrвсегда равенr0.
Чтобы добавить план как базовый, можно действовать, как в случае
с заморозкой: зарегистрировать запрос, выполнить планирование
и заморозить план функцией pgpro_multiplan_freeze с аргументом
'baseline'.
Другой вариант — использовать автозахват, установив параметр
pgpro_multiplan.auto_capturing. В этом случае все выполняющиеся
запросы захватываются и их планы становятся кандидатами на
сохранение. Захваченные запросы с планами видны в представлении
pgpro_multiplan_captured_queries. Захваченный план можно одобрить,
вызвав функцию pgpro_multiplan_captured_approve, после чего он
попадает в общее хранилище и будет применяться.
11
pgpro_multiplan_captured_queries
Базовые планы
pgpro_multiplan_storage
одобрение
SELECT *
FROM bookings
WHERE book_ref > '800000'
Sort
Sort Key
-> Seq Scan
планирование
sql: 457033572132829198
plan: 1320965230
Sort
Sort Key
-> Seq Scan
автозахват
sql: 457033572132829198
plan: 1320965230
Sort
Sort Key
-> Seq Scan
pgpro_multiplan
.auto_capturing
дерево разбора
без параметров
и констант
внутренний
идентификатор
плана
Сервер может совместно использовать перепланирование и
сохранение планов.
Если стандартный планировщик ошибается и срабатывает одно из
условий перепланирования, запрос прерывается и отправляется на
повторное планирование. Расширение pgpro_multiplan может захватить
и одобрить новый план, а при последующих вызовах запроса сразу
подставить сохраненный качественный план. Система больше не
тратит ресурсы на перепланирование этого конкретного запроса —
план стабилен.
Для интеграции нужно включить параметр replan_enable, а параметр
pgpro_multiplan.aqe_mode задает список интегрируемых возможностей:
●
auto_approve_plan — управление планами в реальном времени.
Если срабатывает триггер перепланирования, расширение
pgpro_multiplan сможет автоматически выбрать новый план, одобрить
его и сохранить в основное хранилище для последующего
использования.
●
individual_triggers — индивидуальные триггеры перепланирования
для отдельных запросов.
●
statistics — сбор статистики перепланирования в реальном времени.
13
Совместная работа
Если план неадекватен, срабатывает условие
перепланирования
Расширение pgpro_multiplan перехватывает план
и сохраняет его как одобренный или базовый
replan_enable
pgpro_multiplan.aqe_mode
автоматическое сохранение новых планов
индивидуальные значения триггеров перепланирования
сбор статистики перепланирования в реальном времени
15
Итоги
Расширение pgpro_multiplan позволяет стабилизировать
выполнение запросов, сохраняя план или набор указаний
оптимизатору
Поддерживаются несколько вариантов сопоставления
запросов с планами
Дополнительное хранилище планов удобно для отладки
Расширение интегрировано с перепланированием запросов
16
Практика
1. Получите план выполнения запроса для подсчета количества
пассажиров, летевших рейсами, которые опоздали на время от
одной минуты до четырех часов и с вылетом, и с прибытием.
2. Включите автоматическое одобрение базовых планов,
предварительно переключив pgpro_multiplan на песочницу.
Оптимизируйте запрос с помощью перепланирования
в реальном времени.
3. Отключите перепланирование и сохранение планов.
Проверьте, что для запроса продолжает использоваться
одобренный план.
4. Отключите режим песочницы и убедитесь, что используется
исходный неоптимизированный план.
1. Используйте запрос из темы «Подходы к настройке» курса QPT-16:
SELECT count(*)
FROM flights f
JOIN ticket_flights tf ON tf.flight_id = f.flight_id
JOIN boarding_passes bp ON
bp.flight_id = tf.flight_id AND bp.ticket_no = tf.ticket_no
WHERE f.actual_departure — f.scheduled_departure BETWEEN
interval '1 min' AND interval '4 hours'
AND f.actual_arrival — f.scheduled_arrival BETWEEN
interval '1 min' AND interval '4 hours';
2. Вспомните тему «Перепланирование запросов» этого курса.
