Postgres Pro Enterprise : Документация: 17: F.2. aqo — оптимизация запросов по стоимости выполнения : Компания Postgres Professional

RU
EN
RU EN

F.2. aqo — оптимизация запросов по стоимости выполнения
Пред.	Наверх	Приложение F. Дополнительно поставляемые модули и расширения, поставляемые в `postgrespro-ent-17-contrib`	Начало	След.

F.2. aqo — оптимизация запросов по стоимости выполнения #

F.2.1. Описание
F.2.2. Установка и подготовка
F.2.3. Использование
F.2.4. Справка
F.2.5. Примеры
F.2.6. Автор

Модуль aqo представляет собой расширение Postgres Pro Enterprise для оптимизации запросов по стоимости выполнения. Используя методы машинного обучения, а точнее модификацию алгоритма k-NN, aqo улучшает оценку количества строк, что может способствовать выбору лучшего плана и, как следствие, ускорению запросов.

F.2.1. Описание #

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

Модуль aqo сохраняет все данные обучения (aqo_data), запросы (aqo_query_texts), параметры запросов (aqo_queries) и статистику выполнения запросов (aqo_query_stat) в файлах. При запуске aqo эти данные загружаются в разделяемую память. Вы можете обращаться к данным aqo, используя функции и представления.

При включённом параметре aqo.advanced и запуске aqo в режиме intelligent или learn каждому классу запросов для его идентификации и разделения собранной статистики присваивается уникальное хеш-значение, вычисляемое на основе дерева запросов. Если aqo.advanced отключён, статистика для всех неотслеживаемых классов запросов хранится в общем классе запросов с хешем 0.

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

Параметры оптимизации для каждого запроса хранятся в представлении aqo_queries.

F.2.1.1. Ограничения #

В настоящее время расширение aqo имеет следующие ограничения:

Оптимизация запросов с использованием aqo не поддерживается на ведомых серверах.
Оптимизация запросов с использованием aqo не поддерживается для запросов, обращающихся только к временным объектам.
Оптимизация запросов с использованием aqo не поддерживается для запросов, содержащих функции IMMUTABLE.
Модуль aqo не собирает статистику по репликам, поскольку они доступны только для чтения. Однако он может использовать статистику выполнения запросов с ведущего сервера при работе с физической репликой.
Режимы learn и intelligent не должны работать на уровне кластера с запросами, имеющими динамически генерируемую структуру, поскольку в этих режимах сохраняются все идентификаторы классов запросов, которые различны для всех таких запросов. Тем не менее могут использоваться динамически генерируемые константы.

F.2.2. Установка и подготовка #

Расширение aqo включено в состав Postgres Pro Enterprise. Установив Postgres Pro Enterprise, выполните следующие действия, чтобы подготовить aqo к работе:

Добавьте aqo в параметр shared_preload_libraries в файле postgresql.conf:
```
shared_preload_libraries = 'aqo'
```
Библиотеку aqo нужно предварительно загрузить при запуске сервера, так как адаптивная оптимизация запросов должна быть включена для всего кластера.
Создайте расширение aqo, выполнив следующий запрос:
```
CREATE EXTENSION aqo;
```

Когда расширение будет создано, вы можете приступить к оптимизации запросов.

Чтобы отключить aqo в текущей базе данных, выполните:

DROP EXTENSION aqo;

Чтобы отключить aqo на уровне кластера, выполните следующее:

ALTER SYSTEM SET aqo.enable = off;
SELECT pg_reload_conf();

Чтобы удалить все данные из хранилища aqo, включая собранную статистику, вызовите функцию aqo_reset(). Чтобы удалить данные из текущей базы данных, выполните

SELECT aqo_reset();

Чтобы удалить все данные из хранилища aqo, выполните

SELECT aqo_reset(NULL);

Чтобы расширение aqo не загружалось при перезапуске сервера, удалите строку

shared_preload_libraries = 'aqo'

из файла postgresql.conf.

Важно

Чтобы избежать ошибок во время физической репликации при переносе данных aqo с ведущего сервера на реплику, убедитесь, что на серверах установлены одинаковые версии модуля. Если установлены разные версии aqo, необходимо задать значение off для параметра aqo.wal_rw на обоих серверах, однако в таком случае репликация выполняться не будет.

F.2.2.1. Конфигурирование #

Поведение aqo в основном регулируется параметрами конфигурации aqo.enable, aqo.mode и aqo.advanced. Значения этих параметров по умолчанию позволяют начать обучение aqo в основном режиме, как только для параметра aqo.enable устанавливается значение on.

Чтобы динамически изменить в текущем сеансе любой из этих параметров, например режим, выполните следующую команду:

SET aqo.mode = 'mode';

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

F.2.3. Использование #

F.2.3.1. Использование основного режима aqo #

По умолчанию параметр aqo.advanced отключён. При этом устанавливается рекомендуемый основной режим, в котором статистика собирается для узлов плана (определяемых fss), а собранные данные машинного обучения используются для исправления погрешностей оценки количества строк для всех запросов, план которых содержит определённый узел плана. Как только для параметра aqo.enable устанавливается значение on, aqo начинает обучение. Следует несколько раз выполнить запросы, которые необходимо оптимизировать, пока план не станет достаточно хорошим, и изменить значение параметра aqo.mode на frozen. Чтобы применить данные машинного обучения на уровне экземпляра сервера, выполните следующие команды:

ALTER SYSTEM SET aqo.mode = frozen;
ALTER SYSTEM SET aqo.enable = on;
SELECT pg_reload_conf();

Данные машинного обучения будут применяться не только к запросам, на которых обучался модуль aqo, но также ко всем запросам с планом, содержащим узлы, по которым собиралась статистика. Чтобы данные машинного обучения не влияли на другие запросы, установите для параметра aqo.advanced значение on, и статистика будет собираться для отдельных запросов. За подробностями обратитесь к Подразделу F.2.3.2.

F.2.3.2. Выбор режима работы для расширенной оптимизации запросов #

Если часто выполняются однотипные запросы, например, в приложении ограничено число возможных классов запросов, можно включить параметр aqo.advanced и использовать интеллектуальный режим (intelligent) для улучшения планирования таких запросов. В этом режиме aqo анализирует выполнение каждого запроса и собирает статистику. При этом статистика по разным классам запросов собирается отдельно. Если производительность не увеличивается после 50 итераций, расширение aqo отключается, и планирование выполняется стандартным планировщиком запросов.

Примечание

Можно посмотреть текущий план запроса, воспользовавшись стандартной командой Postgres Pro EXPLAIN с указанием ANALYZE. За подробностями обратитесь к Разделу 14.1.

Так как в режиме intelligent различные классы запросов анализируются отдельно, aqo может не улучшить производительность, если запросы в рабочей нагрузке относятся к нескольким разным классам или постоянно меняются. Для такого профиля нагрузки стоит перевести aqo в режим controlled или попробовать отключить параметр aqo.advanced.

Когда включён параметр aqo.advanced, в режиме controlled расширение aqo не собирает статистику для новых классов запросов, поэтому они не будут оптимизированы, но для известных классов запросов aqo продолжит собирать статистику и использовать оптимизированные алгоритмы планирования. Поэтому следует использовать режим controlled только после обучения aqo в режиме learn или intelligent.

После того, как aqo прошло обучение, рекомендуется использовать режим controlled для рабочей среды. Чтобы aqo работало в этом режиме на уровне всего производственного кластера, выполните

