F.2. aqo — оптимизация запросов по стоимости выполнения #
F.2.1. Описание #
Модуль aqo представляет собой расширение Postgres Pro Shardman для оптимизации запросов по стоимости выполнения. Используя методы машинного обучения, а точнее модификацию алгоритма k-NN, aqo улучшает оценку количества строк, что может способствовать выбору лучшего плана и, как следствие, ускорению запросов.
Модуль aqo может собирать статистику по всем выполняемым запросам, за исключением запросов, обращающихся к системным отношениям. Собираемые статистические данные классифицируются по классам запросов. Если запросы различаются только константами, они считаются относящимися к одному классу. Для каждого класса модуль aqo сохраняет для машинного обучения качество оценки количества строк, время планирования, время выполнения и статистику выполнения. На основе этих данных aqo строит новый план выполнения и использует его для следующего запроса того же класса. В тестах aqo показал значительное увеличение производительности для сложных запросов.
Модуль aqo сохраняет все данные обучения (aqo_data), запросы (aqo_query_texts), параметры запросов (aqo_queries) и статистику выполнения запросов (aqo_query_stat) в файлах. При запуске aqo эти данные загружаются в разделяемую память. Вы можете обращаться к данным aqo, используя функции и представления.
aqo может работать в базовом и расширенном режимах. В расширенном режиме при запуске aqo в режиме работы auto,intelligent или learn каждому классу запросов для его идентификации и разделения собранной статистики присваивается уникальное хеш-значение, вычисляемое на основе дерева запросов. В базовом режиме статистика для всех неотслеживаемых классов запросов хранится в общем классе запросов с хеш-значением, равным 0.
С каждым классом запросов связано отдельное пространство, называемое пространством признаков, в котором собирается статистика для данного класса запросов. Для идентификации этого пространства признаков используется хеш-значение (fs), называемое основным хеш-значением. Оно является общим для запросов, отличающихся только именами таблиц, что позволяет агрегировать данные обучения по таким запросам. С каждым пространством признаков связаны подпространства признаков, в которых собирается информация об избирательности и количестве строк для каждого узла плана запроса. Для идентификации каждого подпространства также используется хеш-значение (fss).
F.2.2. Установка и подготовка #
Расширение aqo включено в состав Postgres Pro Shardman. Установив Postgres Pro Shardman, выполните следующие действия, чтобы подготовить aqo к работе:
Добавьте
aqoв переменную shared_preload_libraries в файлеpostgresql.conf.shared_preload_libraries = 'aqo'
Библиотеку aqo нужно предварительно загрузить при запуске сервера, так как адаптивная оптимизация запросов должна быть включена для всего кластера.
Создайте расширение aqo, выполнив следующий запрос:
CREATE EXTENSION aqo;
Включите расширение aqo, установив для параметра aqo.enable значение
on.ALTER SYSTEM SET aqo.enable = 'on';
Важно
Чтобы избежать ошибок во время физической репликации при переносе данных aqo с ведущего сервера на реплику, убедитесь, что на серверах установлены одинаковые версии модуля. Если установлены разные версии aqo, необходимо задать значение off для параметра aqo.wal_rw на обоих серверах, однако в таком случае репликация выполняться не будет.
F.2.3. Отключение и удаление #
Чтобы временно отключить aqo для всех запросов в текущем сеансе или на уровне всего кластера, но не удалять и не изменять собранную статистику и параметры, можно установить для параметра aqo.enable значение off.
ALTER SYSTEM SET aqo.enable = 'off'; SELECT pg_reload_conf();
Другой способ отключить aqo в текущей базе данных — это удалить расширение.
DROP EXTENSION aqo;
Чтобы удалить все данные aqo, включая собранную статистику, из текущей базы данных, вызовите функцию aqo_reset следующим образом:
SELECT aqo_reset();
Чтобы удалить все данные из хранилища aqo, выполните следующее:
SELECT aqo_reset(NULL);
Чтобы расширение aqo не загружалось при перезапуске сервера, удалите следующую строку из файла postgresql.conf:
shared_preload_libraries = 'aqo'
F.2.4. Ограничения #
В настоящее время расширение aqo имеет следующие ограничения:
Оптимизация запросов с использованием aqo не поддерживается для запросов, содержащих функции
IMMUTABLE.Модуль aqo не собирает статистику по репликам, поскольку они доступны только для чтения. Однако он может использовать статистику выполнения запросов с ведущего сервера при работе с физической репликой.
Режимы
auto,learnиintelligentне предназначены для работы на уровне кластера с запросами, имеющими динамически генерируемую структуру, поскольку в этих режимах сохраняются все идентификаторы классов запросов, которые различны для всех подобных запросов. Тем не менее могут использоваться динамически генерируемые константы.
F.2.5. Использование #
Поведение aqo управляется преимущественно с помощью параметров конфигурации aqo.mode и aqo.advanced. По умолчанию для параметра aqo.advanced установлено значение off. Это означает, что aqo работает в базовом режиме. Если для этого параметра установлено значение on, aqo работает в расширенном режиме.
Точный режим работы определяется параметром aqo.mode. По умолчанию для него установлено значение auto.
Чтобы динамически изменить режим работы в текущем сеансе, выполните следующую команду:
SET aqo.mode = 'режим';Здесь режим — название режима работы, который будет использоваться.
Чтобы переключать режимы на уровне экземпляра сервера, выполните следующее:
ALTER SYSTEM SET aqo.mode = 'режим';
SELECT pg_reload_conf();F.2.5.1. Использование aqo в базовом режиме #
В режиме auto (по умолчанию) aqo собирает статистику по всем выполняемым запросам для узлов плана, определяемых fss, а также обучается и делает предсказания на основе этой статистики. Собранные данные машинного обучения используются для исправления погрешностей оценки количества строк для всех запросов, планы которых содержат определённые узлы. Статистика для всех классов запросов хранится в общем классе запросов с хеш-значением (fs), равным 0.
aqo реализует механизм вытеснения давно неиспользуемых данных (least recently used, LRU) для удаления данных при достижении одного из пределов: aqo.dsm_size_max, aqo.fs_max_items или aqo.fss_max_items. aqo использует два разных кеша для aqo_data и aqo_query_texts. Алгоритм LRU сначала удаляет данные из aqo_query_texts при их наличии, а только потом из aqo_data.
В режиме learn aqo работает так же, как и в режиме auto, но не использует механизм LRU для вытеснения данных из кеша. В этом режиме при достижении пределов кеша aqo прекращает дальнейшее обучение. Данные об избирательности и количестве строк для новых узлов планов запросов больше не будут собираться, а новые данные обучения не появятся в соответствующих представлениях.
Режим intelligent с выключенным параметром aqo.advanced работает точно так же, как и режим learn.
Не рекомендуется использовать режим auto или learn постоянно для всего кластера в производственной среде, поскольку это может привести к ненужным вычислительным издержкам и к небольшому снижению производительности. Выполните запросы, которые необходимо оптимизировать, несколько раз, пока их планы не станут достаточно хорошими или не перестанут меняться, а затем переключитесь на режим frozen или controlled. В режиме frozen aqo делает предсказания только для известных запросов, но не обучается ни на каких запросах. Выбирайте этот режим из-за меньших накладных расходов, если таблицы, задействованные в оптимизируемых запросах, меняются редко. В противном случае выбирайте режим controlled, в котором aqo делает предсказания для известных запросов, а также обучается на них. Обратите внимание, что режимы frozen и controlled должны использоваться только после того, как модуль aqo уже обучился в режиме auto или learn.
Данные машинного обучения применяются не только к запросам, на которых обучался модуль aqo, но также ко всем запросам с планами, содержащими узлы, по которым собиралась статистика. Чтобы данные машинного обучения не влияли на другие запросы, используйте расширенный режим. За подробностями обратитесь к разделу ниже.
Текущий план запроса можно посмотреть с помощью команды EXPLAIN с указанием параметра ANALYZE. За подробностями обратитесь к Разделу 14.1.
F.2.5.2. Использование aqo в расширенном режиме #
Если часто выполняются запросы одного класса, например, в приложении ограничено число возможных классов запросов, можно установить для параметра aqo.advanced значение on. В режимах работы auto (по умолчанию), learn и intelligent aqo анализирует выполнение каждого запроса и собирает статистику по запросам разных классов отдельно.
Работа aqo в режимах auto и learn практически идентична. Единственное отличие заключается в том, как эти режимы обрабатывают ситуации достижения пределов кеша. В режиме auto aqo использует механизм LRU для вытеснения данных из кеша, а в режиме learn aqo прекращает обучение. За подробной информацией обратитесь к описанию базового режима.
Чтобы автоматически определять, какие запросы может оптимизировать aqo, переключите режим работы в intelligent. В этом режиме aqo сохраняет новые запросы с включённым значением auto_tuning. За подробной информацией обратитесь к описанию представления aqo_queries. Если производительность запроса не увеличивается после 50 итераций оптимизации, расширение aqo перестаёт работать и уступает планирование стандартному планировщику запросов по умолчанию.
Как и в базовом режиме, не рекомендуется использовать режим auto, learn или intelligent постоянно для всего производственного кластера, поскольку это может привести к накладным расходам и небольшому снижению производительности. После того как модуль aqo обучился в одном из этих режимов, переключите режим на frozen или controlled.
Расширенный режим не подходит, если запросы в рабочей нагрузке относятся к нескольким разным классам или эти классы постоянно меняются. В таких случаях используйте базовый режим.
F.2.5.3. Тонкая настройка aqo #
Для обращения к представлениям aqo и изменения расширенных параметров запросов необходимо иметь права суперпользователя.
Если данные могут существенно изменяться между запросами, можно включить параметр aqo.delta_rows. В этом случае aqo делает предсказания на основе оценки количества строк стандартного планировщика. Например, при удалении данных из таблицы планировщик предсказывает меньшее число строк. aqo может использовать эту обновлённую информацию, чтобы предсказать меньшее число строк без необходимости дополнительного обучения.
Все обработанные классы запросов и соответствующие хеш-значения можно увидеть в представлении aqo_query_texts.
SELECT * FROM aqo_query_texts;
Чтобы узнать класс запроса, то есть хеш-значение, и режим работы, установите для параметров aqo.show_hash и aqo.show_details значения on и выполните запрос. В результате вывод будет содержать примерно следующее:
... 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.enable='on'; 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(fs, NULL, true, true, false) WHERE fs = (SELECT fs 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(fs, NULL, false, true, false) WHERE fs = (SELECT fs 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(fs, NULL, NULL, NULL, false) WHERE fs = 'hash';
Здесь хеш — это хеш-значение для данного класса запросов. В результате aqo не будет автоматически менять значения learn_aqo и use_aqo.
Чтобы отключить дальнейшее обучение для некоторого класса запросов, выполните следующую команду:
SELECT count(*) FROM aqo_queries, LATERAL aqo_queries_update(fs, NULL, false, NULL, false) WHERE fs = 'hash';
Здесь хеш — это значение хеша для данного класса запросов.
Чтобы полностью отключить aqo для всех запросов и использовать стандартный планировщик, выполните следующее:
SELECT count(*) FROM aqo_queries, LATERAL aqo_disable_class(fs, NULL) WHERE fs <> 0;
F.2.5.4. Режим «песочницы» #
Можно экспериментировать с aqo, не затрагивая его основную базу знаний. Для этого выполните следующую команду:
SET aqo.sandbox = ON;
Она включает режим «песочницы», в котором aqo будет работать в изолированной среде. Однако если включить aqo.sandbox в разных сеансах SQL, они будут использовать одни и те же данные.
Данные, полученные в режиме «песочницы», не реплицируются. Но режим «песочницы» можно использовать на ведомом сервере. Более того, единственный способ обучить aqo на ведомом сервере — включить режим «песочницы» при включённой репликации, то есть при значении true для aqo.wal_rw. Без режима «песочницы» aqo будет работать на ведомом сервере так, как будто aqo.mode = FROZEN, то есть сможет использовать существующую базу знаний, но не сможет её обновлять или расширять.
F.2.6. Справка #
F.2.6.1. Параметры конфигурации #
aqo.enable(boolean) #Включает или отключает aqo. При значении
offaqo не работает, за исключением случаев, когда для параметра aqo.force_collect_stat установлено значениеon.По умолчанию:
off(выкл.).aqo.mode(text) #Устанавливает режим работы aqo. Возможные значения:
auto— расширение собирает статистику по всем выполняемым запросам, а также обучается и делает предсказания на основе этой статистики. Этот режим предотвращает переполнение памяти путём реализации механизма вытеснения давно неиспользуемых данных (least recently used, LRU) для очистки кеша при достижении пределов.learn— расширение работает так же, как и в режимеauto, но не использует механизм LRU для очистки кеша. В этом режиме при достижении пределов кеша aqo перестаёт обучаться.intelligent— расширение анализирует выполнение каждого запроса и собирает статистику. При этом статистика для разных классов запросов хранится отдельно. Если производительность не увеличивается после 50 итераций, расширение aqo отключается. Этот режим работает таким образом, только если для параметра aqo.advanced установлено значениеon, в противном случае работает точно так же, как и режимlearn.controlled— расширение только обучается и делает предсказания для известных запросов.frozen— расширение делает предсказания для известных запросов, но не обучается ни на каких запросах.
За подробной информацией о работе aqo во всех режимах обратитесь к разделу Использование.
По умолчанию:
auto.aqo.advanced(boolean) #Включает расширенную процедуру обучения, которая сохраняет статистику обучения отдельно для каждого класса запроса. Также позволяет настраивать параметры
use_aqoиlearn_aqoв представлении aqo_queries. После тонкой настройки параметры запроса в представленииaqo_queryпродолжают работать при выключенном параметреaqo.advanced.За подробной информацией о работе aqo во всех режимах обратитесь к разделу Использование.
По умолчанию:
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 Shardman, чтобы обеспечить согласованность с другими расширениями, такими как 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.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. При превышении этого количества поведение aqo зависит от его режима работы. За подробной информацией обратитесь к описанию базового режима. Задать этот параметр можно только при запуске сервера.
Для ведомого сервера значение этого параметра должно быть равно или больше значения на ведущем сервере. В противном случае на ведомом сервере не гарантируется согласованность данных aqo.
По умолчанию:
10000.aqo.fss_max_items(integer) #Определяет максимальное количество подпространств признаков, с которыми может работать aqo. При превышении этого количества поведение aqo зависит от его режима работы. За подробной информацией обратитесь к описанию базового режима. Задать этот параметр можно только при запуске сервера.
Для ведомого сервера значение этого параметра должно быть равно или больше значения на ведущем сервере. В противном случае на ведомом сервере не гарантируется согласованность данных aqo.
По умолчанию:
100000.aqo.querytext_max_size(integer) #Определяет максимальный размер запроса в представлении aqo_query_texts. Задать этот параметр можно только при запуске сервера.
Для ведомого сервера значение этого параметра должно быть равно или больше значения на ведущем сервере. В противном случае на ведомом сервере не гарантируется согласованность данных aqo.
По умолчанию:
1000.aqo.dsm_size_max(integer) #Определяет максимальный размер динамической разделяемой памяти, в мегабайтах, которую модуль aqo может выделить для хранения данных обучения и текстов запросов. Если установленное значение меньше объёма сохранённых данных aqo, сервер не запустится. При превышении этого количества поведение 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.6.2. Представления #
F.2.6.2.1. aqo_query_texts #
В представлении aqo_query_texts классифицируются все классы запросов, обрабатываемые aqo. Для каждого класса запросов в представлении отображается текст первого проанализированного запроса этого класса. Количество строк ограничено параметром aqo.fs_max_items.
Таблица F.1. Представление aqo_query_texts
| Имя столбца | Описание |
|---|---|
queryid | Уникальный идентификатор запроса. |
dbid | Идентификатор базы данных. |
fs | Идентификатор пространства признаков. |
query_text | Текст первого проанализированного запроса данного класса. Длина текста запроса ограничивается параметром aqo.querytext_max_size. |
F.2.6.2.2. aqo_queries #
В представлении aqo_queries отображаются свойства оптимизации для разных классов запросов. Один запрос, выполненный в двух разных базах данных, сохраняется дважды с одинаковым значением fs. Количество строк ограничено параметром aqo.fs_max_items.
Таблица F.2. Представление aqo_queries
| Свойство | Описание |
|---|---|
fs | Идентификатор пространства признаков. |
dbid | Идентификатор базы данных, в которой выполнялся запрос. |
learn_aqo | Показывает, включён ли сбор статистики для данного класса запросов. |
use_aqo | Показывает, включено ли предсказание количества строк средствами aqo для следующего выполнения данного класса запросов. |
auto_tuning | Показывает, может ли aqo динамически изменять параметры При включённом свойстве Запросы с |
smart_timeout | Значение «умного» тайм-аута операторов для данного класса запросов. Начальное значение такого тайм-аута для любого запроса определяется параметром конфигурации statement_timeout. |
count_increase_timeout | Показывает, сколько раз увеличивался «умный» тайм-аут операторов для данного класса запросов. |
F.2.6.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 | Уровень достоверности статистики обучения:
|
oids | Список идентификаторов таблиц, которые участвовали в предсказании для этого узла. |
tmpoids | Список идентификаторов временных таблиц, которые участвовали в предсказании для этого узла. |
F.2.6.2.4. aqo_query_stat #
В представлении aqo_query_stat отображается статистика выполнения запросов, группируемая по классам запросов. Расширение aqo использует эти данные, когда для определённого класса запросов включено свойство auto_tuning.
Таблица F.4. Представление aqo_query_stat
| Данные | Описание |
|---|---|
fs | Идентификатор пространства признаков. |
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.6.3. Функции #
Модуль aqo добавляет несколько функций в каталог Postgres Pro Shardman.
F.2.6.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(fsbigint,dbidoid) →voidУстанавливает для
learn_aqo,use_aqoиauto_tuning(только в режимеintelligent) значение true для класса запросов с указаннымиfsиdbid. Можно использовать дляdbidзначение NULL вместо идентификатора текущей базы данных.aqo_disable_class(fsbigint,dbidoid) →voidУстанавливает для
learn_aqo,use_aqoиauto_tuningзначение false для класса запросов с указаннымиfsиdbid. Можно использовать дляdbidзначение NULL вместо идентификатора текущей базы данных.aqo_drop_class(fsbigint,dbidoid) →integerУдаляет все данные, относящиеся к заданному классу запросов и базе данных, из хранилища aqo. Для параметра
dbidможно задать значение NULL вместо идентификатора текущей базы данных. Возвращает количество записей, удалённых из хранилища aqo.aqo_reset(dbidoid) →bigintУдаляет записи из указанной базы данных: данные машинного обучения, тексты запросов, статистику и свойства классов запросов. Если параметр
dbidне указан, данные удаляются из текущей базы данных. Еслиdbidимеет значение NULL, удаляются все записи из хранилища aqo. Возвращает количество удалённых записей.aqo_queries_update(fsbigint,dbidoid,learn_aqoboolean,use_aqoboolean,auto_tuningboolean) →booleanИзменяет или вставляет запись в файл данных, лежащий в основе представления aqo_queries, для указанных
fsиdbid. Вместо идентификатора текущей базы данных вdbidможно указать NULL. Значения NULL для остальных параметров означают, что их следует оставить без изменений. Обратите внимание, что записи с нулевым значениемfsилиdbidне могут быть изменены. Возвращаетfalseв случае ошибки иtrueв противном случае.aqo_query_texts_update(fsbigint,dbidoid,query_texttext) →booleanИзменяет или вставляет запись в файл данных, лежащий в основе представления aqo_query_texts, для указанных
fsиdbid. Можно использовать дляdbidзначение NULL вместо идентификатора текущей базы данных. Обратите внимание, что записи с нулевым значениемfsилиdbidне могут быть изменены. Возвращаетfalseв случае ошибки иtrueв противном случае.aqo_query_stat_update(fsbigint,dbidoid,execution_time_with_aqodouble precision[],execution_time_without_aqodouble precision[],planning_time_with_aqodouble precision[],planning_time_without_aqodouble precision[],cardinality_error_with_aqodouble precision[],cardinality_error_without_aqodouble precision[],executions_with_aqobigint[],executions_without_aqobigint[]) →booleanИзменяет или вставляет запись в файл данных, лежащий в основе представления aqo_query_stat, для указанных
fsиdbid. Записи для обобщенного пространства признаков, с нулевым значениемfsилиdbid, не могут быть изменены. Можно использовать дляdbidзначение NULL вместо идентификатора текущей базы данных. Возвращаетfalseв случае ошибки иtrueв противном случае.aqo_data_update(fsbigint,fssinteger,dbidoid,delta_rowsboolean,nfeaturesinteger,featuresdouble precision[][],targetsdouble precision[],reliabilitydouble precision[],oidsoid[],tmpoidsoid[]) →booleanИзменяет или вставляет запись в файл данных, лежащий в основе представления aqo_data, для указанных
fs,fssиdbid. Можно использовать дляdbidзначение NULL вместо идентификатора текущей базы данных. Если дляdelta_rowsзадать NULL, будет использовано значение параметра конфигурацииaqo.delta_rows. Возвращаетfalseв случае ошибки иtrueв противном случае.aqo_data_delete(fsbigint,fssinteger,dbidoid,delta_rowsboolean) →booleanУдаляет запись из файла данных, лежащего в основе представления aqo_data, для указанных
fs,fssиdbid. Можно использовать дляdbidзначение NULL вместо идентификатора текущей базы данных. Если не указать параметрdelta_rowsили задать для него NULL, будет использовано значение параметра конфигурацииaqo.delta_rows. Возвращаетfalseв случае ошибки иtrueв противном случае.
F.2.6.3.2. Функции управления памятью #
aqo_memory_usage() →setof recordОтображает выделенные и использованные размеры контекстов памяти и хеш-таблиц aqo. Возвращает таблицу со следующими столбцами:
nameКраткое описание контекста памяти или хеш-таблицы
allocated_sizeОбщий размер выделенной памяти
used_sizeРазмер текущей используемой памяти
F.2.6.3.3. Функции для аналитики #
aqo_cardinality_error(controlledboolean) →setof recordПоказывает ошибку оценки количества строк для последнего выполнения запроса. Если
controlledимеет значениеtrue, показывает запросы, выполненные с включённым aqo. Еслиcontrolledимеет значениеfalse, показывает запросы, выполненные с отключённым aqo, но имеющие накопленную статистику. Возвращает таблицу со следующими столбцами:numПорядковый номер
dbidИдентификатор базы данных
fsИдентификатор пространства признаков. Может содержать ноль или основное хеш-значение.
errorПогрешность aqo, рассчитываемая на узлах планов запросов
nexecsКоличество выполнений запросов, связанных с данным
fs
aqo_execution_time(controlledboolean) →setof recordПоказывает время выполнения запросов. Если
controlledимеет значение true, показывает время выполнения последнего запроса с включённым aqo. Еслиcontrolledимеет значение false, возвращает среднее время выполнения запроса для всех записанных в журнал выполнений с отключённым aqo. Время выполнения без aqo можно собрать, если параметр aqo.mode =intelligentили параметр aqo.force_collect_stat =on. Возвращает таблицу со следующими столбцами:numПорядковый номер
dbidИдентификатор базы данных
fsИдентификатор пространства признаков. Может содержать ноль или основное хеш-значение.
exec_timeЕсли
controlledимеет значение true, показывает время выполнения последнего запроса с включённым aqo, в противном случае — среднее время выполнения запроса для всех записанных в журнал выполнений с отключённым aqo.nexecsКоличество выполнений запросов, связанных с данным
fs
F.2.7. Примеры #
Пример F.1. Обучение на запросе (базовый режим)
Рассмотрим оптимизацию запроса с использованием расширения aqo.
Когда запрос выполняется в первый раз, его нет в таблицах, лежащих в основе представлений aqo. Таким образом, данных для предсказания aqo для каждого узла плана нет, и в выводе EXPLAIN появляются строки «AQO not used»:
demo=# EXPLAIN (ANALYZE, SUMMARY OFF, TIMING OFF)
SELECT bp.*
FROM segments s
JOIN flights f ON f.flight_id = s.flight_id
JOIN boarding_passes bp ON bp.ticket_no = s.ticket_no AND bp.flight_id = s.flight_id
WHERE f.scheduled_departure > '2025-12-1 15:00:00+00';
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------
Hash Join (cost=79793.72..237497.86 rows=1201002 width=33) (actual rows=9455.00 loops=1)
AQO not used, fss=4871603661380287993
Hash Cond: ((s.flight_id = f.flight_id) AND (s.ticket_no = bp.ticket_no))
Buffers: shared hit=3713 read=50331, temp read=17210 written=17210
-> Seq Scan on segments s (cost=0.00..72572.70 rows=3941270 width=18) (actual rows=3941249.00 loops=1)
AQO not used, fss=-1745942650988724053
Buffers: shared hit=1853 read=31307
-> Hash (cost=52395.69..52395.69 rows=1201002 width=37) (actual rows=9455.00 loops=1)
Buckets: 131072 Batches: 16 Memory Usage: 1058kB
Buffers: shared hit=1860 read=19024, temp written=45
-> Hash Join (cost=608.55..52395.69 rows=1201002 width=37) (actual rows=9455.00 loops=1)
AQO not used, fss=4705493075117122362
Hash Cond: (bp.flight_id = f.flight_id)
Buffers: shared hit=1860 read=19024
-> Seq Scan on boarding_passes bp (cost=0.00..45318.32 rows=2463832 width=33) (actual rows=2463832.00 loops=1)
AQO not used, fss=1362775811343989307
Buffers: shared hit=1656 read=19024
-> Hash (cost=475.98..475.98 rows=10606 width=4) (actual rows=10594.00 loops=1)
Buckets: 16384 Batches: 1 Memory Usage: 501kB
Buffers: shared hit=204
-> Seq Scan on flights f (cost=0.00..475.98 rows=10606 width=4) (actual rows=10594.00 loops=1)
AQO not used, fss=3484507337497244877
Filter: (scheduled_departure > '2025-12-01 22:00:00+07'::timestamp with time zone)
Rows Removed by Filter: 11164
Buffers: shared hit=204
Planning:
Buffers: shared hit=53
Using aqo: true
AQO mode: AUTO
AQO advanced: OFF
Query hash: 0
JOINS: 2
(32 rows)
Если в представлении aqo_data нет информации об определённом узле, aqo добавит в него соответствующую запись для дальнейшего изучения и предсказания, за исключением узлов с fss=0 в выводе EXPLAIN. Поскольку значения в полях features и targets в представлении aqo_data являются логарифмом по основанию e, чтобы получить фактическое значение, возведите e в соответствующую степень. Например: exp(9.154298981092557):
demo=# select * from aqo_data;
fs | fss | dbid | delta_rows | nfeatures | features | targets | reliability | oids | tmpoids
----+----------------------+-------+------------+-----------+---------------------------------------------+----------------------+-------------+---------------------+---------
0 | 3484507337497244877 | 16556 | f | 1 | {{-0.7185575545175223}} | {9.268043082104471} | {1} | {17452} |
0 | -1745942650988724053 | 16556 | f | 0 | | {15.187008236114766} | {1} | {17488} |
0 | 4705493075117122362 | 16556 | f | 2 | {{-0.7185575545175223,-9.987736784981028}} | {9.154298981092557} | {1} | {17452,17438} |
0 | 1362775811343989307 | 16556 | f | 0 | | {14.71722841949288} | {1} | {17438} |
0 | 4871603661380287993 | 16556 | f | 2 | {{-0.7185575545175223,-14.672062325711414}} | {9.154298981092557} | {1} | {17488,17452,17438} |
(5 rows)
При повторном выполнении запроса aqo распознаёт его и делает предсказание. Обратите внимание на оценку количества строк, предсказанную aqo, и значение погрешности aqo («error=0%»).
demo=# EXPLAIN (ANALYZE, SUMMARY OFF, TIMING OFF)
SELECT bp.*
FROM segments s
JOIN flights f ON f.flight_id = s.flight_id
JOIN boarding_passes bp ON bp.ticket_no = s.ticket_no AND bp.flight_id = s.flight_id
WHERE f.scheduled_departure > '2025-12-1 15:00:00+00';
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------
Gather (cost=1608.83..38142.58 rows=9455 width=33) (actual rows=9455.00 loops=1)
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=27340 read=22325
-> Nested Loop (cost=608.83..36197.08 rows=3940 width=33) (actual rows=3151.67 loops=3)
AQO: rows=9455, error=0%, fss=4871603661380287993
Join Filter: (s.flight_id = f.flight_id)
Buffers: shared hit=27340 read=22325
-> Hash Join (cost=608.40..34249.71 rows=3940 width=37) (actual rows=3151.67 loops=3)
AQO: rows=9455, error=0%, fss=4705493075117122362
Hash Cond: (bp.flight_id = f.flight_id)
Buffers: shared hit=2360 read=18932
-> Parallel Seq Scan on boarding_passes bp (cost=0.00..30945.97 rows=1026597 width=33) (actual rows=821277.33 loops=3)
AQO: rows=2463832, error=0%, fss=1362775811343989307
Buffers: shared hit=1748 read=18932
-> Hash (cost=475.98..475.98 rows=10594 width=4) (actual rows=10594.00 loops=3)
Buckets: 16384 Batches: 1 Memory Usage: 501kB
Buffers: shared hit=612
-> Seq Scan on flights f (cost=0.00..475.98 rows=10594 width=4) (actual rows=10594.00 loops=3)
AQO: rows=10594, error=0%, fss=3484507337497244877
Filter: (scheduled_departure > '2025-12-01 22:00:00+07'::timestamp with time zone)
Rows Removed by Filter: 11164
Buffers: shared hit=612
-> Index Only Scan using segments_pkey on segments s (cost=0.43..0.48 rows=1 width=18) (actual rows=1.00 loops=9455)
AQO not used (early terminated), fss=-5182591529139042748
Index Cond: ((ticket_no = bp.ticket_no) AND (flight_id = bp.flight_id))
Heap Fetches: 0
Index Searches: 9455
Buffers: shared hit=24980 read=3393
Planning:
Buffers: shared hit=53
Using aqo: true
AQO mode: AUTO
AQO advanced: OFF
Query hash: 0
JOINS: 2
(36 rows)
Изменив константу в запросе, можно заметить, что предсказание сделано с ошибкой:
demo=# EXPLAIN (ANALYZE, SUMMARY OFF, TIMING OFF)
SELECT bp.*
FROM segments s
JOIN flights f ON f.flight_id = s.flight_id
JOIN boarding_passes bp ON bp.ticket_no = s.ticket_no AND bp.flight_id = s.flight_id
WHERE f.scheduled_departure > '2025-11-20 15:00:00+00';
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------
Gather (cost=1608.83..38142.58 rows=9455 width=33) (actual rows=438899.00 loops=1)
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=1307156 read=30841
-> Nested Loop (cost=608.83..36197.08 rows=3940 width=33) (actual rows=146299.67 loops=3)
AQO: rows=9455, error=-4542%, fss=4871603661380287993
Join Filter: (s.flight_id = f.flight_id)
Buffers: shared hit=1307156 read=30841
-> Hash Join (cost=608.40..34249.71 rows=3940 width=37) (actual rows=146299.67 loops=3)
AQO: rows=9455, error=-4542%, fss=4705493075117122362
Hash Cond: (bp.flight_id = f.flight_id)
Buffers: shared hit=1521 read=19771
-> Parallel Seq Scan on boarding_passes bp (cost=0.00..30945.97 rows=1026597 width=33) (actual rows=821277.33 loops=3)
AQO: rows=2463832, error=0%, fss=1362775811343989307
Buffers: shared hit=909 read=19771
-> Hash (cost=475.98..475.98 rows=10594 width=4) (actual rows=12593.00 loops=3)
Buckets: 16384 Batches: 1 Memory Usage: 571kB
Buffers: shared hit=612
-> Seq Scan on flights f (cost=0.00..475.98 rows=10594 width=4) (actual rows=12593.00 loops=3)
AQO: rows=10594, error=-19%, fss=3484507337497244877
Filter: (scheduled_departure > '2025-11-20 22:00:00+07'::timestamp with time zone)
Rows Removed by Filter: 9165
Buffers: shared hit=612
-> Index Only Scan using segments_pkey on segments s (cost=0.43..0.48 rows=1 width=18) (actual rows=1.00 loops=438899)
AQO not used (early terminated), fss=-5182591529139042748
Index Cond: ((ticket_no = bp.ticket_no) AND (flight_id = bp.flight_id))
Heap Fetches: 0
Index Searches: 438899
Buffers: shared hit=1305635 read=11070
Planning:
Buffers: shared hit=53
Using aqo: true
AQO mode: AUTO
AQO advanced: OFF
Query hash: 0
JOINS: 2
(36 rows)
Однако вместо пересчёта полей features и targets, aqo добавил новые значения избирательности и оценки количества строк для этого запроса в aqo_data:
demo=# SELECT * FROM aqo_data;
fs | fss | dbid | delta_rows | nfeatures | features | targets | reliability | oids | tmpoids
----+----------------------+-------+------------+-----------+---------------------------------------------------------------------------------------+---------------------------------------+-------------+---------------------+---------
0 | 3484507337497244877 | 16556 | f | 1 | {{-0.7185575545175223},{-0.5463556163769266}} | {9.268043082104471,9.440896383005846} | {1,1} | {17452} |
0 | -1745942650988724053 | 16556 | f | 0 | | {15.187008236114766} | {1} | {17488} |
0 | 4705493075117122362 | 16556 | f | 2 | {{-0.7185575545175223,-9.987736784981028},{-0.5463556163769266,-9.987736784981028}} | {9.154298981092557,12.9920245972504} | {1,1} | {17452,17438} |
0 | 1362775811343989307 | 16556 | f | 0 | | {14.71722841949288} | {1} | {17438} |
0 | 4871603661380287993 | 16556 | f | 2 | {{-0.7185575545175223,-14.672062325711414},{-0.5463556163769266,-14.672062325711414}} | {9.154298981092557,12.9920245972504} | {1,1} | {17488,17452,17438} |
(5 rows)
Теперь в предсказании есть небольшая погрешность примерно в 2%, которая может объясняться погрешностью вычислений:
demo=# EXPLAIN (ANALYZE, SUMMARY OFF, TIMING OFF)
SELECT bp.*
FROM segments s
JOIN flights f ON f.flight_id = s.flight_id
JOIN boarding_passes bp ON bp.ticket_no = s.ticket_no AND bp.flight_id = s.flight_id
WHERE f.scheduled_departure > '2025-11-20 15:00:00+00';
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------
Gather (cost=39355.89..164831.92 rows=429336 width=33) (actual rows=438899.00 loops=1)
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=1619 read=52833, temp read=21086 written=21152
-> Parallel Hash Join (cost=38355.89..120898.32 rows=178890 width=33) (actual rows=146299.67 loops=3)
AQO: rows=429336, error=-2%, fss=4871603661380287993
Hash Cond: ((s.flight_id = f.flight_id) AND (s.ticket_no = bp.ticket_no))
Buffers: shared hit=1619 read=52833, temp read=21086 written=21152
-> Parallel Seq Scan on segments s (cost=0.00..49581.96 rows=1642187 width=18) (actual rows=1313749.67 loops=3)
AQO: rows=3941249, error=0%, fss=-1745942650988724053
Buffers: shared read=33160
-> Parallel Hash (cost=34274.54..34274.54 rows=178890 width=37) (actual rows=146299.67 loops=3)
Buckets: 131072 Batches: 8 Memory Usage: 4928kB
Buffers: shared hit=1619 read=19673, temp written=2812
-> Hash Join (cost=633.24..34274.54 rows=178890 width=37) (actual rows=146299.67 loops=3)
AQO: rows=429336, error=-2%, fss=4705493075117122362
Hash Cond: (bp.flight_id = f.flight_id)
Buffers: shared hit=1619 read=19673
-> Parallel Seq Scan on boarding_passes bp (cost=0.00..30945.97 rows=1026597 width=33) (actual rows=821277.33 loops=3)
AQO: rows=2463832, error=0%, fss=1362775811343989307
Buffers: shared hit=1007 read=19673
-> Hash (cost=475.98..475.98 rows=12581 width=4) (actual rows=12593.00 loops=3)
Buckets: 16384 Batches: 1 Memory Usage: 571kB
Buffers: shared hit=612
-> Seq Scan on flights f (cost=0.00..475.98 rows=12581 width=4) (actual rows=12593.00 loops=3)
AQO: rows=12581, error=-0%, fss=3484507337497244877
Filter: (scheduled_departure > '2025-11-20 22:00:00+07'::timestamp with time zone)
Rows Removed by Filter: 9165
Buffers: shared hit=612
Planning:
Buffers: shared hit=53
Using aqo: true
AQO mode: AUTO
AQO advanced: OFF
Query hash: 0
JOINS: 2
(36 rows)
Можно изменить запрос, добавив некоторую таблицу в список JOIN. В этом случае aqo будет прогнозировать оценку количества строк на узлах, использовавшихся для обучения.
demo=# EXPLAIN (ANALYZE, SUMMARY OFF, TIMING OFF)
SELECT bp.*
FROM segments s
JOIN flights f ON f.flight_id = s.flight_id
JOIN boarding_passes bp ON bp.ticket_no = s.ticket_no AND bp.flight_id = s.flight_id
JOIN tickets t ON t.ticket_no = s.ticket_no
WHERE f.scheduled_departure > '2025-12-1 15:00:00+00';
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------
Gather (cost=1609.40..40084.93 rows=9666 width=33) (actual rows=9455.00 loops=1)
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=53810 read=24225 written=1
-> Nested Loop (cost=609.40..38118.33 rows=4028 width=33) (actual rows=3151.67 loops=3)
AQO not used, fss=3232027643707566962
Buffers: shared hit=53810 read=24225 written=1
-> Nested Loop (cost=608.97..36240.71 rows=4028 width=47) (actual rows=3151.67 loops=3)
AQO: rows=9666, error=2%, fss=4871603661380287993
Join Filter: (s.flight_id = f.flight_id)
Buffers: shared hit=28230 read=21435
-> Hash Join (cost=608.54..34249.84 rows=4028 width=37) (actual rows=3151.67 loops=3)
AQO: rows=9666, error=2%, fss=4705493075117122362
Hash Cond: (bp.flight_id = f.flight_id)
Buffers: shared hit=1006 read=20286
-> Parallel Seq Scan on boarding_passes bp (cost=0.00..30945.97 rows=1026597 width=33) (actual rows=821277.33 loops=3)
AQO: rows=2463832, error=0%, fss=1362775811343989307
Buffers: shared hit=394 read=20286
-> Hash (cost=475.98..475.98 rows=10605 width=4) (actual rows=10594.00 loops=3)
Buckets: 16384 Batches: 1 Memory Usage: 501kB
Buffers: shared hit=612
-> Seq Scan on flights f (cost=0.00..475.98 rows=10605 width=4) (actual rows=10594.00 loops=3)
AQO: rows=10605, error=0%, fss=3484507337497244877
Filter: (scheduled_departure > '2025-12-01 22:00:00+07'::timestamp with time zone)
Rows Removed by Filter: 11164
Buffers: shared hit=612
-> Index Only Scan using segments_pkey on segments s (cost=0.43..0.48 rows=1 width=18) (actual rows=1.00 loops=9455)
AQO not used (early terminated), fss=-5182591529139042748
Index Cond: ((ticket_no = bp.ticket_no) AND (flight_id = bp.flight_id))
Heap Fetches: 0
Index Searches: 9455
Buffers: shared hit=27224 read=1149
-> Index Only Scan using tickets_pkey on tickets t (cost=0.43..0.47 rows=1 width=14) (actual rows=1.00 loops=9455)
AQO not used (early terminated), fss=1810536986390200978
Index Cond: (ticket_no = bp.ticket_no)
Heap Fetches: 0
Index Searches: 9455
Buffers: shared hit=25580 read=2790 written=1
Planning:
Buffers: shared hit=121 read=11 dirtied=1
Using aqo: true
AQO mode: AUTO
AQO advanced: OFF
Query hash: 0
JOINS: 3
(45 rows)
Пример F.2. Использование представления aqo_query_stat
В представлении aqo_query_stat отображается статистика времени планирования запросов, времени выполнения запросов и ошибок оценки количества строк. На основании этих данных вы можете принимать решения об использовании предсказаний aqo для различных классов запросов.
Обратимся к представлению aqo_query_stats:
demo=# SELECT * FROM aqo_query_stat \gx
-[ RECORD 1 ]-----------------+----------------------------------------------------------------
fs | 0
dbid | 16556
execution_time_with_aqo | {1.194791831,0.497019753,0.372696583,0.071416851}
execution_time_without_aqo | {1.194049191,1.003504607}
planning_time_with_aqo | {0.004099525,0.000548588,0.000518923,0.000545041}
planning_time_without_aqo | {0.000568455,0.000472447}
cardinality_error_with_aqo | {0.47163214679982596,0,1.5696609066434117,0.009035905503851183}
cardinality_error_without_aqo | {0.47163214679982596,1.9379745948665572}
executions_with_aqo | 4
executions_without_aqo | 2 Полученные данные относятся к запросу, рассмотренному в примере Пример F.1. Этот запрос выполнялся с каждым из параметров f.scheduled_departure > '2025-11-20 15:00:00+00' и f.scheduled_departure > '2025-12-1 15:00:00+00' по одному разу без aqo и по два раза с aqo. Видно, что с aqo погрешность оценки количества строк уменьшается до 0,009, а минимальная погрешность оценки количества строк без aqo составляет 0,471. Кроме того, время выполнения с aqo меньше, чем без него. Таким образом, можно сделать вывод, что aqo хорошо обучается на этом запросе и предсказание можно использовать для этого класса запросов.
Пример F.3. Использование aqo в расширенном режиме
Расширенный режим позволяет более гибко управлять расширением aqo. При включении данного режима, то есть
demo=# SET aqo.advanced = on;
aqo будет собирать данные машинного обучения отдельно для каждого выполняемого запроса. Например:
demo=# EXPLAIN (ANALYZE, SUMMARY OFF, TIMING OFF)
SELECT bp.*
FROM segments s
JOIN flights f ON f.flight_id = s.flight_id
JOIN boarding_passes bp ON bp.ticket_no = s.ticket_no AND bp.flight_id = s.flight_id
WHERE f.scheduled_departure > '2025-12-1 15:00:00+00';
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------
Hash Join (cost=79793.72..237497.86 rows=1201002 width=33) (actual rows=9455.00 loops=1)
AQO not used, fss=4871603661380287993
Hash Cond: ((s.flight_id = f.flight_id) AND (s.ticket_no = bp.ticket_no))
Buffers: shared hit=4958 read=49086, temp read=17210 written=17210
-> Seq Scan on segments s (cost=0.00..72572.70 rows=3941270 width=18) (actual rows=3941249.00 loops=1)
AQO not used, fss=-1745942650988724053
Buffers: shared hit=2116 read=31044
-> Hash (cost=52395.69..52395.69 rows=1201002 width=37) (actual rows=9455.00 loops=1)
Buckets: 131072 Batches: 16 Memory Usage: 1058kB
Buffers: shared hit=2842 read=18042, temp written=45
-> Hash Join (cost=608.55..52395.69 rows=1201002 width=37) (actual rows=9455.00 loops=1)
AQO not used, fss=4705493075117122362
Hash Cond: (bp.flight_id = f.flight_id)
Buffers: shared hit=2842 read=18042
-> Seq Scan on boarding_passes bp (cost=0.00..45318.32 rows=2463832 width=33) (actual rows=2463832.00 loops=1)
AQO not used, fss=1362775811343989307
Buffers: shared hit=2638 read=18042
-> Hash (cost=475.98..475.98 rows=10606 width=4) (actual rows=10594.00 loops=1)
Buckets: 16384 Batches: 1 Memory Usage: 501kB
Buffers: shared hit=204
-> Seq Scan on flights f (cost=0.00..475.98 rows=10606 width=4) (actual rows=10594.00 loops=1)
AQO not used, fss=3484507337497244877
Filter: (scheduled_departure > '2025-12-01 22:00:00+07'::timestamp with time zone)
Rows Removed by Filter: 11164
Buffers: shared hit=204
Planning:
Buffers: shared hit=463
Using aqo: true
AQO mode: AUTO
AQO advanced: ON
Query hash: 6166891552805381787
JOINS: 2
(32 rows)
Теперь этот запрос хранится в aqo_data с ненулевым fs (по умолчанию fs равен хешу запроса):
demo=# SELECT * FROM aqo_data;
fs | fss | dbid | delta_rows | nfeatures | features | targets | reliability | oids | tmpoids
---------------------+----------------------+-------+------------+-----------+---------------------------------------------+----------------------+-------------+---------------------+---------
6166891552805381787 | 4705493075117122362 | 16556 | f | 2 | {{-0.7185575545175223,-9.987736784981028}} | {9.154298981092557} | {1} | {17452,17438} |
6166891552805381787 | 4871603661380287993 | 16556 | f | 2 | {{-0.7185575545175223,-14.672062325711414}} | {9.154298981092557} | {1} | {17488,17452,17438} |
6166891552805381787 | 1362775811343989307 | 16556 | f | 0 | | {14.71722841949288} | {1} | {17438} |
6166891552805381787 | 3484507337497244877 | 16556 | f | 1 | {{-0.7185575545175223}} | {9.268043082104471} | {1} | {17452} |
6166891552805381787 | -1745942650988724053 | 16556 | f | 0 | | {15.187008236114766} | {1} | {17488} |
(5 rows)
We can make a few settings individually for this query. These are values of learn_aqo, use_aqo and auto_tuning in the aqo_queries view:
demo=# SELECT * FROM aqo_queries;
fs | dbid | learn_aqo | use_aqo | auto_tuning | smart_timeout | count_increase_timeout
---------------------+-------+-----------+---------+-------------+---------------+------------------------
6166891552805381787 | 16556 | t | t | f | 0 | 0
0 | 0 | f | f | f | 0 | 0
(2 rows)
Зададим для use_aqo значение false:
demo=# SELECT aqo_queries_update(6166891552805381787, NULL, NULL, false, NULL); aqo_queries_update -------------------- t (1 row)
Теперь изменим константу в запросе:
demo=# EXPLAIN (ANALYZE, SUMMARY OFF, TIMING OFF)
SELECT bp.*
FROM segments s
JOIN flights f ON f.flight_id = s.flight_id
JOIN boarding_passes bp ON bp.ticket_no = s.ticket_no AND bp.flight_id = s.flight_id
WHERE f.scheduled_departure > '2025-11-20 15:00:00+00';
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------
Hash Join (cost=84966.87..244434.20 rows=1426685 width=33) (actual rows=438899.00 loops=1)
AQO not used, fss=0
Hash Cond: ((s.flight_id = f.flight_id) AND (s.ticket_no = bp.ticket_no))
Buffers: shared hit=5142 read=48902, temp read=20132 written=20132
-> Seq Scan on segments s (cost=0.00..72572.70 rows=3941270 width=18) (actual rows=3941249.00 loops=1)
AQO not used, fss=0
Buffers: shared hit=2208 read=30952
-> Hash (cost=52420.60..52420.60 rows=1426685 width=37) (actual rows=438899.00 loops=1)
Buckets: 131072 Batches: 16 Memory Usage: 2925kB
Buffers: shared hit=2934 read=17950, temp written=2967
-> Hash Join (cost=633.46..52420.60 rows=1426685 width=37) (actual rows=438899.00 loops=1)
AQO not used, fss=0
Hash Cond: (bp.flight_id = f.flight_id)
Buffers: shared hit=2934 read=17950
-> Seq Scan on boarding_passes bp (cost=0.00..45318.32 rows=2463832 width=33) (actual rows=2463832.00 loops=1)
AQO not used, fss=0
Buffers: shared hit=2730 read=17950
-> Hash (cost=475.98..475.98 rows=12599 width=4) (actual rows=12593.00 loops=1)
Buckets: 16384 Batches: 1 Memory Usage: 571kB
Buffers: shared hit=204
-> Seq Scan on flights f (cost=0.00..475.98 rows=12599 width=4) (actual rows=12593.00 loops=1)
AQO not used, fss=0
Filter: (scheduled_departure > '2025-11-20 22:00:00+07'::timestamp with time zone)
Rows Removed by Filter: 9165
Buffers: shared hit=204
Planning:
Buffers: shared hit=53
Using aqo: false
AQO mode: AUTO
AQO advanced: ON
Query hash: 6166891552805381787
JOINS: 2
(32 rows)
aqo не использовался для этого запроса, но в представлении aqo_data появились новые данные:
demo=# SELECT * FROM aqo_data;
fs | fss | dbid | delta_rows | nfeatures | features | targets | reliability | oids | tmpoids
---------------------+----------------------+-------+------------+-----------+---------------------------------------------------------------------------------------+---------------------------------------+-------------+---------------------+---------
6166891552805381787 | 4705493075117122362 | 16556 | f | 2 | {{-0.7185575545175223,-9.987736784981028},{-0.5463556163769266,-9.987736784981028}} | {9.154298981092557,12.9920245972504} | {1,1} | {17452,17438} |
6166891552805381787 | 4871603661380287993 | 16556 | f | 2 | {{-0.7185575545175223,-14.672062325711414},{-0.5463556163769266,-14.672062325711414}} | {9.154298981092557,12.9920245972504} | {1,1} | {17488,17452,17438} |
6166891552805381787 | 1362775811343989307 | 16556 | f | 0 | | {14.71722841949288} | {1} | {17438} |
6166891552805381787 | 3484507337497244877 | 16556 | f | 1 | {{-0.7185575545175223},{-0.5463556163769266}} | {9.268043082104471,9.440896383005846} | {1,1} | {17452} |
6166891552805381787 | -1745942650988724053 | 16556 | f | 0 | | {15.187008236114766} | {1} | {17488} |
(5 rows)
Установленное значение параметра use_aqo не относится к другим запросам. После выполнения другого запроса дважды видно, что aqo обучается на нём и делает для него предсказание:
demo=# EXPLAIN (ANALYZE, SUMMARY OFF, TIMING OFF)
SELECT bp.*
FROM segments s
JOIN flights f ON f.flight_id = s.flight_id
JOIN boarding_passes bp ON bp.ticket_no = s.ticket_no AND bp.flight_id = s.flight_id
JOIN tickets t ON t.ticket_no = s.ticket_no
WHERE f.scheduled_departure > '2025-12-1 15:00:00+00';
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------
Gather (cost=68355.43..129435.66 rows=9455 width=33) (actual rows=9455.00 loops=1)
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=34424 read=48398, temp read=25496 written=25656
-> Nested Loop (cost=67355.43..127490.16 rows=3940 width=33) (actual rows=3151.67 loops=3)
AQO: rows=9455, error=0%, fss=3232027643707566962
Buffers: shared hit=34424 read=48398, temp read=25496 written=25656
-> Parallel Hash Join (cost=67355.00..125653.57 rows=3940 width=47) (actual rows=3151.67 loops=3)
AQO: rows=9455, error=0%, fss=4871603661380287993
Hash Cond: ((bp.flight_id = f.flight_id) AND (bp.ticket_no = s.ticket_no))
Buffers: shared hit=6286 read=48166, temp read=25496 written=25656
-> Parallel Seq Scan on boarding_passes bp (cost=0.00..30945.97 rows=1026597 width=33) (actual rows=821277.33 loops=3)
AQO: rows=2463832, error=0%, fss=1362775811343989307
Buffers: shared hit=3098 read=17582
-> Parallel Hash (cost=54501.93..54501.93 rows=616138 width=22) (actual rows=492910.33 loops=3)
Buckets: 131072 Batches: 16 Memory Usage: 6144kB
Buffers: shared hit=3188 read=30584, temp written=7556
-> Hash Join (cost=608.40..54501.93 rows=616138 width=22) (actual rows=492910.33 loops=3)
AQO: rows=1478731, error=0%, fss=4547398436029445256
Hash Cond: (s.flight_id = f.flight_id)
Buffers: shared hit=3188 read=30584
-> Parallel Seq Scan on segments s (cost=0.00..49581.96 rows=1642187 width=18) (actual rows=1313749.67 loops=3)
AQO: rows=3941249, error=0%, fss=-1745942650988724053
Buffers: shared hit=2576 read=30584
-> Hash (cost=475.98..475.98 rows=10594 width=4) (actual rows=10594.00 loops=3)
Buckets: 16384 Batches: 1 Memory Usage: 501kB
Buffers: shared hit=612
-> Seq Scan on flights f (cost=0.00..475.98 rows=10594 width=4) (actual rows=10594.00 loops=3)
AQO: rows=10594, error=0%, fss=3484507337497244877
Filter: (scheduled_departure > '2025-12-01 22:00:00+07'::timestamp with time zone)
Rows Removed by Filter: 11164
Buffers: shared hit=612
-> Index Only Scan using tickets_pkey on tickets t (cost=0.43..0.47 rows=1 width=14) (actual rows=1.00 loops=9455)
AQO not used (early terminated), fss=1810536986390200978
Index Cond: (ticket_no = bp.ticket_no)
Heap Fetches: 0
Index Searches: 9455
Buffers: shared hit=28138 read=232
Planning:
Buffers: shared hit=85
Using aqo: true
AQO mode: AUTO
AQO advanced: ON
Query hash: 5639045936347396923
JOINS: 3
(45 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 bp.* FROM segments s JOIN flights f ON f.flight_id = s.flight_id JOIN boarding_passes bp ON bp.ticket_no = s.ticket_no AND bp.flight_id = s.flight_id JOIN tickets t ON t.ticket_no = s.ticket_no WHERE f.scheduled_departure > '2025-12-1 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, delta_rows, nfeatures, features, targets, reliability, oids, tmpoids) FROM aqo_data_sandbox WHERE fs = 0; DROP TABLE aqo_data_sandbox;
F.2.8. Автор #
Олег Иванов
F.2. aqo — cost-based query optimization #
F.2.1. Description #
The aqo module is a Postgres Pro Shardman extension for cost-based query optimization. Using machine learning methods, more precisely, a modification of the k-NN algorithm, aqo improves cardinality estimation, which can optimize execution plans and, consequently, speed up query execution.
The aqo module can collect statistics on all the executed queries, excluding queries that access system relations. The collected statistics are classified by query classes. If queries differ in their constants only, they belong to the same class. For each query class, aqo stores the cardinality quality, planning time, execution time, and execution statistics for machine learning. Based on this data, aqo builds a new query plan and uses it for the next query of the same class. aqo test runs have shown significant performance improvements for complex queries.
aqo saves all the learning data (aqo_data), queries (aqo_query_texts), query settings (aqo_queries), and query execution statistics (aqo_query_stat) to files. When aqo starts, it loads this data to shared memory. You can access aqo data through functions and views.
aqo can work in basic and advanced modes. In the advanced mode, when aqo is run in the auto, learn, or intelligent operation mode, a unique hash value, which is computed from the query tree, is assigned to each query class to identify it and separate the collected statistics. In the basic mode, the statistics for all untracked query classes are stored in a common query class with a hash value that equals to 0.
Each query class has an associated separate space called feature space, in which the statistics for this query class are collected. This feature space is identified by a hash value (fs) called a basic hash, which is the same for queries that only differ in table names, so the learning data is aggregated for such queries. Each feature space has associated feature subspaces, where the information about selectivity and cardinality for each query plan node is collected. Each subspace is also identified by a hash value (fss).
F.2.2. Installation and Setup #
The aqo extension is included into Postgres Pro Shardman. Once you have Postgres Pro Shardman installed, complete the following steps to enable aqo:
Add
aqoto the shared_preload_libraries variable in thepostgresql.conffile.shared_preload_libraries = 'aqo'
The
aqolibrary must be preloaded at the server startup, since adaptive query optimization needs to be enabled per cluster.Create the aqo extension using the following query:
CREATE EXTENSION aqo;
Enable the aqo extension by setting the aqo.enable parameter to
on.ALTER SYSTEM SET aqo.enable = 'on';
Important
For smooth physical replication transferring aqo data from the primary to a replica, ensure that the same aqo versions are installed on both. You can have different aqo versions installed, but in this case, set aqo.wal_rw to off on both and anticipate no replication.
F.2.3. Disabling and Removing #
To temporarily disable aqo for all queries in the current session or for the whole cluster but do not remove or change collected statistics and settings, you can set the aqo.enable parameter to off.
ALTER SYSTEM SET aqo.enable = 'off'; SELECT pg_reload_conf();
Another way to disable aqo in the current database is to drop the extension.
DROP EXTENSION aqo;
To remove all the aqo data including the collected statistics from the current database, call the aqo_reset function as follows:
SELECT aqo_reset();
To remove all the data from the aqo storage, run the following:
SELECT aqo_reset(NULL);
If you do not want aqo to be loaded at the server restart, remove the following line from the postgresql.conf file:
shared_preload_libraries = 'aqo'
F.2.4. Limitations #
aqo currently has the following limitations:
Query optimization with aqo does not work for queries that contain
IMMUTABLEfunctions.aqo does not collect statistics on replicas because replicas are read-only. However, aqo may use query execution statistics from the primary if the replica is physical.
auto,learn, andintelligentmodes are not supposed to work for a whole cluster with queries having a dynamically generated structure because these modes store all query class IDs, which are different for all queries in such a workload. Dynamically generated constants are supported, however.
F.2.5. Usage #
aqo behavior is primarily managed using the aqo.mode and aqo.advanced configuration parameters. By default, the aqo.advanced parameter is set to off. This means that aqo works in the basic mode. When this parameter is set to on, aqo works in the advanced mode.
The exact operation mode is defined by the aqo.mode parameter. The default value is auto.
To dynamically change the operation mode in your current session, run the following command:
SET aqo.mode = 'mode';
Here mode is the name of the operation mode to use.
To switch modes at the level of a server instance, run the following:
ALTER SYSTEM SET aqo.mode = 'mode';
SELECT pg_reload_conf();
F.2.5.1. Using aqo in the Basic Mode #
In the default auto mode, aqo collects statistics on all the executed queries for plan nodes identified by fss, as well as learns and makes predictions based on these statistics. The collected machine learning data is used to correct the cardinality error for all queries whose plans contain certain plan nodes. Statistics for all query classes are stored in a common query class with a hash value (fs) equals to 0.
aqo implements the least recently used (LRU) mechanism to remove data when any of the aqo.dsm_size_max, aqo.fs_max_items, and aqo.fss_max_items limits is reached. aqo uses two distinct caches for aqo_data and aqo_query_texts. The LRU algorithm first removes data from aqo_query_texts, if any, and only then from aqo_data.
In the learn mode, aqo behaves like in the auto mode but does not use the LRU cache mechanism. In this mode, when reaching cache limits, aqo stops further learning. The selectivity and cardinality for new query plan nodes are no longer collected, and new learning data does not appear in the corresponding views.
The intelligent mode with the disabled aqo.advanced parameter works exactly like the learn mode.
It is not recommended to use the auto or learn mode permanently for a whole cluster in production because this may lead to unnecessary computational overhead and cause slight performance degradation. Execute queries that you need to optimize several times until their plans become good enough or stop changing and switch the mode to frozen or controlled. In the frozen mode, aqo makes predictions only for known queries but does not learn from any queries. Choose this mode due to lower overhead if tables involved in queries being optimized are changing rarely. Otherwise, choose the controlled mode, in which aqo makes predictions for known queries, as well as learns from them. Note that frozen and controlled modes should be used only after aqo already learned in the auto or learn mode.
The machine learning data is applied not only to the queries on which aqo learned but to all the queries whose plans contain nodes for which the statistics were collected. To prevent machine learning data from affecting other queries, use the advanced mode. Refer to the section below for details.
You can view the current query plan using the EXPLAIN command with the ANALYZE option. For details, see the Section 14.1.
F.2.5.2. Using aqo in the Advanced Mode #
If you often run queries of the same class, for example, your application limits the number of possible query classes, you can set the aqo.advanced parameter to on. In the auto (default), learn, and intelligent operation modes, aqo analyzes each query execution and stores statistics on queries of different classes separately.
aqo operation in the auto and learn modes are primarily identical. The only difference is how these modes handle cases when cache limits are reached. In the auto mode, aqo uses the LRU mechanism to remove data, while in the learn mode, aqo stops learning. For more detailed information, refer to the description of the basic mode.
To automatically identify which queries aqo can optimize, switch the operation mode to intelligent. In this mode, aqo saves new queries with the enabled auto_tuning value. See the description of the aqo_queries view for more details. If query performance is not improved after 50 optimization iterations, aqo stops working and falls back to the default query planner.
As in the basic mode, it is not recommended to use the auto, learn, or intelligent mode permanently for a whole production cluster because this may lead to overhead and slight performance degradation. After aqo learned in one of these modes, switch to the frozen or controlled mode.
The advanced mode is not suitable when queries in the workload are of multiple different classes or these classes are constantly changing. In such cases, use the basic mode instead.
F.2.5.3. Fine-Tuning aqo #
You must have superuser rights to access aqo views and configure advanced query parameters.
If data may change significantly between queries, you can enable the aqo.delta_rows parameter. In this case, aqo makes predictions based on row count estimates of the standard planner. For example, if data is deleted from a table, the planner estimates fewer rows than before. aqo can then use this updated information to predict a lower row count without requiring additional learning steps.
You can view all the processed query classes and their corresponding hash values in the aqo_query_texts view.
SELECT * FROM aqo_query_texts;
To find out a query class, that is, hash, and the operation mode, set the aqo.show_hash and aqo.show_details parameters to on and execute the query. As a result, the output contains something like this:
... Planning Time: 23.538 ms ... Execution Time: 249813.875 ms ... Using aqo: true AQO mode: LEARN AQO advanced: OFF ... Query hash: -2439501042637610315
Each query class has its own optimization settings. These settings are shown in the aqo_queries view.
SELECT * FROM aqo_queries;
You can manually change these settings to adjust the optimization for a particular query class. For example:
-- Add a new query class to the aqo_queries view: SET aqo.enable='on'; SET aqo.advanced='on'; SET aqo.mode='intelligent'; SELECT * FROM a, b WHERE a.id=b.id; SET aqo.mode='controlled'; -- Disable auto_tuning, enable both learn_aqo and use_aqo -- for this query class: SELECT count(*) FROM aqo_queries, LATERAL aqo_queries_update(fs, NULL, true, true, false) WHERE fs = (SELECT fs FROM aqo_query_texts WHERE query_text LIKE 'SELECT * FROM a, b WHERE a.id=b.id;'); -- Run EXPLAIN ANALYZE while the plan changes: EXPLAIN ANALYZE SELECT * FROM a, b WHERE a.id=b.id; EXPLAIN ANALYZE SELECT * FROM a, b WHERE a.id=b.id; -- Disable learning to stop statistics collection -- and use the optimized plan: SELECT count(*) FROM aqo_queries, LATERAL aqo_queries_update(fs, NULL, false, true, false) WHERE fs = (SELECT fs FROM aqo_query_texts WHERE query_text LIKE 'SELECT * FROM a, b WHERE a.id=b.id;');
To stop intelligent tuning for a particular query class, disable the auto_tuning field.
SELECT count(*) FROM aqo_queries, LATERAL aqo_queries_update(fs, NULL, NULL, NULL, false) WHERE fs = 'hash';
where hash is the hash value for this query class. As a result, aqo disables automatic change of the learn_aqo and use_aqo settings.
To disable further learning for a particular query class, use the following command:
SELECT count(*) FROM aqo_queries, LATERAL aqo_queries_update(fs, NULL, false, NULL, false) WHERE fs = 'hash';
where hash is the hash value for this query class.
To fully disable aqo for all queries and use the standard planner, run the following:
SELECT count(*) FROM aqo_queries, LATERAL aqo_disable_class(fs, NULL) WHERE fs <> 0;
F.2.5.4. Sandbox Mode #
You can experiment with aqo without touching its main knowledge base. To do this, execute the command:
SET aqo.sandbox = ON;
This turns on the sandbox mode, which means that aqo will work in the isolated environment. However, if you turn on aqo.sandbox in different SQL sessions, they will use the same data.
Data obtained in the sandbox mode does not get replicated. But the sandbox mode can be used on a standby. Moreover, the only way to train aqo on a standby is turning on the sandbox mode when the replication is turned on, that is, aqo.wal_rw is true. Without the sandbox mode, aqo will work on the standby as if aqo.mode = FROZEN, that is, it will be able to use the existing knowledge base, but not update or extend it.
F.2.6. Reference #
F.2.6.1. Configuration Parameters #
aqo.enable(boolean) #Enables or disables aqo. If set to
off, aqo does not work except when the aqo.force_collect_stat parameter is set toon.Default:
off.aqo.mode(text) #Sets the aqo operation mode. Possible values:
auto— collects statistics on all the executed queries, as well as learns and makes predictions based on these statistics. This mode prevents memory overflow by implementing the least recently used (LRU) mechanism to clear caches when reaching limits.learn— behaves like theautomode but does not use the LRU cache mechanism. In this mode, when reaching cache limits, aqo stops learning.intelligent— analyzes each query execution and stores statistics. Statistics on queries of different classes are stored separately. If performance is not improved after 50 iterations, aqo is disabled. This mode works in this way only if the aqo.advanced parameter is set toon, otherwise, it works exactly like thelearnmode.controlled— only learns and makes predictions for known queries.frozen— makes predictions for known queries but does not learn from any queries.
For detailed information about aqo work in all modes, refer to Usage.
Default:
auto.aqo.advanced(boolean) #Enables the advanced learning routine, which saves separate learning statistics for each query class. Also allows fine-tuning the
use_aqoandlearn_aqosettings in the aqo_queries view. Fine-tuned query settings in theaqo_queryview continue to work ifaqo.advancedis disabled.For detailed information about aqo work in all modes, refer to Usage.
Default:
off.aqo.force_collect_stat(boolean) #Collects statistics on query executions in all aqo modes and even if the
aqo.enableparameter is set tooff.Default:
off.aqo.show_details(boolean) #Adds some details to
EXPLAINoutput of a query, such as the prediction or feature-subspace hash, and shows some additional aqo-specific on-screen information.Default:
on.aqo.show_hash(boolean) #Shows a hash value that uniquely identifies the class of queries or class of plan nodes. aqo uses the native query ID to identify a query class for consistency with other extensions, such as pg_stat_statements. So, the query ID can be taken from the
Query hashfield inEXPLAIN ANALYZEoutput of a query.Default:
on.aqo.join_threshold(integer) #Ignores queries that contain smaller number of joins, which means that statistics for such queries is not collected.
Default:
0(no queries are ignored).aqo.learn_statement_timeout(boolean) #Learns on a plan interrupted by the statement timeout.
Default:
off.aqo.statement_timeout(integer) #Defines the initial value of the smart statement timeout, in milliseconds, which is needed to limit the execution time when manually training aqo on special queries with a poor cardinality forecast. aqo can dynamically change the value of the smart statement timeout during this training. When the cardinality estimation error on nodes exceeds 0.1, the value of
aqo.statement_timeoutis automatically incremented exponentially, but remains not greater than statement_timeout.Default:
0.aqo.min_neighbors_for_predicting(integer) #Defines how many samples collected in previous executions of the query will be used to predict the cardinality next time. If there are fewer of them, aqo will not make any prediction. A too large value may affect performance, but a too small value may reduce the prediction quality.
Default:
3.aqo.predict_with_few_neighbors(boolean) #Enables aqo to make predictions with fewer neighbors than specified by aqo.min_neighbors_for_predicting. When set to
off, then aqo learns, but does not make predictions until the execution count for the query with different constants reaches 3 (default foraqo.min_neighbors_for_predicting).Default:
on.aqo.fs_max_items(integer) #Defines the maximum number of feature spaces that aqo can operate with. When this limit is exceeded, aqo behavior depends on its operation mode. Refer to the description of the basic mode for details. This parameter can only be set at server start.
When running a standby server, you must set this parameter to have the same or higher value as on the primary server. Otherwise, consistency of aqo data on the standby server is not guaranteed.
Default:
10000.aqo.fss_max_items(integer) #Defines the maximum number of feature subspaces that aqo can operate with. When this limit is exceeded, aqo behavior depends on its operation mode. Refer to the description of the basic mode for details. This parameter can only be set at server start.
When running a standby server, you must set this parameter to have the same or higher value as on the primary server. Otherwise, consistency of aqo data on the standby server is not guaranteed.
Default:
100000.aqo.querytext_max_size(integer) #Defines the maximum size of the query in the aqo_query_texts view. This parameter can only be set at server start.
When running a standby server, you must set this parameter to have the same or higher value as on the primary server. Otherwise, consistency of aqo data on the standby server is not guaranteed.
Default:
1000.aqo.dsm_size_max(integer) #Defines the maximum size of dynamic shared memory, in megabytes, that aqo can allocate to store learning data and query texts. If set to a number that is less than the size of the saved aqo data, the server will not start. When this limit is exceeded, aqo behavior depends on its operation mode. Refer to the description of the basic mode for details. This parameter can only be set at server start.
When running a standby server, you must set this parameter to have the same or higher value as on the primary server. Otherwise, consistency of aqo data on the standby server is not guaranteed.
Default:
100.aqo.wal_rw(boolean) #Enables physical replication and allows complete aqo data recovery after failure. When set to
offon the primary, no data is transferred from it to a replica. When set tooffon a replica, any data transferred from the primary is ignored. With this value, when the server fails, data can only be restored as of the last checkpoint. This parameter can only be set at server start.Default:
on.aqo.sandbox(boolean) #Enables reserving a separate memory area in shared memory to be used by a primary or standby node, which allows collecting and using statistics with the data in this memory area. If enabled on the primary, the extension uses the separate shared memory area that is not replicated to the standby. Changing the value of this parameter resets the aqo cache. Only superusers can change this setting.
Default:
off.aqo.delta_rows(boolean) #Enables a learning mechanism where aqo adjusts the planner's row count estimates with its own predictions. If disabled, aqo uses its own predictions.
Default:
off.
F.2.6.2. Views #
F.2.6.2.1. aqo_query_texts #
The aqo_query_texts view classifies all the query classes processed by aqo. For each query class, the view shows the text of the first analyzed query of this class. The number of rows is limited by aqo.fs_max_items.
Table F.1. aqo_query_texts View
| Column Name | Description |
|---|---|
queryid | The unique identifier of the query. |
dbid | The identifier of the database. |
fs | The identifier of the feature space. |
query_text | Text of the first analyzed query of the given class. The query text length is limited by aqo.querytext_max_size. |
F.2.6.2.2. aqo_queries #
The aqo_queries view shows optimization settings for different query classes. One query executed in two different databases is stored twice although the fs is the same. The number of rows is limited by aqo.fs_max_items.
Table F.2. aqo_queries View
| Setting | Description |
|---|---|
fs | The identifier of the feature space. |
dbid | The identifier of the database in which the query was executed. |
learn_aqo | Shows whether statistics collection for this query class is enabled. |
use_aqo | Shows whether the aqo cardinality prediction for the next execution of this query class is enabled. |
auto_tuning | Shows whether aqo can dynamically change When For queries with |
smart_timeout | The value of the smart statement timeout for this query class. The initial value of the smart statement timeout for any query is defined by the statement_timeout configuration parameter. |
count_increase_timeout | Shows how many times the smart statement timeout increased for this query class. |
F.2.6.2.3. aqo_data #
The aqo_data view shows machine learning data for cardinality estimation refinement. The number of rows is limited by aqo.fss_max_items. To discard all the collected statistics for a particular query class, you can delete all rows from aqo_data with the corresponding fs.
Table F.3. aqo_data View
| Data | Description |
|---|---|
fs | The identifier (hash) of the feature space. |
fss | The identifier (hash) of the feature subspace. |
dbid | The identifier of the database. |
delta_rows | If true, aqo makes predictions based on the planner's estimates, otherwise false. |
nfeatures | Feature-subspace size for the query plan node. |
features | Logarithm of the selectivity which the cardinality prediction is based on. |
targets | Cardinality logarithm for the query plan node. |
reliability | Confidence level of the learning statistics. Equals:
|
oids | List of IDs of tables that were involved in the prediction for this node. |
tmpoids | List of IDs of temporary tables that were involved in the prediction for this node. |
F.2.6.2.4. aqo_query_stat #
The aqo_query_stat view shows statistics on query execution, by query class. aqo uses this data when auto_tuning is enabled for a particular query class.
Table F.4. aqo_query_stat View
| Data | Description |
|---|---|
fs | The identifier of the feature space. |
dbid | The identifier of the database. |
execution_time_with_aqo | Array of execution times for queries run with aqo enabled. |
execution_time_without_aqo | Array of execution times for queries run with aqo disabled. |
planning_time_with_aqo | Array of planning times for queries run with aqo enabled. |
planning_time_without_aqo | Array of planning times for queries run with aqo disabled. |
cardinality_error_with_aqo | Array of cardinality estimation errors in the selected query plans with aqo enabled. |
cardinality_error_without_aqo | Array of cardinality estimation errors in the selected query plans with aqo disabled. |
executions_with_aqo | Number of queries run with aqo enabled. |
executions_without_aqo | Number of queries run with aqo disabled. |
F.2.6.3. Functions #
aqo adds several functions to Postgres Pro Shardman catalog.
F.2.6.3.1. Storage Management Functions #
Important
Functions aqo_queries_update, aqo_query_texts_update, aqo_query_stat_update, aqo_data_update and aqo_data_delete modify data files underlying aqo views. Therefore, call these functions only if you understand the logic of adaptive query optimization.
aqo_cleanup() →setof integerRemoves data related to query classes that are linked (may be partially) with removed relations. Returns the number of removed feature spaces (classes) and feature subspaces. Insensitive to removing other objects.
aqo_enable_class(fsbigint,dbidoid) →voidSets
learn_aqo,use_aqoandauto_tuning(only in theintelligentmode) to true for the query class with the specifiedfsanddbid. You can setdbidto NULL instead of the ID of the current database.aqo_disable_class(fsbigint,dbidoid) →voidSets
learn_aqo,use_aqoandauto_tuningto false for the query class with the specifiedfsanddbid. You can setdbidto NULL instead of the ID of the current database.aqo_drop_class(fsbigint,dbidoid) →integerRemoves all data related to the specified query class and database from the aqo storage. You can set
dbidto NULL instead of the ID of the current database. Returns the number of records removed from the aqo storage.aqo_reset(dbidoid) →bigintRemoves records from the specified database: machine learning data, query texts, statistics and query class preferences. If
dbidis omitted, removes the data from the current database. Ifdbidis NULL, removes all records from the aqo storage. Returns the number of records removed.aqo_queries_update(fsbigint,dbidoid,learn_aqoboolean,use_aqoboolean,auto_tuningboolean) →booleanUpdates or inserts a record in a data file underlying the aqo_queries view for the specified
fsanddbid. You can setdbidto NULL instead of the ID of the current database. NULL values for parameters being set mean leave them as is. Note that records with a zero value offsordbidcannot be updated. Returnsfalsein case of error,trueotherwise.aqo_query_texts_update(fsbigint,dbidoid,query_texttext) →booleanUpdates or inserts a record in a data file underlying the aqo_query_texts view for the specified
fsanddbid. You can setdbidto NULL instead of the ID of the current database. Note that records with a zero value offsordbidcannot be updated. Returnsfalsein case of error,trueotherwise.aqo_query_stat_update(fsbigint,dbidoid,execution_time_with_aqodouble precision[],execution_time_without_aqodouble precision[],planning_time_with_aqodouble precision[],planning_time_without_aqodouble precision[],cardinality_error_with_aqodouble precision[],cardinality_error_without_aqodouble precision[],executions_with_aqobigint[],executions_without_aqobigint[]) →booleanUpdates or inserts a record in a data file underlying the aqo_query_stat view for the specified
fsanddbid. You cannot update a record for a common feature space, i.e., forfsordbidequal to zero. You can setdbidto NULL instead of the ID of the current database. Returnsfalsein case of error,trueotherwise.aqo_data_update(fsbigint,fssinteger,dbidoid,delta_rowsboolean,nfeaturesinteger,featuresdouble precision[][],targetsdouble precision[],reliabilitydouble precision[],oidsoid[],tmpoidsoid[]) →booleanUpdates or inserts a record in a data file underlying the aqo_data view for the specified
fs,fssanddbid. You can setdbidto NULL instead of the ID of the current database. If you setdelta_rowsto NULL, the value ofaqo.delta_rowsis used. Returnsfalsein case of error,trueotherwise.aqo_data_delete(fsbigint,fssinteger,dbidoid,delta_rowsboolean) →booleanRemoves a record from a data file underlying the aqo_data view for the specified
fs,fssanddbid. You can setdbidto NULL instead of the ID of the current database. If you omitdelta_rowsor set it to NULL, the value ofaqo.delta_rowsis used. Returnsfalsein case of error,trueotherwise.
F.2.6.3.2. Memory Management Functions #
aqo_memory_usage() →setof recordShows allocated and used sizes of aqo memory contexts and hash tables. Returns a table:
nameShort description of the memory context or hash table
allocated_sizeTotal size of the allocated memory
used_sizeSize of the currently used memory
F.2.6.3.3. Functions for Analytics #
aqo_cardinality_error(controlledboolean) →setof recordShows the cardinality error for the last execution of queries. If
controlledis true, shows queries executed with aqo enabled. Ifcontrolledis false, shows queries that were executed with aqo disabled, but that have collected aqo statistics. Returns a table:numSequential number
dbidThe identifier of the database
fsThe identifier of the feature space. Can be zero or the basic hash.
erroraqo error calculated on query plan nodes
nexecsNumber of executions of queries associated with this
fs
aqo_execution_time(controlledboolean) →setof recordShows the execution time for queries. If
controlledis true, shows the execution time of the last execution with aqo enabled. Ifcontrolledis false, returns the average execution time for all logged executions with aqo disabled. Execution time without aqo can be collected when aqo.mode =intelligentor aqo.force_collect_stat =on. Returns a table:numSequential number
dbidThe identifier of the database
fsThe identifier of the feature space. Can be zero or the basic hash.
exec_timeIf
controlled= true, last query execution time with aqo, otherwise, average execution time for all executions without aqonexecsNumber of executions of queries associated with this
fs
F.2.7. Examples #
Example F.1. Learning on a Query (Basic Mode)
Consider optimization of a query using aqo.
When the query is executed for the first time, it is missing in tables underlying aqo views. So there is no data for predicting with aqo for each plan node, and “AQO not used” lines appear in the EXPLAIN output:
demo=# EXPLAIN (ANALYZE, SUMMARY OFF, TIMING OFF)
SELECT bp.*
FROM segments s
JOIN flights f ON f.flight_id = s.flight_id
JOIN boarding_passes bp ON bp.ticket_no = s.ticket_no AND bp.flight_id = s.flight_id
WHERE f.scheduled_departure > '2025-12-1 15:00:00+00';
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------
Hash Join (cost=79793.72..237497.86 rows=1201002 width=33) (actual rows=9455.00 loops=1)
AQO not used, fss=4871603661380287993
Hash Cond: ((s.flight_id = f.flight_id) AND (s.ticket_no = bp.ticket_no))
Buffers: shared hit=3713 read=50331, temp read=17210 written=17210
-> Seq Scan on segments s (cost=0.00..72572.70 rows=3941270 width=18) (actual rows=3941249.00 loops=1)
AQO not used, fss=-1745942650988724053
Buffers: shared hit=1853 read=31307
-> Hash (cost=52395.69..52395.69 rows=1201002 width=37) (actual rows=9455.00 loops=1)
Buckets: 131072 Batches: 16 Memory Usage: 1058kB
Buffers: shared hit=1860 read=19024, temp written=45
-> Hash Join (cost=608.55..52395.69 rows=1201002 width=37) (actual rows=9455.00 loops=1)
AQO not used, fss=4705493075117122362
Hash Cond: (bp.flight_id = f.flight_id)
Buffers: shared hit=1860 read=19024
-> Seq Scan on boarding_passes bp (cost=0.00..45318.32 rows=2463832 width=33) (actual rows=2463832.00 loops=1)
AQO not used, fss=1362775811343989307
Buffers: shared hit=1656 read=19024
-> Hash (cost=475.98..475.98 rows=10606 width=4) (actual rows=10594.00 loops=1)
Buckets: 16384 Batches: 1 Memory Usage: 501kB
Buffers: shared hit=204
-> Seq Scan on flights f (cost=0.00..475.98 rows=10606 width=4) (actual rows=10594.00 loops=1)
AQO not used, fss=3484507337497244877
Filter: (scheduled_departure > '2025-12-01 22:00:00+07'::timestamp with time zone)
Rows Removed by Filter: 11164
Buffers: shared hit=204
Planning:
Buffers: shared hit=53
Using aqo: true
AQO mode: AUTO
AQO advanced: OFF
Query hash: 0
JOINS: 2
(32 rows)
If there is no information on a certain node in the aqo_data view, aqo will add the appropriate record there for future learning and predictions except for nodes with fss=0 in the EXPLAIN output. As each of features and targets in the aqo_data view is a logarithm to base e, to get the actual value, raise e to this power. For example: exp(9.154298981092557):
demo=# select * from aqo_data;
fs | fss | dbid | delta_rows | nfeatures | features | targets | reliability | oids | tmpoids
----+----------------------+-------+------------+-----------+---------------------------------------------+----------------------+-------------+---------------------+---------
0 | 3484507337497244877 | 16556 | f | 1 | {{-0.7185575545175223}} | {9.268043082104471} | {1} | {17452} |
0 | -1745942650988724053 | 16556 | f | 0 | | {15.187008236114766} | {1} | {17488} |
0 | 4705493075117122362 | 16556 | f | 2 | {{-0.7185575545175223,-9.987736784981028}} | {9.154298981092557} | {1} | {17452,17438} |
0 | 1362775811343989307 | 16556 | f | 0 | | {14.71722841949288} | {1} | {17438} |
0 | 4871603661380287993 | 16556 | f | 2 | {{-0.7185575545175223,-14.672062325711414}} | {9.154298981092557} | {1} | {17488,17452,17438} |
(5 rows)
When the query is executed for the second time, aqo recognizes the query and makes a prediction. Pay attention to the cardinality predicted by aqo and the value of aqo error (“error=0%”).
demo=# EXPLAIN (ANALYZE, SUMMARY OFF, TIMING OFF)
SELECT bp.*
FROM segments s
JOIN flights f ON f.flight_id = s.flight_id
JOIN boarding_passes bp ON bp.ticket_no = s.ticket_no AND bp.flight_id = s.flight_id
WHERE f.scheduled_departure > '2025-12-1 15:00:00+00';
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------
Gather (cost=1608.83..38142.58 rows=9455 width=33) (actual rows=9455.00 loops=1)
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=27340 read=22325
-> Nested Loop (cost=608.83..36197.08 rows=3940 width=33) (actual rows=3151.67 loops=3)
AQO: rows=9455, error=0%, fss=4871603661380287993
Join Filter: (s.flight_id = f.flight_id)
Buffers: shared hit=27340 read=22325
-> Hash Join (cost=608.40..34249.71 rows=3940 width=37) (actual rows=3151.67 loops=3)
AQO: rows=9455, error=0%, fss=4705493075117122362
Hash Cond: (bp.flight_id = f.flight_id)
Buffers: shared hit=2360 read=18932
-> Parallel Seq Scan on boarding_passes bp (cost=0.00..30945.97 rows=1026597 width=33) (actual rows=821277.33 loops=3)
AQO: rows=2463832, error=0%, fss=1362775811343989307
Buffers: shared hit=1748 read=18932
-> Hash (cost=475.98..475.98 rows=10594 width=4) (actual rows=10594.00 loops=3)
Buckets: 16384 Batches: 1 Memory Usage: 501kB
Buffers: shared hit=612
-> Seq Scan on flights f (cost=0.00..475.98 rows=10594 width=4) (actual rows=10594.00 loops=3)
AQO: rows=10594, error=0%, fss=3484507337497244877
Filter: (scheduled_departure > '2025-12-01 22:00:00+07'::timestamp with time zone)
Rows Removed by Filter: 11164
Buffers: shared hit=612
-> Index Only Scan using segments_pkey on segments s (cost=0.43..0.48 rows=1 width=18) (actual rows=1.00 loops=9455)
AQO not used (early terminated), fss=-5182591529139042748
Index Cond: ((ticket_no = bp.ticket_no) AND (flight_id = bp.flight_id))
Heap Fetches: 0
Index Searches: 9455
Buffers: shared hit=24980 read=3393
Planning:
Buffers: shared hit=53
Using aqo: true
AQO mode: AUTO
AQO advanced: OFF
Query hash: 0
JOINS: 2
(36 rows)
Let's change a constant in the query, and you will notice that the prediction is made with an error:
demo=# EXPLAIN (ANALYZE, SUMMARY OFF, TIMING OFF)
SELECT bp.*
FROM segments s
JOIN flights f ON f.flight_id = s.flight_id
JOIN boarding_passes bp ON bp.ticket_no = s.ticket_no AND bp.flight_id = s.flight_id
WHERE f.scheduled_departure > '2025-11-20 15:00:00+00';
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------
Gather (cost=1608.83..38142.58 rows=9455 width=33) (actual rows=438899.00 loops=1)
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=1307156 read=30841
-> Nested Loop (cost=608.83..36197.08 rows=3940 width=33) (actual rows=146299.67 loops=3)
AQO: rows=9455, error=-4542%, fss=4871603661380287993
Join Filter: (s.flight_id = f.flight_id)
Buffers: shared hit=1307156 read=30841
-> Hash Join (cost=608.40..34249.71 rows=3940 width=37) (actual rows=146299.67 loops=3)
AQO: rows=9455, error=-4542%, fss=4705493075117122362
Hash Cond: (bp.flight_id = f.flight_id)
Buffers: shared hit=1521 read=19771
-> Parallel Seq Scan on boarding_passes bp (cost=0.00..30945.97 rows=1026597 width=33) (actual rows=821277.33 loops=3)
AQO: rows=2463832, error=0%, fss=1362775811343989307
Buffers: shared hit=909 read=19771
-> Hash (cost=475.98..475.98 rows=10594 width=4) (actual rows=12593.00 loops=3)
Buckets: 16384 Batches: 1 Memory Usage: 571kB
Buffers: shared hit=612
-> Seq Scan on flights f (cost=0.00..475.98 rows=10594 width=4) (actual rows=12593.00 loops=3)
AQO: rows=10594, error=-19%, fss=3484507337497244877
Filter: (scheduled_departure > '2025-11-20 22:00:00+07'::timestamp with time zone)
Rows Removed by Filter: 9165
Buffers: shared hit=612
-> Index Only Scan using segments_pkey on segments s (cost=0.43..0.48 rows=1 width=18) (actual rows=1.00 loops=438899)
AQO not used (early terminated), fss=-5182591529139042748
Index Cond: ((ticket_no = bp.ticket_no) AND (flight_id = bp.flight_id))
Heap Fetches: 0
Index Searches: 438899
Buffers: shared hit=1305635 read=11070
Planning:
Buffers: shared hit=53
Using aqo: true
AQO mode: AUTO
AQO advanced: OFF
Query hash: 0
JOINS: 2
(36 rows)
However, instead of recalculating features and targets, aqo added new values of selectivity and cardinality for this query to aqo_data:
demo=# SELECT * FROM aqo_data;
fs | fss | dbid | delta_rows | nfeatures | features | targets | reliability | oids | tmpoids
----+----------------------+-------+------------+-----------+---------------------------------------------------------------------------------------+---------------------------------------+-------------+---------------------+---------
0 | 3484507337497244877 | 16556 | f | 1 | {{-0.7185575545175223},{-0.5463556163769266}} | {9.268043082104471,9.440896383005846} | {1,1} | {17452} |
0 | -1745942650988724053 | 16556 | f | 0 | | {15.187008236114766} | {1} | {17488} |
0 | 4705493075117122362 | 16556 | f | 2 | {{-0.7185575545175223,-9.987736784981028},{-0.5463556163769266,-9.987736784981028}} | {9.154298981092557,12.9920245972504} | {1,1} | {17452,17438} |
0 | 1362775811343989307 | 16556 | f | 0 | | {14.71722841949288} | {1} | {17438} |
0 | 4871603661380287993 | 16556 | f | 2 | {{-0.7185575545175223,-14.672062325711414},{-0.5463556163769266,-14.672062325711414}} | {9.154298981092557,12.9920245972504} | {1,1} | {17488,17452,17438} |
(5 rows)
Now the prediction has a small error of about 2%, which can be explained by a calculation error:
demo=# EXPLAIN (ANALYZE, SUMMARY OFF, TIMING OFF)
SELECT bp.*
FROM segments s
JOIN flights f ON f.flight_id = s.flight_id
JOIN boarding_passes bp ON bp.ticket_no = s.ticket_no AND bp.flight_id = s.flight_id
WHERE f.scheduled_departure > '2025-11-20 15:00:00+00';
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------
Gather (cost=39355.89..164831.92 rows=429336 width=33) (actual rows=438899.00 loops=1)
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=1619 read=52833, temp read=21086 written=21152
-> Parallel Hash Join (cost=38355.89..120898.32 rows=178890 width=33) (actual rows=146299.67 loops=3)
AQO: rows=429336, error=-2%, fss=4871603661380287993
Hash Cond: ((s.flight_id = f.flight_id) AND (s.ticket_no = bp.ticket_no))
Buffers: shared hit=1619 read=52833, temp read=21086 written=21152
-> Parallel Seq Scan on segments s (cost=0.00..49581.96 rows=1642187 width=18) (actual rows=1313749.67 loops=3)
AQO: rows=3941249, error=0%, fss=-1745942650988724053
Buffers: shared read=33160
-> Parallel Hash (cost=34274.54..34274.54 rows=178890 width=37) (actual rows=146299.67 loops=3)
Buckets: 131072 Batches: 8 Memory Usage: 4928kB
Buffers: shared hit=1619 read=19673, temp written=2812
-> Hash Join (cost=633.24..34274.54 rows=178890 width=37) (actual rows=146299.67 loops=3)
AQO: rows=429336, error=-2%, fss=4705493075117122362
Hash Cond: (bp.flight_id = f.flight_id)
Buffers: shared hit=1619 read=19673
-> Parallel Seq Scan on boarding_passes bp (cost=0.00..30945.97 rows=1026597 width=33) (actual rows=821277.33 loops=3)
AQO: rows=2463832, error=0%, fss=1362775811343989307
Buffers: shared hit=1007 read=19673
-> Hash (cost=475.98..475.98 rows=12581 width=4) (actual rows=12593.00 loops=3)
Buckets: 16384 Batches: 1 Memory Usage: 571kB
Buffers: shared hit=612
-> Seq Scan on flights f (cost=0.00..475.98 rows=12581 width=4) (actual rows=12593.00 loops=3)
AQO: rows=12581, error=-0%, fss=3484507337497244877
Filter: (scheduled_departure > '2025-11-20 22:00:00+07'::timestamp with time zone)
Rows Removed by Filter: 9165
Buffers: shared hit=612
Planning:
Buffers: shared hit=53
Using aqo: true
AQO mode: AUTO
AQO advanced: OFF
Query hash: 0
JOINS: 2
(36 rows)
We can modify the query by adding some table to the JOIN list. In this case, aqo will predict the cardinality of nodes on which it learned before.
demo=# EXPLAIN (ANALYZE, SUMMARY OFF, TIMING OFF)
SELECT bp.*
FROM segments s
JOIN flights f ON f.flight_id = s.flight_id
JOIN boarding_passes bp ON bp.ticket_no = s.ticket_no AND bp.flight_id = s.flight_id
JOIN tickets t ON t.ticket_no = s.ticket_no
WHERE f.scheduled_departure > '2025-12-1 15:00:00+00';
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------
Gather (cost=1609.40..40084.93 rows=9666 width=33) (actual rows=9455.00 loops=1)
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=53810 read=24225 written=1
-> Nested Loop (cost=609.40..38118.33 rows=4028 width=33) (actual rows=3151.67 loops=3)
AQO not used, fss=3232027643707566962
Buffers: shared hit=53810 read=24225 written=1
-> Nested Loop (cost=608.97..36240.71 rows=4028 width=47) (actual rows=3151.67 loops=3)
AQO: rows=9666, error=2%, fss=4871603661380287993
Join Filter: (s.flight_id = f.flight_id)
Buffers: shared hit=28230 read=21435
-> Hash Join (cost=608.54..34249.84 rows=4028 width=37) (actual rows=3151.67 loops=3)
AQO: rows=9666, error=2%, fss=4705493075117122362
Hash Cond: (bp.flight_id = f.flight_id)
Buffers: shared hit=1006 read=20286
-> Parallel Seq Scan on boarding_passes bp (cost=0.00..30945.97 rows=1026597 width=33) (actual rows=821277.33 loops=3)
AQO: rows=2463832, error=0%, fss=1362775811343989307
Buffers: shared hit=394 read=20286
-> Hash (cost=475.98..475.98 rows=10605 width=4) (actual rows=10594.00 loops=3)
Buckets: 16384 Batches: 1 Memory Usage: 501kB
Buffers: shared hit=612
-> Seq Scan on flights f (cost=0.00..475.98 rows=10605 width=4) (actual rows=10594.00 loops=3)
AQO: rows=10605, error=0%, fss=3484507337497244877
Filter: (scheduled_departure > '2025-12-01 22:00:00+07'::timestamp with time zone)
Rows Removed by Filter: 11164
Buffers: shared hit=612
-> Index Only Scan using segments_pkey on segments s (cost=0.43..0.48 rows=1 width=18) (actual rows=1.00 loops=9455)
AQO not used (early terminated), fss=-5182591529139042748
Index Cond: ((ticket_no = bp.ticket_no) AND (flight_id = bp.flight_id))
Heap Fetches: 0
Index Searches: 9455
Buffers: shared hit=27224 read=1149
-> Index Only Scan using tickets_pkey on tickets t (cost=0.43..0.47 rows=1 width=14) (actual rows=1.00 loops=9455)
AQO not used (early terminated), fss=1810536986390200978
Index Cond: (ticket_no = bp.ticket_no)
Heap Fetches: 0
Index Searches: 9455
Buffers: shared hit=25580 read=2790 written=1
Planning:
Buffers: shared hit=121 read=11 dirtied=1
Using aqo: true
AQO mode: AUTO
AQO advanced: OFF
Query hash: 0
JOINS: 3
(45 rows)
Example F.2. Using the aqo_query_stat View
The aqo_query_stats view shows statistics on the query planning time, query execution time and cardinality error. Based on this data you can make a decision whether to use aqo predictions for different query classes.
Let's query the aqo_query_stats view:
demo=# SELECT * FROM aqo_query_stat \gx
-[ RECORD 1 ]-----------------+----------------------------------------------------------------
fs | 0
dbid | 16556
execution_time_with_aqo | {1.194791831,0.497019753,0.372696583,0.071416851}
execution_time_without_aqo | {1.194049191,1.003504607}
planning_time_with_aqo | {0.004099525,0.000548588,0.000518923,0.000545041}
planning_time_without_aqo | {0.000568455,0.000472447}
cardinality_error_with_aqo | {0.47163214679982596,0,1.5696609066434117,0.009035905503851183}
cardinality_error_without_aqo | {0.47163214679982596,1.9379745948665572}
executions_with_aqo | 4
executions_without_aqo | 2
The retrieved data is for the query from Example F.1, which was executed once without aqo for each of the parameters f.scheduled_departure > '2025-11-20 15:00:00+00' and f.scheduled_departure > '2025-12-1 15:00:00+00' and twice with aqo for each of these parameters. It is clear that with aqo, the cardinality error decreases to 0.009, while the minimum cardinality error without aqo is 0.471. Besides, the execution time with aqo is lower than without it. So the conclusion is that aqo learns well on this query, and the prediction can be used for this query class.
Example F.3. Using aqo in the Advanced Mode
The advanced mode allows a more flexible control over aqo. When this mode is activated, that is,
demo=# SET aqo.advanced = on;
aqo will collect the machine learning data separately for each query executed. For example:
demo=# EXPLAIN (ANALYZE, SUMMARY OFF, TIMING OFF)
SELECT bp.*
FROM segments s
JOIN flights f ON f.flight_id = s.flight_id
JOIN boarding_passes bp ON bp.ticket_no = s.ticket_no AND bp.flight_id = s.flight_id
WHERE f.scheduled_departure > '2025-12-1 15:00:00+00';
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------
Hash Join (cost=79793.72..237497.86 rows=1201002 width=33) (actual rows=9455.00 loops=1)
AQO not used, fss=4871603661380287993
Hash Cond: ((s.flight_id = f.flight_id) AND (s.ticket_no = bp.ticket_no))
Buffers: shared hit=4958 read=49086, temp read=17210 written=17210
-> Seq Scan on segments s (cost=0.00..72572.70 rows=3941270 width=18) (actual rows=3941249.00 loops=1)
AQO not used, fss=-1745942650988724053
Buffers: shared hit=2116 read=31044
-> Hash (cost=52395.69..52395.69 rows=1201002 width=37) (actual rows=9455.00 loops=1)
Buckets: 131072 Batches: 16 Memory Usage: 1058kB
Buffers: shared hit=2842 read=18042, temp written=45
-> Hash Join (cost=608.55..52395.69 rows=1201002 width=37) (actual rows=9455.00 loops=1)
AQO not used, fss=4705493075117122362
Hash Cond: (bp.flight_id = f.flight_id)
Buffers: shared hit=2842 read=18042
-> Seq Scan on boarding_passes bp (cost=0.00..45318.32 rows=2463832 width=33) (actual rows=2463832.00 loops=1)
AQO not used, fss=1362775811343989307
Buffers: shared hit=2638 read=18042
-> Hash (cost=475.98..475.98 rows=10606 width=4) (actual rows=10594.00 loops=1)
Buckets: 16384 Batches: 1 Memory Usage: 501kB
Buffers: shared hit=204
-> Seq Scan on flights f (cost=0.00..475.98 rows=10606 width=4) (actual rows=10594.00 loops=1)
AQO not used, fss=3484507337497244877
Filter: (scheduled_departure > '2025-12-01 22:00:00+07'::timestamp with time zone)
Rows Removed by Filter: 11164
Buffers: shared hit=204
Planning:
Buffers: shared hit=463
Using aqo: true
AQO mode: AUTO
AQO advanced: ON
Query hash: 6166891552805381787
JOINS: 2
(32 rows)
Now this query is stored in aqo_data with a non-zero fs (fs is equal to the query hash by default):
demo=# SELECT * FROM aqo_data;
fs | fss | dbid | delta_rows | nfeatures | features | targets | reliability | oids | tmpoids
---------------------+----------------------+-------+------------+-----------+---------------------------------------------+----------------------+-------------+---------------------+---------
6166891552805381787 | 4705493075117122362 | 16556 | f | 2 | {{-0.7185575545175223,-9.987736784981028}} | {9.154298981092557} | {1} | {17452,17438} |
6166891552805381787 | 4871603661380287993 | 16556 | f | 2 | {{-0.7185575545175223,-14.672062325711414}} | {9.154298981092557} | {1} | {17488,17452,17438} |
6166891552805381787 | 1362775811343989307 | 16556 | f | 0 | | {14.71722841949288} | {1} | {17438} |
6166891552805381787 | 3484507337497244877 | 16556 | f | 1 | {{-0.7185575545175223}} | {9.268043082104471} | {1} | {17452} |
6166891552805381787 | -1745942650988724053 | 16556 | f | 0 | | {15.187008236114766} | {1} | {17488} |
(5 rows)
We can make a few settings individually for this query. These are values of learn_aqo, use_aqo and auto_tuning in the aqo_queries view:
demo=# SELECT * FROM aqo_queries;
fs | dbid | learn_aqo | use_aqo | auto_tuning | smart_timeout | count_increase_timeout
---------------------+-------+-----------+---------+-------------+---------------+------------------------
6166891552805381787 | 16556 | t | t | f | 0 | 0
0 | 0 | f | f | f | 0 | 0
(2 rows)
Let's set use_aqo to false:
demo=# SELECT aqo_queries_update(6166891552805381787, NULL, NULL, false, NULL); aqo_queries_update -------------------- t (1 row)
Now we change a constant in the query:
demo=# EXPLAIN (ANALYZE, SUMMARY OFF, TIMING OFF)
SELECT bp.*
FROM segments s
JOIN flights f ON f.flight_id = s.flight_id
JOIN boarding_passes bp ON bp.ticket_no = s.ticket_no AND bp.flight_id = s.flight_id
WHERE f.scheduled_departure > '2025-11-20 15:00:00+00';
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------
Hash Join (cost=84966.87..244434.20 rows=1426685 width=33) (actual rows=438899.00 loops=1)
AQO not used, fss=0
Hash Cond: ((s.flight_id = f.flight_id) AND (s.ticket_no = bp.ticket_no))
Buffers: shared hit=5142 read=48902, temp read=20132 written=20132
-> Seq Scan on segments s (cost=0.00..72572.70 rows=3941270 width=18) (actual rows=3941249.00 loops=1)
AQO not used, fss=0
Buffers: shared hit=2208 read=30952
-> Hash (cost=52420.60..52420.60 rows=1426685 width=37) (actual rows=438899.00 loops=1)
Buckets: 131072 Batches: 16 Memory Usage: 2925kB
Buffers: shared hit=2934 read=17950, temp written=2967
-> Hash Join (cost=633.46..52420.60 rows=1426685 width=37) (actual rows=438899.00 loops=1)
AQO not used, fss=0
Hash Cond: (bp.flight_id = f.flight_id)
Buffers: shared hit=2934 read=17950
-> Seq Scan on boarding_passes bp (cost=0.00..45318.32 rows=2463832 width=33) (actual rows=2463832.00 loops=1)
AQO not used, fss=0
Buffers: shared hit=2730 read=17950
-> Hash (cost=475.98..475.98 rows=12599 width=4) (actual rows=12593.00 loops=1)
Buckets: 16384 Batches: 1 Memory Usage: 571kB
Buffers: shared hit=204
-> Seq Scan on flights f (cost=0.00..475.98 rows=12599 width=4) (actual rows=12593.00 loops=1)
AQO not used, fss=0
Filter: (scheduled_departure > '2025-11-20 22:00:00+07'::timestamp with time zone)
Rows Removed by Filter: 9165
Buffers: shared hit=204
Planning:
Buffers: shared hit=53
Using aqo: false
AQO mode: AUTO
AQO advanced: ON
Query hash: 6166891552805381787
JOINS: 2
(32 rows)
aqo was not used for this query, but there is new data in the aqo_data view:
demo=# SELECT * FROM aqo_data;
fs | fss | dbid | delta_rows | nfeatures | features | targets | reliability | oids | tmpoids
---------------------+----------------------+-------+------------+-----------+---------------------------------------------------------------------------------------+---------------------------------------+-------------+---------------------+---------
6166891552805381787 | 4705493075117122362 | 16556 | f | 2 | {{-0.7185575545175223,-9.987736784981028},{-0.5463556163769266,-9.987736784981028}} | {9.154298981092557,12.9920245972504} | {1,1} | {17452,17438} |
6166891552805381787 | 4871603661380287993 | 16556 | f | 2 | {{-0.7185575545175223,-14.672062325711414},{-0.5463556163769266,-14.672062325711414}} | {9.154298981092557,12.9920245972504} | {1,1} | {17488,17452,17438} |
6166891552805381787 | 1362775811343989307 | 16556 | f | 0 | | {14.71722841949288} | {1} | {17438} |
6166891552805381787 | 3484507337497244877 | 16556 | f | 1 | {{-0.7185575545175223},{-0.5463556163769266}} | {9.268043082104471,9.440896383005846} | {1,1} | {17452} |
6166891552805381787 | -1745942650988724053 | 16556 | f | 0 | | {15.187008236114766} | {1} | {17488} |
(5 rows)
The use_aqo setting does not apply to other queries. After executing another query twice, we can see that aqo learns on it and makes prediction for it:
demo=# EXPLAIN (ANALYZE, SUMMARY OFF, TIMING OFF)
SELECT bp.*
FROM segments s
JOIN flights f ON f.flight_id = s.flight_id
JOIN boarding_passes bp ON bp.ticket_no = s.ticket_no AND bp.flight_id = s.flight_id
JOIN tickets t ON t.ticket_no = s.ticket_no
WHERE f.scheduled_departure > '2025-12-1 15:00:00+00';
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------
Gather (cost=68355.43..129435.66 rows=9455 width=33) (actual rows=9455.00 loops=1)
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=34424 read=48398, temp read=25496 written=25656
-> Nested Loop (cost=67355.43..127490.16 rows=3940 width=33) (actual rows=3151.67 loops=3)
AQO: rows=9455, error=0%, fss=3232027643707566962
Buffers: shared hit=34424 read=48398, temp read=25496 written=25656
-> Parallel Hash Join (cost=67355.00..125653.57 rows=3940 width=47) (actual rows=3151.67 loops=3)
AQO: rows=9455, error=0%, fss=4871603661380287993
Hash Cond: ((bp.flight_id = f.flight_id) AND (bp.ticket_no = s.ticket_no))
Buffers: shared hit=6286 read=48166, temp read=25496 written=25656
-> Parallel Seq Scan on boarding_passes bp (cost=0.00..30945.97 rows=1026597 width=33) (actual rows=821277.33 loops=3)
AQO: rows=2463832, error=0%, fss=1362775811343989307
Buffers: shared hit=3098 read=17582
-> Parallel Hash (cost=54501.93..54501.93 rows=616138 width=22) (actual rows=492910.33 loops=3)
Buckets: 131072 Batches: 16 Memory Usage: 6144kB
Buffers: shared hit=3188 read=30584, temp written=7556
-> Hash Join (cost=608.40..54501.93 rows=616138 width=22) (actual rows=492910.33 loops=3)
AQO: rows=1478731, error=0%, fss=4547398436029445256
Hash Cond: (s.flight_id = f.flight_id)
Buffers: shared hit=3188 read=30584
-> Parallel Seq Scan on segments s (cost=0.00..49581.96 rows=1642187 width=18) (actual rows=1313749.67 loops=3)
AQO: rows=3941249, error=0%, fss=-1745942650988724053
Buffers: shared hit=2576 read=30584
-> Hash (cost=475.98..475.98 rows=10594 width=4) (actual rows=10594.00 loops=3)
Buckets: 16384 Batches: 1 Memory Usage: 501kB
Buffers: shared hit=612
-> Seq Scan on flights f (cost=0.00..475.98 rows=10594 width=4) (actual rows=10594.00 loops=3)
AQO: rows=10594, error=0%, fss=3484507337497244877
Filter: (scheduled_departure > '2025-12-01 22:00:00+07'::timestamp with time zone)
Rows Removed by Filter: 11164
Buffers: shared hit=612
-> Index Only Scan using tickets_pkey on tickets t (cost=0.43..0.47 rows=1 width=14) (actual rows=1.00 loops=9455)
AQO not used (early terminated), fss=1810536986390200978
Index Cond: (ticket_no = bp.ticket_no)
Heap Fetches: 0
Index Searches: 9455
Buffers: shared hit=28138 read=232
Planning:
Buffers: shared hit=85
Using aqo: true
AQO mode: AUTO
AQO advanced: ON
Query hash: 5639045936347396923
JOINS: 3
(45 rows)
Example F.4. Using the Sandbox Mode
SET aqo.sandbox = ON; SET aqo.enable = ON; SET aqo.advanced = OFF; -- Clean up the sandbox knowledge base without touching the main data SELECT aqo_reset(); EXPLAIN (ANALYZE, SUMMARY OFF, TIMING OFF) SELECT bp.* FROM segments s JOIN flights f ON f.flight_id = s.flight_id JOIN boarding_passes bp ON bp.ticket_no = s.ticket_no AND bp.flight_id = s.flight_id JOIN tickets t ON t.ticket_no = s.ticket_no WHERE f.scheduled_departure > '2025-12-1 15:00:00+00'; -- Be executing the previous query until plans get stabilized ... -- Copy data obtained with aqo.advanced = OFF from sandbox CREATE TABLE aqo_data_sandbox AS SELECT * FROM aqo_data; SET aqo.sandbox = OFF; SELECT aqo_data_update (fs, fss, dbid, delta_rows, nfeatures, features, targets, reliability, oids, tmpoids) FROM aqo_data_sandbox WHERE fs = 0; DROP TABLE aqo_data_sandbox;
F.2.8. Author #
Oleg Ivanov
