Перейти к содержимому

10 Занятие 10: Производительность / Перепланирование запросов

Исходная страница в нативном Markdown: Производительность / Перепланирование запросов
Примечание

Эта страница извлечена из официального PDF в нативный Markdown. Текст слайдов, код и комментарии сохранены в порядке чтения; исходные PDF и HTML остаются эталоном макета. HTML source: pgpro_10_replan.html Archive: PGPRO-handouts-16.zip Страниц PDF: 14

Page 1: Производительность / Перепланирование запросов

Авторские права

© Postgres Professional, 2023–2026 Авторы: Алексей Береснев, Илья Баштанов, Павел Толмачев, Игорь Гнатюк Фото: Олег Бартунов (монастырь Пху и пик Бхрикути, Непал)

Использование материалов курса

Некоммерческое использование материалов курса (презентации, демонстрации) разрешается без ограничений. Коммерческое использование возможно только с письменного разрешения компании Postgres Professional. Запрещается внесение изменений в материалы курса.

Обратная связь

Отзывы, замечания и предложения направляйте по адресу: edu@postgrespro.ru

Отказ от ответственности

Компания Postgres Professional не несет никакой ответственности за любые повреждения и убытки, включая потерю дохода, нанесенные прямым или непрямым, специальным или случайным использованием материалов курса. Компания Postgres Professional не предоставляет каких-либо гарантий на материалы курса. Материалы курса предоставляются на основе принципа «как есть» и компания Postgres Professional не обязана предоставлять сопровождение, поддержку, обновления, расширения и изменения.

Page 2: Темы

  • Проблема и решение
  • Механизм работы
  • Условия перепланирования
  • Особенности реализации

Page 3: Проблема и решение

Ошибки в оценке кардинальности

  • статистика устаревает из-за частых изменений данных
  • неравномерное распределение данных
  • параметризованные запросы и подготовленные операторы
  • При сильном несоответствии
    • выполнение прерывается
    • запрос планируется повторно с учетом полученных данных
  • Классы запросов
    • тяжелые аналитические OLAP-запросы
    • проблемные OLTP-запросы

Комментарии преподавателя

Как правило, ошибки при планировании вызваны некорректной оценкой кардинальности. Частая причина этого — неактуальная статистика планировщика. Например, в OLTP-системах это может быть вызвано устареванием статистики из-за часто изменяющихся данных, неравномерным распределением или использованием подготовленных запросов с параметрами (когда оптимизатор переключается на общий план выполнения).

Если в процессе выполнения запроса обнаруживается, что показатели значительно отличаются от ожидаемых, можно прервать выполнение и произвести планирование повторно, воспользовавшись накопленными в процессе выполнения дополнительными данными. Для этого необходимо настроить желаемые условия перепланирования.

Перепланирование запросов в реальном времени особенно актуально для тяжелых аналитических OLAP-запросов, для которых накладные расходы на перепланирование будут относительно невелики. Перепланирование можно использовать и для проблемных OLTP-запросов.

Page 4: Перепланирование запросов

student$ psql demo

Рассмотрим запрос:

=> EXPLAIN (analyze, timing off, summary off) SELECT a1.airport_name, a2.airport_name
FROM airports a1, airports a2
WHERE a1.city = 'Москва';
                                             QUERY PLAN
------------------------------------------------------------------------------------------
-----------
 Nested Loop  (cost=0.00..88.16 rows=104 width=64) (actual rows=312 loops=1)
   ->  Seq Scan on airports_data ml  (cost=0.00..30.56 rows=1 width=61) (actual rows=3
loops=1)
         Filter: ((city ->> lang()) = 'Москва'::text)
         Rows Removed by Filter: 101
   ->  Seq Scan on airports_data ml_1  (cost=0.00..4.04 rows=104 width=61) (actual
rows=104 loops=3)
(5 rows)

Точное количество строк отличается от оценки. Посмотрим на параметры перепланирования:

=> \dconfig replan_*
  List of configuration parameters
          Parameter          | Value
-----------------------------+-------
 replan_enable               | off
 replan_max_attempts         | 100
 replan_memory_limit         | -1
 replan_overrun_limit        | -1
 replan_query_execution_time | -1
 replan_regression_mode      | off
 replan_show_signature       | off
(7 rows)

Параметры, значения которых равны минус единице, — это условия перепланирования, которые нужно будет настроить.

Page 5: Механизм работы