ALTER SYSTEM SET aqo.mode = 'controlled';
SELECT pg_reload_conf();

При включённом параметре aqo.advanced расширение в режиме learn собирает статистику по всем выполненным запросам и обновляет данные для классов запросов. Этот режим аналогичен режиму intelligent, за исключением возможности интеллектуальной настройки. Его не рекомендуется использовать постоянно для всего кластера, поскольку он пытается оптимизировать aqo для каждого класса запросов, даже для тех, которым это не нужно, и может привести к ненужным вычислительным издержкам и снижению производительности.

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

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

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

F.2.3.3. Тонкая настройка aqo #

Для обращения к представлениям aqo и изменения расширенных свойств запросов необходимо иметь права суперпользователя.

Все обработанные классы запросов и соответствующие хеш-значения можно увидеть в представлении aqo_query_texts:

SELECT * FROM aqo_query_texts;

Чтобы узнать класс (то есть хеш) запроса и режим aqo, включите переменные среды aqo.show_hash (boolean) (boolean) и aqo.show_details (boolean) (boolean) и выполните запрос. Вывод будет примерно следующим:

...
Planning Time: 23.538 ms
...
Execution Time: 249813.875 ms
...
Using aqo: true
AQO mode: LEARN
AQO advanced: OFF
...
Query hash: -2439501042637610315

Разные классы запросов имеют собственные свойства оптимизации. Эти свойства отображаются в представлении aqo_queries:

SELECT * FROM aqo_queries;

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

 -- Добавление нового класса запросов в представление aqo_queries:

SET aqo.advanced='on';
SET aqo.mode='intelligent';
SELECT * FROM a, b WHERE a.id=b.id;
SET aqo.mode='controlled';

 -- Отключение автонастройки, включение learn_aqo и use_aqo 
 -- для данного класса запросов:

SELECT count(*) FROM aqo_queries,
  LATERAL aqo_queries_update(queryid, NULL, NULL, true, true, false)
  WHERE queryid = (SELECT queryid FROM aqo_query_texts 
  WHERE query_text LIKE 'SELECT * FROM a, b WHERE a.id=b.id;');

 -- Запуск EXPLAIN ANALYZE и наблюдение изменённого плана:

EXPLAIN ANALYZE SELECT * FROM a, b WHERE a.id=b.id;
EXPLAIN ANALYZE SELECT * FROM a, b WHERE a.id=b.id;

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

SELECT count(*) FROM aqo_queries,
  LATERAL aqo_queries_update(queryid, NULL, NULL, false, true, false)
  WHERE queryid = (SELECT queryid FROM aqo_query_texts 
  WHERE query_text LIKE 'SELECT * FROM a, b WHERE a.id=b.id;');

Чтобы предотвратить интеллектуальную настройку для определённого класса запросов, отключите свойство auto_tuning:

SELECT count(*) FROM aqo_queries,
  LATERAL aqo_queries_update(queryid, NULL, NULL, NULL, NULL, false)
  WHERE queryid = 'hash';

Здесь хеш — это значение хеша для данного класса запросов. В результате aqo не будет автоматически менять свойства learn_aqo и use_aqo.

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

SELECT count(*) FROM aqo_queries,
  LATERAL aqo_queries_update(queryid, NULL, NULL, false, NULL, false)
  WHERE queryid = 'hash';

Здесь хеш — это значение хеша для данного класса запросов.

Чтобы полностью отключить aqo для всех запросов и использовать стандартный планировщик Postgres Pro, выполните:

SELECT count(*) FROM aqo_queries,
  LATERAL aqo_disable_class(queryid, NULL)
  WHERE queryid <> 0;

Чтобы временно отключить aqo для всех запросов в текущем сеансе или на уровне всего кластера, но не удалять и не изменять собранную статистику и параметры, отключите параметр aqo.enable, как указано ниже:

    SET aqo.enable = 'off';

или

    ALTER SYSTEM SET aqo.enable = 'off'

F.2.3.4. Режим «песочницы» #

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

SET aqo.sandbox = ON;

Она включит режим «песочницы», в котором aqo будет работать в изолированной среде. Однако если включить aqo.sandbox в разных сеансах SQL, они будут использовать одни и те же данные.

Данные, полученные в режиме «песочницы», не реплицируются. Но режим «песочницы» можно использовать на ведомом сервере. Более того, единственный способ обучить aqo на ведомом сервере — включить режим «песочницы» при включённой репликации, то есть при значении true для aqo.wal_rw. Без режима «песочницы» aqo будет работать на ведомом сервере так, как будто aqo.mode = FROZEN, то есть сможет использовать существующую базу знаний, но не сможет её обновлять или расширять.

F.2.4. Справка #

F.2.4.1. Параметры конфигурации #

aqo.enable (boolean) #

Определяет состояние расширения aqo. Если установлено значение off, aqo не работает, за исключением случаев, когда параметр aqo.force_collect_stat = on.

По умолчанию: off (выкл.).

aqo.mode (text) #

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

intelligent — расширение сохраняет новые запросы с включённым auto_tuning. За дополнительными сведениями обратитесь к описанию представления aqo_queries. В данном режиме aqo может отключиться для запроса в случае снижения средней производительности. Этот режим работает таким образом, только если параметр aqo.advanced = on, в противном случае работает аналогично режиму learn.
learn — расширение собирает статистику по всем выполненным запросам, обучается и делает предсказания на основе этой статистики.
controlled — расширение только обучается и делает предсказания для известных запросов.
frozen — расширение делает предсказания для известных запросов, но не обучается ни на каких запросах.

По умолчанию: learn.

aqo.advanced (boolean) #

Включает расширенную процедуру обучения, которая сохраняет статистику обучения отдельно для каждого класса запроса. Также позволяет настраивать параметры use_aqo и learn_aqo в представлении aqo_queries. После тонкой настройки параметры запроса в представлении aqo_query продолжают работать при выключенном параметре aqo.advanced.

По умолчанию: off (выкл.).

aqo.force_collect_stat (boolean) #

Собирать статистику выполнения запросов во всех режимах aqo даже при aqo.enable = off.

По умолчанию: off (выкл.).

aqo.show_details (boolean) #

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

По умолчанию: on (вкл.).

aqo.show_hash (boolean) #

Показывать хеш-значение, однозначно идентифицирующее класс запросов или класс узлов плана. Расширение aqo использует в качестве идентификатора класса запроса идентификатор самого Postgres Pro, чтобы обеспечить согласованность с другими расширениями, такими как pg_stat_statements. Таким образом, идентификатор запроса можно получить из поля Query hash в выводе команды EXPLAIN ANALYZE.

По умолчанию: on (вкл.).

aqo.join_threshold (integer) #

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

По умолчанию: 0 (запросы не игнорируются).

aqo.learn_statement_timeout (boolean) #

Обучаться на планах запросов, прерванных по тайм-ауту оператора.

По умолчанию: off (выкл.).

aqo.statement_timeout (integer) #

Определяет начальное значение так называемого «умного» тайм-аута операторов в миллисекундах, который необходим для ограничения времени выполнения при ручном обучении aqo на специальных запросах с неудовлетворительным прогнозом количества строк. Расширение aqo может динамически изменять умный тайм-аут операторов во время этого обучения. Когда погрешность оценки количества строк на узлах превышает 0.1, значение aqo.statement_timeout автоматически увеличивается экспоненциально, но не превышает statement_timeout.

По умолчанию: 0.

aqo.wide_search (boolean) #

