# Занятие 11: Производительность / Сохранение планов запросов

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

---

Индекс LLMS: [llms.txt](/ru/llms.txt)

---

<!-- Generated by scripts/generate_skeleton.py. -->

> [!NOTE]
> Эта страница извлечена из официального PDF в нативный Markdown. Текст слайдов, код и комментарии сохранены в порядке чтения; исходные PDF и HTML остаются эталоном макета.
> **HTML source:** [pgpro_11_multiplan.html](https://edu.postgrespro.ru/16/pgpro-16/pgpro_11_multiplan.html)
> **Archive:** [PGPRO-handouts-16.zip](https://edu.postgrespro.ru/16/PGPRO-handouts-16.zip)
> **Страниц PDF:** 33

## 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}

- Модуль pgpro\_multiplan
- Замороженные планы
- Песочница
- Планы с шаблонами
- Базовые планы
- Совместная работа с перепланированием

## Page 3: Модуль pgpro_multiplan {#source-page-003}

**Стабилизация планов выполнения запросов**
  - заморозка плана для конкретного запроса или группы запросов
  - разделяемый кеш замороженных планов
  - планы сохраняются в файлах сервера,
  - для репликации: pgpro\_multiplan.wal\_rw
- Заменяет устаревшее расширение sr\_plan

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

Расширение pgpro\_multiplan позволяет «заморозить» определенный план выполнения запроса (или привязать план к целой группе запросов) и при дальнейшем исполнении запроса использовать этот план вместо того, который построил бы планировщик. Стабилизация некоторых планов полезна, например, при обновлении сервера (в новых версиях планировщик в среднем работает лучше, но планы небольшой части запросов могут измениться и в худшую сторону), или для оптимизации отдельного запроса, когда не удается иными средствами убедить планировщик построить нужный план.

Замороженные планы сохраняются в файлы в отдельном каталоге, а работа с ними происходит в разделяемой памяти. Таким образом, в отличие от обычного локального кеша разобранных запросов и их планов, кеш замороженных планов — общий для всех сеансов.

Планы хранятся в файлах, а не в таблицах БД, чтобы обеспечить совместимость расширения с системой 1С. Обратной стороной такого решения является то, что замороженные планы не передаются с мастера на реплику автоматически: если это необходимо, требуется включить параметр pgpro\_multiplan.wal\_rw на обоих серверах.

<https://postgrespro.ru/docs/enterprise/16/pgpro-multiplan>

До версии Postgres Pro Enterprise 16 включительно в продукте присутствует расширение sr\_plan с аналогичным функционалом. Начиная с версии 15, оно считается устаревшим и вместо него рекомендуется использовать расширение pgpro\_multiplan.

<https://postgrespro.ru/docs/enterprise/16/pgpro-multiplan#PGPRO-MULTIPL> AN-MIGRATION-FROM-SR-PLAN

## Page 4: Типы планов {#source-page-004}

**Сериализованный — serialized**
  - хранится внутреннее представление плана
  - зависит от системного каталога: OID, структуры таблиц и т. п.
  - действует только в текущей базе данных
  - планирование не требуется
- Набор указаний — hintset
  - хранятся указания расширения pg\_hint\_plan
  - запрос планируется с учетом указаний

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

План может сохраняться в двух вариантах.

Сериализованный план — это дерево плана, представленное в виде текстовой строки.

Сериализованный план действителен только в текущей базе данных, он зависит от идентификаторов объектов, структуры таблиц и индексов. Например, если пересоздать используемую в плане таблицу (даже с тем же именем), то сохраненный план станет недействительным.

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

Набор указаний — хранится как указания расширения pg\_hint\_plan. Для этого требуется, чтобы было установлено и само расширение pg\_hint\_plan, иначе указания будут проигнорированы. В таком случае план не зависит от идентификаторов объектов. Запрос при выполнении приходится планировать, но указания подсказывают планировщику, какой именно план требуется получить.

<https://postgrespro.ru/docs/enterprise/16/pgpro-multiplan#PGPRO-MULTIPL> AN-SUPPORTED-MODES

## Page 5: Заморозка плана {#source-page-005}

![Source slide page 5: Заморозка плана](images/page-005.png){#source-figure-005 caption="Source slide page 5: Заморозка плана"}

```sql
SELECT *
FROM bookings
WHERE book_ref > '800000'
```
```text
 Sort
   Sort Key
   ->  Seq Scan
```
```text
  sql hash: 457033572132829198
const hash: 1320965230
```
```text
  sql: 457033572132829198
const: 1320965230
```
```text
Sort
   Sort Key
   ->  Seq Scan
```

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

Первый из режимов работы расширения — заморозка плана — состоит из трех этапов: регистрации запроса, построения плана и собственно заморозки.

Зарегистрировать запрос можно одним из двух способов:

● функцией pgpro\_multiplan\_register\_query, в которую необходимо передать текст запроса (можно в параметризованном виде) и список типов параметров (можно доверить расширению автоматически определить типы);

● с помощью автоматической регистрации запросов (параметр pgpro\_multiplan.auto\_tracking, по умолчанию выключен).

При этом запрос идентифицируется двумя хеш-кодами:

● sql\_hash — вычисляется по дереву разбора без учета параметров и констант (тот же хеш-код используется, например, в расширении pg\_stat\_statements; требуется включенный compute\_query\_id);

● const\_hash — вычисляется по типам и значениям констант.

Далее запрос планируется, при этом допускается влиять на план любыми доступными способами: параметрами оптимизатора, с помощью расширений AQO или pg\_hint\_plan и т. п.

На последнем шаге необходимо заморозить последний выполненный план с помощью функции pgpro\_multiplan\_freeze. После этого при выполнении зарегистрированного запроса будет обеспечиваться применение замороженного плана. Замороженный план будет применяться только к точно такому же запросу, для которого замораживался план.

## Page 6: Замороженные планы {#source-page-006}

Начнем с установки расширения:
```sql
=> ALTER SYSTEM SET shared_preload_libraries = pg_hint_plan, pgpro_multiplan;
```
```sql
ALTER SYSTEM
```
Обратите внимание, что библиотека pg\_hint\_plan загружается перед pgpro\_multiplan.
Необходим перезапуск экземпляра:
```sh
student$ sudo systemctl restart postgrespro-ent-16.service
```
```sh
student$ psql demo
```
Добавим расширение pgpro\_multiplan в базу данных:
```sql
=> CREATE EXTENSION pgpro_multiplan;
```
```sql
CREATE EXTENSION
```
По умолчанию функционал расширения pgpro\_multiplan выключен. Включим его для текущего сеанса: установим режим frozen, позволяющий использовать замороженные планы.
```text
=> SET pgpro_multiplan.mode = frozen;
```
```text
SET
```
Запрос, выбирающий половину бронирований, использует индексный доступ:
```text
=> \pset format wrapped
```
```text
Output format is wrapped.
```
```text
=> \pset columns 90
```
```text
Target width is 90.
```
```sql
=> EXPLAIN (costs off)
SELECT * FROM bookings WHERE book_ref > '800000';
```
```text
                 QUERY PLAN
---------------------------------------------
 Index Scan using bookings_pkey on bookings
   Index Cond: (book_ref > '800000'::bpchar)
(2 rows)
```
Допустим, мы считаем, что последовательное сканирование было бы эффективнее. Воспользуемся расширением, и сначала зарегистрируем интересующий нас запрос (используем не константу, а параметр):
```sql
=> SELECT * FROM pgpro_multiplan_register_query('SELECT * FROM bookings WHERE book_ref > $1;');
```
```text
      sql_hash       | const_hash
---------------------+------------
 3221999367686871845 |          0
(1 row)
```
Теперь в плане видно, что запрос отслеживается расширением (в корне плана появился узел Custom Scan (MultiplanScan) с пометкой Plan is: tracked):
```sql
=> EXPLAIN (costs off)
SELECT * FROM bookings WHERE book_ref > '800000';
```
```text
                    QUERY PLAN
--------------------------------------------------
 Custom Scan (MultiplanScan)
   Plan is: tracked
   SQL hash: 3221999367686871845
   Const hash: 0
   Plan hash: 0
   ->  Index Scan using bookings_pkey on bookings
         Index Cond: (book_ref > $1)
(7 rows)
```
Запретим использовать индексный доступ:
```text
=> SET enable_indexscan = off;
```

## Page 7: Page 7 {#source-page-007}

```text
SET
```
```text
=> SET enable_bitmapscan = off;
```
```text
SET
```
```sql
=> EXPLAIN (costs off)
SELECT * FROM bookings WHERE book_ref > '800000';
```
```text
           QUERY PLAN
---------------------------------
 Custom Scan (MultiplanScan)
   Plan is: tracked
   SQL hash: 3221999367686871845
   Const hash: 0
   Plan hash: 0
   ->  Seq Scan on bookings
         Filter: (book_ref > $1)
(7 rows)
```
Чтобы заморозить полученный план, воспользуемся функцией pgpro\_multiplan\_freeze:
```sql
=> SELECT pgpro_multiplan_freeze(); -- 'serialized'
```
```text
 pgpro_multiplan_freeze
------------------------
 t
(1 row)
```
Вернем оптимизатору все возможности для построения планов:
```text
=> RESET enable_indexscan;
```
```text
RESET
```
```text
=> RESET enable_bitmapscan;
```
```text
RESET
```
И повторно выполним запрос — возьмем для примера другую константу:
```sql
=> EXPLAIN (costs off)
SELECT * FROM bookings WHERE book_ref > '777777';
```
```text
           QUERY PLAN
---------------------------------
 Custom Scan (MultiplanScan)
   Plan is: frozen, serialized
   SQL hash: 3221999367686871845
   Const hash: 0
   Plan hash: 0
   ->  Seq Scan on bookings
         Filter: (book_ref > $1)
(7 rows)
```
Теперь сервер использует замороженный план: Plan is: frozen, serialized.
Информацию о замороженных планах можно увидеть в представлении pgpro\_multiplan\_storage:
```text
=> \pset xheader_width 90
```
```text
Expanded header width is 90.
```
```sql
=> SELECT dbid, sql_hash, const_hash, valid, query_string, paramtypes, plan_type
FROM pgpro_multiplan_storage \gx
```
```sql
-[ RECORD 1 ]+--------------------------------------------
dbid         | 16388
sql_hash     | 3221999367686871845
const_hash   | 0
valid        | t
query_string | SELECT * FROM bookings WHERE book_ref > $1;
paramtypes   | {character}
plan_type    | serialized
```
План можно заморозить и с типом hintset, то есть в виде указаний, сформированных с помощью расширения pg\_hint\_plan.
Добавим расширение pg\_hint\_plan в текущую базу данных:

## Page 8: Page 8 {#source-page-008}

```sql
=> CREATE EXTENSION pg_hint_plan;
```
```sql
CREATE EXTENSION
```
В начале этой демонстрации мы уже добавили библиотеку этого расширения в shared\_preload\_libraries.
Следующий запрос использует последовательное сканирование:
```sql
=> EXPLAIN SELECT * FROM ticket_flights WHERE flight_id < 1000;
```
```text
                                      QUERY PLAN
--------------------------------------------------------------------------------------
 Gather  (cost=1000.00..20812.09 rows=55256 width=32)
   Workers Planned: 2
   ->  Parallel Seq Scan on ticket_flights  (cost=0.00..14286.49 rows=23023 width=32)
         Filter: (flight_id < 1000)
(4 rows)
```
Зарегистрируем его — на этот раз используем не параметр, а константу:
```sql
=> SELECT * FROM pgpro_multiplan_register_query('SELECT * FROM ticket_flights WHERE flight_id < 1000;');
```
```text
       sql_hash       | const_hash
----------------------+------------
 -8587368766006511412 | 1941396791
(1 row)
```
Попросим планировщик использовать сканирование индекса, используя подсказки pg\_hint\_plan:
```sql
=> /*+ IndexScan(ticket_flights ticket_flights_pkey) */
 EXPLAIN SELECT * FROM ticket_flights WHERE flight_id < 1000;
```
```text
                                        QUERY PLAN
------------------------------------------------------------------------------------------
 Custom Scan (MultiplanScan)  (cost=0.42..64454.01 rows=55256 width=32)
   Plan is: tracked
   SQL hash: -8587368766006511412
   Const hash: 1941396791
   Plan hash: 0
   ->  Index Scan using ticket_flights_pkey on ticket_flights  (cost=0.42..64454.01 rows=.
.55256 width=32)
         Index Cond: (flight_id < 1000)
(7 rows)
```
Заморозим план с типом hintset:
```sql
=> SELECT pgpro_multiplan_freeze('hintset');
```
```text
 pgpro_multiplan_freeze
------------------------
 t
(1 row)
```
И проверим план — в нем без указаний pg\_hint\_plan используется сканирование индекса:
```sql
=> EXPLAIN SELECT * FROM ticket_flights WHERE flight_id < 1000;
```
```text
                                        QUERY PLAN
------------------------------------------------------------------------------------------
 Custom Scan (MultiplanScan)  (cost=0.42..64454.01 rows=55256 width=32)
   Plan is: frozen, hintset
   SQL hash: -8587368766006511412
   Const hash: 1941396791
   Plan hash: 0
   ->  Index Scan using ticket_flights_pkey on ticket_flights  (cost=0.42..64454.01 rows=.
.55256 width=32)
         Index Cond: (flight_id < 1000)
(7 rows)
```
На этот раз замена константы приводит к запросу, для которого план не замораживался, поэтому планировщик выбирает план с последовательным сканированием:
```sql
=> EXPLAIN SELECT * FROM ticket_flights WHERE flight_id < 1001;
```

## Page 9: Page 9 {#source-page-009}

```text
                                      QUERY PLAN
--------------------------------------------------------------------------------------
 Gather  (cost=1000.00..20814.09 rows=55276 width=32)
   Workers Planned: 2
   ->  Parallel Seq Scan on ticket_flights  (cost=0.00..14286.49 rows=23032 width=32)
         Filter: (flight_id < 1001)
(4 rows)
```
Посмотрим на информацию о замороженных планах:
```sql
=> SELECT dbid, sql_hash, const_hash, valid, query_string, paramtypes, plan_type, hintstr
FROM pgpro_multiplan_storage \gx
```
```sql
-[ RECORD 1 ]+---------------------------------------------------------------------------
dbid         | 16388
sql_hash     | 3221999367686871845
const_hash   | 0
valid        | t
query_string | SELECT * FROM bookings WHERE book_ref > $1;
paramtypes   | {character}
plan_type    | serialized
hintstr      | Set(enable_bitmapscan "off") Set(enable_indexscan "off") Set(search_path ".
             |.bookings, public") SeqScan("bookings")
-[ RECORD 2 ]+---------------------------------------------------------------------------
dbid         | 16388
sql_hash     | -8587368766006511412
const_hash   | 1941396791
valid        | t
query_string | SELECT * FROM ticket_flights WHERE flight_id < 1000;
paramtypes   |
plan_type    | hintset
hintstr      | Set(search_path "bookings, public") IndexScan("ticket_flights" "ticket_fli.
             |.ghts_pkey")
```

## Page 10: Песочница {#source-page-010}

**Альтернативное хранилище планов**
  - pgpro\_multiplan.sandbox = off
  - необходимы права суперпользователя
- Поддерживается перенос планов в основное хранилище
  - ручное копирование из представления pgpro\_multiplan\_storage
  - данные не реплицируются из песочницы на ведомый сервер

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

Расширение pgpro\_multiplan позволяет задействовать для хранения замороженных планов песочницу: отдельную, независимую от основного хранилища область разделяемой памяти и каталог на диске. Переключением на альтернативное хранилище планов управляет параметр pgpro\_multiplan.sandbox (по умолчанию он выключен). Менять значение этого параметра могут только суперпользователи.

И к основному хранилищу, и к песочнице существует только один общий интерфейс доступа — представление pgpro\_multiplan\_storage.

План, сохраненный в песочнице, можно перенести в основное хранилище. Для этого необходимо сохранить данные из представления pgpro\_multiplan\_storage в отдельную таблицу, затем отключить песочницу и восстановить данные в основное хранилище, используя функцию pgpro\_multiplan\_restore.

Данные из песочницы не реплицируются на ведомый сервер.

## Page 11: Песочница {#source-page-011}

Включим параметр pgpro\_multiplan.sandbox, чтобы переключиться на альтернативное хранилище:
```text
=> SET pgpro_multiplan.sandbox = on;
```
```text
SET
```
Теперь в представлении pgpro\_multiplan\_storage пусто, так как в песочнице пока нет ни одного плана:
```sql
=> SELECT dbid, sql_hash, valid, query_string, paramtypes, plan_type
FROM pgpro_multiplan_storage;
```
```text
 dbid | sql_hash | valid | query_string | paramtypes | plan_type
------+----------+-------+--------------+------------+-----------
(0 rows)
```
Повторим предыдущие действия, но для нового запроса к таблице tickets, который использует индексное сканирование:
```sql
=> EXPLAIN (costs off)
SELECT * FROM tickets WHERE ticket_no = '0005432001001';
```
```text
                     QUERY PLAN
-----------------------------------------------------
 Index Scan using tickets_pkey on tickets
   Index Cond: (ticket_no = '0005432001001'::bpchar)
(2 rows)
```
Зарегистрируем запрос:
```sql
=> SELECT * FROM pgpro_multiplan_register_query('SELECT * FROM tickets WHERE ticket_no = $1;');
```
```text
       sql_hash       | const_hash
----------------------+------------
 -8230881202064992936 |          0
(1 row)
```
Запретим использовать индексный доступ:
```text
=> SET enable_indexscan = off;
```
```text
SET
```
```text
=> SET enable_bitmapscan = off;
```
```text
SET
```
```sql
=> EXPLAIN (costs off)
SELECT * FROM tickets WHERE ticket_no = '0005432001001';
```
```text
                QUERY PLAN
------------------------------------------
 Custom Scan (MultiplanScan)
   Plan is: tracked
   SQL hash: -8230881202064992936
   Const hash: 0
   Plan hash: 0
   ->  Gather
         Workers Planned: 2
         ->  Parallel Seq Scan on tickets
               Filter: (ticket_no = $1)
(9 rows)
```
Заморозим полученный план:
```sql
=> SELECT pgpro_multiplan_freeze();
```
```text
 pgpro_multiplan_freeze
------------------------
 t
(1 row)
```
Вернем оптимизатору все возможности для построения планов:
```text
=> RESET enable_indexscan;
```

## Page 12: Page 12 {#source-page-012}

```text
RESET
```
```text
=> RESET enable_bitmapscan;
```
```text
RESET
```
И повторно выполним запрос:
```sql
=> EXPLAIN (costs off)
SELECT * FROM tickets WHERE ticket_no = '0005432001001';
```
```text
                QUERY PLAN
------------------------------------------
 Custom Scan (MultiplanScan)
   Plan is: frozen, serialized
   SQL hash: -8230881202064992936
   Const hash: 0
   Plan hash: 0
   ->  Gather
         Workers Planned: 2
         ->  Parallel Seq Scan on tickets
               Filter: (ticket_no = $1)
(9 rows)
```
Этот план сохранен в песочнице:
```sql
=> SELECT dbid, sql_hash, valid,
query_string, sample_string, paramtypes, plan_type
FROM pgpro_multiplan_storage \gx
```
```sql
-[ RECORD 1 ]-+---------------------------------------------------------
dbid          | 16388
sql_hash      | -8230881202064992936
valid         | t
query_string  | SELECT * FROM tickets WHERE ticket_no = $1;
sample_string | SELECT * FROM tickets WHERE ticket_no = '0005432001001';
paramtypes    | {character}
plan_type     | serialized
```
План можно перенести из песочницы в основное хранилище, сохранив данные из представления pgpro\_multiplan\_storage в таблицу:
```sql
=> CREATE TABLE storage_copy AS SELECT *
FROM pgpro_multiplan_storage;
```
```sql
SELECT 1
```
Если переключиться на основное хранилище, расширение будет видеть сохраненные ранее данные:
```text
=> SET pgpro_multiplan.sandbox = off;
```
```text
SET
```
```sql
=> SELECT dbid, sql_hash, valid, query_string, paramtypes, plan_type
FROM pgpro_multiplan_storage \gx
```
```sql
-[ RECORD 1 ]+-----------------------------------------------------
dbid         | 16388
sql_hash     | 3221999367686871845
valid        | t
query_string | SELECT * FROM bookings WHERE book_ref > $1;
paramtypes   | {character}
plan_type    | serialized
-[ RECORD 2 ]+-----------------------------------------------------
dbid         | 16388
sql_hash     | -8587368766006511412
valid        | t
query_string | SELECT * FROM ticket_flights WHERE flight_id < 1000;
paramtypes   |
plan_type    | hintset
```
Например, для запроса к таблице bookings с неравенством будет снова использоваться сохраненный план:
```sql
=> EXPLAIN (costs off)
SELECT * FROM bookings WHERE book_ref > '800000';
```

## Page 13: Page 13 {#source-page-013}

```text
           QUERY PLAN
---------------------------------
 Custom Scan (MultiplanScan)
   Plan is: frozen, serialized
   SQL hash: 3221999367686871845
   Const hash: 0
   Plan hash: 0
   ->  Seq Scan on bookings
         Filter: (book_ref > $1)
(7 rows)
```
Чтобы добавить замороженный план из копии песочницы в основное хранилище, нужно воспользоваться функцией:
```sql
=> SELECT pgpro_multiplan_restore(query_string, sample_string, hintstr, paramtypes, plan_type, NULL, NULL)
FROM storage_copy;
```
```text
 pgpro_multiplan_restore
--------------------------
 (-8230881202064992936,0)
(1 row)
```
```sql
=> SELECT dbid, sql_hash, valid, query_string, paramtypes, plan_type FROM pgpro_multiplan_storage \gx
```
```sql
-[ RECORD 1 ]+-----------------------------------------------------
dbid         | 16388
sql_hash     | 3221999367686871845
valid        | t
query_string | SELECT * FROM bookings WHERE book_ref > $1;
paramtypes   | {character}
plan_type    | serialized
-[ RECORD 2 ]+-----------------------------------------------------
dbid         | 16388
sql_hash     | -8587368766006511412
valid        | t
query_string | SELECT * FROM ticket_flights WHERE flight_id < 1000;
paramtypes   |
plan_type    | hintset
-[ RECORD 3 ]+-----------------------------------------------------
dbid         | 16388
sql_hash     | -8230881202064992936
valid        | t
query_string | SELECT * FROM tickets WHERE ticket_no = $1;
paramtypes   | {character}
plan_type    | serialized
```
Этот же способ можно использовать для переноса плана из песочницы в другую базу или на другой сервер.

## Page 14: Планы с шаблонами {#source-page-014}

![Source slide page 14: Планы с шаблонами](images/page-014.png){#source-figure-014 caption="Source slide page 14: Планы с шаблонами"}

```sql
SELECT *
FROM bookings
WHERE book_ref > '800000'
```
```text
 Sort
   Sort Key
   ->  Seq Scan
```
```text
  sql hash: 457033572132829198
table hash: 289967942
```
```text
      sql: 457033572132829198
wildcards: ^t[0-9]\_bookings
```
```text
Sort
   Sort Key
   ->  Seq Scan
```

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

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

Говоря точнее, запрос идентифицируется двумя хеш-кодами:

● sql\_hash — построен по дереву разбора,

● table\_hash — построен по именам таблиц, не соответствующим регулярному выражению из параметра pgpro\_multiplan.wildcards.

Константы (const\_hash) не учитываются. Значение параметра pgpro\_multiplan.wildcards сохраняется вместе с замороженным планом (table\_hash не сохраняется, а вычисляется при необходимости).

План с шаблонами позволяет применять один и тот же план к запросам с одинаковой структурой, но разными названиями таблиц (например, запросы к временным таблицам, генерируемые системой 1С).

Такой план применяется, только если не нашлось точного соответствия (плана с типом serialized или hintset).

<https://postgrespro.ru/docs/enterprise/16/pgpro-multiplan#PGPRO-MULTIPL> AN-SUPPORTED-MODES

## Page 15: Планы с шаблонами {#source-page-015}

Для планов с шаблонами нужно расширение pg\_hint\_plan, оно уже загружено в базу данных.
Добавим режим wildcards, позволяющий использовать шаблоны:
```text
=> SET pgpro_multiplan.mode = 'frozen,wildcards';
```
```text
SET
```
Удалим всю собранную информацию из хранилища расширения:
```sql
=> SELECT pgpro_multiplan_reset();
```
```text
 pgpro_multiplan_reset
-----------------------
                     3
(1 row)
```
Для экспериментов нам понадобятся две временные таблицы. Создадим их на основе таблицы bookings и дадим схожие имена:
```sql
=> CREATE TEMP TABLE t1_bookings AS SELECT * FROM bookings;
```
```sql
SELECT 262788
```
```sql
=> CREATE TEMP TABLE t2_bookings AS SELECT * FROM bookings;
```
```sql
SELECT 262788
```
```text
=> ANALYZE t1_bookings, t2_bookings;
```
```text
ANALYZE
```
Построим индексы, чтобы у планировщика было больше вариантов доступа к таблице:
```sql
=> CREATE INDEX ON t1_bookings(book_ref);
```
```sql
CREATE INDEX
```
```sql
=> CREATE INDEX ON t2_bookings(book_ref);
```
```sql
CREATE INDEX
```
Как и в предыдущих примерах, в запросе используется индексное сканирование:
```sql
=> EXPLAIN (costs off)
SELECT * FROM t1_bookings WHERE book_ref > '800000';
```
```text
                        QUERY PLAN
----------------------------------------------------------
 Index Scan using t1_bookings_book_ref_idx on t1_bookings
   Index Cond: (book_ref > '800000'::bpchar)
(2 rows)
```
Укажем для параметра pgpro\_multiplan.wildcards регулярное выражение:
```text
=> SET pgpro_multiplan.wildcards = '^t[0-9]\_bookings';
```
```text
SET
```
Этому регулярному выражению соответствуют имена, состоящие из символа t, цифры и суффикса \_bookings — как у наших временных таблиц:
```sql
=> SELECT 't1_bookings' ~ '^t[0-9]\_bookings';
```
```text
 ?column?
----------
 t
(1 row)
```
Поэтому расширение не будет различать запросы к ним.
Снова временно отключим индексный доступ:
```text
=> SET enable_indexscan = off;
```
```text
SET
```

## Page 16: Page 16 {#source-page-016}

```text
=> SET enable_bitmapscan = off;
```
```text
SET
```
Зарегистрируем запрос к таблице t1\_bookings:
```sql
=> SELECT * FROM pgpro_multiplan_register_query('SELECT * FROM t1_bookings WHERE book_ref > ''800000'';');
```
```text
      sql_hash       | const_hash
---------------------+------------
 3221999366451720086 | 3711756922
(1 row)
```
Выполним запрос:
```sql
=> EXPLAIN (costs off)
SELECT * FROM t1_bookings WHERE book_ref > '800000';
```
```text
                  QUERY PLAN
-----------------------------------------------
 Custom Scan (MultiplanScan)
   Plan is: tracked
   SQL hash: 3221999366451720086
   Const hash: 3711756922
   Plan hash: 0
   ->  Seq Scan on t1_bookings
         Filter: (book_ref > '800000'::bpchar)
(7 rows)
```
Он использует последовательное сканирование и отслеживается расширением (tracked).
Сохраним полученный план как шаблонный:
```sql
=> SELECT pgpro_multiplan_freeze('template');
```
```text
 pgpro_multiplan_freeze
------------------------
 t
(1 row)
```
Вместе с планом расширение сохранило текущее значение параметра pgpro\_multiplan.wildcards:
```sql
=> SELECT sql_hash, query_string, paramtypes, plan_type, wildcards, hintstr
FROM pgpro_multiplan_storage \gx
```
```sql
-[ RECORD 1 ]+---------------------------------------------------------------------------
sql_hash     | 4146829285245706965
query_string | SELECT * FROM t1_bookings WHERE book_ref > '800000';
paramtypes   |
plan_type    | template
wildcards    | ^t[0-9]\_bookings
hintstr      | Set(enable_bitmapscan "off") Set(enable_indexscan "off") Set(search_path ".
             |.bookings, public") SeqScan("pgpro_multiplan_0")
```
В столбце hintstr находится набор указаний для pg\_hint\_plan.
Вернем оптимизатору все возможности для построения планов:
```text
=> RESET enable_indexscan;
```
```text
RESET
```
```text
=> RESET enable_bitmapscan;
```
```text
RESET
```
Второй запрос теперь тоже выполняется с помощью последовательного сканирования, поскольку он совпадает с первым с точностью до имен таблиц, соответствующих шаблону. Константы не учитываются, несмотря на то, что запрос регистрировался без параметров:
```sql
=> EXPLAIN (costs off)
SELECT * FROM t2_bookings WHERE book_ref > '777777';
```

## Page 17: Page 17 {#source-page-017}

```text
                  QUERY PLAN
-----------------------------------------------
 Custom Scan (MultiplanScan)
   Plan is: frozen, template
   SQL hash: 4146829285245706965
   Const hash: 2654435769
   Plan hash: 0
   ->  Seq Scan on t2_bookings
         Filter: (book_ref > '777777'::bpchar)
(7 rows)
```
Теперь выполним тот же запрос с таблицей, имя которой не соответствует шаблону:
```sql
=> EXPLAIN (costs off)
SELECT * FROM bookings WHERE book_ref > '800000';
```
```text
                 QUERY PLAN
---------------------------------------------
 Index Scan using bookings_pkey on bookings
   Index Cond: (book_ref > '800000'::bpchar)
(2 rows)
```
Используется индексное сканирование, поскольку замороженного плана для этого запроса нет.

## Page 18: Базовые планы {#source-page-018}

![Source slide page 18: Базовые планы](images/page-018.png){#source-figure-018 caption="Source slide page 18: Базовые планы"}

```text
 Sort
   Sort Key
   ->  Seq Scan
```
```sql
SELECT *
FROM bookings
WHERE book_ref > '800000'
```
```text
 sql: 457033572132829198
plan: 1320965230
```
```text
 sql: 457033572132829198
plan: 1320965230
```
```text
Sort
   Sort Key
   ->  Seq Scan
```
```text
Sort
   Sort Key
   ->  Seq Scan
```

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

Базовые планы применяют, когда для одного запроса может существовать несколько удачных планов. Если планировщик выбрал один из базовых планов, запрос выполняется с этим планом. В противном случае расширение принудительно устанавливает для запроса самый дешевый из существующих базовых планов. Базовые планы рассматриваются, только если не нашлось замороженного плана (с типом serialized, hintset или template).

Базовые планы идентифицируются двумя хеш-кодами:

● sql\_hash — вычисляется так же, как и для замороженных планов;

● plan\_hash — внутренний идентификатор плана.

Константы не учитываются, а const\_hash всегда равен 0.

Чтобы добавить план как базовый, можно действовать, как в случае с заморозкой: зарегистрировать запрос, выполнить планирование и заморозить план функцией pgpro\_multiplan\_freeze с аргументом 'baseline'.

Другой вариант — использовать автозахват, установив параметр pgpro\_multiplan.auto\_capturing. В этом случае все выполняющиеся запросы захватываются и их планы становятся кандидатами на сохранение. Захваченные запросы с планами видны в представлении pgpro\_multiplan\_captured\_queries. Захваченный план можно одобрить, вызвав функцию pgpro\_multiplan\_captured\_approve, после чего он попадает в общее хранилище и будет применяться.

<https://postgrespro.ru/docs/enterprise/16/pgpro-multiplan#PGPRO-MULTIPL> AN-SUPPORTED-MODES

## Page 19: Базовые планы {#source-page-019}

Чтобы захватывать запросы, включаем конфигурационный параметр:
```text
=> SET pgpro_multiplan.auto_capturing = on;
```
```text
SET
```
Воспользуемся тем же примером:
```text
=> SET enable_indexscan = off;
```
```text
SET
```
```text
=> SET enable_bitmapscan = off;
```
```text
SET
```
Запрос использует последовательное сканирование:
```sql
=> EXPLAIN (costs off)
SELECT * FROM bookings WHERE book_ref > '80000';
```
```text
               QUERY PLAN
----------------------------------------
 Seq Scan on bookings
   Filter: (book_ref > '80000'::bpchar)
(2 rows)
```
Захваченный запрос и план его выполнения можно увидеть в представлении:
```sql
=> SELECT dbid, sql_hash, plan_hash, query_string, cost, explain_plan
FROM pgpro_multiplan_captured_queries \gx
```
```sql
-[ RECORD 1 ]+-------------------------------------------------------------------------
dbid         | 16388
sql_hash     | 3221999367686871845
plan_hash    | -395657591727867684
query_string | SELECT * FROM bookings WHERE book_ref > $1;
cost         | 4996.85
explain_plan | Seq Scan on bookings.bookings  (cost=0.00..4996.85 rows=131394 width=21)+
             |   Output: book_ref, book_date, total_amount                             +
             |   Filter: (bookings.book_ref > '80000'::bpchar)                         +
             | Query Identifier: -8197269004796392149                                  +
             |
```
Чтобы планировщик мог использовать захваченный план, его надо одобрить:
```sql
=> SELECT pgpro_multiplan_captured_approve(
  dbid => 16388,
  sql_hash => 3221999367686871845,
  plan_hash => -395657591727867684
);
```
```text
 pgpro_multiplan_captured_approve
----------------------------------
 t
(1 row)
```
После одобрения план удаляется из хранилища захваченных планов:
```sql
=> SELECT * FROM pgpro_multiplan_captured_queries \gx
```
```text
(0 rows)
```
Получим и одобрим еще один план:
```text
=> RESET enable_bitmapscan;
```
```text
RESET
```
```text
=> SET enable_seqscan = off;
```
```text
SET
```
```sql
=> EXPLAIN (costs off)
SELECT * FROM bookings WHERE book_ref > '80000';
```

## Page 20: Page 20 {#source-page-020}

```text
                    QUERY PLAN
--------------------------------------------------
 Bitmap Heap Scan on bookings
   Recheck Cond: (book_ref > '80000'::bpchar)
   ->  Bitmap Index Scan on bookings_pkey
         Index Cond: (book_ref > '80000'::bpchar)
(4 rows)
```
```sql
=> SELECT dbid, sql_hash, plan_hash, query_string, cost, explain_plan
FROM pgpro_multiplan_captured_queries \gx
```
```sql
-[ RECORD 1 ]+---------------------------------------------------------------------------
dbid         | 16388
sql_hash     | 3221999367686871845
plan_hash    | -6676099641389655070
query_string | SELECT * FROM bookings WHERE book_ref > $1;
cost         | 5821.151
explain_plan | Bitmap Heap Scan on bookings.bookings  (cost=2466.73..5821.15 rows=131394 .
             |.width=21)                                                                 +
             |   Output: book_ref, book_date, total_amount                               +
             |   Recheck Cond: (bookings.book_ref > '80000'::bpchar)                     +
             |   ->  Bitmap Index Scan on bookings_pkey  (cost=0.00..2433.88 rows=131394 .
             |.width=0)                                                                  +
             |         Index Cond: (bookings.book_ref > '80000'::bpchar)                 +
             | Query Identifier: -8197269004796392149                                    +
             |
```
```sql
=> SELECT pgpro_multiplan_captured_approve(
  dbid => 16388,
  sql_hash => 3221999367686871845,
  plan_hash => -6676099641389655070
);
```
```text
 pgpro_multiplan_captured_approve
----------------------------------
 t
(1 row)
```
Сбросим параметры и отключим автоматический захват планов:
```text
=> RESET enable_indexscan;
```
```text
RESET
```
```text
=> RESET enable_seqscan;
```
```text
RESET
```
```text
=> SET pgpro_multiplan.auto_capturing = off;
```
```text
SET
```
Одобренные планы находятся в общем хранилище:
```sql
=> SELECT sql_hash, query_string, cost, plan_type, hintstr
FROM pgpro_multiplan_storage
WHERE plan_type = 'baseline' \gx
```
```sql
-[ RECORD 1 ]+---------------------------------------------------------------------------
sql_hash     | 3221999367686871845
query_string | SELECT * FROM bookings WHERE book_ref > $1;
cost         | 4996.85
plan_type    | baseline
hintstr      | Set(enable_bitmapscan "off") Set(enable_indexscan "off") Set(search_path ".
             |.bookings, public") SeqScan("bookings")
-[ RECORD 2 ]+---------------------------------------------------------------------------
sql_hash     | 3221999367686871845
query_string | SELECT * FROM bookings WHERE book_ref > $1;
cost         | 5821.151
plan_type    | baseline
hintstr      | Set(enable_indexscan "off") Set(enable_seqscan "off") Set(search_path "boo.
             |.kings, public") BitmapScan("bookings" "bookings_pkey")
```
Теперь добавим baseline к списку режимов расширения, чтобы планировщик выбирал только одобренные базовые планы. Добавим также режим statistics, чтобы включить сбор статистики:
```text
=> SET pgpro_multiplan.mode = 'frozen,wildcards,baseline,statistics';
```
```text
SET
```

## Page 21: Page 21 {#source-page-021}

Доступ к статистике можно получить через представление pgpro\_multiplan\_stats. Пока оно пусто, поскольку сбор статистики был отключен:
```sql
=> SELECT * FROM pgpro_multiplan_stats \gx
```
```text
(0 rows)
```
Повторим запрос:
```sql
=> EXPLAIN (costs off)
SELECT * FROM bookings WHERE book_ref > '800000';
```
```text
                  QUERY PLAN
-----------------------------------------------
 Custom Scan (MultiplanScan)
   Plan is: baseline
   SQL hash: 3221999367686871845
   Const hash: 0
   Plan hash: -395657591727867684
   ->  Seq Scan on bookings
         Filter: (book_ref > '800000'::bpchar)
(7 rows)
```
Вместо индексного сканирования используется план с последовательным сканированием, как наиболее дешевый из одобренных.
Но планировщик имеет право выбрать и другой одобренный план:
```text
=> SET enable_seqscan = off;
```
```text
SET
```
```text
=> SET enable_indexscan = off;
```
```text
SET
```
```sql
=> EXPLAIN (costs off)
SELECT * FROM bookings WHERE book_ref > '777777';
```
```text
                       QUERY PLAN
---------------------------------------------------------
 Custom Scan (MultiplanScan)
   Plan is: baseline
   SQL hash: 3221999367686871845
   Const hash: 0
   Plan hash: -6676099641389655070
   ->  Bitmap Heap Scan on bookings
         Recheck Cond: (book_ref > '777777'::bpchar)
         ->  Bitmap Index Scan on bookings_pkey
               Index Cond: (book_ref > '777777'::bpchar)
(9 rows)
```
Обратите внимание, что константы при идентификации запроса и плана не учитываются.
Посмотрим на собранную статистику. Каждый из двух планов использовался по одному разу:
```sql
=> SELECT * FROM pgpro_multiplan_stats \gx
```
```text
-[ RECORD 1 ]--------------------
dbid       | 16388
sql_hash   | 3221999367686871845
const_hash | 0
plan_hash  | -395657591727867684
planid     | -8936307906260647768
counter    | 1
-[ RECORD 2 ]--------------------
dbid       | 16388
sql_hash   | 3221999367686871845
const_hash | 0
plan_hash  | -6676099641389655070
planid     | 6680313372433506506
counter    | 1
```
Если используется расширение pgpro\_stats, о котором рассказывается в следующей теме, с его помощью можно собирать более детальную статистику планирования и выполнения запросов. Получить к ней доступ можно с помощью представления pgpro\_stats\_statements.
Восстановим значения параметров:

## Page 22: Page 22 {#source-page-022}

```text
=> RESET enable_seqscan;
```
```text
RESET
```
```text
=> RESET enable_indexscan;
```
```text
RESET
```

## Page 23: Совместная работа {#source-page-023}

**Если план неадекватен, срабатывает условие перепланирования**
- Расширение pgpro\_multiplan перехватывает план и сохраняет его как одобренный или базовый
  - replan\_enable
  - pgpro\_multiplan.aqe\_mode
  - автоматическое сохранение новых планов
  - индивидуальные значения триггеров перепланирования
  - сбор статистики перепланирования в реальном времени

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

Сервер может совместно использовать перепланирование и сохранение планов.

Если стандартный планировщик ошибается и срабатывает одно из условий перепланирования, запрос прерывается и отправляется на повторное планирование. Расширение pgpro\_multiplan может захватить и одобрить новый план, а при последующих вызовах запроса сразу подставить сохраненный качественный план. Система больше не тратит ресурсы на перепланирование этого конкретного запроса — план стабилен.

Для интеграции нужно включить параметр replan\_enable, а параметр pgpro\_multiplan.aqe\_mode задает список интегрируемых возможностей:

● auto\_approve\_plan — управление планами в реальном времени.

Если срабатывает триггер перепланирования, расширение pgpro\_multiplan сможет автоматически выбрать новый план, одобрить его и сохранить в основное хранилище для последующего использования.

● individual\_triggers — индивидуальные триггеры перепланирования для отдельных запросов.

● statistics — сбор статистики перепланирования в реальном времени.

<https://postgrespro.ru/docs/enterprise/16/pgpro-multiplan#PGPRO-MULTIPL> AN-USING-REAL-TIME-QUERY-REPLANNING

## Page 24: Совместная работа {#source-page-024}

Модуль pgpro\_multiplan можно использовать совместно с перепланированием запросов.
```text
=> LOAD 'pg_hint_plan';
```
```text
LOAD
```
```text
=> LOAD 'pgpro_multiplan';
```
```text
LOAD
```
Выполним запрос:
```sql
=> EXPLAIN (analyze, timing off)
SELECT *
FROM ticket_flights tf
  JOIN tickets t ON t.ticket_no = tf.ticket_no
WHERE tf.fare_conditions = 'Business'
  AND length(tf.ticket_no) = 13
  AND tf.amount >  50000;
```
```text
                                        QUERY PLAN
------------------------------------------------------------------------------------------
 Gather  (cost=1000.42..18693.22 rows=38 width=136) (actual rows=30133 loops=1)
   Workers Planned: 2
   Workers Launched: 2
   ->  Nested Loop  (cost=0.42..17689.42 rows=16 width=136) (actual rows=10044 loops=3)
         ->  Parallel Seq Scan on ticket_flights tf  (cost=0.00..17554.38 rows=16 width=3.
.2) (actual rows=10044 loops=3)
               Filter: ((amount > '50000'::numeric) AND ((fare_conditions)::text = 'Busin.
.ess'::text) AND (length(ticket_no) = 13))
               Rows Removed by Filter: 338531
         ->  Index Scan using tickets_pkey on tickets t  (cost=0.42..8.44 rows=1 width=10.
.4) (actual rows=1 loops=30133)
               Index Cond: (ticket_no = tf.ticket_no)
 Planning Time: 30.136 ms
 Execution Time: 307.999 ms
(11 rows)
```
Из-за большой ошибки в оценке кардинальности предложенный оптимизатором план вряд ли будет наилучшим.
Включим перепланирование и зададим триггер, указав коэффициент допустимой ошибки в узле плана:
```text
=> SET replan_enable = true;
```
```text
SET
```
```text
=> SET replan_overrun_limit = 2;
```
```text
SET
```
```text
=> SET replan_show_signature = true;
```
```text
SET
```
Включим автоматическое одобрение планов:
```text
=> SET pgpro_multiplan.aqe_mode = 'auto_approve_plans';
```
```text
SET
```
Префикс aqe\_ в именах параметров отражает тот факт, что с 17-й версии Postgres Pro Enterprise перепланирование запросов называется AQE (adaptive query execution).
```sql
=> SELECT pgpro_multiplan_reset();
```
```text
 pgpro_multiplan_reset
-----------------------
                     5
(1 row)
```
Выполним запрос еще раз:

## Page 25: Page 25 {#source-page-025}

```sql
=> EXPLAIN (analyze, timing off)
SELECT *
FROM ticket_flights tf
```
```sql
  JOIN tickets t ON t.ticket_no = tf.ticket_no
WHERE tf.fare_conditions = 'Business'
  AND length(tf.ticket_no) = 13
  AND tf.amount >  50000;
```
```sql
                                        QUERY PLAN
------------------------------------------------------------------------------------------
 Gather  (cost=18711.32..30054.00 rows=30133 width=136) (actual rows=30133 loops=1)
   NodeSign: 9024223846054589848
   Workers Planned: 2
   Workers Launched: 2
   ->  Parallel Hash Join  (cost=17711.32..26040.70 rows=12555 width=136) (actual rows=10.
.044 loops=3)
         NodeSign: 9024223846054589848
         Hash Cond: (t.ticket_no = tf.ticket_no)
         ->  Parallel Seq Scan on tickets t  (cost=0.00..7704.05 rows=152805 width=104) (.
.actual rows=122244 loops=3)
               NodeSign: 6333310549343027579
         ->  Parallel Hash  (cost=17554.38..17554.38 rows=12555 width=32) (actual rows=10.
.044 loops=3)
               NodeSign: 16504425124700661118
               Buckets: 32768  Batches: 1  Memory Usage: 2432kB
               ->  Parallel Seq Scan on ticket_flights tf  (cost=0.00..17554.38 rows=1255.
.5 width=32) (actual rows=10044 loops=3)
                     NodeSign: 16504425124700661118
                     Filter: ((amount > '50000'::numeric) AND ((fare_conditions)::text = .
.'Business'::text) AND (length(ticket_no) = 13))
                     Rows Removed by Filter: 338531
 Planning Time: 290.358 ms
 Execution Time: 333.601 ms
 Replan Active: true
 Table Entries: 2
 Controlled Statements: 1
 Replanning Attempts: 1
 Final Run Planning Time: 0.485 ms
 Total Time Elapsed: 623.959 ms
(24 rows)
```
Запрос был перепланирован в реальном времени (Replanning Attempts: 1). Обратите внимание, что оценки кардинальности улучшились и план изменился.
Заглянем в хранилище планов:
```sql
=> SELECT sql_hash, const_hash, plan_hash, cost, sample_string, plan_type, hintstr
FROM pgpro_multiplan_storage \gx
```
```sql
-[ RECORD 1 ]-+--------------------------------------------------------------------------
sql_hash      | 3551150519806575491
const_hash    | 0
plan_hash     | 7463164036004167325
cost          | 30054.003749999996
sample_string | SELECT *                                                                 +
              | FROM ticket_flights tf                                                   +
              |   JOIN tickets t ON t.ticket_no = tf.ticket_no                           +
              | WHERE tf.fare_conditions = 'Business'                                    +
              |   AND length(tf.ticket_no) = 13                                          +
              |   AND tf.amount >  50000;
plan_type     | baseline
hintstr       | Set(search_path "bookings, public") Leading(("t" "tf" )) HashJoin("tf" "t.
              |.")  SeqScan("t") SeqScan("tf")
```
План был захвачен и одобрен как базовый, указания в столбце hintstr предписывают использовать последовательное сканирование и соединение хешированием.
Теперь, когда мы получили адекватный план, отключим перепланирование:
```text
=> RESET replan_enable;
```
```text
RESET
```
Отключим автоматическое одобрение и разрешим использование сохраненных базовых планов:
```text
=> RESET pgpro_multiplan.aqe_mode;
```
```text
RESET
```
```text
=> SET pgpro_multiplan.mode = 'baseline';
```

## Page 26: Page 26 {#source-page-026}

```text
SET
```
При следующих выполнениях запроса время планирования заметно сократится — используется сохраненный план:
```sql
=> EXPLAIN (analyze, timing off, costs off)
SELECT *
FROM ticket_flights tf
  JOIN tickets t ON t.ticket_no = tf.ticket_no
WHERE tf.fare_conditions = 'Business'
  AND length(tf.ticket_no) = 13
  AND tf.amount >  50000;
```
```sql
                                        QUERY PLAN
------------------------------------------------------------------------------------------
 Custom Scan (MultiplanScan) (actual rows=30133 loops=1)
   Plan is: baseline
   SQL hash: 3551150519806575491
   Const hash: 0
   Plan hash: 7463164036004167325
   ->  Gather (actual rows=30133 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         ->  Parallel Hash Join (actual rows=10044 loops=3)
               Hash Cond: (t.ticket_no = tf.ticket_no)
               ->  Parallel Seq Scan on tickets t (actual rows=122244 loops=3)
               ->  Parallel Hash (actual rows=10044 loops=3)
                     Buckets: 32768 (originally 1024)  Batches: 1 (originally 1)  Memory .
.Usage: 2648kB
                     ->  Parallel Seq Scan on ticket_flights tf (actual rows=10044 loops=.
.3)
                           Filter: ((amount > '50000'::numeric) AND ((fare_conditions)::t.
.ext = 'Business'::text) AND (length(ticket_no) = 13))
                           Rows Removed by Filter: 338531
 Planning Time: 0.545 ms
 Execution Time: 311.506 ms
(18 rows)
```
Выполним теперь запрос с другим значением константы:
```sql
=> EXPLAIN (analyze, timing off, costs off)
SELECT *
FROM ticket_flights tf
  JOIN tickets t ON t.ticket_no = tf.ticket_no
WHERE tf.fare_conditions = 'Business'
  AND length(tf.ticket_no) = 13
  AND tf.amount >  10000;
```
```sql
                                        QUERY PLAN
------------------------------------------------------------------------------------------
 Custom Scan (MultiplanScan) (actual rows=102038 loops=1)
   Plan is: baseline
   SQL hash: 3551150519806575491
   Const hash: 0
   Plan hash: 7463164036004167325
   ->  Gather (actual rows=102038 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         ->  Parallel Hash Join (actual rows=34013 loops=3)
               Hash Cond: (t.ticket_no = tf.ticket_no)
               ->  Parallel Seq Scan on tickets t (actual rows=122244 loops=3)
               ->  Parallel Hash (actual rows=34013 loops=3)
                     Buckets: 131072 (originally 1024)  Batches: 1 (originally 1)  Memory.
. Usage: 9304kB
                     ->  Parallel Seq Scan on ticket_flights tf (actual rows=34013 loops=.
.3)
                           Filter: ((amount > '10000'::numeric) AND ((fare_conditions)::t.
.ext = 'Business'::text) AND (length(ticket_no) = 13))
                           Rows Removed by Filter: 314563
 Planning Time: 0.867 ms
 Execution Time: 359.372 ms
(18 rows)
```
Сервер использует тот же базовый план.

## Page 27: Итоги {#source-page-027}

**Расширение pgpro\\\_multiplan позволяет стабилизировать выполнение запросов, сохраняя план или набор указаний оптимизатору**
- Поддерживаются несколько вариантов сопоставления запросов с планами
- Дополнительное хранилище планов удобно для отладки
- Расширение интегрировано с перепланированием запросов

## Page 28: Практика {#source-page-028}

1. Получите план выполнения запроса для подсчета количества пассажиров, летевших рейсами, которые опоздали на время от одной минуты до четырех часов и с вылетом, и с прибытием.
- 2. Включите автоматическое одобрение базовых планов, предварительно переключив pgpro\_multiplan на песочницу. Оптимизируйте запрос с помощью перепланирования в реальном времени.
- 3. Отключите перепланирование и сохранение планов. Проверьте, что для запроса продолжает использоваться одобренный план.
- 4. Отключите режим песочницы и убедитесь, что используется исходный неоптимизированный план.

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

1. Используйте запрос из темы «Подходы к настройке» курса QPT-16:

```sql
SELECT count(*)
FROM flights f
  JOIN ticket_flights tf ON tf.flight_id = f.flight_id
  JOIN boarding_passes bp ON
       bp.flight_id = tf.flight_id AND bp.ticket_no = tf.ticket_no
WHERE f.actual_departure — f.scheduled_departure BETWEEN
       interval '1 min' AND interval '4 hours'
  AND f.actual_arrival — f.scheduled_arrival BETWEEN
       interval '1 min' AND interval '4 hours';
```

2. Вспомните тему «Перепланирование запросов» этого курса.

## Page 29: 1. Неоптимизированный запрос {#source-page-029}

Подключимся к базе данных demo и получим план выполнения запроса:
```sql
=> \c demo
```
```text
You are now connected to database "demo" as user "student".
```
```text
=> \pset format wrapped
```
```text
Output format is wrapped.
```
```text
=> \pset columns 90
```
```text
Target width is 90.
```
```sql
=> EXPLAIN (analyze, buffers, timing off)
SELECT count(*)
FROM flights f
  JOIN ticket_flights tf ON tf.flight_id = f.flight_id
  JOIN boarding_passes bp ON bp.flight_id = tf.flight_id AND bp.ticket_no = tf.ticket_no
WHERE f.actual_departure - f.scheduled_departure BETWEEN interval '1 min' AND interval '4 hours'
  AND f.actual_arrival - f.scheduled_arrival BETWEEN interval '1 min' AND interval '4 hours';
```
```sql
                                        QUERY PLAN
------------------------------------------------------------------------------------------
 Aggregate  (cost=1635.64..1635.65 rows=1 width=8) (actual rows=1 loops=1)
   Buffers: shared hit=1562335 read=10013
   ->  Nested Loop  (cost=5.35..1635.59 rows=18 width=0) (actual rows=504286 loops=1)
         Join Filter: (f.flight_id = tf.flight_id)
         Buffers: shared hit=1562335 read=10013
         ->  Nested Loop  (cost=4.92..1626.71 rows=18 width=22) (actual rows=504286 loops.
.=1)
               Buffers: shared hit=53304 read=6185
               ->  Seq Scan on flights f  (cost=0.00..1387.63 rows=1 width=4) (actual row.
.s=14677 loops=1)
                     Filter: (((actual_departure - scheduled_departure) >= '00:01:00'::in.
.terval) AND ((actual_departure - scheduled_departure) <= '04:00:00'::interval) AND ((act.
.ual_arrival - scheduled_arrival) >= '00:01:00'::interval) AND ((actual_arrival - schedul.
.ed_arrival) <= '04:00:00'::interval))
                     Rows Removed by Filter: 18444
                     Buffers: shared read=394
               ->  Bitmap Heap Scan on boarding_passes bp  (cost=4.92..238.44 rows=64 wid.
.th=18) (actual rows=34 loops=14677)
                     Recheck Cond: (f.flight_id = flight_id)
                     Heap Blocks: exact=13714
                     Buffers: shared hit=53304 read=5791
                     ->  Bitmap Index Scan on boarding_passes_flight_id_seat_no_key  (cos.
.t=0.00..4.91 rows=64 width=0) (actual rows=34 loops=14677)
                           Index Cond: (flight_id = f.flight_id)
                           Buffers: shared hit=43802 read=1579
         ->  Index Only Scan using ticket_flights_pkey on ticket_flights tf  (cost=0.42...
.0.48 rows=1 width=18) (actual rows=1 loops=504286)
               Index Cond: ((ticket_no = bp.ticket_no) AND (flight_id = bp.flight_id))
               Heap Fetches: 0
               Buffers: shared hit=1509031 read=3828
 Planning:
   Buffers: shared hit=383 read=70
 Planning Time: 1536.847 ms
 Execution Time: 3872.919 ms
(26 rows)
```
В плане заметны ошибка оценки кардинальности в узле Seq Scan on flights f и большое число прочитанных страниц.
### 2. Перепланирование и базовый план в песочнице
Добавим библиотеки в shared\_preload\_libraries:
```sql
=> ALTER SYSTEM SET shared_preload_libraries = pg_hint_plan, pgpro_multiplan;
```
```sql
ALTER SYSTEM
```
Перезапустим экземпляр:
```sh
student$ sudo systemctl restart postgrespro-ent-16.service
```

## Page 30: Page 30 {#source-page-030}

```sh
student$ psql demo
```
Добавим расширение pgpro\_multiplan в базу данных demo:
```sql
=> CREATE EXTENSION pgpro_multiplan;
```
```sql
CREATE EXTENSION
```
Используем автоматическое перепланирование, которое будет срабатывать при ошибке кардинальности более, чем в два раза.
```text
=> SET replan_enable = true;
```
```text
SET
```
```text
=> SET replan_overrun_limit = 2;
```
```text
SET
```
```text
=> SET replan_show_signature = true;
```
```text
SET
```
Планы должны захватываться и одобряться автоматически.
```text
=> SET pgpro_multiplan.mode = 'baseline';
```
```text
SET
```
```text
=> SET pgpro_multiplan.aqe_mode = 'auto_approve_plans';
```
```text
SET
```
Включим режим песочницы:
```text
=> SET pgpro_multiplan.sandbox = true;
```
```text
SET
```
Выполним запрос:
```sql
=> EXPLAIN (analyze, timing off)
SELECT count(*)
FROM flights f
  JOIN ticket_flights tf ON tf.flight_id = f.flight_id
  JOIN boarding_passes bp ON bp.flight_id = tf.flight_id AND bp.ticket_no = tf.ticket_no
WHERE f.actual_departure - f.scheduled_departure BETWEEN interval '1 min' AND interval '4 hours'
  AND f.actual_arrival - f.scheduled_arrival BETWEEN interval '1 min' AND interval '4 hours';
```

## Page 31: Page 31 {#source-page-031}

```sql
                                        QUERY PLAN
------------------------------------------------------------------------------------------
 Finalize Aggregate  (cost=10919.90..10919.91 rows=1 width=8) (actual rows=1 loops=1)
   NodeSign: 277254305835914273
   ->  Gather  (cost=10919.69..10919.90 rows=2 width=8) (actual rows=3 loops=1)
         NodeSign: not supported
         Workers Planned: 2
         Workers Launched: 2
         ->  Partial Aggregate  (cost=9919.69..9919.70 rows=1 width=8) (actual rows=1 loo.
.ps=3)
               NodeSign: not supported
               ->  Nested Loop  (cost=1571.52..9914.91 rows=1909 width=0) (actual rows=16.
.8095 loops=3)
                     NodeSign: 16877879010983805578
                     Join Filter: (f.flight_id = tf.flight_id)
                     ->  Hash Join  (cost=1571.09..8972.56 rows=1909 width=22) (actual ro.
.ws=168095 loops=3)
                           NodeSign: 5919140159923417660
                           Hash Cond: (bp.flight_id = f.flight_id)
                           ->  Parallel Seq Scan on boarding_passes bp  (cost=0.00..6767..
.36 rows=241536 width=18) (actual rows=193229 loops=3)
                                 NodeSign: 11059471175340681660
                           ->  Hash  (cost=1387.63..1387.63 rows=14677 width=4) (actual r.
.ows=14677 loops=3)
                                 NodeSign: 10237082780770536679
                                 Buckets: 16384  Batches: 1  Memory Usage: 644kB
                                 ->  Seq Scan on flights f  (cost=0.00..1387.63 rows=1467.
.7 width=4) (actual rows=14677 loops=3)
                                       NodeSign: 10237082780770536679
                                       Filter: (((actual_departure - scheduled_departure).
. >= '00:01:00'::interval) AND ((actual_departure - scheduled_departure) <= '04:00:00'::i.
.nterval) AND ((actual_arrival - scheduled_arrival) >= '00:01:00'::interval) AND ((actual.
._arrival - scheduled_arrival) <= '04:00:00'::interval))
                                       Rows Removed by Filter: 18444
                     ->  Index Only Scan using ticket_flights_pkey on ticket_flights tf  .
.(cost=0.42..0.48 rows=1 width=18) (actual rows=1 loops=504286)
                           NodeSign: 17341206759017999067
                           Index Cond: ((ticket_no = bp.ticket_no) AND (flight_id = bp.fl.
.ight_id))
                           Heap Fetches: 0
 Planning Time: 74.175 ms
 Execution Time: 2322.304 ms
 Replan Active: true
 Table Entries: 3
 Controlled Statements: 1
 Replanning Attempts: 2
 Replan Deactivation Reason: repeated plan
 Final Run Planning Time: 0.814 ms
 Total Time Elapsed: 2396.480 ms
(36 rows)
```
Сработало перепланирование, время выполнения снизилось.
План попал в хранилище:
```sql
=> SELECT sql_hash, const_hash, plan_hash, cost, sample_string, plan_type, hintstr
FROM pgpro_multiplan_storage \gx
```
```sql
-[ RECORD 1 ]-+--------------------------------------------------------------------------
sql_hash      | -8124575748975674992
const_hash    | 0
plan_hash     | 8234005806038340721
cost          | 10919.910869265286
sample_string | SELECT count(*)                                                          +
              | FROM flights f                                                           +
              |   JOIN ticket_flights tf ON tf.flight_id = f.flight_id                   +
              |   JOIN boarding_passes bp ON bp.flight_id = tf.flight_id AND bp.ticket_no.
              |. = tf.ticket_no                                                          +
              | WHERE f.actual_departure - f.scheduled_departure BETWEEN interval '1 min'.
              |. AND interval '4 hours'                                                  +
              |   AND f.actual_arrival - f.scheduled_arrival BETWEEN interval '1 min' AND.
              |. interval '4 hours';
plan_type     | baseline
hintstr       | Set(search_path "bookings, public") Leading((("bp" "f" )"tf" )) HashJoin(.
              |."f" "bp") NestLoop("f" "tf" "bp")  SeqScan("bp") IndexOnlyScan("tf" "tick.
              |.et_flights_pkey") SeqScan("f")
```
### 3. Проверка одобренного плана

## Page 32: Page 32 {#source-page-032}

Отключим перепланирование:
```text
=> RESET replan_enable;
```
```text
RESET
```
```text
=> RESET replan_overrun_limit;
```
```text
RESET
```
```text
=> RESET replan_show_signature;
```
```text
RESET
```
Отключим автоматическое одобрение:
```text
=> RESET pgpro_multiplan.aqe_mode;
```
```text
RESET
```
Для нашего запроса используется одобренный план:
```sql
=> EXPLAIN (analyze, timing off)
SELECT count(*)
FROM flights f
  JOIN ticket_flights tf ON tf.flight_id = f.flight_id
  JOIN boarding_passes bp ON bp.flight_id = tf.flight_id AND bp.ticket_no = tf.ticket_no
WHERE f.actual_departure - f.scheduled_departure BETWEEN interval '1 min' AND interval '4 hours'
  AND f.actual_arrival - f.scheduled_arrival BETWEEN interval '1 min' AND interval '4 hours';
```
```sql
                                        QUERY PLAN
------------------------------------------------------------------------------------------
 Custom Scan (MultiplanScan)  (cost=10065.01..10065.02 rows=1 width=8) (actual rows=1 loo.
.ps=1)
   Plan is: baseline
   SQL hash: -8124575748975674992
   Const hash: 0
   Plan hash: 8234005806038340721
   ->  Finalize Aggregate  (cost=10065.01..10065.02 rows=1 width=8) (actual rows=1 loops=.
.1)
         ->  Gather  (cost=10064.80..10065.01 rows=2 width=8) (actual rows=3 loops=1)
               Workers Planned: 2
               Workers Launched: 2
               ->  Partial Aggregate  (cost=9064.80..9064.81 rows=1 width=8) (actual rows.
.=1 loops=3)
                     ->  Nested Loop  (cost=1388.07..9064.78 rows=8 width=0) (actual rows.
.=168095 loops=3)
                           Join Filter: (f.flight_id = tf.flight_id)
                           ->  Hash Join  (cost=1387.64..9060.83 rows=8 width=22) (actual.
. rows=168095 loops=3)
                                 Hash Cond: (bp.flight_id = f.flight_id)
                                 ->  Parallel Seq Scan on boarding_passes bp  (cost=0.00..
..6767.36 rows=241536 width=18) (actual rows=193229 loops=3)
                                 ->  Hash  (cost=1387.63..1387.63 rows=1 width=4) (actual.
. rows=14677 loops=3)
                                       Buckets: 16384 (originally 1024)  Batches: 1 (orig.
.inally 1)  Memory Usage: 644kB
                                       ->  Seq Scan on flights f  (cost=0.00..1387.63 row.
.s=1 width=4) (actual rows=14677 loops=3)
                                             Filter: (((actual_departure - scheduled_depa.
.rture) >= '00:01:00'::interval) AND ((actual_departure - scheduled_departure) <= '04:00:.
.00'::interval) AND ((actual_arrival - scheduled_arrival) >= '00:01:00'::interval) AND ((.
.actual_arrival - scheduled_arrival) <= '04:00:00'::interval))
                                             Rows Removed by Filter: 18444
                           ->  Index Only Scan using ticket_flights_pkey on ticket_flight.
.s tf  (cost=0.42..0.48 rows=1 width=18) (actual rows=1 loops=504286)
                                 Index Cond: ((ticket_no = bp.ticket_no) AND (flight_id =.
. bp.flight_id))
                                 Heap Fetches: 0
 Planning Time: 2.810 ms
 Execution Time: 2118.608 ms
(25 rows)
```
### 4. Проверка с выключенной песочницей
Отключим песочницу:
```text
=> RESET pgpro_multiplan.sandbox;
```
```text
RESET
```

## Page 33: Page 33 {#source-page-033}

Убедимся, что в хранилище плана нет:
```sql
=> SELECT sql_hash, const_hash, plan_hash, cost, sample_string, plan_type, hintstr
FROM pgpro_multiplan_storage \gx
```
```text
(0 rows)
```
Повторим запрос:
```sql
=> EXPLAIN (analyze, timing off)
SELECT count(*)
FROM flights f
  JOIN ticket_flights tf ON tf.flight_id = f.flight_id
  JOIN boarding_passes bp ON bp.flight_id = tf.flight_id AND bp.ticket_no = tf.ticket_no
WHERE f.actual_departure - f.scheduled_departure BETWEEN interval '1 min' AND interval '4 hours'
  AND f.actual_arrival - f.scheduled_arrival BETWEEN interval '1 min' AND interval '4 hours';
```
```sql
                                        QUERY PLAN
------------------------------------------------------------------------------------------
 Aggregate  (cost=1635.64..1635.65 rows=1 width=8) (actual rows=1 loops=1)
   ->  Nested Loop  (cost=5.35..1635.59 rows=18 width=0) (actual rows=504286 loops=1)
         Join Filter: (f.flight_id = tf.flight_id)
         ->  Nested Loop  (cost=4.92..1626.71 rows=18 width=22) (actual rows=504286 loops.
.=1)
               ->  Seq Scan on flights f  (cost=0.00..1387.63 rows=1 width=4) (actual row.
.s=14677 loops=1)
                     Filter: (((actual_departure - scheduled_departure) >= '00:01:00'::in.
.terval) AND ((actual_departure - scheduled_departure) <= '04:00:00'::interval) AND ((act.
.ual_arrival - scheduled_arrival) >= '00:01:00'::interval) AND ((actual_arrival - schedul.
.ed_arrival) <= '04:00:00'::interval))
                     Rows Removed by Filter: 18444
               ->  Bitmap Heap Scan on boarding_passes bp  (cost=4.92..238.44 rows=64 wid.
.th=18) (actual rows=34 loops=14677)
                     Recheck Cond: (f.flight_id = flight_id)
                     Heap Blocks: exact=13714
                     ->  Bitmap Index Scan on boarding_passes_flight_id_seat_no_key  (cos.
.t=0.00..4.91 rows=64 width=0) (actual rows=34 loops=14677)
                           Index Cond: (flight_id = f.flight_id)
         ->  Index Only Scan using ticket_flights_pkey on ticket_flights tf  (cost=0.42...
.0.48 rows=1 width=18) (actual rows=1 loops=504286)
               Index Cond: ((ticket_no = bp.ticket_no) AND (flight_id = bp.flight_id))
               Heap Fetches: 0
 Planning Time: 0.894 ms
 Execution Time: 2899.823 ms
(17 rows)
```
Сервер вернулся к исходному плану.
### Уборка
Удалим расширение, созданное в базе demo и очистим список загружаемых библиотек.
```sql
=> DROP EXTENSION pgpro_multiplan;
```
```sql
DROP EXTENSION
```
```sql
=> ALTER SYSTEM RESET shared_preload_libraries;
```
```sql
ALTER SYSTEM
```
```sh
student$ sudo systemctl restart postgrespro-ent-16.service
```