09 Занятие 09: Производительность / Адаптивная оптимизация

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

Примечание

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

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

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

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

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

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

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

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

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

Page 2: Темы

Предназначение AQO
Принцип работы AQO
Обучение без классификации
Классификация запросов
Песочница

Page 3: Предназначение AQO

Исправление неверных оценок кардинальности, когда штатные средства не помогают

отклонение кардинальности от вычисленной планировщиком
AQO улучшает оценки в запросах с простыми условиями
- атрибут оператор-сравнения константа
Основная сфера применения — сложные аналитические запросы

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

Модуль адаптивной оптимизации запросов AQO (Adaptive Query Optimization) можно применить в тех случаях, когда стандартные инструменты улучшения оценок селективности и кардинальности (подробно описанные в курсе QPT) не срабатывают.

Модуль AQO работает с простыми условиями вида «атрибут = константа», «атрибут > константа» и подобными.

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

Основная сфера применения AQO — сложные аналитические запросы с повторяющейся структурой, для которых EXPLAIN ANALYZE выдает неверную оценку кардинальности.

Page 4: Пример запроса с неверной оценкой

Начнем с установки расширения:

=> ALTER SYSTEM SET shared_preload_libraries = 'aqo';

ALTER SYSTEM

Необходима перезагрузка экземпляра.

student$ sudo systemctl restart postgrespro-ent-16.service

student$ psql demo

Добавим расширение AQO в базу данных:

=> CREATE EXTENSION aqo;

CREATE EXTENSION

В качестве примера мы будем использовать запрос с ошибкой вычисления кардинальности, вызванной коррелированными предикатами.

=> EXPLAIN SELECT * FROM flights
WHERE departure_airport = 'VKO'
  AND flight_no = 'PG0007';

                                               QUERY PLAN
------------------------------------------------------------------------------------------
---------------
 Bitmap Heap Scan on flights  (cost=4.62..132.14 rows=2 width=63)
   Recheck Cond: (flight_no = 'PG0007'::bpchar)
   Filter: (departure_airport = 'VKO'::bpchar)
   ->  Bitmap Index Scan on flights_flight_no_scheduled_departure_key  (cost=0.00..4.62
rows=44 width=0)
         Index Cond: (flight_no = 'PG0007'::bpchar)
(5 rows)

Точное количество строк отличается от оценки:

=> SELECT count(*)
FROM flights
WHERE departure_airport = 'VKO'
  AND flight_no = 'PG0007';

 count
-------
    61
(1 row)

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

Page 5: Принцип работы AQO