Включает поиск соседей с одним и тем же подпространством признаков среди разных классов запросов. Работает, только если параметр aqo.advanced = on.

По умолчанию: off (выкл.).

aqo.min_neighbors_for_predicting (integer) #

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

По умолчанию: 3.

aqo.predict_with_few_neighbors (boolean) #

Позволяет aqo делать предсказания с меньшим количеством соседей, чем указано в параметре aqo.min_neighbors_for_predicting. Если установлено значение off, aqo обучается, но не делает предсказания до тех пор, пока счётчик выполнений запроса с разными константами не достигнет 3 (по умолчанию для aqo.min_neighbors_for_predicting).

По умолчанию: on (вкл.).

aqo.fs_max_items (integer) #

Определяет максимальное количество пространств признаков, с которыми может работать aqo. При превышении этого количества обучение на новых классах запросов прекратится, и они не будут отображаться в представлениях. Задать этот параметр можно только при запуске сервера.

По умолчанию: 10000.

aqo.fss_max_items (integer) #

Определяет максимальное количество подпространств признаков, с которыми может работать aqo. При превышении этого количества данные об избирательности и предсказание количества строк для новых узлов плана запроса больше не будут собираться и новые подпространства признаков не будут отображаться в представлении aqo_data. Задать этот параметр можно только при запуске сервера.

По умолчанию: 100000.

aqo.querytext_max_size (integer) #

Определяет максимальный размер запроса в представлении aqo_query_texts. Задать этот параметр можно только при запуске сервера.

По умолчанию: 1000.

aqo.dsm_size_max (integer) #

Определяет максимальный размер динамической разделяемой памяти в мегабайтах, которую модуль aqo может выделить для хранения данных обучения и текстов запросов. Если установленное значение меньше объёма сохранённых данных aqo, сервер не запустится. Задать этот параметр можно только при запуске сервера.

По умолчанию: 100.

aqo.wal_rw (boolean) #

Включает физическую репликацию и обеспечивает полное восстановление данных aqo после сбоя. При значении off на ведущем сервере данные на реплику не передаются. При значении off на реплике данные, передаваемые с ведущего сервера, игнорируются. В таком случае при сбое сервера данные могут быть восстановлены только до последней контрольной точки. Этот параметр можно задать только при запуске сервера.

По умолчанию: on (вкл.).

aqo.sandbox (boolean) #

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

По умолчанию: off (выкл.).

aqo.delta_rows (boolean) #

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

По умолчанию: off (выкл.).

F.2.4.2. Представления #

F.2.4.2.1. `aqo_query_texts` #

В представлении aqo_query_texts классифицируются все классы запросов, обрабатываемые aqo. Для каждого класса запросов в представлении отображается текст первого проанализированного запроса этого класса.

Таблица F.1. Представление aqo_query_texts

Имя столбца	Описание
`queryid`	Уникальный идентификатор класса запросов.
`dbid`	Идентификатор базы данных.
`query_text`	Текст первого проанализированного запроса данного класса. Длина текста запроса ограничивается параметром aqo.querytext_max_size.

F.2.4.2.2. `aqo_queries` #

В представлении aqo_queries отображаются свойства оптимизации для разных классов запросов. Один запрос, выполненный в двух разных базах данных, сохраняется дважды с одинаковым идентификатором запроса (queryid).

Таблица F.2. Представление aqo_queries

Свойство	Описание
`queryid`	Уникальный идентификатор класса запросов.
`dbid`	Идентификатор базы данных, в которой выполнялся запрос.
`fs`	Уникальный идентификатор (хеш) пространства признаков, в котором собирается статистика для данного класса запросов. По умолчанию используется `queryid`. Можно вручную установить одно и то же значение `fs` для разных классов запросов, особенно если они похожи.
`learn_aqo`	Показывает, включён ли сбор статистики для данного класса запросов.
`use_aqo`	Показывает, включено ли предсказание количества строк средствами aqo для следующего выполнения данного класса запросов.
`auto_tuning`	Показывает, может ли aqo динамически изменять параметры `use_aqo` и `learn_aqo` для этого класса запросов. По умолчанию для новых запросов устанавливается значение `true`, если включён параметр aqo.advanced и режим aqo.mode = `intelligent`. При включённом свойстве `auto_tuning`, если для нескольких последовательных выполнений запроса с выключенным `use_aqo` ошибка оценки количества строк остаётся достаточно малой и стабильной, aqo включает `use_aqo`. Запросы с `learn_aqo`=true (все новые запросы) несколько раз выполняются с использованием aqo и без него, со стандартным планировщиком. Чем быстрее запрос выполняется с aqo, тем вероятнее, что модуль будет использоваться для следующих запросов. Если после нескольких выполнений окажется, что запросы с использованием aqo выполняются дольше, чем с использованием стандартного планировщика, aqo больше не будет использоваться для этого класса запросов: для `auto_tuning`, `use_aqo` и `learn_aqo` устанавливается значение `off`.
`smart_timeout`	Значение «умного» тайм-аута операторов для данного класса запросов. Начальное значение такого тайм-аута для любого запроса определяется параметром конфигурации statement_timeout.
`count_increase_timeout`	Показывает, сколько раз увеличивался «умный» тайм-аут операторов для данного класса запросов.

F.2.4.2.3. `aqo_data` #

В представлении aqo_data отображаются данные машинного обучения для уточнения оценки количества строк. Количество строк ограничено параметром aqo.fss_max_items. Чтобы сбросить всю собранную статистику для определённого класса запросов, можно удалить из представления aqo_data все строки с соответствующим fs.

Таблица F.3. Представление aqo_data

Данные	Описание
`fs`	Идентификатор (хеш) пространства признаков.
`fss`	Идентификатор (хеш) подпространства признаков.
`dbid`	Идентификатор базы данных.
`delta_rows`	Если true, aqo делает предсказания на основе оценки планировщика; в противном случае `false`.
`nfeatures`	Размер подпространства признаков для узла плана запроса.
`features`	Логарифм избирательности, на котором основано предсказание количества строк.
`targets`	Логарифм количества строк для узла плана запроса.
`reliability`	Уровень достоверности статистики обучения: 1 (по умолчанию) — указывает, что данные получены после нормального выполнения запроса 0,1 — указывает, что данные получены от частично выполненного узла (они не нужны, так как ненадёжны) 0,9 — указывает, что данные получены от готового узла, но от частично выполненного оператора
`oids`	Список идентификаторов таблиц, которые участвовали в предсказании для этого узла.

F.2.4.2.4. `aqo_query_stat` #

В представлении aqo_query_stat отображается статистика выполнения запросов, группируемая по классам запросов. Расширение aqo использует эти данные, когда для определённого класса запросов включено свойство auto_tuning.

Таблица F.4. Представление aqo_query_stat

Данные	Описание
`queryid`	Уникальный идентификатор класса запросов.
`dbid`	Идентификатор базы данных.
`execution_time_with_aqo`	Массив значений времени выполнения запросов со включённым aqo.
`execution_time_without_aqo`	Массив значений времени выполнения запросов с отключённым aqo.
`planning_time_with_aqo`	Массив значений времени планирования запросов со включённым aqo.
`planning_time_without_aqo`	Массив значений времени планирования запросов с отключённым aqo.
`cardinality_error_with_aqo`	Массив ошибок оценки количества строк со включённым aqo в планах выбранных запросов.
`cardinality_error_without_aqo`	Массив ошибок оценки количества строк с отключённым aqo в планах выбранных запросов.
`executions_with_aqo`	Число запросов, выполненных со включённым aqo.
`executions_without_aqo`	Число запросов, выполненных с отключённым aqo.

