Postgres Pro Enterprise 13
Оптимизация запросов
13
Авторские права
© Postgres Professional, 2023 год.
Авторы: Алексей Береснев, Илья Баштанов, Павел Толмачев
Использование материалов курса
Некоммерческое использование материалов курса (презентации,
демонстрации) разрешается без ограничений. Коммерческое
использование возможно только с письменного разрешения компании
Postgres Professional. Запрещается внесение изменений в материалы
курса.
Обратная связь
Отзывы, замечания и предложения направляйте по адресу:
Отказ от ответственности
Компания Postgres Professional не несет никакой ответственности за
любые повреждения и убытки, включая потерю дохода, нанесенные
прямым или непрямым, специальным или случайным использованием
материалов курса. Компания Postgres Professional не предоставляет
каких-либо гарантий на материалы курса. Материалы курса
предоставляются на основе принципа «как есть» и компания Postgres
Professional не обязана предоставлять сопровождение, поддержку,
обновления, расширения и изменения.
2
Темы
Перенос статистики
Мониторинг выполнения
Указания оптимизатору
3
Перенос статистики
Расширение dump_stat
4
Расширение dump_stat
Позволяет сохранить в файле статистику планировщика
SQL-скрипт с командами INSERT
Восстановление статистики также использует dump_stat
можно восстановить на другом сервере (той же основной версии)
не требуется выполнение 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 «Оптимизация запросов».
12
Таблица указаний
Таблица hint_plan.hints
id — автоматически заполняемый номер
norm_query_string — шаблон для выбора запросов
application_name — имя приложения для выбора сеанса
hint — код указания
Указания в таблице приоритетнее, чем указания в коде
pg_hint_plan.enable_hint_table = on
Часто бывает затруднительно изменить код SQL в приложении для
явной вставки указаний. В таких случаях указания можно внести
в специальную таблицу hint_plan.hints, разрешив ее использование
с помощью параметра pg_hint_plan.enable_hint_table.
Идентификатор указания в этой таблице (поле id) заполняется
автоматически.
Столбец norm_query_string устанавливает шаблон для выбора
запросов, к которым будет применяться указание. Константы в шаблоне
должны быть заменены символом знака вопроса. Пробельные символы
значимы.
Столбец application_name определяет, к каким сеансам будет
применяться указание. Если указана пустая строка, будут выбираться
все сеансы.
Столбец hint задает указания (без обрамляющей разметки
комментариев).
Указания, заданные в таблице, имеют бо
t
льший приоритет, чем
указания в комментариях.
14
Расширение 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 обнуляет эти оценки.
16
Итоги
Модуль dump_stat позволяет выгружать и загружать
статистику без необходимости выполнять ANALYZE
Расширение pg_query_state помогает отслеживать ход
выполнения долгих запросов
Модули pg_hint_plan и plantuner позволяют управлять
планами запросов
17
Практика
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';
