F.3. aqo

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

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

Важно

Оптимизация запросов с использованием aqo не поддерживается на ведомых серверах.

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

Предупреждение

Учтите, что aqo может работать некорректно непосредственно после обновлений расширения, которые изменяют его ядро, и после обновлений Postgres Pro. Поэтому после каждого обновления Postgres Pro следует вызывать функцию aqo_reset() и выполнять команду DROP EXTENSION aqo.

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

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

  1. Добавьте aqo в параметр shared_preload_libraries в файле postgresql.conf:

    shared_preload_libraries = 'aqo'

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

  2. Создайте расширение aqo, выполнив следующий запрос:

    CREATE EXTENSION aqo;

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

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

DROP EXTENSION aqo;

Примечание

Когда модуль aqo удаляется и вновь создаётся командами DROP EXTENSION -> CREATE EXTENSION, он сохраняет своё внутреннее состояние.

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

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

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

  • intelligent — в этом режиме выполняется автонастройка запросов на основе статистики, собранной по классам запросов.

  • forced — в этом режиме собирается статистика по всем запросам, вне зависимости от их класса.

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

  • learn — в этом режиме собирается статистика по всем выполненным запросам и обновляются данные о классах запросов.

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

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

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

SET aqo.mode = 'режим';

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

Важно

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

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

F.3.2.1. Выбор режима работы для оптимизации запросов

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

Примечание

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

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

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

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

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

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

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

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

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

Работая в интеллектуальном режиме (intelligent), aqo назначает уникальное хеш-значение каждому классу запросов для разделения собираемой статистики. В режиме forced статистика всех ранее ненаблюдаемых классов запросов собирается вместе, в одной записи для общего класса с хешем, равным 0. Просмотреть все обработанные классы запросов и их хеш-значения можно в представлении aqo_query_texts:

SELECT * FROM aqo_query_texts;

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

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

SELECT * FROM aqo_queries;

Для каждого класса запросов хранятся следующие свойства:

  • queryid содержит идентификатор запроса, однозначно определяющий класс запроса.

  • learn_aqo включает сбор статистики для данного класса запросов.

  • use_aqo включает предсказание количества строк средствами aqo для следующего выполнения данного класса запросов.

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

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

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

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

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

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

SELECT count(*) FROM (SELECT queryid FROM aqo_queries) AS q1,
  LATERAL aqo_queries_update(q1.queryid, NULL, true, true, false) AS q2
  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 (SELECT queryid FROM aqo_queries) AS q1,
  LATERAL aqo_queries_update(q1.queryid, NULL, false, true, false) AS q2 
  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 (SELECT queryid FROM aqo_queries) AS q1,
  LATERAL aqo_queries_update(q1.queryid, NULL, true, true, false) AS q2
  WHERE queryid = 'hash');

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

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

SELECT count(*) FROM (SELECT queryid FROM aqo_queries) AS q1,
  LATERAL aqo_queries_update(q1.queryid, NULL, false, true, false) AS q2
  WHERE queryid = 'hash');

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

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

SELECT count(*) FROM (SELECT queryid FROM aqo_queries) AS q1,
  LATERAL aqo_queries_update(q1.queryid, NULL, false, false, false) AS q2
  WHERE queryid IN (SELECT queryid FROM aqo_query_texts);

F.3.3. Справка

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

aqo.mode

Определяет режим работы aqo. Возможные значения перечислены в Таблице F.2.

Таблица F.2. Параметры aqo.mode

ЗначениеОписание
intelligentАвтонастройка запросов на основе статистики, собранной по классам запросов.
forcedСобирается статистика по всем запросам, вне зависимости от их класса.
controlledРежим по умолчанию. Для всех новых запросов используется стандартный планировщик, но для уже известных классов запросов может использоваться ранее собранная статистика.
learnСобирается статистика по всем выполненным запросам и обновляются данные о классах запросов.
frozenИспользуется статистика, собранная для запросов известных классов, но новые данные не собираются, чтобы уменьшить влияние aqo на время планирования и исполнения запросов.
disabledПолностью отключает aqo для всех запросов. При этом конфигурация и собранная статистика aqo сохраняется для возможного использования в будущем.


aqo.show_hash

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

aqo.show_details

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

aqo.join_threshold

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

aqo.statement_timeout

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

aqo.force_collect_stat

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

aqo.dsm_size_max

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

aqo.fs_max_items

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

aqo.fss_max_items

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

Включает поиск соседей с одним и тем же подпространством признаков среди разных классов запросов.

aqo.querytext_max_size

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

aqo.min_neighbors_for_predicting

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

aqo.predict_with_few_neighbors

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

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

F.3.3.2.1. aqo_query_texts

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

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