F.2.4.3. Функции #

Модуль aqo добавляет несколько функций в каталог Postgres Pro.

F.2.4.3.1. Функции управления хранилищем #

Важно

Функции aqo_queries_update, aqo_query_texts_update, aqo_query_stat_update, aqo_data_update и aqo_data_delete изменяют файлы данных, на которых основаны соответствующие представления aqo. Поэтому вызывайте эти функции только в том случае, если вы понимаете логику адаптивной оптимизации запросов.

aqo_cleanup() → setof integer: Удаляет данные, относящиеся к классам запросов, которые связаны (возможно частично) с удалёнными отношениями. Возвращает количество удалённых пространств признаков (классов) и подпространств признаков. Игнорирует удаление других объектов.
aqo_enable_class (queryid bigint, dbid oid) → void: Устанавливает для learn_aqo, use_aqo и auto_tuning (только в режиме intelligent) значение true для класса запросов с указанными queryid и dbid. Можно использовать для dbid значение NULL вместо идентификатора текущей базы данных.
aqo_disable_class (queryid bigint, dbid oid) → void: Устанавливает для learn_aqo, use_aqo и auto_tuning значение false для класса запросов с указанными queryid и dbid. Можно использовать для dbid значение NULL вместо идентификатора текущей базы данных.
aqo_drop_class (queryid bigint, dbid oid) → integer: Удаляет все данные, относящиеся к заданному классу запросов и базе данных, из хранилища aqo. Для параметра dbid можно задать значение NULL вместо идентификатора текущей базы данных. Возвращает количество записей, удалённых из хранилища aqo.
aqo_reset (dbid oid) → bigint: Удаляет записи из указанной базы данных: данные машинного обучения, тексты запросов, статистику и свойства классов запросов. Если параметр dbid не указан, данные удаляются из текущей базы данных. Если dbid имеет значение NULL, удаляются все записи из хранилища aqo. Возвращает количество удалённых записей.
aqo_queries_update (queryid bigint, dbid oid, fs bigint, learn_aqo boolean, use_aqo boolean, auto_tuning boolean) → boolean: Изменяет или вставляет запись в файл данных, лежащий в основе представления aqo_queries, для указанных queryid и dbid. Можно использовать для dbid значение NULL вместо идентификатора текущей базы данных. Значения NULL для остальных параметров означают, что их следует оставить без изменений. Обратите внимание, что записи с нулевым значением queryid или dbid не могут быть изменены. Возвращает false в случае ошибки и true в противном случае.
aqo_query_texts_update (queryid bigint, dbid oid, query_text text) → boolean: Изменяет или вставляет запись в файл данных, лежащий в основе представления aqo_query_texts, для указанных queryid и dbid. Можно использовать для dbid значение NULL вместо идентификатора текущей базы данных. Значения NULL для остальных параметров означают, что их следует оставить без изменений. Обратите внимание, что записи с нулевым значением queryid или dbid не могут быть изменены. Возвращает false в случае ошибки и true в противном случае.
aqo_query_stat_update (queryid bigint, dbid oid, execution_time_with_aqo double precision[], execution_time_without_aqo double precision[], planning_time_with_aqo double precision[], planning_time_without_aqo double precision[], cardinality_error_with_aqo double precision[], cardinality_error_without_aqo double precision[], executions_with_aqo bigint[], executions_without_aqo bigint[]) → boolean: Изменяет или вставляет запись в файл данных, лежащий в основе представления aqo_query_stat, для указанных queryid и dbid. Можно использовать для dbid значение NULL вместо идентификатора текущей базы данных. Возвращает false в случае ошибки и true в противном случае.
aqo_data_update (fs bigint, fss integer, dbid oid, delta_rows boolean, nfeatures integer, features double precision[][], targets double precision[], reliability double precision[], oids oid[]) → boolean: Изменяет или вставляет запись в файл данных, лежащий в основе представления aqo_data, для указанных fs, fss и dbid. Можно использовать для dbid значение NULL вместо идентификатора текущей базы данных. Если для delta_rows задать NULL, будет использовано значение параметра конфигурации aqo.delta_rows. Возвращает false в случае ошибки и true в противном случае.
aqo_data_delete (fs bigint, fss integer, dbid oid, delta_rows boolean) → boolean: Удаляет запись из файла данных, лежащего в основе представления aqo_data, для указанных fs, fss и dbid. Можно использовать для dbid значение NULL вместо идентификатора текущей базы данных. Если не указать параметр delta_rows или задать для него NULL, будет использовано значение параметра конфигурации aqo.delta_rows. Возвращает false в случае ошибки и true в противном случае.

F.2.4.3.2. Функции управления памятью #

aqo_memory_usage () → setof record

Отображает выделенные и использованные размеры контекстов памяти и хеш-таблиц aqo. Возвращает таблицу со следующими столбцами:

name: Краткое описание контекста памяти или хеш-таблицы
allocated_size: Общий размер выделенной памяти
used_size: Размер текущей используемой памяти

F.2.4.3.3. Функции для аналитики #

aqo_cardinality_error (controlled boolean) → setof record

Показывает ошибку оценки количества строк для последнего выполнения запроса. Если controlled имеет значение true, показывает запросы, выполненные с включённым aqo. Если controlled имеет значение false, показывает запросы, выполненные с отключённым aqo, но имеющие накопленную статистику. Возвращает таблицу со следующими столбцами:

num: Порядковый номер
queryid: Уникальный идентификатор класса запросов
dbid: Идентификатор базы данных
fs: Идентификатор пространства признаков, обычно равен нулю или queryid.
error: Погрешность aqo, рассчитываемая на узлах планов запросов
nexecs: Количество выполнений запросов, связанных с данным queryid

aqo_execution_time (controlled boolean) → setof record

Показывает время выполнения запросов. Если controlled имеет значение true, показывает время выполнения последнего запроса с включённым aqo. Если controlled имеет значение false, возвращает среднее время выполнения запроса для всех записанных в журнал выполнений с отключённым aqo. Время выполнения без aqo можно собрать, если параметр aqo.mode = intelligent или параметр aqo.force_collect_stat = on. Возвращает таблицу со следующими столбцами:

num: Порядковый номер
queryid: Уникальный идентификатор класса запросов
dbid: Идентификатор базы данных
fs: Идентификатор пространства признаков, обычно равен нулю или queryid.
exec_time: Если controlled имеет значение true, показывает время выполнения последнего запроса с включённым aqo, в противном случае — среднее время выполнения запроса для всех записанных в журнал выполнений с отключённым aqo.
nexecs: Количество выполнений запросов, связанных с данным queryid

F.2.5. Примеры #

Пример F.1. Обучение на запросе (Основной режим)

Рассмотрим оптимизацию запроса с использованием расширения aqo.

Когда запрос выполняется в первый раз, его нет в таблицах, лежащих в основе представлений aqo. Таким образом, данных для предсказания aqo для каждого узла плана нет, и в выводе EXPLAIN появляются строки «AQO not used»:

