# Занятие 08: Производительность / Оптимизация запросов > Исходная страница в нативном Markdown: Производительность / Оптимизация запросов --- Индекс LLMS: [llms.txt](/ru/llms.txt) --- > [!NOTE] > Эта страница извлечена из официального PDF в нативный Markdown. Текст слайдов, код и комментарии сохранены в порядке чтения; исходные PDF и HTML остаются эталоном макета. > **HTML source:** [pgpro_08_qoptim.html](https://edu.postgrespro.ru/16/pgpro-16/pgpro_08_qoptim.html) > **Archive:** [PGPRO-handouts-16.zip](https://edu.postgrespro.ru/16/PGPRO-handouts-16.zip) > **Страниц PDF:** 26 ## Page 1: Производительность / Оптимизация запросов {#source-page-001} ### Авторские права © Postgres Professional, 2023–2026 Авторы: Алексей Береснев, Илья Баштанов, Павел Толмачев, Игорь Гнатюк Фото: Олег Бартунов (монастырь Пху и пик Бхрикути, Непал) ### Использование материалов курса Некоммерческое использование материалов курса (презентации, демонстрации) разрешается без ограничений. Коммерческое использование возможно только с письменного разрешения компании Postgres Professional. Запрещается внесение изменений в материалы курса. ### Обратная связь Отзывы, замечания и предложения направляйте по адресу: edu@postgrespro.ru ### Отказ от ответственности Компания Postgres Professional не несет никакой ответственности за любые повреждения и убытки, включая потерю дохода, нанесенные прямым или непрямым, специальным или случайным использованием материалов курса. Компания Postgres Professional не предоставляет каких-либо гарантий на материалы курса. Материалы курса предоставляются на основе принципа «как есть» и компания Postgres Professional не обязана предоставлять сопровождение, поддержку, обновления, расширения и изменения. ## Page 2: Темы {#source-page-002} - Перенос статистики - Мониторинг выполнения - Указания оптимизатору ## Page 3: Перенос статистики {#source-page-003} **Расширение dump\\\_stat** ## Page 4: Расширение dump_stat {#source-page-004} **Сохраняет статистику планировщика** - SQL-скрипт с командами INSERT - Восстанавливает сохраненную статистику - можно восстановить на другом сервере (той же основной версии) - не требуется выполнение ANALYZE ### Комментарии преподавателя Модуль dump\_stat позволяет выгрузить и восстановить содержимое таблицы pg\_statistic. С помощью этого модуля собранную статистику планировщика можно перенести на другой сервер. При этом не придется выполнять команды ANALYZE, что сократит время простоя для больших баз данных. Еще один сценарий использования — перенос статистики с производственного сервера на тестовый стенд с меньшим набором данных, чтобы сохранить планы выполнения запросов. Функция dump\_statistic модуля выдает SQL-команды INSERT, которые можно записать в скрипт, а затем применить к совместимой базе данных. Для успешного восстановления статистики расширение должно быть установлено и на исходном, и на целевом сервере, так как в сгенерированных командах используются вызовы функций расширения. При обновлении версии СУБД структура таблицы pg\_statistic может поменяться, поэтому скрипт может оказаться несовместимым. Подробная информация о модуле: ## Page 5: Расширение dump_stat {#source-page-005} Создадим таблицу по образцу bookings.flights и соберем статистику. ```sql => \c demo ``` ```text You are now connected to database "demo" as user "student". ``` ```sql => CREATE TABLE public.flights AS SELECT * FROM bookings.flights; ``` ```sql SELECT 33121 ``` ```text => ANALYZE public.flights; ``` ```text ANALYZE ``` ```sql => SELECT attname, null_frac, avg_width, n_distinct FROM pg_stats WHERE tablename = 'flights' AND schemaname = 'public'; ``` ```text attname | null_frac | avg_width | n_distinct ---------------------+------------+-----------+------------- flight_id | 0 | 4 | -1 flight_no | 0 | 7 | 710 scheduled_departure | 0 | 8 | -0.30741826 scheduled_arrival | 0 | 8 | -0.28586093 departure_airport | 0 | 4 | 104 arrival_airport | 0 | 4 | 104 status | 0 | 8 | 6 aircraft_code | 0 | 4 | 8 actual_departure | 0.4935 | 8 | -0.3595302 actual_arrival | 0.49523333 | 8 | -0.3512575 (10 rows) ``` Подключим расширение dump\_stat: ```sql => CREATE EXTENSION dump_stat; ``` ```sql CREATE EXTENSION ``` Перегруженная функция dump\_statistic позволяет выгрузить статистику по всей базе данных, по таблицам и индексам заданной схемы или по конкретной таблице. Выгрузим статистику по таблице public.flights: ```sql => COPY (SELECT dump_statistic('public','flights')) TO '/tmp/flights.stat'; ``` ```text COPY 10 ``` Теперь удалим таблицу и создадим ее заново, но с выключенной автоочисткой, чтобы исключить автоматический сбор статистики. ```sql => DROP TABLE public.flights; ``` ```sql DROP TABLE ``` ```sql => CREATE TABLE public.flights WITH (autovacuum_enabled = off) AS SELECT * FROM bookings.flights; ``` ```sql SELECT 33121 ``` Статистики пока нет: ```sql => SELECT attname, null_frac, avg_width, n_distinct FROM pg_stats WHERE tablename = 'flights' AND schemaname = 'public'; ``` ```text attname | null_frac | avg_width | n_distinct ---------+-----------+-----------+------------ (0 rows) ``` Загрузим сохраненную ранее статистику: ```text => \i /tmp/flights.stat ``` ## Page 6: Page 6 {#source-page-006} ```sql INSERT 0 1 INSERT 0 1 INSERT 0 1 INSERT 0 1 INSERT 0 1 INSERT 0 1 INSERT 0 1 INSERT 0 1 INSERT 0 1 INSERT 0 1 ``` ```sql => SELECT attname, null_frac, avg_width, n_distinct FROM pg_stats WHERE tablename = 'flights' and schemaname = 'public'; ``` ```text attname | null_frac | avg_width | n_distinct ---------------------+------------+-----------+------------- flight_id | 0 | 4 | -1 flight_no | 0 | 7 | 710 scheduled_departure | 0 | 8 | -0.30741826 scheduled_arrival | 0 | 8 | -0.28586093 departure_airport | 0 | 4 | 104 arrival_airport | 0 | 4 | 104 status | 0 | 8 | 6 aircraft_code | 0 | 4 | 8 actual_departure | 0.4935 | 8 | -0.3595302 actual_arrival | 0.49523333 | 8 | -0.3512575 (10 rows) ``` Статистика успешно загрузилась. Расширение больше не требуется, удалим его. ```sql => DROP EXTENSION dump_stat; ``` ```sql DROP EXTENSION ``` ## Page 7: Мониторинг выполнения {#source-page-007} **Расширение pg\\\_query\\\_state** ## Page 8: Расширение pg_query_state {#source-page-008} **Информация о выполняющемся запросе** - текущее состояние выполнения - актуальная статистика - Формат вывода как у EXPLAIN ANALYZE ### Комментарии преподавателя Модуль pg\_query\_state выводит текущее состояние выполнения запроса в работающем обслуживающем процессе. В отличие от команды EXPLAIN ANALYZE, предоставляющей данные об уже выполненном запросе, модуль pg\_query\_state информирует о ходе выполнения запроса, который работает в настоящий момент. Формат вывода практически идентичен выводу обычной команды EXPLAIN ANALYZE. Модуль позволяет исследовать любой обслуживающий процесс и определять его фактическое состояние. Это полезно, когда выполняется длительный запрос и необходимо оценить оставшееся время. Модуль показывает состояние как основного запроса, так и запросов, вызванных из функций. Для распараллеленных запросов модуль позволяет наблюдать за операциями, которые выполняет как ведущий, так и рабочие процессы. Подробная информация о модуле: ## Page 9: Расширение pg_query_state {#source-page-009} Подключим расширение и соответствующую разделяемую библиотеку. ```sql => CREATE EXTENSION pg_query_state; ``` ```sql CREATE EXTENSION ``` ```sql => ALTER SYSTEM SET shared_preload_libraries = 'pg_query_state'; ``` ```sql ALTER SYSTEM ``` Необходима перезагрузка экземпляра. ```sh student$ sudo systemctl restart postgrespro-ent-16.service ``` ```sh student$ psql demo ``` Откроем еще один сеанс: ```sh student$ psql demo ``` Номер обслуживающего процесса второго сеанса: ```sql => SELECT * FROM pg_backend_pid(); ``` ```text pg_backend_pid ---------------- 808573 (1 row) ``` Запустим во втором сеансе запрос. Запрос написан таким образом, что перебирает в цикле все перелеты из таблицы ticket\_flights, для каждого из них выбирает номер бронирования из таблицы tickets, и по номеру находит дату бронирования в таблице booking. Это очень неэффективный способ получения результата, но большое время выполнения запроса позволит отследить в первом сеансе ход его выполнения. Мы выведем оценку стоимости и кардинальности (аналогичную той, что выдает команда EXPLAIN) и актуальную информацию о выполнении узлов плана. ```sql => 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; ``` ```sql => SELECT plan FROM pg_query_state(808573, costs => true); ``` ```text plan ------------------------------------------------------------------------------------------ --------------------------------------------------- Seq Scan on ticket_flights tf (cost=0.00..17671152.14 rows=1045726 width=40) (Current loop: actual rows=1, loop number=1) + SubPlan 2 + -> Index Scan using tickets_pkey on tickets t (cost=0.42..16.88 rows=1 width=8) (actual rows=1 loops=1) + Index Cond: (ticket_no = tf.ticket_no) + SubPlan 1 + -> Index Scan using bookings_pkey on bookings b (cost=0.42..8.44 rows=1 width=8) (Current loop: actual rows=1, loop number=1)+ Index Cond: (book_ref = t.book_ref) (1 row) ``` Небольшая пауза... ```sql => SELECT plan FROM pg_query_state(808573, costs => true); ``` ## Page 10: Page 10 {#source-page-010} ```text plan ------------------------------------------------------------------------------------------ -------------------------------------- Seq Scan on ticket_flights tf (cost=0.00..17671152.14 rows=1045726 width=40) (Current loop: actual rows=65797, loop number=1)+ SubPlan 2 + -> Index Scan using tickets_pkey on tickets t (cost=0.42..16.88 rows=1 width=8) (actual rows=1 loops=65797) + Index Cond: (ticket_no = tf.ticket_no) + SubPlan 1 + -> Index Scan using bookings_pkey on bookings b (cost=0.42..8.44 rows=1 width=8) (actual rows=1 loops=65797) + Index Cond: (book_ref = t.book_ref) (1 row) ``` Сравните для узла Seq Scan прогноз числа строк (rows) с актуальной информацией (Current loop: rows). Таким образом можно обоснованно предположить, какая часть работы уже проделана. Узлы Index Scan вложенных планов выполняются во вложенных циклах. Для них выводятся среднее значение по прошедшим циклам (actual, по аналогии с выводом команды EXPLAIN ANALYZE) и информация о текущем, еще не завершенном цикле (Current loop). Как и команда EXPLAIN ANALYZE, расширение позволяет выводить информацию о времени выполнения узлов и о количестве использованных буферов, а также отслеживать параллельные запросы и запросы, выполняющиеся в вызываемых функциях. Удалим расширение и перезапустим экземпляр. ```sql => DROP EXTENSION pg_query_state; ``` ```sql DROP EXTENSION ``` ```sql => ALTER SYSTEM RESET shared_preload_libraries; ``` ```sql ALTER SYSTEM ``` ```sh student$ sudo systemctl restart postgrespro-ent-16.service ``` ## Page 11: Указания оптимизатору {#source-page-011} **Расширение pg\\\_hint\\\_plan** - Расширение plantuner ## Page 12: Расширение pg_hint_plan {#source-page-012} ![Source slide page 12: Расширение pg_hint_plan](images/page-012.png){#source-figure-012 caption="Source slide page 12: Расширение pg_hint_plan"} **Корректировка плана выполнения с помощью указаний** - в комментарии специального вида в коде запроса - в служебной таблице расширения ### Комментарии преподавателя Модуль pg\_hint\_plan разработан японской компанией NTT и включен в состав Postgres Pro Enterprise. Репозиторий проекта: Расширение предназначено для принудительной корректировки планов выполнения с помощью указаний (hints), помещаемых непосредственно в код SQL в виде комментариев специального вида /\*+ ... \*/. В случаях, когда принудительно выбранный план выполнить нельзя, планировщик игнорирует указания. Обработчик pg\_hint\_plan считывает указания только из первого блочного комментария и немедленно прекращает разбор, обнаруживая недопустимый символ. Допустимыми символами являются буквы, цифры, пробелы, подчеркивания, запятые и скобки. Комментарий с указаниями не может содержать в себе другой блочный комментарий. Указания можно размещать в нескольких строках для улучшения читаемости. Ошибки в записи или неправильные имена указаний считаются ошибками синтаксиса и выводятся в журнал сообщений сервера. Указания с неправильными обращениями к объектам игнорируются, а в журнале помечаются как неиспользованные. При дублировании или противоречиях в указаниях будет применяться последнее указание, а в журнал выведется сообщение о дублировании. Подробности в документации: ## Page 13: Типы указаний {#source-page-013} **Метод доступа** - Способ соединения - Порядок соединений - Корректировка числа строк соединения - Параллельная обработка таблицы - Установка параметра на время планирования ### Комментарии преподавателя Фразы указаний состоят из имени указания и последующих параметров, заключенных в скобки и разделенных пробелами. Модуль поддерживает шесть типов указаний: -LIST Методы доступа, способы соединения и другие вопросы, относящиеся к планам выполнения запросов, подробно рассматриваются в курсе QPT «Оптимизация запросов». ## Page 14: Расширение pg_hint_plan {#source-page-014} Расширение pg\_hint\_plan входит в Postgres Pro Enterprise в виде отдельного пакета, который устанавливается средствами ОС из репозитория. В виртуальной машине курса этот пакет уже установлен. Так как функционал расширения реализован в разделяемой библиотеке, она должна быть предварительно загружена в сеансе с помощью команды LOAD или указана в конфигурационном параметре shared\_preload\_libraries. ```sh student$ psql demo ``` ```text => LOAD 'pg_hint_plan'; ``` ```text LOAD ``` Для данного запроса используется параллельный план с двумя рабочими процессами: ```sql => EXPLAIN (costs off) SELECT count(*) FROM ticket_flights; ``` ```text QUERY PLAN ------------------------------------------------------- Finalize Aggregate -> Gather Workers Planned: 2 -> Partial Aggregate -> Parallel Seq Scan on ticket_flights (5 rows) ``` Для управления параллелизмом применяется указание Parallel. С параметром hard оно принудительно задает количество процессов. В этом примере мы отключаем параллельное выполнение, указывая ноль: ```sql => /*+ Parallel(ticket_flights 0 hard) */ EXPLAIN (costs off) SELECT count(*) FROM ticket_flights; ``` ```text QUERY PLAN ---------------------------------- Aggregate -> Seq Scan on ticket_flights (2 rows) ``` Принудительно установим три рабочих процесса: ```sql => /*+ Parallel(ticket_flights 3 hard) */ EXPLAIN (costs off) SELECT count(*) FROM ticket_flights; ``` ```text QUERY PLAN ------------------------------------------------------- Finalize Aggregate -> Gather Workers Planned: 3 -> Partial Aggregate -> Parallel Seq Scan on ticket_flights (5 rows) ``` Параметр soft лишь меняет значение параметра max\_parallel\_workers\_per\_gather, а в остальном планировщику остается свобода выбора. В данном случае планировщик ограничился двумя процессами из-за относительного небольшого размера таблицы: ```sql => /*+ Parallel(ticket_flights 3 soft) */ EXPLAIN (costs off) SELECT count(*) FROM ticket_flights; ``` ```text QUERY PLAN ------------------------------------------------------- Finalize Aggregate -> Gather Workers Planned: 2 -> Partial Aggregate -> Parallel Seq Scan on ticket_flights (5 rows) ``` Указание Leading позволяет влиять на порядок соединений. Если указаны двойные скобки, то задается не только порядок, но и направление (внешняя таблица и внутренняя ## Page 15: Page 15 {#source-page-015} таблица) соединений: ```sql => /*+ Leading((t (tf f))) */ EXPLAIN (costs off) SELECT t.passenger_name, tf.flight_id, f.flight_no FROM tickets t, ticket_flights tf, flights f WHERE t.ticket_no = tf.ticket_no AND tf.flight_id = f.flight_id ; ``` ```sql QUERY PLAN ------------------------------------------------------- Hash Join Hash Cond: (t.ticket_no = tf.ticket_no) -> Seq Scan on tickets t -> Hash -> Hash Join Hash Cond: (tf.flight_id = f.flight_id) -> Seq Scan on ticket_flights tf -> Hash -> Seq Scan on flights f (9 rows) ``` Попробуем другой порядок: ```sql => /*+ Leading(((tf f) t)) */ EXPLAIN (costs off) SELECT t.passenger_name, tf.flight_id, f.flight_no FROM tickets t, ticket_flights tf, flights f WHERE t.ticket_no = tf.ticket_no AND tf.flight_id = f.flight_id ; ``` ```sql QUERY PLAN ------------------------------------------------- Hash Join Hash Cond: (tf.ticket_no = t.ticket_no) -> Hash Join Hash Cond: (tf.flight_id = f.flight_id) -> Seq Scan on ticket_flights tf -> Hash -> Seq Scan on flights f -> Hash -> Seq Scan on tickets t (9 rows) ``` Часто достаточно указать лишь несколько начальных таблиц, оставив планировщику свободу выбора для остальных соединений. В этом примере планировщик начнет с соединения таблиц f и tf, и поскольку пара помещена в одинарные скобки, сможет сам выбрать направление соединения: ```sql => /*+ Leading(f tf) */ EXPLAIN (costs off) SELECT t.passenger_name, tf.flight_id, f.flight_no FROM tickets t, ticket_flights tf, flights f WHERE t.ticket_no = tf.ticket_no AND tf.flight_id = f.flight_id ; ``` ```sql QUERY PLAN -------------------------------------------------------- Nested Loop -> Hash Join Hash Cond: (tf.flight_id = f.flight_id) -> Seq Scan on ticket_flights tf -> Hash -> Seq Scan on flights f -> Memoize Cache Key: tf.ticket_no Cache Mode: logical -> Index Scan using tickets_pkey on tickets t Index Cond: (ticket_no = tf.ticket_no) (11 rows) ``` ## Page 16: Таблица указаний {#source-page-016} **Таблица 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 задает указания (без обрамляющей разметки комментариев). Указания, заданные в таблице, имеют бо́льший приоритет, чем указания в комментариях. ## Page 17: Таблица указаний {#source-page-017} Вместо редактирования текстов запросов воспользуемся таблицей hint\_plan.hints. Она создается при добавлении расширения в базу данных. ```sql => CREATE EXTENSION pg_hint_plan; ``` ```sql CREATE EXTENSION ``` Возьмем запрос с индексным доступом: ```sql => EXPLAIN (costs off) SELECT * FROM bookings WHERE book_ref = 'CDE08B'; ``` ```text QUERY PLAN --------------------------------------------- Index Scan using bookings_pkey on bookings Index Cond: (book_ref = 'CDE08B'::bpchar) (2 rows) ``` Поместим в таблицу указаний требование использовать последовательное сканирование. Константы, фигурирующие в целевом запросе, в столбце norm\_query\_string таблицы указаний должны заменяться знаками «?». В остальном текст запроса должен посимвольно совпадать, включая переводы строк: ```sql => INSERT INTO hint_plan.hints (norm_query_string, application_name, hints) VALUES ( E'EXPLAIN (costs off)\nSELECT * FROM bookings WHERE book_ref = ?;', 'psql', 'SeqScan(bookings)' ); ``` ```sql INSERT 0 1 ``` Разрешим использование таблицы указаний: ```text => SET pg_hint_plan.enable_hint_table TO on; ``` ```text SET ``` Теперь план запроса содержит последовательное сканирование: ```sql => EXPLAIN (costs off) SELECT * FROM bookings WHERE book_ref = 'CDE08B'; ``` ```text QUERY PLAN ----------------------------------------------- Gather Workers Planned: 1 -> Parallel Seq Scan on bookings Filter: (book_ref = 'CDE08B'::bpchar) (4 rows) ``` Отключим расширение. ```sql => DROP EXTENSION pg_hint_plan; ``` ```sql DROP EXTENSION ``` ## Page 18: Расширение plantuner {#source-page-018} **Исключает индексы из рассмотрения оптимизатором** - 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 обнуляет эти оценки. ## Page 19: Расширение plantuner {#source-page-019} Создадим для таблицы ticket\_flights индексы по стоимости билета и по классу обслуживания: ```sql => \c demo ``` ```text You are now connected to database "demo" as user "student". ``` ```sql => CREATE INDEX ticket_flights_amount_idx ON ticket_flights(amount); ``` ```sql CREATE INDEX ``` ```sql => CREATE INDEX ticket_flights_fare_conditions_idx ON ticket_flights(fare_conditions); ``` ```sql CREATE INDEX ``` Следующий запрос будет использовать индекс по стоимости для любого класса обслуживания: ```text => \set fare Business ``` ```sql => EXPLAIN (costs off) SELECT avg(amount) FROM ticket_flights WHERE fare_conditions=:'fare' and amount>50000; ``` ```text QUERY PLAN -------------------------------------------------------------- Aggregate -> Bitmap Heap Scan on ticket_flights Recheck Cond: (amount > '50000'::numeric) Filter: ((fare_conditions)::text = 'Business'::text) -> Bitmap Index Scan on ticket_flights_amount_idx Index Cond: (amount > '50000'::numeric) (6 rows) ``` ```text => \set fare Economy ``` ```sql => EXPLAIN (costs off) SELECT avg(amount) FROM ticket_flights WHERE fare_conditions=:'fare' and amount>50000; ``` ```text QUERY PLAN ------------------------------------------------------------- Aggregate -> Bitmap Heap Scan on ticket_flights Recheck Cond: (amount > '50000'::numeric) Filter: ((fare_conditions)::text = 'Economy'::text) -> Bitmap Index Scan on ticket_flights_amount_idx Index Cond: (amount > '50000'::numeric) (6 rows) ``` Расширение plantuner позволяет исключить из рассмотрения индекс по стоимости, сохранив при этом возможность выбора метода доступа: ```text => LOAD 'plantuner'; ``` ```text LOAD ``` ```text => SET plantuner.disable_index='ticket_flights_amount_idx'; ``` ```text SET ``` Если в запросе есть предикат с высокой селективностью, буден выбран другой индекс: ```text => \set fare Business ``` ```sql => EXPLAIN (costs off) SELECT avg(amount) FROM ticket_flights WHERE fare_conditions=:'fare' and amount>50000; ``` ## Page 20: Page 20 {#source-page-020} ```text QUERY PLAN ------------------------------------------------------------------------ Aggregate -> Bitmap Heap Scan on ticket_flights Recheck Cond: ((fare_conditions)::text = 'Business'::text) Filter: (amount > '50000'::numeric) -> Bitmap Index Scan on ticket_flights_fare_conditions_idx Index Cond: ((fare_conditions)::text = 'Business'::text) (6 rows) ``` А если селективность низкая, планировщик выбирает полное сканирование: ```text => \set fare Economy ``` ```sql => EXPLAIN (costs off) SELECT avg(amount) FROM ticket_flights WHERE fare_conditions=:'fare' and amount>50000; ``` ```text QUERY PLAN ------------------------------------------------------------------------------------------ ----------------- Finalize Aggregate -> Gather Workers Planned: 2 -> Partial Aggregate -> Parallel Seq Scan on ticket_flights Filter: ((amount > '50000'::numeric) AND ((fare_conditions)::text = 'Economy'::text)) (6 rows) ``` Следует понимать, что указания оптимизатору, как правило, дают эффект лишь в частных случаях: при определенных значениях констант и определенном состоянии данных в таблицах. ## Page 21: Итоги {#source-page-021} **Модуль dump\\\_stat позволяет выгружать и загружать статистику без необходимости выполнять ANALYZE** - Расширение pg\_query\_state помогает отслеживать ход выполнения долгих запросов - Модули pg\_hint\_plan и plantuner позволяют управлять планами запросов ## Page 22: Практика {#source-page-022} 1. В базе данных demo выполните запрос с соединением таблиц tickets и ticket\_flights по равенству значений столбцов ticket\_no. Проверьте, что соединение выполняется с помощью слияния (merge join). - 2. Используя расширение pg\_hint\_plan, заставьте планировщик выбрать соединение хешированием (hash join). - 3. С помощью этого же расширения добейтесь, чтобы для доступа к таблице ticket\_flights использовалось сканирование по битовой карте. - 4. Подключите расширение plantuner и запретите использовать индекс tickets\_pkey при выполнении запроса из п. 3. ### Комментарии преподавателя 1. Используйте следующий запрос: ```sql 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 ``` ```text 2. Загрузите библиотеку командой LOAD 'pg_hint_plan'. ``` ```text Используйте указание /*+ HashJoin(t tf) */. ``` ```text 3. Используйте указание /*+ BitmapScan(t) */. ``` ```text 4. Загрузите библиотеку командой LOAD 'plantuner'. ``` Запретите использовать индекс с помощью команды: ```text SET plantuner.disable_index='tickets_pkey'; ``` ## Page 23: 1. Запрос с соединением слиянием {#source-page-023} ```sql => \c demo ``` ```text You are now connected to database "demo" as user "student". ``` Получим план запроса с соединением таблиц tickets и ticket\_flights. ```sql => 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; ``` ```sql QUERY PLAN ------------------------------------------------------------------------------------------ ----- Merge Join (actual rows=1045726 loops=1) Merge Cond: (t.ticket_no = tf.ticket_no) -> Index Scan using tickets_pkey on tickets t (actual rows=366733 loops=1) -> Index Scan using ticket_flights_pkey on ticket_flights tf (actual rows=1045726 loops=1) (4 rows) ``` Соединение выполнено слиянием (merge join). ### 2. Указание выполнить соединение хешированием Загрузим динамическую библиотеку pg\_hint\_plan: ```text => LOAD 'pg_hint_plan'; ``` ```text LOAD ``` Используем указание HashJoin: ```sql => EXPLAIN (analyze, summary off, timing off, costs off) /*+ HashJoin(t tf) */ 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; ``` ```sql QUERY PLAN --------------------------------------------------------------------------------------- Gather Merge (actual rows=1045726 loops=1) Workers Planned: 2 Workers Launched: 2 -> Sort (actual rows=348575 loops=3) Sort Key: t.ticket_no Sort Method: external merge Disk: 15328kB Worker 0: Sort Method: external merge Disk: 24048kB Worker 1: Sort Method: external merge Disk: 13840kB -> Parallel Hash Join (actual rows=348575 loops=3) Hash Cond: (tf.ticket_no = t.ticket_no) -> Parallel Seq Scan on ticket_flights tf (actual rows=348575 loops=3) -> Parallel Hash (actual rows=122244 loops=3) Buckets: 131072 Batches: 4 Memory Usage: 6944kB -> Parallel Seq Scan on tickets t (actual rows=122244 loops=3) (14 rows) ``` ### 3. Указание использовать сканирование по битовой карте Используем указание BitmapScan: ```sql => EXPLAIN (analyze, summary off, timing off, costs off) /*+ BitmapScan(t) */ 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; ``` ## Page 24: Page 24 {#source-page-024} ```text QUERY PLAN ------------------------------------------------------------------------------------------ -------------------------- Gather Merge (actual rows=1045726 loops=1) Workers Planned: 2 Workers Launched: 2 -> Sort (actual rows=348575 loops=3) Sort Key: t.ticket_no Sort Method: external merge Disk: 15336kB Worker 0: Sort Method: external merge Disk: 23288kB Worker 1: Sort Method: external merge Disk: 14600kB -> Nested Loop (actual rows=348575 loops=3) -> Parallel Seq Scan on ticket_flights tf (actual rows=348575 loops=3) -> Memoize (actual rows=1 loops=1045726) Cache Key: tf.ticket_no Cache Mode: logical Hits: 81840 Misses: 219468 Evictions: 159603 Overflows: 0 Memory Usage: 8193kB Worker 0: Hits: 117490 Misses: 339900 Evictions: 280032 Overflows: 0 Memory Usage: 8193kB Worker 1: Hits: 76196 Misses: 210832 Evictions: 150964 Overflows: 0 Memory Usage: 8193kB -> Bitmap Heap Scan on tickets t (actual rows=1 loops=770200) Recheck Cond: (ticket_no = tf.ticket_no) Heap Blocks: exact=219468 -> Bitmap Index Scan on tickets_pkey (actual rows=1 loops=770200) Index Cond: (ticket_no = tf.ticket_no) (21 rows) ``` ### 4. Запрет использования индекса Загрузим динамическую библиотеку plantuner: ```text => LOAD 'plantuner'; ``` ```text LOAD ``` Запретим использовать индекс tickets\_pkey: ```text => SET plantuner.disable_index='tickets_pkey'; ``` ```text SET ``` Получим план: ```sql => 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; ``` ```sql QUERY PLAN ------------------------------------------------------------------------------------------ ----- Merge Join (actual rows=1045726 loops=1) Merge Cond: (tf.ticket_no = t.ticket_no) -> Index Scan using ticket_flights_pkey on ticket_flights tf (actual rows=1045726 loops=1) -> Materialize (actual rows=1045725 loops=1) -> Sort (actual rows=366733 loops=1) Sort Key: t.ticket_no Sort Method: external merge Disk: 14432kB -> Seq Scan on tickets t (actual rows=366733 loops=1) (8 rows) ``` ## Page 25: Практика PPEM {#source-page-025} 1. Подготовьте базу данных demo основного экземпляра для работы с расширением pg\_query\_state. - 2. Откройте psql-консоль и запустите в ней длительно выполняющийся запрос из демонстрации. - 3. Используя вкладку «Активность» экземпляра, исследуйте ход выполнения запроса из п. 2. ### Комментарии преподавателя 2. Запрос для исследования выполнения: ```sql 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-консоль, открыть вкладку «Активность» и установить в ней фильтр по базе данных. ## Page 26: 1. Установка расширения pg_query_state {#source-page-026} Выберите основной экземпляр ent-16 и на вкладке «Расширения» навигационной панели нажмите «Установить расширение». Заполните поля открывшейся формы и нажмите кнопку «Установить». На вкладке «Параметры» добавьте в параметр shared\_preload\_libraries библиотеку pg\_query\_state. Сохраните и примените параметр (потребуется перезапуск экземпляра). ### 2. Длительно выполняющийся запрос В списке «Базы данных» найдите demo и в столбце «Действия» нажмите на пиктограмму «\{/\}» (открыть PSQL-консоль). В открывшемся окне наберите и запустите на выполнение запрос: ```sql 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, откройте вкладку «Активность» и для процесса, выполняющего запрос, в столбце «Действия» нажмите на пиктограмму «План активного запроса». Откроется окно, в котором для отслеживания хода выполнения будет кнопка «Обновить статистику выполнения».