Производительность
Оптимизация запросов
16
Авторские права
© Postgres Professional, 2023–2026
Авторы: Алексей Береснев, Илья Баштанов, Павел Толмачев, Игорь
Гнатюк
Фото: Олег Бартунов (монастырь Пху и пик Бхрикути, Непал)
Использование материалов курса
Некоммерческое использование материалов курса (презентации,
демонстрации) разрешается без ограничений. Коммерческое
использование возможно только с письменного разрешения компании
Postgres Professional. Запрещается внесение изменений в материалы
курса.
Обратная связь
Отзывы, замечания и предложения направляйте по адресу:
edu@postgrespro.ru
Отказ от ответственности
Компания Postgres Professional не несет никакой ответственности за
любые повреждения и убытки, включая потерю дохода, нанесенные
прямым или непрямым, специальным или случайным использованием
материалов курса. Компания Postgres Professional не предоставляет
каких-либо гарантий на материалы курса. Материалы курса
предоставляются на основе принципа «как есть» и компания Postgres
Professional не обязана предоставлять сопровождение, поддержку,
обновления, расширения и изменения.
2
Темы
Перенос статистики
Мониторинг выполнения
Указания оптимизатору
3
Перенос статистики
Расширение dump_stat
4
Расширение dump_stat
Сохраняет статистику планировщика
SQL-скрипт с командами INSERT
Восстанавливает сохраненную статистику
можно восстановить на другом сервере (той же основной версии)
не требуется выполнение ANALYZE
Модуль dump_stat позволяет выгрузить и восстановить содержимое
таблицы pg_statistic.
С помощью этого модуля собранную статистику планировщика можно
перенести на другой сервер. При этом не придется выполнять команды
ANALYZE, что сократит время простоя для больших баз данных. Еще
один сценарий использования — перенос статистики
с производственного сервера на тестовый стенд с меньшим набором
данных, чтобы сохранить планы выполнения запросов.
Функция dump_statistic модуля выдает SQL-команды INSERT, которые
можно записать в скрипт, а затем применить к совместимой базе
данных.
Для успешного восстановления статистики расширение должно быть
установлено и на исходном, и на целевом сервере, так как
в сгенерированных командах используются вызовы функций
расширения.
При обновлении версии СУБД структура таблицы pg_statistic может
поменяться, поэтому скрипт может оказаться несовместимым.
Подробная информация о модуле:
6
Мониторинг выполнения
Расширение pg_query_state
7
Расширение pg_query_state
Информация о выполняющемся запросе
текущее состояние выполнения
актуальная статистика
Формат вывода как у EXPLAIN ANALYZE
Модуль pg_query_state выводит текущее состояние выполнения
запроса в работающем обслуживающем процессе. В отличие от
команды EXPLAIN ANALYZE, предоставляющей данные об уже
выполненном запросе, модуль pg_query_state информирует о ходе
выполнения запроса, который работает в настоящий момент. Формат
вывода практически идентичен выводу обычной команды EXPLAIN
ANALYZE.
Модуль позволяет исследовать любой обслуживающий процесс
и определять его фактическое состояние. Это полезно, когда
выполняется длительный запрос и необходимо оценить оставшееся
время.
Модуль показывает состояние как основного запроса, так и запросов,
вызванных из функций. Для распараллеленных запросов модуль
позволяет наблюдать за операциями, которые выполняет как ведущий,
так и рабочие процессы.
Подробная информация о модуле:
9
Указания оптимизатору
Расширение pg_hint_plan
Расширение plantuner
10
Расширение pg_hint_plan
Корректировка плана выполнения
с помощью указаний
в комментарии специального вида в коде запроса
в служебной таблице расширения
текст
запроса
дерево
запроса
дерево
запроса
план
запроса
/*+ … */ SELECT …
планирование
разбор переписывание
Модуль pg_hint_plan разработан японской компанией NTT и включен
в состав Postgres Pro Enterprise. Репозиторий проекта:
Расширение предназначено для принудительной корректировки планов
выполнения с помощью указаний (hints), помещаемых непосредственно
в код SQL в виде комментариев специального вида
/*+ … */. В случаях, когда принудительно выбранный план выполнить
нельзя, планировщик игнорирует указания.
Обработчик pg_hint_plan считывает указания только из первого
блочного комментария и немедленно прекращает разбор, обнаруживая
недопустимый символ. Допустимыми символами являются буквы,
цифры, пробелы, подчеркивания, запятые и скобки. Комментарий
с указаниями не может содержать в себе другой блочный комментарий.
Указания можно размещать в нескольких строках для улучшения
читаемости.
Ошибки в записи или неправильные имена указаний считаются
ошибками синтаксиса и выводятся в журнал сообщений сервера.
Указания с неправильными обращениями к объектам игнорируются,
а в журнале помечаются как неиспользованные. При дублировании
или противоречиях в указаниях будет применяться последнее указание,
а в журнал выведется сообщение о дублировании.
Подробности в документации:
11
Типы указаний
Метод доступа
Способ соединения
Порядок соединений
Корректировка числа строк соединения
Параллельная обработка таблицы
Установка параметра на время планирования
Фразы указаний состоят из имени указания и последующих параметров,
заключенных в скобки и разделенных пробелами.
Модуль поддерживает шесть типов указаний:
Методы доступа, способы соединения и другие вопросы, относящиеся
к планам выполнения запросов, подробно рассматриваются в курсе
QPT «Оптимизация запросов».
13
Таблица указаний
Таблица hint_plan.hints
шаблон для выбора запросов
имя приложения для выбора сеанса
код указания
Указания в таблице приоритетнее, чем указания в коде
pg_hint_plan.enable_hint_table = on
Часто бывает затруднительно изменить код SQL в приложении для
явной вставки указаний. В таких случаях указания можно внести
в специальную таблицу hint_plan.hints, разрешив ее использование
с помощью параметра pg_hint_plan.enable_hint_table.
Идентификатор указания в этой таблице (поле id) заполняется
автоматически.
Столбец norm_query_string устанавливает шаблон для выбора
запросов, к которым будет применяться указание. Константы в шаблоне
должны быть заменены символом знака вопроса. Пробельные символы
значимы.
Столбец application_name определяет, к каким сеансам будет
применяться указание. Если указана пустая строка, будут выбираться
все сеансы.
Столбец hint задает указания (без обрамляющей разметки
комментариев).
Указания, заданные в таблице, имеют бо
v
льший приоритет, чем указания
в комментариях.
15
Расширение plantuner
Исключает индексы из рассмотрения оптимизатором
plantuner.disable_index
plantuner.enable_index
Обнуляет статистику для только что созданных таблиц
plantuner.fix_empty_table
Расширение plantuner позволяет исключить из рассмотрения
оптимизатора определенные индексы (pg_hint_plan не дает такой
возможности). Оптимизатор не будет рассматривать планы, которые
используют индексы, указанные в параметре plantuner.disable_index.
Однако если имя индекса указать в параметре plantuner.enable_index,
он все же будет доступен оптимизатору – таким образом можно
разрешить или даже заставить оптимизатор использовать
определенный индекс для конкретного запроса, задав параметр на
уровне сеанса или функции.
Если таблица создана недавно, ее страницы находятся только
в буферном кеше. В таком случае оптимизатор, оценивая число
страниц и строк в таблице, иногда ошибается в бóльшую сторону.
Указание plantuner.fix_empty_table = on обнуляет эти оценки.
17
Итоги
Модуль dump_stat позволяет выгружать и загружать
статистику без необходимости выполнять ANALYZE
Расширение pg_query_state помогает отслеживать ход
выполнения долгих запросов
Модули pg_hint_plan и plantuner позволяют управлять
планами запросов
18
Практика
1. В базе данных demo выполните запрос с соединением таблиц
tickets и ticket_flights по равенству значений столбцов ticket_no.
Проверьте, что соединение выполняется с помощью слияния
(merge join).
2. Используя расширение pg_hint_plan, заставьте планировщик
выбрать соединение хешированием (hash join).
3. С помощью этого же расширения добейтесь, чтобы для
доступа к таблице ticket_flights использовалось сканирование
по битовой карте.
4. Подключите расширение plantuner и запретите использовать
индекс tickets_pkey при выполнении запроса из п. 3.
1. Используйте следующий запрос:
EXPLAIN (analyze, summary off, timing off, costs off)
SELECT t.ticket_no, t.passenger_name, tf.flight_id, tf.amount
FROM tickets t
JOIN ticket_flights tf ON t.ticket_no = tf.ticket_no
ORDER BY t.ticket_no
2. Загрузите библиотеку командой LOAD 'pg_hint_plan'.
Используйте указание /*+ HashJoin(t tf) */.
3. Используйте указание /*+ BitmapScan(t) */.
4. Загрузите библиотеку командой LOAD 'plantuner'.
Запретите использовать индекс с помощью команды:
SET plantuner.disable_index='tickets_pkey';
19
Практика PPEM
1. Подготовьте базу данных demo основного экземпляра для
работы с расширением pg_query_state.
2. Откройте psql-консоль и запустите в ней длительно
выполняющийся запрос из демонстрации.
3. Используя вкладку «Активность» экземпляра, исследуйте ход
выполнения запроса из п. 2.
2. Запрос для исследования выполнения:
SELECT tf.*, (
SELECT (
SELECT b.book_date FROM bookings b WHERE b.book_ref =
t.book_ref
)
FROM tickets t WHERE t.ticket_no = tf.ticket_no
)
FROM ticket_flights tf;
3. Удобно, не закрывая psql-консоль, открыть вкладку «Активность»
и установить в ней фильтр по базе данных.