demo=# EXPLAIN (ANALYZE, SUMMARY OFF, TIMING OFF)
SELECT bp.*
FROM ticket_flights tf
JOIN flights f ON f.flight_id = tf.flight_id
JOIN boarding_passes bp ON bp.ticket_no = tf.ticket_no AND bp.flight_id = tf.flight_id
WHERE f.scheduled_departure > '2017-08-15 15:00:00+00';
                                                         QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
 Hash Join  (cost=63796.33..140286.73 rows=477728 width=25) (actual rows=2215 loops=1)
   AQO not used, fss=8598194613120045129
   Hash Cond: ((bp.flight_id = f.flight_id) AND (bp.ticket_no = tf.ticket_no))
   ->  Seq Scan on boarding_passes bp  (cost=0.00..32894.95 rows=1894295 width=25) (actual rows=1894295 loops=1)
         AQO not used, fss=1362775811343989307
   ->  Hash  (cost=51379.43..51379.43 rows=595260 width=22) (actual rows=468255 loops=1)
         Buckets: 131072  Batches: 8  Memory Usage: 4223kB
         ->  Hash Join  (cost=1859.80..51379.43 rows=595260 width=22) (actual rows=468255 loops=1)
               AQO not used, fss=-7651474063207585780
               Hash Cond: (tf.flight_id = f.flight_id)
               ->  Seq Scan on ticket_flights tf  (cost=0.00..43323.35 rows=2360335 width=18) (actual rows=2360335 loops=1)
                     AQO not used, fss=-6410966714754547713
               ->  Hash  (cost=1652.80..1652.80 rows=16560 width=4) (actual rows=16340 loops=1)
                     Buckets: 32768  Batches: 1  Memory Usage: 831kB
                     ->  Seq Scan on flights f  (cost=0.00..1652.80 rows=16560 width=4) (actual rows=16340 loops=1)
                           AQO not used, fss=-1289471166524579716
                           Filter: (scheduled_departure > '2017-08-15 22:00:00+07'::timestamp with time zone)
                           Rows Removed by Filter: 49324
 Using aqo: true
 AQO mode: LEARN
 AQO advanced: OFF
 Query hash: 402936317627943257
 JOINS: 2
(23 rows)

Если в представлении aqo_data нет информации об определённом узле, aqo добавит в него соответствующую запись для дальнейшего изучения и предсказания, за исключением узлов с fss=0 в выводе EXPLAIN. Поскольку значения в полях features и targets в представлении aqo_data являются логарифмом по основанию e, чтобы получить фактическое значение, возведите e в соответствующую степень. Например: exp(7.703007682479236):

 demo=# select * from aqo_data;
 fs |         fss          | dbid  | nfeatures |                  features                   |       targets        | reliability |        oids
----+----------------------+-------+-----------+---------------------------------------------+----------------------+-------------+---------------------
  0 |  1362775811343989307 | 16429 |         0 |                                             | {14.454357295615447} | {1}         | {16452}
  0 | -6410966714754547713 | 16429 |         0 |                                             | {14.674314116080508} | {1}         | {16479}
  0 | -1289471166524579716 | 16429 |         1 | {{-1.3775704575284085}}                     | {9.701371368413994}  | {1}         | {16458}
  0 |  8598194613120045129 | 16429 |         2 | {{-13.492416828684513,-1.3775704575284085}} | {7.703007682479236}  | {1}         | {16479,16458,16452}
  0 | -7651474063207585780 | 16429 |         2 | {{-11.092306109090387,-1.3775704575284085}} | {13.056768298305919} | {1}         | {16479,16458}
(5 rows)

При повторном выполнении запроса aqo распознаёт его и делает предсказание. Обратите внимание на оценку количества строк, предсказанную aqo, и значение погрешности aqo («error=0%»).

demo=# EXPLAIN (ANALYZE, SUMMARY OFF, TIMING OFF)
SELECT bp.*
FROM ticket_flights tf
JOIN flights f ON f.flight_id = tf.flight_id
JOIN boarding_passes bp ON bp.ticket_no = tf.ticket_no AND bp.flight_id = tf.flight_id
WHERE f.scheduled_departure > '2017-08-15 15:00:00+00';
                                                         QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
 Hash Join  (cost=61144.50..136890.86 rows=2215 width=25) (actual rows=2215 loops=1)
   AQO: rows=2215, error=0%, fss=8598194613120045129
   Hash Cond: ((bp.flight_id = f.flight_id) AND (bp.ticket_no = tf.ticket_no))
   ->  Seq Scan on boarding_passes bp  (cost=0.00..32894.95 rows=1894295 width=25) (actual rows=1894295 loops=1)
         AQO: rows=1894295, error=0%, fss=1362775811343989307
   ->  Hash  (cost=51376.68..51376.68 rows=468255 width=22) (actual rows=468255 loops=1)
         Buckets: 131072  Batches: 4  Memory Usage: 7438kB
         ->  Hash Join  (cost=1857.05..51376.68 rows=468255 width=22) (actual rows=468255 loops=1)
               AQO: rows=468255, error=0%, fss=-7651474063207585780
               Hash Cond: (tf.flight_id = f.flight_id)
               ->  Seq Scan on ticket_flights tf  (cost=0.00..43323.35 rows=2360335 width=18) (actual rows=2360335 loops=1)
                     AQO: rows=2360335, error=0%, fss=-6410966714754547713
               ->  Hash  (cost=1652.80..1652.80 rows=16340 width=4) (actual rows=16340 loops=1)
                     Buckets: 16384  Batches: 1  Memory Usage: 703kB
                     ->  Seq Scan on flights f  (cost=0.00..1652.80 rows=16340 width=4) (actual rows=16340 loops=1)
                           AQO: rows=16340, error=0%, fss=-1289471166524579716
                           Filter: (scheduled_departure > '2017-08-15 22:00:00+07'::timestamp with time zone)
                           Rows Removed by Filter: 49324
 Using aqo: true
 AQO mode: LEARN
 AQO advanced: OFF
 Query hash: 402936317627943257
 JOINS: 2
(23 rows)

Изменив константу в запросе, можно заметить, что предсказание сделано с ошибкой:

demo=# EXPLAIN (ANALYZE, SUMMARY OFF, TIMING OFF)
SELECT bp.*
FROM ticket_flights tf
JOIN flights f ON f.flight_id = tf.flight_id
JOIN boarding_passes bp ON bp.ticket_no = tf.ticket_no AND bp.flight_id = tf.flight_id
WHERE f.scheduled_departure > '2017-08-10 15:00:00+00';
                                                         QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
 Hash Join  (cost=61144.50..136890.86 rows=2215 width=25) (actual rows=111397 loops=1)
   AQO: rows=2215, error=-4929%, fss=8598194613120045129
   Hash Cond: ((bp.flight_id = f.flight_id) AND (bp.ticket_no = tf.ticket_no))
   ->  Seq Scan on boarding_passes bp  (cost=0.00..32894.95 rows=1894295 width=25) (actual rows=1894295 loops=1)
         AQO: rows=1894295, error=0%, fss=1362775811343989307
   ->  Hash  (cost=51376.68..51376.68 rows=468255 width=22) (actual rows=577437 loops=1)
         Buckets: 131072 (originally 131072)  Batches: 8 (originally 4)  Memory Usage: 7169kB
         ->  Hash Join  (cost=1857.05..51376.68 rows=468255 width=22) (actual rows=577437 loops=1)
               AQO: rows=468255, error=-23%, fss=-7651474063207585780
               Hash Cond: (tf.flight_id = f.flight_id)
               ->  Seq Scan on ticket_flights tf  (cost=0.00..43323.35 rows=2360335 width=18) (actual rows=2360335 loops=1)
                     AQO: rows=2360335, error=0%, fss=-6410966714754547713
               ->  Hash  (cost=1652.80..1652.80 rows=16340 width=4) (actual rows=19040 loops=1)
                     Buckets: 32768 (originally 16384)  Batches: 1 (originally 1)  Memory Usage: 926kB
                     ->  Seq Scan on flights f  (cost=0.00..1652.80 rows=16340 width=4) (actual rows=19040 loops=1)
                           AQO: rows=16340, error=-17%, fss=-1289471166524579716
                           Filter: (scheduled_departure > '2017-08-10 22:00:00+07'::timestamp with time zone)
                           Rows Removed by Filter: 46624
 Using aqo: true
 AQO mode: LEARN
 AQO advanced: OFF
 Query hash: 402936317627943257
 JOINS: 2