Source slide page 5: Механизм работы{#source-figure-005 caption=“Source slide page 5: Механизм работы”}

Комментарии преподавателя

Перепланирование запросов встраивается в стандартную схему работы планировщика Postgres Pro.

Если во время или после выполнения узла плана выполняется условие перепланирования, запрос прерывается и актуальная кардинальность узлов плана (а также дополнительная служебная информация) сохраняются. Далее запускается процесс перепланирования, который может использовать сохраненную кардинальность для получения лучшего плана.

Кардинальность сохраняется только для тех узлов плана, в которых реальная кардинальность оказалась больше плановой. Эта информация хранится только в процессе перепланирования текущего запроса. Если запрос выполняется часто, новый план можно запомнить с помощью расширения pgpro_multiplan (рассматривается в теме «Сохранение планов запросов»).

Перед попыткой планирования создается вложенная транзакция. При срабатывании условия перепланирования эта транзакция обрывается и начинается новая.

Все выбранные планы для всех попыток перепланирования сохраняются в журнале сообщений сервера.

В любом случае, перепланирование необходимо использовать аккуратно и проверять его влияние на систему в целом.

Подробности можно прочитать в статье Алены Рыбакиной и Андрея Лепихова: https://habr.com/ru/companies/postgrespro/articles/819911/

Page 6: Условия перепланирования

Автоматически

  • время выполнения превысило replan_query_execution_time
  • узел отработал и ошибка прогноза кардинальности оказалась больше
  • replan_overrun_limit
  • рабочий процесс использует более replan_memory_limit памяти
  • и ошибка прогноза кардинальности превысила replan_overrun_limit
  • Вручную
    • функция replan_signal

Комментарии преподавателя

По умолчанию перепланирование запросов отключено. Для включения достаточно установить конфигурационный параметр replan_enable и настроить условия перепланирования, или триггеры (не имеющие отношения к обычным триггерам базы данных).

В Postgres Pro Enterprise 16 перепланирование срабатывает в трех следующих случаях:

● По времени выполнения.

Запрос выполняется дольше, чем указано в параметре replan_query_execution_time.

● По ошибке оценки кардинальности после выполнения узла.

После выполнения узла плана количество обработанных строк превысило число, ожидаемое планировщиком, умноженное на значение replan_overrun_limit.

● По перерасходу памяти и ошибке оценки кардинальности во время выполнения узла.

Во время выполнения узла плана потребление памяти рабочим процессом превысило значение replan_memory_limit, а количество уже обработанных строк превысило число, ожидаемое планировщиком, умноженное на значение replan_overrun_limit.

Перепланирование можно инициировать и вручную с помощью функции replan_signal.

https://postgrespro.ru/docs/enterprise/16/realtime-query-replanning-how#RE ALTIME-QUERY-REPLANNING-TRIGGERS

Page 7: Пример перепланирования запросов

Повторим запрос:

=> EXPLAIN (analyze, timing off, summary off) SELECT a1.airport_name, a2.airport_name
FROM airports a1, airports a2
WHERE a1.city = 'Москва';
                                             QUERY PLAN
------------------------------------------------------------------------------------------
-----------
 Nested Loop  (cost=0.00..88.16 rows=104 width=64) (actual rows=312 loops=1)
   ->  Seq Scan on airports_data ml  (cost=0.00..30.56 rows=1 width=61) (actual rows=3
loops=1)
         Filter: ((city ->> lang()) = 'Москва'::text)
         Rows Removed by Filter: 101
   ->  Seq Scan on airports_data ml_1  (cost=0.00..4.04 rows=104 width=61) (actual
rows=104 loops=3)
(5 rows)

Планировщик считает, что во внешнем наборе всего одна строка, и выбирает план без материализации. Однако в процессе выполнения запроса обнаруживается, что строк три, и в этом случае план с материализацией выглядит привлекательнее. Включим перепланирование:

=> SET replan_enable = true;
SET

Триггер по количеству строк срабатывает, если число обработанных строк превосходит ожидаемое в replan_overrun_limit раз. Пусть перепланирование срабатывает, если планировщик ошибся как минимум вдвое:

=> SET replan_overrun_limit = 2;
SET
=> EXPLAIN (analyze, timing off, summary off) SELECT a1.airport_name, a2.airport_name
FROM airports a1, airports a2
WHERE a1.city = 'Москва';
                                              QUERY PLAN
------------------------------------------------------------------------------------------
------------
 Nested Loop  (cost=0.00..196.07 rows=312 width=64) (actual rows=312 loops=1)
   ->  Seq Scan on airports_data ml_1  (cost=0.00..4.04 rows=104 width=61) (actual
rows=104 loops=1)
   ->  Materialize  (cost=0.00..30.58 rows=3 width=61) (actual rows=3 loops=104)
         ->  Seq Scan on airports_data ml  (cost=0.00..30.56 rows=3 width=61) (actual
rows=3 loops=1)
               Filter: ((city ->> lang()) = 'Москва'::text)
               Rows Removed by Filter: 101
(6 rows)

При перепланировании использовалась реальная кардинальность узлов, полученная при выполнении плана. Это привело к выбору плана с материализацией, который в данном случае более эффективен. Повторим запрос, включив вывод информации о перепланировании и добавив к команде EXPLAIN параметр verbose:

=> SET replan_show_signature = true;
SET
=> EXPLAIN (analyze, timing off, verbose) SELECT a1.airport_name, a2.airport_name
FROM airports a1, airports a2
WHERE a1.city = 'Москва';

Page 8: Page 8

                                                  QUERY PLAN
------------------------------------------------------------------------------------------
---------------------
 Nested Loop  (cost=0.00..196.07 rows=312 width=64) (actual rows=312 loops=1)
   NodeSign: 395408946556254163
   Cardinality: 312
   Groups Number: -1
   Finished: false
   Output: (ml.airport_name ->> lang()), (ml_1.airport_name ->> lang())
   ->  Seq Scan on bookings.airports_data ml_1  (cost=0.00..4.04 rows=104 width=61)
(actual rows=104 loops=1)
         NodeSign: 6649604475734118433
         Cardinality: 104
         Groups Number: -1
         Finished: true
         Output: ml_1.airport_code, ml_1.airport_name, ml_1.city, ml_1.coordinates,
ml_1.timezone
   ->  Materialize  (cost=0.00..30.58 rows=3 width=61) (actual rows=3 loops=104)
         NodeSign: subordinate
         Output: ml.airport_name
         ->  Seq Scan on bookings.airports_data ml  (cost=0.00..30.56 rows=3 width=61)
(actual rows=3 loops=1)
               NodeSign: 953600500042743201
               Cardinality: 3
               Groups Number: -1
               Finished: true
               Output: ml.airport_name
               Filter: ((ml.city ->> lang()) = 'Москва'::text)
               Rows Removed by Filter: 101
 Planning Time: 1.775 ms
 Execution Time: 2.405 ms
 Replan Active: true
 Table Entries: 4
 Controlled Statements: 1
 Replanning Attempts: 1
 Final Run Planning Time: 0.077 ms
 Total Time Elapsed: 4.179 ms
(31 rows)

Теперь для узлов плана отображается более подробная информация. В нижней части вывода видно, что потребовалась одна попытка перепланирования (Replanning Attempts: 1).

Page 9: Особенности

Является частью ядра СУБД

  • не нужно устанавливать расширения
  • Ограничения
    • поддерживает только SELECT
    • не поддерживает курсоры и изменчивые функции

Комментарии преподавателя

Механизм перепланирования запросов в реальном времени является частью Postgres Pro, поэтому установки дополнительных расширений не требуется.

На данный момент у функциональности перепланирования имеются некоторые ограничения: поддерживаются только операторы SELECT (за исключением SELECT FOR UPDATE и SELECT FOR SHARE) и не поддерживаются курсоры и изменчивые (volatile) функции.

https://postgrespro.ru/docs/enterprise/16/realtime-query-replanning

В 17-й версии Postgres Pro Enterprise функциональность перепланирования переименована в адаптивное выполнение запросов (AQE, Adaptive Query Execution).

Page 10: Итоги

Если запрос выполняется неэффективно, сервер может прервать его и повторить планирование

  • При перепланировании используется накопленная информация о кардинальности узлов плана
  • Перепланирование позволяет автоматически ускорить проблемные запросы

Page 11: Практика

  1. Создайте таблицу с одним столбцом типа char(2000), с отключенной автоочисткой. Заполните ее данными.
    1. Получите план выполнения запроса, выполняющего декартово произведение таблицы самой с собой. Убедитесь в наличии ошибки оценки кардинальности.
    1. Включите перепланирование, ограничив количество попыток одной. Получите новый план.
    1. Снимите ограничение, установленное на количество попыток перепланирования. Сравните три получившихся плана.

Комментарии преподавателя

  1. Воспользуйтесь параметром конфигурации replan_max_attempts.

https://postgrespro.ru/docs/enterprise/16/runtime-config-query#GUC-REPLA N-MAX-ATTEMPTS

Page 12: 1. Таблица

Создадим базу данных и таблицу:

=> CREATE DATABASE pgpro_replan;
CREATE DATABASE
=> \c pgpro_replan
You are now connected to database "pgpro_replan" as user "student".
=> CREATE TABLE t(
  a char(2000)
)
WITH (autovacuum_enabled = off);
CREATE TABLE

Вставим некоторое количество строк:

=> INSERT INTO t SELECT 'A' FROM generate_series(1, 1_000);
INSERT 0 1000

2. Стандартный планировщик

Перепланирование отключено.

=> SHOW replan_enable;
 replan_enable
---------------
 off
(1 row)

Получим план выполнения запроса, соединяющего таблицу саму с собой:

=> EXPLAIN (analyze, timing off)
SELECT *
FROM t t1
  CROSS JOIN t t2;
                                            QUERY PLAN
------------------------------------------------------------------------------------------
--------
 Nested Loop  (cost=0.00..1286.88 rows=62500 width=16008) (actual rows=1000000 loops=1)
   ->  Seq Scan on t t1  (cost=0.00..252.50 rows=250 width=8004) (actual rows=1000
loops=1)
   ->  Materialize  (cost=0.00..253.75 rows=250 width=8004) (actual rows=1000 loops=1000)
         ->  Seq Scan on t t2  (cost=0.00..252.50 rows=250 width=8004) (actual rows=1000
loops=1)
 Planning Time: 56.533 ms
 Execution Time: 156.360 ms
(6 rows)

Налицо ошибка оценки кардинальности в обоих узлах Seq Scan. Она связана с отключенной автоочисткой: анализ не выполнялся, планировщик не знает количество строк в таблице. Планировщик считает, что 250 строк поместятся в оперативной памяти (work_mem), поэтому вставляет узел Materialize, чтобы не считывать таблицу t2 тысячу раз с диска (или из буферного кеша). Обратите внимание, что декартово произведение всегда выполняется только вложенным циклом (подробности можно узнать из курса «QPT. Оптимизация запросов»), поэтому план в любом случае не изменится — пример носит иллюстративный, а не практический характер.

3. Перепланирование с одной попыткой

Включим перепланирование:

=> SET replan_enable = true;
SET

Page 13: Page 13

=> SET replan_overrun_limit = 2; -- достаточно для нашего примера
SET
=> SET replan_max_attempts = 1;  -- количество попыток перепланирования
SET

Получим новый план:

=> EXPLAIN (analyze, timing off)
SELECT *
FROM t t1
  CROSS JOIN t t2;
                                            QUERY PLAN
------------------------------------------------------------------------------------------
--------
 Nested Loop  (cost=0.00..3630.62 rows=250000 width=16008) (actual rows=1000000 loops=1)
   ->  Seq Scan on t t2  (cost=0.00..252.50 rows=1000 width=8004) (actual rows=1000
loops=1)
   ->  Materialize  (cost=0.00..253.75 rows=250 width=8004) (actual rows=1000 loops=1000)
         ->  Seq Scan on t t1  (cost=0.00..252.50 rows=250 width=8004) (actual rows=1000
loops=1)
 Planning Time: 3.327 ms
 Execution Time: 163.357 ms
(6 rows)

Что произошло? После завершения внутреннего цикла по таблице t2 (и одной итерации внешнего) планировщику стало известно, что в таблице t2 находится 1000 строк. Сработал триггер перепланирования. Теперь внутренний цикл выполняется по таблице t1 с промежуточным узлом материализации, поскольку планировщик все еще считает, что в t1 всего 250 строк. Кардинальность узла Seq Scan по t2 оценена верно.

4. Перепланирование с двумя попытками

Снимем ограничение на количество попыток перепланирования:

=> RESET replan_max_attempts;
RESET

Снова получаем план:

=> EXPLAIN (analyze, timing off)
SELECT *
FROM t t1
  CROSS JOIN t t2;
                                           QUERY PLAN
------------------------------------------------------------------------------------------
------
 Nested Loop  (cost=0.00..262752.50 rows=1000000 width=16008) (actual rows=1000000
loops=1)
   ->  Seq Scan on t t1  (cost=0.00..252.50 rows=1000 width=8004) (actual rows=1000
loops=1)
   ->  Seq Scan on t t2  (cost=0.00..252.50 rows=1000 width=8004) (actual rows=1000
loops=1000)
 Planning Time: 2.841 ms
 Execution Time: 449.833 ms
(5 rows)

Поскольку информация перепланирования не сохраняется между запросами, процесс повторяется. После завершения внутреннего цикла по таблице t2 планировщику стало известно, что в таблице t2 находится 1000 строк. Сработал триггер перепланирования, план был перестроен так, как мы видели в п. 3, а запрос начал выполняться с начала. Теперь после завершения внутреннего цикла по таблице t1 планировщику стало известно, что и в таблице t1 находится 1000 строк. Снова сработал триггер перепланирования, план был перестроен во второй раз. Теперь кардинальность обоих узлов Seq Scan оценена правильно; узел материализации пропал, поскольку тысяча строк не помещается в память work_mem.

Уборка

Удалим созданную базу данных.

=> \c student

Page 14: Page 14

You are now connected to database "student" as user "student".
=> DROP DATABASE pgpro_replan WITH (FORCE);
DROP DATABASE