{#source-figure-005 caption=“Source slide page 5: Принцип работы AQO”}

Сбор статистики по выполняемым запросам и улучшение оценок количества строк с помощью машинного обучения

Лучшие оценки — основа более оптимальных планов

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

Модуль AQO собирает статистику по выполненным запросам для обучения модели, с помощью которой AQO предоставляет уточненные оценки. Именно это концептуально отличает AQO от обычного планировщика, на работу которого никак не влияют ранее выполненные запросы.

Для оценки кардинальности используется метод машинного обучения на базе алгоритма поиска ближайших соседей k-NN вместо обычной статистики планировщика.

Page 6: Принцип работы AQO

{#source-figure-006 caption=“Source slide page 6: Принцип работы AQO”}

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

Расширение AQO включается параметром aqo.enable.

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

Когда статистическая информация накоплена, модель может использоваться для уточнения оценки планировщика.

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

Если установить параметру aqo.advanced значение on, выполняется классификация: в один класс попадают запросы, отличающиеся только константами. В таком случае статистика будет собираться и применяться для каждого класса отдельно.

Как именно обучается и используется модель, определяется режимом работы (параметр aqo.mode).

https://postgrespro.ru/docs/enterprise/16/aqo

Page 7: Без классификации

Режим learn

статистика применяется и собирается
начальное обучение
Режим frozen
- статистика применяется, но новая не собирается
- стабилизация

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

Если классификация не применяется (aqo.advanced = off), все запросы относятся к одному общему классу. В этом случае имеют смысл два режима работы: learn и frozen.

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

Этот режим используется для начального обучения AQO: после включения расширения надо выполнять проблемные запросы, варьируя константы, до тех пор, пока не будет достигнуто необходимое качество их планирования. Обучать модель на всех запросах не требуется: обычно большая их часть планируется правильно и без AQO, а в некоторых случаях применение AQO может и ухудшить производительность.

Затем расширение переводится в режим frozen, в котором собранная статистика продолжает использоваться, но новые данные не собираются.

Этот режим позволяет стабилизировать планы и снизить влияние AQO на время планирования запросов. Конечно, при дальнейшем изменении данных точность планирования может ухудшиться; в таком случае можно на время вернуться в режим обучения, чтобы обновить статистику.

Page 8: Без классификации: режим learn

С помощью параметра aqo.enable включим aqo на уровне кластера и перечитаем конфигурацию:

=> ALTER SYSTEM SET aqo.enable = on;

ALTER SYSTEM

=> SELECT pg_reload_conf();

 pg_reload_conf
----------------
 t
(1 row)

По умолчанию устанавливается режим learn:

=> SHOW aqo.mode;

 aqo.mode
----------
 learn
(1 row)

Выполним показанный ранее запрос:

=> EXPLAIN (analyze, timing off, summary off)
SELECT * FROM flights
WHERE departure_airport = 'VKO'
  AND flight_no = 'PG0007';

                                                            QUERY PLAN
------------------------------------------------------------------------------------------
----------------------------------------
 Bitmap Heap Scan on flights  (cost=4.62..132.14 rows=2 width=63) (actual rows=61 loops=1)
   AQO not used, fss=-4388671362309763767
   Recheck Cond: (flight_no = 'PG0007'::bpchar)
   Filter: (departure_airport = 'VKO'::bpchar)
   Heap Blocks: exact=2
   ->  Bitmap Index Scan on flights_flight_no_scheduled_departure_key  (cost=0.00..4.62
rows=44 width=0) (actual rows=61 loops=1)
         Index Cond: (flight_no = 'PG0007'::bpchar)
 Using aqo: true
 AQO mode: LEARN
 AQO advanced: OFF
 Query hash: -6712624238167333493
 JOINS: 0
(12 rows)

Для AQO этот запрос является новым (AQO not used), поэтому для планирования использовались обычные оценки. Но AQO уже получил данные для обучения модели. Найдем статистику AQO по идентификатору запроса (query hash):

=> SELECT * FROM aqo_query_stat
WHERE queryid = -6712624238167333493 \gx

-[ RECORD 1 ]-----------------+---------------------
queryid                       | -6712624238167333493
dbid                          | 16388
execution_time_with_aqo       | {0.000174149}
execution_time_without_aqo    | {}
planning_time_with_aqo        | {0.001055677}
planning_time_without_aqo     | {}
cardinality_error_with_aqo    | {3.417726683613366}
cardinality_error_without_aqo | {}
executions_with_aqo           | 1
executions_without_aqo        | 0

Выполним запрос еще раз. Теперь используется уточненная оценка (AQO: error=0%):

=> EXPLAIN (analyze, timing off, summary off)
SELECT * FROM flights
WHERE departure_airport = 'VKO'
  AND flight_no = 'PG0007';

Page 9: Page 9

                                                            QUERY PLAN
------------------------------------------------------------------------------------------
----------------------------------------
 Bitmap Heap Scan on flights  (cost=4.64..132.16 rows=61 width=63) (actual rows=61
loops=1)
   AQO: rows=61, error=0%, fss=-4388671362309763767
   Recheck Cond: (flight_no = 'PG0007'::bpchar)
   Filter: (departure_airport = 'VKO'::bpchar)
   Heap Blocks: exact=2
   ->  Bitmap Index Scan on flights_flight_no_scheduled_departure_key  (cost=0.00..4.62
rows=44 width=0) (actual rows=61 loops=1)
         Index Cond: (flight_no = 'PG0007'::bpchar)
 Using aqo: true
 AQO mode: LEARN
 AQO advanced: OFF
 Query hash: -6712624238167333493
 JOINS: 0
(12 rows)

Количество выполнений с AQO увеличилось:

=> SELECT * FROM aqo_query_stat
WHERE queryid = -6712624238167333493 \gx

-[ RECORD 1 ]-----------------+--------------------------
queryid                       | -6712624238167333493
dbid                          | 16388
execution_time_with_aqo       | {0.000174149,0.000170702}
execution_time_without_aqo    | {}
planning_time_with_aqo        | {0.001055677,0.000129444}
planning_time_without_aqo     | {}
cardinality_error_with_aqo    | {3.417726683613366,0}
cardinality_error_without_aqo | {}
executions_with_aqo           | 2
executions_without_aqo        | 0

Спрогнозировать правильную кардинальность для других констант модель пока не может:

=> EXPLAIN (analyze, timing off, summary off)
SELECT * FROM flights
WHERE departure_airport = 'OVB'
  AND flight_no = 'PG0047';

                                                           QUERY PLAN
------------------------------------------------------------------------------------------
---------------------------------------
 Bitmap Heap Scan on flights  (cost=4.64..132.16 rows=61 width=63) (actual rows=9 loops=1)
   AQO: rows=61, error=85%, fss=-4388671362309763767
   Recheck Cond: (flight_no = 'PG0047'::bpchar)
   Filter: (departure_airport = 'OVB'::bpchar)
   Heap Blocks: exact=1
   ->  Bitmap Index Scan on flights_flight_no_scheduled_departure_key  (cost=0.00..4.62
rows=44 width=0) (actual rows=9 loops=1)
         Index Cond: (flight_no = 'PG0047'::bpchar)
 Using aqo: true
 AQO mode: LEARN
 AQO advanced: OFF
 Query hash: -6712624238167333493
 JOINS: 0
(12 rows)

Но, получив новые данные, модель выдает исправленную оценку:

=> EXPLAIN (analyze, timing off, summary off)
SELECT *
FROM flights f
WHERE f.departure_airport = 'OVB' AND f.flight_no = 'PG0047';

Page 10: Page 10

                                                           QUERY PLAN
------------------------------------------------------------------------------------------
---------------------------------------
 Bitmap Heap Scan on flights f  (cost=4.62..132.14 rows=9 width=63) (actual rows=9
loops=1)
   AQO: rows=9, error=0%, fss=-4388671362309763767
   Recheck Cond: (flight_no = 'PG0047'::bpchar)
   Filter: (departure_airport = 'OVB'::bpchar)
   Heap Blocks: exact=1
   ->  Bitmap Index Scan on flights_flight_no_scheduled_departure_key  (cost=0.00..4.62
rows=44 width=0) (actual rows=9 loops=1)
         Index Cond: (flight_no = 'PG0047'::bpchar)
 Using aqo: true
 AQO mode: LEARN
 AQO advanced: OFF
 Query hash: -6712624238167333493
 JOINS: 0
(12 rows)

Обратите внимание, что расширение ориентируется на дерево разбора, и поэтому не чувствительно к таким изменениям, как регистр текста, переносы строк, псевдонимы таблиц и т. п. Более того: поскольку статистика не делится по классам запросов, накопленная информация об узлах будет применяться к любым планам, которые содержат известные узлы (сравните подписи к узлам Nested Loop и Bitmap Heap Scan).

=> EXPLAIN (analyze, timing off, summary off)
SELECT * FROM flights f
  JOIN airports a ON a.airport_code = f.departure_airport
WHERE f.departure_airport = 'VKO'
  AND f.flight_no = 'PG0007';

                                                               QUERY PLAN
------------------------------------------------------------------------------------------
----------------------------------------------
 Nested Loop  (cost=4.64..167.87 rows=61 width=162) (actual rows=61 loops=1)
   AQO not used, fss=-7344153368946762870
   ->  Seq Scan on airports_data ml  (cost=0.00..4.30 rows=1 width=145) (actual rows=1
loops=1)
         AQO not used, fss=702648804789554594
         Filter: (airport_code = 'VKO'::bpchar)
         Rows Removed by Filter: 103
   ->  Bitmap Heap Scan on flights f  (cost=4.64..132.16 rows=61 width=63) (actual
rows=61 loops=1)
         AQO: rows=61, error=0%, fss=-4388671362309763767
         Recheck Cond: (flight_no = 'PG0007'::bpchar)
         Filter: (departure_airport = 'VKO'::bpchar)
         Heap Blocks: exact=2
         ->  Bitmap Index Scan on flights_flight_no_scheduled_departure_key
(cost=0.00..4.62 rows=44 width=0) (actual rows=61 loops=1)
               Index Cond: (flight_no = 'PG0007'::bpchar)
 Using aqo: true
 AQO mode: LEARN
 AQO advanced: OFF
 Query hash: -1728112134488687408
 JOINS: 1
(18 rows)

Без классификации: режим frozen

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

=> SET aqo.mode = frozen;

SET

Для известных констант сохраняется уточненная оценка:

=> EXPLAIN (analyze, timing off, summary off)
SELECT * FROM flights
WHERE departure_airport = 'OVB'
  AND flight_no = 'PG0047';

Page 11: Page 11

                                                           QUERY PLAN
------------------------------------------------------------------------------------------
---------------------------------------
 Bitmap Heap Scan on flights  (cost=4.62..132.14 rows=9 width=63) (actual rows=9 loops=1)
   AQO: rows=9, error=0%, fss=-4388671362309763767
   Recheck Cond: (flight_no = 'PG0047'::bpchar)
   Filter: (departure_airport = 'OVB'::bpchar)
   Heap Blocks: exact=1
   ->  Bitmap Index Scan on flights_flight_no_scheduled_departure_key  (cost=0.00..4.62
rows=44 width=0) (actual rows=9 loops=1)
         Index Cond: (flight_no = 'PG0047'::bpchar)
 Using aqo: true
 AQO mode: FROZEN
 AQO advanced: OFF
 Query hash: -6712624238167333493
 JOINS: 0
(12 rows)

Для новых значений модель выдает прогноз на основе тех данных, на которых она была обучена:

=> EXPLAIN (analyze, timing off, summary off)
SELECT * FROM flights
WHERE departure_airport = 'DME'
  AND flight_no = 'PG0005';

                                                            QUERY PLAN
------------------------------------------------------------------------------------------
----------------------------------------
 Bitmap Heap Scan on flights  (cost=4.63..132.15 rows=31 width=63) (actual rows=26
loops=1)
   AQO: rows=31, error=16%, fss=-4388671362309763767
   Recheck Cond: (flight_no = 'PG0005'::bpchar)
   Filter: (departure_airport = 'DME'::bpchar)
   Heap Blocks: exact=2
   ->  Bitmap Index Scan on flights_flight_no_scheduled_departure_key  (cost=0.00..4.62
rows=44 width=0) (actual rows=26 loops=1)
         Index Cond: (flight_no = 'PG0005'::bpchar)
 Using aqo: true
 AQO mode: FROZEN
 AQO advanced: OFF
 Query hash: -6712624238167333493
 JOINS: 0
(12 rows)

Page 12: Классификация запросов

Режимы learn и frozen

Режим controlled
- статистика применяется (use_aqo),
- но обновляется (learn_aqo) только для известных запросов
- стабилизация без «заморозки»
Режим intelligent
- автоматическое управление обучением и применением (auto_tune)
- сначала только сбор статистики;
- применение при реальном улучшении, иначе — отказ от использования
Тонкая настройка
- ручное управление свойствами use_aqo, learn_aqo, auto_tune

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

При включенной классификации (aqo.advanced = on) статистика собирается отдельно для каждого класса запросов; класс составляют запросы, у которых деревья разбора отличаются только константами. В этом случае к уже рассмотренным режимам работы learn и frozen добавляются еще два: controlled и intelligent (без классификации они не отличаются от обычного learn).

В режиме controlled модель применяется для уже известных классов запросов: она продолжает обучаться на них (свойство learn_aqo) и используется для уточнения оценок (use_aqo). Но для новых классов запросов статистика не собирается. Этот режим можно использовать вместо frozen, чтобы тратить ресурсы только на нужные запросы, но при этом не «замораживать» статистику, а продолжать обновлять ее.

В режиме intelligent расширение самостоятельно управляет обучением и использованием (свойство auto_tune). Новые запросы сначала выполняются шесть раз без AQO, а затем обученная модель начинает использоваться (включается свойство use_aqo). Далее модель может выключаться или включаться в зависимости от того, уменьшает ли ее использование время планирования и выполнения запроса. Если после 50 итераций улучшения не происходит, AQO перестает собирать статистику по данному классу запросов и пытаться ее применить (выключает свойства use_aqo и learn_aqo).

Этот режим работы следует применять с осторожностью, поскольку принимаемые решения могут оказаться недостаточно точными.

Свойствами use_aqo, learn_aqo и auto_tune можно управлять вручную отдельно для каждого класса запросов.

Page 13: С классификацией: режим controlled

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

=> SET aqo.advanced = on;

SET

=> SELECT aqo_reset();

 aqo_reset
-----------
         5
(1 row)

Сначала обучим модель на запросах одного класса (класс составляют запросы с одинаковым деревом разбора без учета констант).

=> SET aqo.mode = learn;

SET

=> EXPLAIN (analyze, timing off, summary off)
SELECT * FROM flights
WHERE departure_airport = 'VKO'
  AND flight_no = 'PG0007';

                                                            QUERY PLAN
------------------------------------------------------------------------------------------
----------------------------------------
 Bitmap Heap Scan on flights  (cost=4.62..132.14 rows=2 width=63) (actual rows=61 loops=1)
   AQO not used, fss=-4388671362309763767
   Recheck Cond: (flight_no = 'PG0007'::bpchar)
   Filter: (departure_airport = 'VKO'::bpchar)
   Heap Blocks: exact=2
   ->  Bitmap Index Scan on flights_flight_no_scheduled_departure_key  (cost=0.00..4.62
rows=44 width=0) (actual rows=61 loops=1)
         Index Cond: (flight_no = 'PG0007'::bpchar)
 Using aqo: true
 AQO mode: LEARN
 AQO advanced: ON
 Query hash: -6712624238167333493
 JOINS: 0
(12 rows)

=> EXPLAIN (analyze, timing off, summary off)
SELECT * FROM flights
WHERE departure_airport = 'OVB'
  AND flight_no = 'PG0047';

                                                           QUERY PLAN
------------------------------------------------------------------------------------------
---------------------------------------
 Bitmap Heap Scan on flights  (cost=4.64..132.16 rows=61 width=63) (actual rows=9 loops=1)
   AQO: rows=61, error=85%, fss=-4388671362309763767
   Recheck Cond: (flight_no = 'PG0047'::bpchar)
   Filter: (departure_airport = 'OVB'::bpchar)
   Heap Blocks: exact=1
   ->  Bitmap Index Scan on flights_flight_no_scheduled_departure_key  (cost=0.00..4.62
rows=44 width=0) (actual rows=9 loops=1)
         Index Cond: (flight_no = 'PG0047'::bpchar)
 Using aqo: true
 AQO mode: LEARN
 AQO advanced: ON
 Query hash: -6712624238167333493
 JOINS: 0
(12 rows)

Если раньше информация об известных узлах применялась ко всем запросам, то теперь для каждого класса собирается собственная статистика. Поэтому в следующем запросе все узлы имеют подпись AQO not used — именно для такого класса узел Bitmap Heap Scan еще не встречался:

Page 14: Page 14

=> EXPLAIN (analyze, timing off, summary off)

SELECT * FROM flights f
  JOIN airports a ON a.airport_code = f.departure_airport
WHERE f.departure_airport = 'VKO'
  AND f.flight_no = 'PG0007';

                                                               QUERY PLAN
------------------------------------------------------------------------------------------
----------------------------------------------
 Nested Loop  (cost=4.62..137.47 rows=2 width=162) (actual rows=61 loops=1)
   AQO not used, fss=-7344153368946762870
   ->  Seq Scan on airports_data ml  (cost=0.00..4.30 rows=1 width=145) (actual rows=1
loops=1)
         AQO not used, fss=702648804789554594
         Filter: (airport_code = 'VKO'::bpchar)
         Rows Removed by Filter: 103
   ->  Bitmap Heap Scan on flights f  (cost=4.62..132.14 rows=2 width=63) (actual rows=61
loops=1)
         AQO not used, fss=-4388671362309763767
         Recheck Cond: (flight_no = 'PG0007'::bpchar)
         Filter: (departure_airport = 'VKO'::bpchar)
         Heap Blocks: exact=2
         ->  Bitmap Index Scan on flights_flight_no_scheduled_departure_key
(cost=0.00..4.62 rows=44 width=0) (actual rows=61 loops=1)
               Index Cond: (flight_no = 'PG0007'::bpchar)
 Using aqo: true
 AQO mode: LEARN
 AQO advanced: ON
 Query hash: -1728112134488687408
 JOINS: 1
(18 rows)

Теперь переведем расширение в режим controlled:

=> SET aqo.mode = controlled;

SET

В этом режиме работы запросы из неизвестных классов не обрабатываются (Using aqo: false):

=> EXPLAIN (analyze, timing off, summary off)
SELECT count(*) FROM flights;

                                          QUERY PLAN
------------------------------------------------------------------------------------------
-----
 Aggregate  (cost=808.01..808.02 rows=1 width=8) (actual rows=1 loops=1)
   ->  Seq Scan on flights  (cost=0.00..725.21 rows=33121 width=0) (actual rows=33121
loops=1)
 Using aqo: false
 AQO mode: CONTROLLED
 AQO advanced: ON
 Query hash: 5250232765826456330
 JOINS: -1
(7 rows)

Но для уже известных классов запросов модель продолжает обучаться. Оценка для новой пары «аэропорт — номер рейса» не точна:

=> EXPLAIN (analyze, timing off, summary off)
SELECT * FROM flights f
WHERE f.departure_airport = 'SVO' AND f.flight_no = 'PG0505';

Page 15: Page 15

                                                            QUERY PLAN
------------------------------------------------------------------------------------------
----------------------------------------
 Bitmap Heap Scan on flights f  (cost=4.63..132.15 rows=32 width=63) (actual rows=61
loops=1)
   AQO: rows=32, error=-91%, fss=-4388671362309763767
   Recheck Cond: (flight_no = 'PG0505'::bpchar)
   Filter: (departure_airport = 'SVO'::bpchar)
   Heap Blocks: exact=1
   ->  Bitmap Index Scan on flights_flight_no_scheduled_departure_key  (cost=0.00..4.62
rows=44 width=0) (actual rows=61 loops=1)
         Index Cond: (flight_no = 'PG0505'::bpchar)
 Using aqo: true
 AQO mode: CONTROLLED
 AQO advanced: ON
 Query hash: -6712624238167333493
 JOINS: 0
(12 rows)

Но в следующий раз модель исправляется, получив новые данные:

=> EXPLAIN (analyze, timing off, summary off)
SELECT * FROM flights
WHERE departure_airport = 'SVO'
  AND flight_no = 'PG0505';

                                                            QUERY PLAN
------------------------------------------------------------------------------------------
----------------------------------------
 Bitmap Heap Scan on flights  (cost=4.64..132.16 rows=61 width=63) (actual rows=61
loops=1)
   AQO: rows=61, error=0%, fss=-4388671362309763767
   Recheck Cond: (flight_no = 'PG0505'::bpchar)
   Filter: (departure_airport = 'SVO'::bpchar)
   Heap Blocks: exact=1
   ->  Bitmap Index Scan on flights_flight_no_scheduled_departure_key  (cost=0.00..4.62
rows=44 width=0) (actual rows=61 loops=1)
         Index Cond: (flight_no = 'PG0505'::bpchar)
 Using aqo: true
 AQO mode: CONTROLLED
 AQO advanced: ON
 Query hash: -6712624238167333493
 JOINS: 0
(12 rows)

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

=> SELECT queryid, learn_aqo, use_aqo
FROM aqo_queries
WHERE queryid != 0;

       queryid        | learn_aqo | use_aqo
----------------------+-----------+---------
 -1728112134488687408 | t         | t
 -6712624238167333493 | t         | t
(2 rows)

Например, можно временно отключить использование модели, но продолжить обучение (NULL сохраняет существующее значение):

=> SELECT aqo_queries_update(
  queryid => -6712624238167333493,
  dbid => NULL,
  fs => NULL,
  learn_aqo => true,
  use_aqo => false,
  auto_tuning => NULL
);

 aqo_queries_update
--------------------
 t
(1 row)

Page 16: Page 16

=> EXPLAIN (analyze, timing off, summary off)
SELECT * FROM flights
WHERE departure_airport = 'DME'
  AND flight_no = 'PG0005';

                                                            QUERY PLAN
------------------------------------------------------------------------------------------
----------------------------------------
 Bitmap Heap Scan on flights  (cost=4.62..132.14 rows=4 width=63) (actual rows=26 loops=1)
   AQO not used, fss=0
   Recheck Cond: (flight_no = 'PG0005'::bpchar)
   Filter: (departure_airport = 'DME'::bpchar)
   Heap Blocks: exact=2
   ->  Bitmap Index Scan on flights_flight_no_scheduled_departure_key  (cost=0.00..4.62
rows=44 width=0) (actual rows=26 loops=1)
         Index Cond: (flight_no = 'PG0005'::bpchar)
 Using aqo: false
 AQO mode: CONTROLLED
 AQO advanced: ON
 Query hash: -6712624238167333493
 JOINS: 0
(12 rows)

Сбросив свойство learn_aqo, можно «заморозить» отдельный класс запросов:

=> SELECT aqo_queries_update(
  queryid => -6712624238167333493,
  dbid => NULL,
  fs => NULL,
  learn_aqo => false,
  use_aqo => true,
  auto_tuning => NULL
);

 aqo_queries_update
--------------------
 t
(1 row)

Теперь оценка учитывает данные, полученные в прошлый раз, но дальнейшее обучение остановлено:

=> EXPLAIN (analyze, timing off, summary off)
SELECT * FROM flights
WHERE departure_airport = 'DME'
  AND flight_no = 'PG0005';

                                                            QUERY PLAN
------------------------------------------------------------------------------------------
----------------------------------------
 Bitmap Heap Scan on flights  (cost=4.63..132.15 rows=51 width=63) (actual rows=26
loops=1)
   AQO: rows=51, error=49%, fss=-4388671362309763767
   Recheck Cond: (flight_no = 'PG0005'::bpchar)
   Filter: (departure_airport = 'DME'::bpchar)
   Heap Blocks: exact=2
   ->  Bitmap Index Scan on flights_flight_no_scheduled_departure_key  (cost=0.00..4.62
rows=44 width=0) (actual rows=26 loops=1)
         Index Cond: (flight_no = 'PG0005'::bpchar)
 Using aqo: true
 AQO mode: CONTROLLED
 AQO advanced: ON
 Query hash: -6712624238167333493
 JOINS: 0
(12 rows)

В режиме intelligent можно управлять и свойством auto_tune.

С классификацией: режим intelligent

Отключим параллельное выполнение для упрощения планов запросов:

=> SET max_parallel_workers_per_gather = 0;

SET

Сбросим еще раз статистику AQO и установим режим intelligent:

Page 17: Page 17

=> SELECT aqo_reset();

 aqo_reset
-----------
        10
(1 row)

=> SET aqo.mode = intelligent;

SET

Включим параметр, позволяющий собирать статистику при отключенном использовании AQO:

=> SET aqo.force_collect_stat = on;

SET

Несколько раз выполним запрос с большой ошибкой оценки кардинальности. В режиме intelligent сначала происходит обучение, но оценки при планировании не уточняются.

=> EXPLAIN (analyze) SELECT *
FROM ticket_flights tf
  JOIN tickets t ON t.ticket_no = tf.ticket_no
WHERE tf.fare_conditions = 'Business'
  AND tf.amount > 50000
  AND length(tf.ticket_no) = 13;

                                                             QUERY PLAN
------------------------------------------------------------------------------------------
------------------------------------------
 Nested Loop  (cost=0.42..30075.24 rows=38 width=136) (actual time=0.194..403.252
rows=30133 loops=1)
   AQO not used, fss=0
   ->  Seq Scan on ticket_flights tf  (cost=0.00..29754.52 rows=38 width=32) (actual
time=0.145..211.525 rows=30133 loops=1)
         AQO not used, fss=0
         Filter: ((amount > '50000'::numeric) AND ((fare_conditions)::text =
'Business'::text) AND (length(ticket_no) = 13))
         Rows Removed by Filter: 1015593
   ->  Index Scan using tickets_pkey on tickets t  (cost=0.42..8.44 rows=1 width=104)
(actual time=0.006..0.006 rows=1 loops=30133)
         AQO not used, fss=0
         Index Cond: (ticket_no = tf.ticket_no)
 Planning Time: 842.994 ms
 Execution Time: 405.288 ms
 Using aqo: false
 AQO mode: INTELLIGENT
 AQO advanced: ON
 Query hash: 4602601022030847932
 JOINS: 1
(16 rows)

=> \g /dev/null

=> \g /dev/null

=> \g /dev/null

=> \g /dev/null

=> \g /dev/null

На седьмой раз AQO начинает использовать модель и исправляет оценку кардинальности. Обратите внимание, что при этом поменялся и алгоритм соединения — вложенному циклу планировщик предпочел хеширование:

=> EXPLAIN (analyze) SELECT *
FROM ticket_flights tf
  JOIN tickets t ON t.ticket_no = tf.ticket_no
WHERE tf.fare_conditions = 'Business'
  AND tf.amount > 50000
  AND length(tf.ticket_no) = 13;

Page 18: Page 18

                                                              QUERY PLAN
------------------------------------------------------------------------------------------
--------------------------------------------
 Hash Join  (cost=30131.18..42567.92 rows=30133 width=136) (actual time=215.235..370.196
rows=30133 loops=1)
   AQO: rows=30133, error=0%, fss=8105558446173165231
   Hash Cond: (t.ticket_no = tf.ticket_no)
   ->  Seq Scan on tickets t  (cost=0.00..9843.33 rows=366733 width=104) (actual
time=0.037..55.035 rows=366733 loops=1)
         AQO not used, fss=-782071859942470067
   ->  Hash  (cost=29754.52..29754.52 rows=30133 width=32) (actual time=214.908..214.910
rows=30133 loops=1)
         Buckets: 32768  Batches: 1  Memory Usage: 2258kB
         ->  Seq Scan on ticket_flights tf  (cost=0.00..29754.52 rows=30133 width=32)
(actual time=0.168..205.589 rows=30133 loops=1)
               AQO: rows=30133, error=0%, fss=3731686215164005158
               Filter: ((amount > '50000'::numeric) AND ((fare_conditions)::text =
'Business'::text) AND (length(ticket_no) = 13))
               Rows Removed by Filter: 1015593
 Planning Time: 0.500 ms
 Execution Time: 372.079 ms
 Using aqo: true
 AQO mode: INTELLIGENT
 AQO advanced: ON
 Query hash: 4602601022030847932
 JOINS: 1
(18 rows)

=> SELECT queryid, learn_aqo, use_aqo, auto_tuning
FROM aqo_queries
WHERE queryid != 0;

       queryid       | learn_aqo | use_aqo | auto_tuning
---------------------+-----------+---------+-------------
 4602601022030847932 | t         | t       | t
(1 row)

=> SELECT * FROM aqo_query_stat WHERE queryid != 0 \gx

-[ RECORD 1
]-----------------+-----------------------------------------------------------------------
---------------------------------------
queryid                       | 4602601022030847932
dbid                          | 16388
execution_time_with_aqo       | {0.371713365}
execution_time_without_aqo    |
{0.40541465,0.382183913,0.399997997,0.404536646,0.388363817,0.398315885}
planning_time_with_aqo        | {0.000472313}
planning_time_without_aqo     |
{0.842976038,0.000458545,0.000399877,0.000655214,0.000358467,0.000393757}
cardinality_error_with_aqo    | {0}
cardinality_error_without_aqo |
{4.450526690651791,4.450526690651791,4.450526690651791,4.450526690651791,4.450526690651791
,4.450526690651791}
executions_with_aqo           | 1
executions_without_aqo        | 6

Теперь, когда модель используется, автоматика режима intelligent будет смотреть на среднее время планирования и выполнения запроса с AQO и без AQO, включая или выключая использование модели. Выполним запрос еще несколько раз и проверим статистику:

=> SELECT *
FROM ticket_flights tf
  JOIN tickets t ON t.ticket_no = tf.ticket_no
WHERE tf.fare_conditions = 'Business'
  AND tf.amount > 50000
  AND length(tf.ticket_no) = 13\g /dev/null

=> \g /dev/null

=> \g /dev/null

=> \g /dev/null

=> \g /dev/null

=> \g /dev/null

Page 19: Page 19

=> \g /dev/null

=> \g /dev/null

=> \g /dev/null

=> \g /dev/null

=> SELECT
  executions_with_aqo,
  (SELECT avg(e) FROM unnest(execution_time_with_aqo) e) execution_time_with_aqo,
  executions_without_aqo,
  (SELECT avg(e) FROM unnest(execution_time_without_aqo) e) execution_time_without_aqo
FROM aqo_query_stat
WHERE queryid != 0 \gx

-[ RECORD 1 ]--------------+--------------------
executions_with_aqo        | 8
execution_time_with_aqo    | 0.517654343875
executions_without_aqo     | 9
execution_time_without_aqo | 0.46125627288888893

Если AQO не даст выигрыша по времени выполнения в течение 50 выполнений, оптимизация для данного класса запроса совсем отключится.

Page 20: Песочница

Отдельная база знаний

в общей памяти сервера
изолирована от основной базы
общая для всех сеансов, где включена (aqo.sandbox = on)
Позволяет
- экспериментировать с AQO, не влияя на основную активность
- работать на физической реплике

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

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

На сервере существует единственная песочница, которая располагается в его общей памяти. Во всех сеансах, в которых задано значение aqo.sandbox = on, расширение AQO будет работает не с основной базой знаний, а с общей песочницей.

Это дает возможность экспериментировать с настройками, не влияя на выполняющиеся запросы пользователей, а затем переносить статистику в основную базу знаний.

Также песочница позволяет обучать AQO на запросах, выполняющихся на физической реплике. По умолчанию (если aqo.wal_rw = true) изменения основной базы знаний записываются в WAL и передаются на реплику. Поэтому запросы на реплике могут использовать статистику AQO основного сервера. Однако обучение на реплике невозможно, поскольку на ней нельзя изменять данные. Песочница позволяет обойти это ограничение.

Page 21: Песочница

Переключим AQO на песочницу в текущем сеансе:

=> SET aqo.sandbox = on;

SET

=> SET aqo.advanced = off;

SET

=> SET aqo.mode = learn;

SET

Сбросим статистику песочницы, не затрагивая основную базу знаний:

=> SELECT aqo_reset();

 aqo_reset
-----------
         0
(1 row)

Выполняем запрос, пока оценка кардинальности не станет удовлетворительной:

=> EXPLAIN (analyze) SELECT * FROM ticket_flights
WHERE fare_conditions = 'Business' AND amount > 50000;

                                                      QUERY PLAN
------------------------------------------------------------------------------------------
----------------------------
 Seq Scan on ticket_flights  (cost=0.00..24525.89 rows=7605 width=32) (actual
time=0.504..263.187 rows=30133 loops=1)
   AQO not used, fss=8156007843730226217
   Filter: ((amount > '50000'::numeric) AND ((fare_conditions)::text = 'Business'::text))
   Rows Removed by Filter: 1015593
 Planning Time: 0.194 ms
 Execution Time: 264.801 ms
 Using aqo: true
 AQO mode: LEARN
 AQO advanced: OFF
 Query hash: 5235755627948238838
 JOINS: 0
(11 rows)

=> EXPLAIN (analyze) SELECT * FROM ticket_flights
WHERE fare_conditions = 'Business' AND amount > 50000;

                                                      QUERY PLAN
------------------------------------------------------------------------------------------
-----------------------------
 Seq Scan on ticket_flights  (cost=0.00..24525.89 rows=30133 width=32) (actual
time=0.185..266.773 rows=30133 loops=1)
   AQO: rows=30133, error=0%, fss=8156007843730226217
   Filter: ((amount > '50000'::numeric) AND ((fare_conditions)::text = 'Business'::text))
   Rows Removed by Filter: 1015593
 Planning Time: 0.314 ms
 Execution Time: 268.548 ms
 Using aqo: true
 AQO mode: LEARN
 AQO advanced: OFF
 Query hash: 5235755627948238838
 JOINS: 0
(11 rows)

Теперь мы можем скопировать статистику из песочницы в таблицу:

=> CREATE TABLE aqo_data_sandbox AS SELECT * FROM aqo_data;

SELECT 1

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

Page 22: Page 22

=> SET aqo.sandbox = off;

SET

=> SELECT aqo_reset();

 aqo_reset
-----------
         7
(1 row)

=> SELECT aqo_data_update(fs, fss, dbid, nfeatures, features, targets, reliability, oids, tmpoids)
FROM aqo_data_sandbox WHERE fs = 0;

 aqo_data_update
-----------------
 t
(1 row)

=> DROP TABLE aqo_data_sandbox;

DROP TABLE

Запрос сразу же использует AQO:

=> EXPLAIN (analyze) SELECT * FROM ticket_flights
WHERE fare_conditions = 'Business' AND amount > 50000;

                                                      QUERY PLAN
------------------------------------------------------------------------------------------
-----------------------------
 Seq Scan on ticket_flights  (cost=0.00..24525.89 rows=30133 width=32) (actual
time=0.365..255.763 rows=30133 loops=1)
   AQO: rows=30133, error=0%, fss=8156007843730226217
   Filter: ((amount > '50000'::numeric) AND ((fare_conditions)::text = 'Business'::text))
   Rows Removed by Filter: 1015593
 Planning Time: 0.256 ms
 Execution Time: 257.376 ms
 Using aqo: true
 AQO mode: LEARN
 AQO advanced: OFF
 Query hash: 5235755627948238838
 JOINS: 0
(11 rows)

Page 23: Итоги

Адаптивная оптимизация запросов AQO — инструмент для улучшения планов методом машинного обучения

Автоматическое исправление оценок кардинальности, которого сложно достичь традиционными методами
Несколько режимов работы и возможность тонкой настройки на уровне отдельных классов запросов

Page 24: Практика

В базе данных demo создайте индекс для таблицы flights по столбцу scheduled_departure и для ticket_flights по столбцу fare_conditions.

1. Отключите параллельное выполнение, увеличьте размер рабочей памяти до 256 Мбайт и выполните запрос для получения количества пассажиров, летавших бизнес-классом с 01.08.2016 и прибывших с опозданием не более часа.
1. Оптимизируйте запрос с помощью AQO.

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

Используйте следующий запрос:

SELECT t.ticket_no
FROM flights f
  JOIN ticket_flights tf ON f.flight_id = tf.flight_id
  JOIN tickets t ON tf.ticket_no = t.ticket_no
WHERE f.scheduled_departure > '2016-08-01'::timestamptz
  AND f.actual_arrival < f.scheduled_arrival + interval '1 hour'
  AND tf.fare_conditions = 'Business';

Сбросьте статистику AQO и исследуйте план запроса в режиме обучения (learn).

Page 25: 1. Индексы

=> \c demo

You are now connected to database "demo" as user "student".

Чтобы у планировщика PostgreSQL было больше вариантов соединения таблиц, создадим два индекса.

=> CREATE INDEX flights_sd_idx ON flights (scheduled_departure);

CREATE INDEX

=> CREATE INDEX ticket_flights_fc_idx ON ticket_flights (fare_conditions);

CREATE INDEX

2. Запрос с обычным планированием

Отключим параллельную обработку и увеличим work_mem, чтобы вся работа происходила в оперативной памяти.

=> ALTER SYSTEM SET max_parallel_workers_per_gather = 0;

ALTER SYSTEM

=> ALTER SYSTEM SET work_mem = '256MB';

ALTER SYSTEM

=> SELECT pg_reload_conf();

 pg_reload_conf
----------------
 t
(1 row)

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

=> EXPLAIN (analyze, buffers, timing off)
SELECT t.ticket_no
FROM flights f
  JOIN ticket_flights tf ON f.flight_id = tf.flight_id
  JOIN tickets t ON tf.ticket_no = t.ticket_no
WHERE f.scheduled_departure > '2016-08-01'::timestamptz
  AND f.actual_arrival < f.scheduled_arrival + interval '1 hour'
  AND tf.fare_conditions = 'Business';

Page 26: Page 26

                                                                                    QUERY
PLAN
------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------
 Hash Join  (cost=13223.28..24799.72 rows=35786 width=14) (actual rows=56111 loops=1)
   Hash Cond: (t.ticket_no = tf.ticket_no)
   Buffers: shared hit=406 read=13628
   ->  Seq Scan on tickets t  (cost=0.00..9843.33 rows=366733 width=14) (actual
rows=366733 loops=1)
         Buffers: shared read=6176
   ->  Hash  (cost=12775.96..12775.96 rows=35786 width=14) (actual rows=56111 loops=1)
         Buckets: 65536  Batches: 1  Memory Usage: 3033kB
         Buffers: shared hit=406 read=7452
         ->  Hash Join  (cost=2312.09..12775.96 rows=35786 width=14) (actual rows=56111
loops=1)
               Hash Cond: (tf.flight_id = f.flight_id)
               Buffers: shared hit=406 read=7452
               ->  Bitmap Heap Scan on ticket_flights tf  (cost=1200.47..11382.49
rows=107361 width=18) (actual rows=107642 loops=1)
                     Recheck Cond: ((fare_conditions)::text = 'Business'::text)
                     Heap Blocks: exact=7369
                     Buffers: shared hit=12 read=7452
                     ->  Bitmap Index Scan on ticket_flights_fc_idx  (cost=0.00..1173.63
rows=107361 width=0) (actual rows=107642 loops=1)
                           Index Cond: ((fare_conditions)::text = 'Business'::text)
                           Buffers: shared read=95
               ->  Hash  (cost=973.62..973.62 rows=11040 width=4) (actual rows=15918
loops=1)
                     Buckets: 16384  Batches: 1  Memory Usage: 688kB
                     Buffers: shared hit=394
                     ->  Seq Scan on flights f  (cost=0.00..973.62 rows=11040 width=4)
(actual rows=15918 loops=1)
                           Filter: ((scheduled_departure > '2016-08-01
00:00:00+03'::timestamp with time zone) AND (actual_arrival < (scheduled_arrival +
'01:00:00'::interval)))
                           Rows Removed by Filter: 17203
                           Buffers: shared hit=394
 Planning:
   Buffers: shared hit=299 read=47
 Planning Time: 613.970 ms
 Execution Time: 319.226 ms
(29 rows)

Имеется несоответствие по количествам планируемых и реально полученных строк.

3. AQO

Чтобы заработал AQO, нужно загрузить библиотеку:

=> ALTER SYSTEM SET shared_preload_libraries = 'aqo';

ALTER SYSTEM

=> \q

student$ sudo systemctl restart postgrespro-ent-16.service

Установим расширение:

student$ psql demo

=> CREATE EXTENSION aqo;

CREATE EXTENSION

Режим по умолчанию — learn:

=> SHOW aqo.mode;

 aqo.mode
----------
 learn
(1 row)

Сбросим предыдущую статистику AQO:

=> SELECT aqo_reset();

Page 27: Page 27

 aqo_reset
-----------
         0
(1 row)

Включим AQO:

=> SET aqo.enable = on;

SET

Выполним запрос и исследуем его план:

=> EXPLAIN (analyze, buffers, timing off)
SELECT t.ticket_no
FROM flights f
  JOIN ticket_flights tf ON f.flight_id = tf.flight_id
  JOIN tickets t ON tf.ticket_no = t.ticket_no
WHERE f.scheduled_departure > '2016-08-01'::timestamptz
  AND f.actual_arrival < f.scheduled_arrival + interval '1 hour'
  AND tf.fare_conditions = 'Business';

                                                                                    QUERY
PLAN
------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------
 Hash Join  (cost=13223.28..24799.72 rows=35786 width=14) (actual rows=56111 loops=1)
   AQO not used, fss=-1169867161196126870
   Hash Cond: (t.ticket_no = tf.ticket_no)
   Buffers: shared read=14034
   ->  Seq Scan on tickets t  (cost=0.00..9843.33 rows=366733 width=14) (actual
rows=366733 loops=1)
         AQO not used, fss=-782071859942470067
         Buffers: shared read=6176
   ->  Hash  (cost=12775.96..12775.96 rows=35786 width=14) (actual rows=56111 loops=1)
         Buckets: 65536  Batches: 1  Memory Usage: 3033kB
         Buffers: shared read=7858
         ->  Hash Join  (cost=2312.09..12775.96 rows=35786 width=14) (actual rows=56111
loops=1)
               AQO not used, fss=-1710832237659048278
               Hash Cond: (tf.flight_id = f.flight_id)
               Buffers: shared read=7858
               ->  Bitmap Heap Scan on ticket_flights tf  (cost=1200.47..11382.49
rows=107361 width=18) (actual rows=107642 loops=1)
                     AQO not used, fss=4723136667746782336
                     Recheck Cond: ((fare_conditions)::text = 'Business'::text)
                     Heap Blocks: exact=7369
                     Buffers: shared read=7464
                     ->  Bitmap Index Scan on ticket_flights_fc_idx  (cost=0.00..1173.63
rows=107361 width=0) (actual rows=107642 loops=1)
                           Index Cond: ((fare_conditions)::text = 'Business'::text)
                           Buffers: shared read=95
               ->  Hash  (cost=973.62..973.62 rows=11040 width=4) (actual rows=15918
loops=1)
                     Buckets: 16384  Batches: 1  Memory Usage: 688kB
                     Buffers: shared read=394
                     ->  Seq Scan on flights f  (cost=0.00..973.62 rows=11040 width=4)
(actual rows=15918 loops=1)
                           AQO not used, fss=-1465870163991728921
                           Filter: ((scheduled_departure > '2016-08-01
00:00:00+03'::timestamp with time zone) AND (actual_arrival < (scheduled_arrival +
'01:00:00'::interval)))
                           Rows Removed by Filter: 17203
                           Buffers: shared read=394
 Planning:
   Buffers: shared hit=306 read=52
 Planning Time: 3.450 ms
 Execution Time: 299.720 ms
 Using aqo: true
 AQO mode: LEARN
 AQO advanced: OFF
 Query hash: 5090759953117466762
 JOINS: 2