(23 rows)

Однако вместо пересчёта полей features и targets, aqo добавил новые значения избирательности и оценки количества строк для этого запроса в aqo_data:

demo=# select * from aqo_data;
 fs |         fss          | dbid  | nfeatures |                                       features                                        |                 targets                 | reliability |        oids

----+----------------------+-------+-----------+---------------------------------------------------------------------------------------+-----------------------------------------+-------------+------------
---------
  0 |  1362775811343989307 | 16429 |         0 |                                                                                       | {14.454357295615447}                    | {1}         | {16452}
  0 | -6410966714754547713 | 16429 |         0 |                                                                                       | {14.674314116080508}                    | {1}         | {16479}
  0 | -1289471166524579716 | 16429 |         1 | {{-1.3775704575284085},{-1.2287385463463019}}                                         | {9.701371368413994,9.854297308345357}   | {1,1}       | {16458}
  0 |  8598194613120045129 | 16429 |         2 | {{-13.492416828684513,-1.3775704575284085},{-13.492416828684513,-1.2287385463463019}} | {7.703007682479236,11.620855676130656}  | {1,1}       | {16479,1645
8,16452}
  0 | -7651474063207585780 | 16429 |         2 | {{-11.092306109090387,-1.3775704575284085},{-11.092306109090387,-1.2287385463463019}} | {13.056768298305919,13.266354624518149} | {1,1}       | {16479,1645
8}
(5 rows)

Теперь в предсказании есть небольшая погрешность примерно в 3%, которая может объясняться погрешностью вычислений:

demo=# EXPLAIN (ANALYZE, SUMMARY OFF, TIMING OFF)
SELECT bp.*
FROM ticket_flights tf
JOIN flights f ON f.flight_id = tf.flight_id
JOIN boarding_passes bp ON bp.ticket_no = tf.ticket_no AND bp.flight_id = tf.flight_id
WHERE f.scheduled_departure > '2017-08-10 15:00:00+00';
                                                         QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
 Hash Join  (cost=63438.72..139820.11 rows=108541 width=25) (actual rows=111397 loops=1)
   AQO: rows=108541, error=-3%, fss=8598194613120045129
   Hash Cond: ((bp.flight_id = f.flight_id) AND (bp.ticket_no = tf.ticket_no))
   ->  Seq Scan on boarding_passes bp  (cost=0.00..32894.95 rows=1894295 width=25) (actual rows=1894295 loops=1)
         AQO: rows=1894295, error=0%, fss=1362775811343989307
   ->  Hash  (cost=51410.19..51410.19 rows=576635 width=22) (actual rows=577437 loops=1)
         Buckets: 131072  Batches: 8  Memory Usage: 4966kB
         ->  Hash Join  (cost=1890.56..51410.19 rows=576635 width=22) (actual rows=577437 loops=1)
               AQO: rows=576635, error=-0%, fss=-7651474063207585780
               Hash Cond: (tf.flight_id = f.flight_id)
               ->  Seq Scan on ticket_flights tf  (cost=0.00..43323.35 rows=2360335 width=18) (actual rows=2360335 loops=1)
                     AQO: rows=2360335, error=0%, fss=-6410966714754547713
               ->  Hash  (cost=1652.80..1652.80 rows=19021 width=4) (actual rows=19040 loops=1)
                     Buckets: 32768  Batches: 1  Memory Usage: 926kB
                     ->  Seq Scan on flights f  (cost=0.00..1652.80 rows=19021 width=4) (actual rows=19040 loops=1)
                           AQO: rows=19021, error=-0%, fss=-1289471166524579716
                           Filter: (scheduled_departure > '2017-08-10 22:00:00+07'::timestamp with time zone)
                           Rows Removed by Filter: 46624
 Using aqo: true
 AQO mode: LEARN
 AQO advanced: OFF
 Query hash: 402936317627943257
 JOINS: 2
(23 rows)

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

demo=# EXPLAIN (ANALYZE, SUMMARY OFF, TIMING OFF)
SELECT t.*
FROM ticket_flights tf
JOIN flights f ON f.flight_id = tf.flight_id
JOIN boarding_passes bp ON bp.ticket_no = tf.ticket_no AND bp.flight_id = tf.flight_id
JOIN tickets t ON t.ticket_no = tf.ticket_no
WHERE f.scheduled_departure > '2017-08-15 15:00:00+00';
                                                            QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=61158.91..134296.78 rows=2273 width=104) (actual rows=2215 loops=1)
   AQO not used, fss=-8581941154270057231
   ->  Hash Join  (cost=61158.48..133208.83 rows=2273 width=28) (actual rows=2215 loops=1)
         AQO: rows=2273, error=3%, fss=8598194613120045129
         Hash Cond: ((bp.flight_id = f.flight_id) AND (bp.ticket_no = tf.ticket_no))
         ->  Seq Scan on boarding_passes bp  (cost=0.00..32894.95 rows=1894295 width=18) (actual rows=1894295 loops=1)
               AQO: rows=1894295, error=0%, fss=1362775811343989307
         ->  Hash  (cost=51376.89..51376.89 rows=468906 width=22) (actual rows=468255 loops=1)
               Buckets: 131072  Batches: 4  Memory Usage: 7438kB
               ->  Hash Join  (cost=1857.26..51376.89 rows=468906 width=22) (actual rows=468255 loops=1)
                     AQO: rows=468906, error=0%, fss=-7651474063207585780
                     Hash Cond: (tf.flight_id = f.flight_id)
                     ->  Seq Scan on ticket_flights tf  (cost=0.00..43323.35 rows=2360335 width=18) (actual rows=2360335 loops=1)
                           AQO: rows=2360335, error=0%, fss=-6410966714754547713
                     ->  Hash  (cost=1652.80..1652.80 rows=16357 width=4) (actual rows=16340 loops=1)
                           Buckets: 16384  Batches: 1  Memory Usage: 703kB
                           ->  Seq Scan on flights f  (cost=0.00..1652.80 rows=16357 width=4) (actual rows=16340 loops=1)
                                 AQO: rows=16357, error=0%, fss=-1289471166524579716
                                 Filter: (scheduled_departure > '2017-08-15 22:00:00+07'::timestamp with time zone)
                                 Rows Removed by Filter: 49324
   ->  Index Scan using tickets_pkey on tickets t  (cost=0.42..0.48 rows=1 width=104) (actual rows=1 loops=2215)
         AQO not used, fss=2731022528523952664
         Index Cond: (ticket_no = bp.ticket_no)
 Using aqo: true
 AQO mode: LEARN
 AQO advanced: OFF
 Query hash: 7809046947949890015
 JOINS: 3
(28 rows)

Пример F.2. Использование представления aqo_query_stat

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

Обратимся к представлению aqo_query_stats:

