# Занятие 10: Производительность / Перепланирование запросов
> Исходная страница в нативном Markdown: Производительность / Перепланирование запросов
---
Индекс LLMS: [llms.txt](/ru/llms.txt)
---
> [!NOTE]
> Эта страница извлечена из официального PDF в нативный Markdown. Текст слайдов, код и комментарии сохранены в порядке чтения; исходные PDF и HTML остаются эталоном макета.
> **HTML source:** [pgpro_10_replan.html](https://edu.postgrespro.ru/16/pgpro-16/pgpro_10_replan.html)
> **Archive:** [PGPRO-handouts-16.zip](https://edu.postgrespro.ru/16/PGPRO-handouts-16.zip)
> **Страниц PDF:** 14
## 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}
**Ошибки в оценке кардинальности**
- статистика устаревает из-за частых изменений данных
- неравномерное распределение данных
- параметризованные запросы и подготовленные операторы
- При сильном несоответствии
- выполнение прерывается
- запрос планируется повторно с учетом полученных данных
- Классы запросов
- тяжелые аналитические OLAP-запросы
- проблемные OLTP-запросы
### Комментарии преподавателя
Как правило, ошибки при планировании вызваны некорректной оценкой кардинальности. Частая причина этого — неактуальная статистика планировщика. Например, в OLTP-системах это может быть вызвано устареванием статистики из-за часто изменяющихся данных, неравномерным распределением или использованием подготовленных запросов с параметрами (когда оптимизатор переключается на общий план выполнения).
Если в процессе выполнения запроса обнаруживается, что показатели значительно отличаются от ожидаемых, можно прервать выполнение и произвести планирование повторно, воспользовавшись накопленными в процессе выполнения дополнительными данными. Для этого необходимо настроить желаемые условия перепланирования.
Перепланирование запросов в реальном времени особенно актуально для тяжелых аналитических OLAP-запросов, для которых накладные расходы на перепланирование будут относительно невелики. Перепланирование можно использовать и для проблемных OLTP-запросов.
## Page 4: Перепланирование запросов {#source-page-004}
```sh
student$ psql demo
```
Рассмотрим запрос:
```sql
=> EXPLAIN (analyze, timing off, summary off) SELECT a1.airport_name, a2.airport_name
FROM airports a1, airports a2
WHERE a1.city = 'Москва';
```
```text
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)
```
Точное количество строк отличается от оценки.
Посмотрим на параметры перепланирования:
```text
=> \dconfig replan_*
```
```text
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-page-005}
{#source-figure-005 caption="Source slide page 5: Механизм работы"}
### Комментарии преподавателя
Перепланирование запросов встраивается в стандартную схему работы планировщика Postgres Pro.
Если во время или после выполнения узла плана выполняется условие перепланирования, запрос прерывается и актуальная кардинальность узлов плана (а также дополнительная служебная информация) сохраняются. Далее запускается процесс перепланирования, который может использовать сохраненную кардинальность для получения лучшего плана.
Кардинальность сохраняется только для тех узлов плана, в которых реальная кардинальность оказалась больше плановой. Эта информация хранится только в процессе перепланирования текущего запроса. Если запрос выполняется часто, новый план можно запомнить с помощью расширения pgpro\_multiplan (рассматривается в теме «Сохранение планов запросов»).
Перед попыткой планирования создается вложенная транзакция. При срабатывании условия перепланирования эта транзакция обрывается и начинается новая.
Все выбранные планы для всех попыток перепланирования сохраняются в журнале сообщений сервера.
В любом случае, перепланирование необходимо использовать аккуратно и проверять его влияние на систему в целом.
Подробности можно прочитать в статье Алены Рыбакиной и Андрея Лепихова:
## Page 6: Условия перепланирования {#source-page-006}
**Автоматически**
- время выполнения превысило 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.
ALTIME-QUERY-REPLANNING-TRIGGERS
## Page 7: Пример перепланирования запросов {#source-page-007}
Повторим запрос:
```sql
=> EXPLAIN (analyze, timing off, summary off) SELECT a1.airport_name, a2.airport_name
FROM airports a1, airports a2
WHERE a1.city = 'Москва';
```
```text
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)
```
Планировщик считает, что во внешнем наборе всего одна строка, и выбирает план без материализации. Однако в процессе выполнения запроса обнаруживается, что строк три, и в этом случае план с материализацией выглядит привлекательнее.
Включим перепланирование:
```text
=> SET replan_enable = true;
```
```text
SET
```
Триггер по количеству строк срабатывает, если число обработанных строк превосходит ожидаемое в replan\_overrun\_limit раз. Пусть перепланирование срабатывает, если планировщик ошибся как минимум вдвое:
```text
=> SET replan_overrun_limit = 2;
```
```text
SET
```
```sql
=> EXPLAIN (analyze, timing off, summary off) SELECT a1.airport_name, a2.airport_name
FROM airports a1, airports a2
WHERE a1.city = 'Москва';
```
```text
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:
```text
=> SET replan_show_signature = true;
```
```text
SET
```
```sql
=> EXPLAIN (analyze, timing off, verbose) SELECT a1.airport_name, a2.airport_name
FROM airports a1, airports a2
WHERE a1.city = 'Москва';
```
## Page 8: Page 8 {#source-page-008}
```text
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: Особенности {#source-page-009}
**Является частью ядра СУБД**
- не нужно устанавливать расширения
- Ограничения
- поддерживает только SELECT
- не поддерживает курсоры и изменчивые функции
### Комментарии преподавателя
Механизм перепланирования запросов в реальном времени является частью Postgres Pro, поэтому установки дополнительных расширений не требуется.
На данный момент у функциональности перепланирования имеются некоторые ограничения: поддерживаются только операторы SELECT (за исключением SELECT FOR UPDATE и SELECT FOR SHARE) и не поддерживаются курсоры и изменчивые (volatile) функции.
В 17-й версии Postgres Pro Enterprise функциональность перепланирования переименована в адаптивное выполнение запросов (AQE, Adaptive Query Execution).
## Page 10: Итоги {#source-page-010}
**Если запрос выполняется неэффективно, сервер может прервать его и повторить планирование**
- При перепланировании используется накопленная информация о кардинальности узлов плана
- Перепланирование позволяет автоматически ускорить проблемные запросы
## Page 11: Практика {#source-page-011}
1. Создайте таблицу с одним столбцом типа char(2000), с отключенной автоочисткой. Заполните ее данными.
- 2. Получите план выполнения запроса, выполняющего декартово произведение таблицы самой с собой. Убедитесь в наличии ошибки оценки кардинальности.
- 3. Включите перепланирование, ограничив количество попыток одной. Получите новый план.
- 4. Снимите ограничение, установленное на количество попыток перепланирования. Сравните три получившихся плана.
### Комментарии преподавателя
3. Воспользуйтесь параметром конфигурации replan\_max\_attempts.
N-MAX-ATTEMPTS
## Page 12: 1. Таблица {#source-page-012}
Создадим базу данных и таблицу:
```sql
=> CREATE DATABASE pgpro_replan;
```
```sql
CREATE DATABASE
```
```sql
=> \c pgpro_replan
```
```text
You are now connected to database "pgpro_replan" as user "student".
```
```sql
=> CREATE TABLE t(
a char(2000)
)
WITH (autovacuum_enabled = off);
```
```sql
CREATE TABLE
```
Вставим некоторое количество строк:
```sql
=> INSERT INTO t SELECT 'A' FROM generate_series(1, 1_000);
```
```sql
INSERT 0 1000
```
### 2. Стандартный планировщик
Перепланирование отключено.
```text
=> SHOW replan_enable;
```
```text
replan_enable
---------------
off
(1 row)
```
Получим план выполнения запроса, соединяющего таблицу саму с собой:
```sql
=> EXPLAIN (analyze, timing off)
SELECT *
FROM t t1
CROSS JOIN t t2;
```
```text
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. Перепланирование с одной попыткой
Включим перепланирование:
```text
=> SET replan_enable = true;
```
```text
SET
```
## Page 13: Page 13 {#source-page-013}
```text
=> SET replan_overrun_limit = 2; -- достаточно для нашего примера
```
```text
SET
```
```text
=> SET replan_max_attempts = 1; -- количество попыток перепланирования
```
```text
SET
```
Получим новый план:
```sql
=> EXPLAIN (analyze, timing off)
SELECT *
FROM t t1
CROSS JOIN t t2;
```
```text
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. Перепланирование с двумя попытками
Снимем ограничение на количество попыток перепланирования:
```text
=> RESET replan_max_attempts;
```
```text
RESET
```
Снова получаем план:
```sql
=> EXPLAIN (analyze, timing off)
SELECT *
FROM t t1
CROSS JOIN t t2;
```
```text
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.
### Уборка
Удалим созданную базу данных.
```sql
=> \c student
```
## Page 14: Page 14 {#source-page-014}
```text
You are now connected to database "student" as user "student".
```
```sql
=> DROP DATABASE pgpro_replan WITH (FORCE);
```
```sql
DROP DATABASE
```