(39 rows)

AQO не оптимизирует запрос, если встречает его впервые. Повторим запрос:

Page 28: Page 28

=> EXPLAIN (analyze, buffers, timing off)
SELECT t.ticket_no
FROM flights f
  JOIN ticket_flights tf ON f.flight_id = tf.flight_id
  JOIN tickets t ON tf.ticket_no = t.ticket_no
WHERE f.scheduled_departure > '2016-08-01'::timestamptz
  AND f.actual_arrival < f.scheduled_arrival + interval '1 hour'
  AND tf.fare_conditions = 'Business';

                                                                                    QUERY
PLAN
------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------
 Hash Join  (cost=13539.13..25777.23 rows=56111 width=14) (actual rows=56111 loops=1)
   AQO: rows=56111, error=0%, fss=-1169867161196126870
   Hash Cond: (t.ticket_no = tf.ticket_no)
   Buffers: shared hit=7890 read=6144
   ->  Seq Scan on tickets t  (cost=0.00..9843.33 rows=366733 width=14) (actual
rows=366733 loops=1)
         AQO: rows=366733, error=0%, fss=-782071859942470067
         Buffers: shared hit=32 read=6144
   ->  Hash  (cost=12837.74..12837.74 rows=56111 width=14) (actual rows=56111 loops=1)
         Buckets: 65536  Batches: 1  Memory Usage: 3033kB
         Buffers: shared hit=7858
         ->  Hash Join  (cost=2373.14..12837.74 rows=56111 width=14) (actual rows=56111
loops=1)
               AQO: rows=56111, error=0%, fss=-1710832237659048278
               Hash Cond: (tf.flight_id = f.flight_id)
               Buffers: shared hit=7858
               ->  Bitmap Heap Scan on ticket_flights tf  (cost=1200.54..11382.56
rows=107642 width=18) (actual rows=107642 loops=1)
                     AQO: rows=107642, error=0%, fss=4723136667746782336
                     Recheck Cond: ((fare_conditions)::text = 'Business'::text)
                     Heap Blocks: exact=7369
                     Buffers: shared hit=7464
                     ->  Bitmap Index Scan on ticket_flights_fc_idx  (cost=0.00..1173.63
rows=107361 width=0) (actual rows=107642 loops=1)
                           Index Cond: ((fare_conditions)::text = 'Business'::text)
                           Buffers: shared hit=95
               ->  Hash  (cost=973.62..973.62 rows=15918 width=4) (actual rows=15918
loops=1)
                     Buckets: 16384  Batches: 1  Memory Usage: 688kB
                     Buffers: shared hit=394
                     ->  Seq Scan on flights f  (cost=0.00..973.62 rows=15918 width=4)
(actual rows=15918 loops=1)
                           AQO: rows=15918, error=0%, fss=-1465870163991728921
                           Filter: ((scheduled_departure > '2016-08-01
00:00:00+03'::timestamp with time zone) AND (actual_arrival < (scheduled_arrival +
'01:00:00'::interval)))
                           Rows Removed by Filter: 17203
                           Buffers: shared hit=394
 Planning:
   Buffers: shared hit=27
 Planning Time: 0.795 ms
 Execution Time: 244.128 ms
 Using aqo: true
 AQO mode: LEARN
 AQO advanced: OFF
 Query hash: 5090759953117466762
 JOINS: 2
(39 rows)

Теперь количества планируемых и реально полученных строк стали одинаковыми.

Уборка

Удалим индексы и расширение, созданные в базе demo и сбросим настройки параметров конфигурации.

=> DROP INDEX flights_sd_idx;

DROP INDEX

=> DROP INDEX ticket_flights_fc_idx;

DROP INDEX

=> DROP EXTENSION aqo;

DROP EXTENSION

Page 29: Page 29

=> ALTER SYSTEM RESET ALL;

ALTER SYSTEM

student$ sudo systemctl restart postgrespro-ent-16.service