demo=# SELECT * FROM aqo_query_stat \gx
-[ RECORD 1 ]-----------------+---------------------------------------------------------------
queryid                       | 7809046947949890015
dbid                          | 16429
execution_time_with_aqo       | {1.039218233,0.925258453,0.831166925,0.779602353}
execution_time_without_aqo    | {1.022052611,0.936486619}
planning_time_with_aqo        | {0.003305339,0.002129048,0.002538877,0.002142972}
planning_time_without_aqo     | {0.000767553,0.000711208}
cardinality_error_with_aqo    | {0.4854215265638894,0,1.1711726076352047,0.007732205169478082}
cardinality_error_without_aqo | {0.4854215265638894,1.571562511977072}
executions_with_aqo           | 4
executions_without_aqo        | 2

Полученные данные относятся к запросу, рассмотренному в примере Пример F.1. Этот запрос выполнялся с каждым из параметров f.scheduled_departure > '2017-08-10 15:00:00+00' и f.scheduled_departure > '2017-08-15 15:00:00+00' по одному разу без aqo и по два раза с aqo. Видно, что с aqo погрешность оценки количества строк уменьшается до 0,0077, а минимальная погрешность оценки количества строк без aqo составляет 0,4854. Кроме того, время выполнения с aqo меньше, чем без него. Таким образом, можно сделать вывод, что aqo хорошо обучается на этом запросе и предсказание можно использовать для этого класса запросов.

Пример F.3. Использование расширенного режима aqo

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

demo=# SET aqo.advanced = on;

aqo будет собирать данные машинного обучения отдельно для каждого выполняемого запроса. Например:

demo=# EXPLAIN (ANALYZE, SUMMARY OFF, TIMING OFF)
SELECT bp.*
FROM ticket_flights tf
JOIN flights f ON f.flight_id = tf.flight_id
JOIN boarding_passes bp ON bp.ticket_no = tf.ticket_no AND bp.flight_id = tf.flight_id
WHERE f.scheduled_departure > '2017-08-15 15:00:00+00';
                                                         QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
 Hash Join  (cost=63796.33..140286.73 rows=477728 width=25) (actual rows=2215 loops=1)
   AQO not used, fss=8598194613120045129
   Hash Cond: ((bp.flight_id = f.flight_id) AND (bp.ticket_no = tf.ticket_no))
   ->  Seq Scan on boarding_passes bp  (cost=0.00..32894.95 rows=1894295 width=25) (actual rows=1894295 loops=1)
         AQO not used, fss=1362775811343989307
   ->  Hash  (cost=51379.43..51379.43 rows=595260 width=22) (actual rows=468255 loops=1)
         Buckets: 131072  Batches: 8  Memory Usage: 4223kB
         ->  Hash Join  (cost=1859.80..51379.43 rows=595260 width=22) (actual rows=468255 loops=1)
               AQO not used, fss=-7651474063207585780
               Hash Cond: (tf.flight_id = f.flight_id)
               ->  Seq Scan on ticket_flights tf  (cost=0.00..43323.35 rows=2360335 width=18) (actual rows=2360335 loops=1)
                     AQO not used, fss=-6410966714754547713
               ->  Hash  (cost=1652.80..1652.80 rows=16560 width=4) (actual rows=16340 loops=1)
                     Buckets: 32768  Batches: 1  Memory Usage: 831kB
                     ->  Seq Scan on flights f  (cost=0.00..1652.80 rows=16560 width=4) (actual rows=16340 loops=1)
                           AQO not used, fss=-1289471166524579716
                           Filter: (scheduled_departure > '2017-08-15 22:00:00+07'::timestamp with time zone)
                           Rows Removed by Filter: 49324
 Using aqo: true
 AQO mode: LEARN
 AQO advanced: ON
 Query hash: 402936317627943257
 JOINS: 2
(23 rows)

Теперь этот запрос хранится в aqo_data с ненулевым fs (по умолчанию fs равен хешу запроса):

demo=# SELECT * FROM aqo_data;
         fs         |         fss          | dbid  | nfeatures |                  features                   |       targets        | reliability |        oids
--------------------+----------------------+-------+-----------+---------------------------------------------+----------------------+-------------+---------------------
 402936317627943257 | -6410966714754547713 | 16429 |         0 |                                             | {14.674314116080508} | {1}         | {16479}
 402936317627943257 |  1362775811343989307 | 16429 |         0 |                                             | {14.454357295615447} | {1}         | {16452}
 402936317627943257 | -1289471166524579716 | 16429 |         1 | {{-1.3775704575284085}}                     | {9.701371368413994}  | {1}         | {16458}
 402936317627943257 |  8598194613120045129 | 16429 |         2 | {{-13.492416828684513,-1.3775704575284085}} | {7.703007682479236}  | {1}         | {16479,16458,16452}
 402936317627943257 | -7651474063207585780 | 16429 |         2 | {{-11.092306109090387,-1.3775704575284085}} | {13.056768298305919} | {1}         | {16479,16458}
(5 rows)

Можно настроить несколько параметров только для данного запроса. Это значения learn_aqo, use_aqo и auto_tuning в представлении aqo_queries:

demo=# SELECT * FROM aqo_queries;
      queryid       | dbid  |         fs         | learn_aqo | use_aqo | auto_tuning | smart_timeout | count_increase_timeout
--------------------+-------+--------------------+-----------+---------+-------------+---------------+------------------------
 402936317627943257 | 16429 | 402936317627943257 | t         | t       | f           |             0 |                      0
                  0 |     0 |                  0 | f         | f       | f           |             0 |                      0
(2 rows)

Зададим для use_aqo значение false:

demo=# SELECT aqo_queries_update(402936317627943257, NULL, NULL, NULL, false, NULL);
 aqo_queries_update
--------------------
 t
(1 row)

Теперь изменим константу в запросе:

demo=# EXPLAIN (ANALYZE, SUMMARY OFF, TIMING OFF)
SELECT bp.*
FROM ticket_flights tf
JOIN flights f ON f.flight_id = tf.flight_id
JOIN boarding_passes bp ON bp.ticket_no = tf.ticket_no AND bp.flight_id = tf.flight_id
WHERE f.scheduled_departure > '2017-08-10 15:00:00+00';
                                                         QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
 Hash Join  (cost=65822.16..142872.60 rows=554378 width=25) (actual rows=111397 loops=1)
   AQO not used, fss=0
   Hash Cond: ((bp.flight_id = f.flight_id) AND (bp.ticket_no = tf.ticket_no))
   ->  Seq Scan on boarding_passes bp  (cost=0.00..32894.95 rows=1894295 width=25) (actual rows=1894295 loops=1)
         AQO not used, fss=0
   ->  Hash  (cost=51412.64..51412.64 rows=690768 width=22) (actual rows=577437 loops=1)
         Buckets: 131072  Batches: 8  Memory Usage: 4966kB
         ->  Hash Join  (cost=1893.01..51412.64 rows=690768 width=22) (actual rows=577437 loops=1)
               AQO not used, fss=0
               Hash Cond: (tf.flight_id = f.flight_id)
               ->  Seq Scan on ticket_flights tf  (cost=0.00..43323.35 rows=2360335 width=18) (actual rows=2360335 loops=1)
                     AQO not used, fss=0
               ->  Hash  (cost=1652.80..1652.80 rows=19217 width=4) (actual rows=19040 loops=1)
                     Buckets: 32768  Batches: 1  Memory Usage: 926kB
                     ->  Seq Scan on flights f  (cost=0.00..1652.80 rows=19217 width=4) (actual rows=19040 loops=1)
                           AQO not used, fss=0
                           Filter: (scheduled_departure > '2017-08-10 22:00:00+07'::timestamp with time zone)
                           Rows Removed by Filter: 46624
 Using aqo: false
 AQO mode: LEARN
 AQO advanced: ON
 Query hash: 402936317627943257
 JOINS: 2