Имя столбцаОписание
queryidСодержит идентификатор запроса, однозначно определяющий класс запроса.
query_textСодержит текст первого проанализированного запроса данного класса.

F.3.3.2.2. aqo_queries

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

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

СвойствоОписание
queryidСодержит идентификатор запроса, однозначно определяющий класс запроса.
learn_aqoВключает сбор статистики для данного класса запросов.
use_aqoВключает предсказание количества строк средствами aqo для следующего выполнения данного класса запросов. Если модель оценки стоимости неполная, это может привести к замедлению при выполнении запросов.
fspace_hashЗадаёт уникальный идентификатор отдельного пространства, в котором собирается статистика для данного класса запросов. По умолчанию fspace_hash равняется queryid. Вы можете присвоить ему другой queryid, чтобы оптимизировать разные классы запросов вместе. В результате может сократиться объём памяти для моделей и даже увеличиться скорость запросов. Однако изменение этого свойства может приводить и к неожиданному поведению aqo, так что использовать это следует, только если вы точно понимаете, что делаете.
auto_tuningПоказывает, будет ли aqo пытаться настроить другие параметры для данного запроса. По умолчанию автонастройка включена в интеллектуальном режиме (intelligent).
smart_timeoutПоказывает значение «умного» тайм-аута операторов для данного класса запросов.
count_increase_timeoutПоказывает, сколько раз увеличивался «умный» тайм-аут операторов для данного класса запросов.

F.3.3.2.3. aqo_data

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

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

ДанныеОписание
fsХеш пространства признаков.
fssХеш подпространства признаков.
nfeaturesРазмер подпространства признаков для узла плана запроса.
featuresЛогарифм избирательности, на котором основано предсказание количества строк.
targetsЛогарифм количества строк для узла плана запроса.
reliabilityРавнозначно:
  • 1 — указывает, что данные получены после нормального выполнения запроса

  • 0,1 — указывает, что данные получены от частично выполненного узла (они не нужны, так как ненадёжны)

  • 0,9 — указывает, что данные получены от готового узла, но от частично выполненного оператора

oidsСписок идентификаторов таблиц, которые участвовали в предсказании для этого узла.

F.3.3.2.4. aqo_query_stat

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

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

ДанныеОписание
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.3.3.3. Функции

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

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

Важно

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

aqo_cleanup() → setof integer

Удаляет данные, относящиеся к классам запросов, которые связаны (возможно частично) с удалёнными отношениями. Возвращает количество удалённых пространств признаков (классов) и подпространств признаков. Игнорирует удаление других объектов.

aqo_enable_class (queryid bigint) → void

Устанавливает для learn_aqo, use_aqo и auto_tuning значение true для данного класса запросов.

aqo_disable_class (queryid bigint) → void

Устанавливает для learn_aqo, use_aqo и auto_tuning значение false для данного класса запросов.

aqo_drop_class (queryid bigint) → integer

Удаляет все данные, относящиеся к заданному классу запросов, из хранилища aqo. Возвращает количество записей, удалённых из хранилища aqo.

aqo_reset() → bigint

Удаляет следующие данные из хранилища aqo: данные машинного обучения, тексты запросов, свойства оптимизации для классов запросов и статистику. Возвращает количество записей, удалённых из хранилища aqo.

aqo_queries_update (queryid bigint, fs bigint, learn_aqo boolean, use_aqo boolean, auto_tuning boolean) → boolean

Присваивает новые значения следующим параметрам в представлении aqo_queries для данного класса запросов: fspace_hash, learn_aqo, use_aqo и auto_tuning. Значение NULL означает «оставить как есть».

aqo_query_texts_update (queryid bigint, query_text text) → boolean

Изменяет или добавляет запись в представление aqo_query_texts для данного queryid.

aqo_query_stat_update (queryid bigint, 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.

aqo_data_update (fs bigint, fss integer, nfeatures integer, features double precision[][], targets double precision[], reliability double precision[], oids oid[]) → boolean

Изменяет или добавляет запись в представление aqo_data для заданных fs и fss.

F.3.3.3.2. Функции управления памятью
aqo_memory_usage () → setof record

Отображает размеры контекстов памяти и хеш-таблиц aqo.

F.3.3.3.3. Функции аналитики
aqo_cardinality_error (controlled boolean) → setof record

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

aqo_execution_time (controlled boolean) → setof record

Показывает время выполнения для каждого класса запросов. Если controlled имеет значение true, показывает время выполнения последнего запроса с включённым aqo. Если controlled имеет значение false, возвращает среднее время выполнения запроса для всех записанных в журнал выполнений с отключённым aqo.

F.3.4. Автор

Олег Иванов