(23 rows)

aqo не использовался для этого запроса, но в представлении aqo_data появились новые данные:

demo=# SELECT * FROM aqo_data;
         fs         |         fss          | dbid  | nfeatures |                                       features                                        |                 targets                 | reliability |        oids
--------------------+----------------------+-------+-----------+---------------------------------------------------------------------------------------+-----------------------------------------+-------------+---------------------
 402936317627943257 | -6410966714754547713 | 16429 |         0 |                                                                                       | {14.674314116080508}                    | {1}         | {16479}
 402936317627943257 |  1362775811343989307 | 16429 |         0 |                                                                                       | {14.454357295615447}                    | {1}         | {16452}
 402936317627943257 | -1289471166524579716 | 16429 |         1 | {{-1.3775704575284085},{-1.2287385463463019}}                                         | {9.701371368413994,9.854297308345357}   | {1,1}       | {16458}
 402936317627943257 |  8598194613120045129 | 16429 |         2 | {{-13.492416828684513,-1.3775704575284085},{-13.492416828684513,-1.2287385463463019}} | {7.703007682479236,11.620855676130656}  | {1,1}       | {16479,16458,16452}
 402936317627943257 | -7651474063207585780 | 16429 |         2 | {{-11.092306109090387,-1.3775704575284085},{-11.092306109090387,-1.2287385463463019}} | {13.056768298305919,13.266354624518149} | {1,1}       | {16479,16458}
(5 rows)

Установленное значение параметра use_aqo не относится к другим запросам. После выполнения другого запроса дважды видно, что aqo обучается на нём и делает для него предсказание:

EXPLAIN (ANALYZE, SUMMARY OFF, TIMING OFF)
SELECT t.*
FROM ticket_flights tf
JOIN flights f ON f.flight_id = tf.flight_id
JOIN boarding_passes bp ON bp.ticket_no = tf.ticket_no AND bp.flight_id = tf.flight_id
JOIN tickets t ON t.ticket_no = tf.ticket_no
WHERE f.scheduled_departure > '2017-08-15 15:00:00+00';
                                                            QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=61144.93..134251.05 rows=2215 width=104) (actual rows=2215 loops=1)
   AQO: rows=2215, error=0%, fss=-8581941154270057231
   ->  Hash Join  (cost=61144.50..133190.86 rows=2215 width=28) (actual rows=2215 loops=1)
         AQO: rows=2215, error=0%, fss=8598194613120045129
         Hash Cond: ((bp.flight_id = f.flight_id) AND (bp.ticket_no = tf.ticket_no))
         ->  Seq Scan on boarding_passes bp  (cost=0.00..32894.95 rows=1894295 width=18) (actual rows=1894295 loops=1)
               AQO: rows=1894295, error=0%, fss=1362775811343989307
         ->  Hash  (cost=51376.68..51376.68 rows=468255 width=22) (actual rows=468255 loops=1)
               Buckets: 131072  Batches: 4  Memory Usage: 7438kB
               ->  Hash Join  (cost=1857.05..51376.68 rows=468255 width=22) (actual rows=468255 loops=1)
                     AQO: rows=468255, error=0%, fss=-7651474063207585780
                     Hash Cond: (tf.flight_id = f.flight_id)
                     ->  Seq Scan on ticket_flights tf  (cost=0.00..43323.35 rows=2360335 width=18) (actual rows=2360335 loops=1)
                           AQO: rows=2360335, error=0%, fss=-6410966714754547713
                     ->  Hash  (cost=1652.80..1652.80 rows=16340 width=4) (actual rows=16340 loops=1)
                           Buckets: 16384  Batches: 1  Memory Usage: 703kB
                           ->  Seq Scan on flights f  (cost=0.00..1652.80 rows=16340 width=4) (actual rows=16340 loops=1)
                                 AQO: rows=16340, error=0%, fss=-1289471166524579716
                                 Filter: (scheduled_departure > '2017-08-15 22:00:00+07'::timestamp with time zone)
                                 Rows Removed by Filter: 49324
   ->  Index Scan using tickets_pkey on tickets t  (cost=0.42..0.48 rows=1 width=104) (actual rows=1 loops=2215)
         AQO not used, fss=2731022528523952664
         Index Cond: (ticket_no = bp.ticket_no)
 Using aqo: true
 AQO mode: LEARN
 AQO advanced: ON
 Query hash: 7809046947949890015
 JOINS: 3
(28 rows)

Пример F.4. Использование режима «песочницы»

SET aqo.sandbox = ON;
SET aqo.enable = ON;
SET aqo.advanced = OFF;
-- Очистка базы знаний песочницы, не затрагивающая основные данные
SELECT aqo_reset();

EXPLAIN (ANALYZE, SUMMARY OFF, TIMING OFF)
SELECT t.*
FROM ticket_flights tf
JOIN flights f ON f.flight_id = tf.flight_id
JOIN boarding_passes bp ON bp.ticket_no = tf.ticket_no AND bp.flight_id = tf.flight_id
JOIN tickets t ON t.ticket_no = tf.ticket_no
WHERE f.scheduled_departure > '2017-08-15 15:00:00+00';

-- Выполнение предыдущего запроса, пока планы не стабилизируются.
...

-- Копирование данных, полученных из песочницы с aqo.advanced = OFF
CREATE TABLE aqo_data_sandbox AS SELECT * FROM aqo_data;
SET aqo.sandbox = OFF;
SELECT aqo_data_update (fs, fss, dbid, nfeatures, features, targets, reliability, oids)
FROM aqo_data_sandbox WHERE fs = 0;
DROP TABLE aqo_data_sandbox;

F.2.6. Автор #

Олег Иванов

Пред.	Наверх	След.
F.1. amcheck — модуль с инструментами, проверяющими целостность таблиц и индексов	Начало	F.3. auth_delay — задержка при ошибке аутентификации

Вход в личный кабинет

Восстановление пароля

Подтверждение аккаунта

Изменение пароля

F.2. aqo — оптимизация запросов по стоимости выполнения #

F.2.1. Описание #

F.2.1.1. Ограничения #

F.2.2. Установка и подготовка #

Важно

F.2.2.1. Конфигурирование #

F.2.3. Использование #

F.2.3.1. Использование основного режима aqo #

F.2.3.2. Выбор режима работы для расширенной оптимизации запросов #

Примечание

F.2.3.3. Тонкая настройка aqo #

F.2.3.4. Режим «песочницы» #

F.2.4. Справка #

F.2.4.1. Параметры конфигурации #

F.2.4.2. Представления #

F.2.4.2.1. aqo_query_texts #

F.2.4.2.2. aqo_queries #

F.2.4.2.3. aqo_data #

F.2.4.2.4. aqo_query_stat #

F.2.4.3. Функции #

F.2.4.3.1. Функции управления хранилищем #

Важно

F.2.4.3.2. Функции управления памятью #

F.2.4.3.3. Функции для аналитики #

F.2.5. Примеры #

F.2.6. Автор #

F.2.4.2.1. `aqo_query_texts` #

F.2.4.2.2. `aqo_queries` #

F.2.4.2.3. `aqo_data` #

F.2.4.2.4. `aqo_query_stat` #