F.3. aqo
Пред. НаверхПриложение F. Дополнительные модули и расширения, поставляемые в postgrespro-std-14-contribНачало След.

F.3. aqo

F.3.1. Установка и подготовка
F.3.2. Использование
F.3.3. Справка
F.3.4. Примеры
F.3.5. Автор

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

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

Важно

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

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

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

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

При обновлении минорных версий также выполните команду ALTER EXTENSION aqo UPDATE и имейте в виду, что откат к более низкой версии расширения aqo будет невозможен.

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

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

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

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

    shared_preload_libraries = 'aqo'

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

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

    CREATE EXTENSION aqo;

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

Команда 

DROP EXTENSION aqo;

удалит интерфейс aqo только на уровне кластера. Расширение aqo всё равно будет работать на всех серверах, пока оно указано в shared_preload_libraries, и при перезапуске сервера продолжит работу в режиме, указанном в postgresql.conf. Кроме того, aqo сохранит своё внутреннее состояние после последовательного выполнения команд DROP EXTENSION -> CREATE EXTENSION.

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

SELECT aqo_reset();

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

ALTER SYSTEM SET aqo.mode = 'disabled';
SELECT pg_reload_conf();
DROP EXTENSION aqo;

Если вы не хотите, чтобы aqo загружался при перезапуске сервера, удалите строку 

shared_preload_libraries = 'aqo'

из файла postgresql.conf.

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

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

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

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

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

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

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

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

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

ALTER SYSTEM SET aqo.mode = 'режим';
SELECT pg_reload_conf();

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

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

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

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

Примечание

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

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

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

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

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

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

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

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

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

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

SELECT * FROM aqo_query_texts;

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

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

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

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

SELECT * FROM aqo_queries;

Доступные параметры перечислены в таблице Представление aqo_queries.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

F.3.3. Справка

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

aqo.mode (text)

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

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

aqo.show_hash (boolean)

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

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

aqo.show_details (boolean)

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

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

aqo.join_threshold (integer)

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

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

aqo.statement_timeout (integer)

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

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

aqo.force_collect_stat (boolean)

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

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

aqo.dsm_size_max (integer)

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

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

aqo.fs_max_items (integer)

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

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

aqo.fss_max_items (integer)

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

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

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

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

aqo.querytext_max_size (integer)

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

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

aqo.min_neighbors_for_predicting (integer)

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

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

aqo.predict_with_few_neighbors (boolean)

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

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

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

F.3.3.2.1. aqo_query_texts

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

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

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

F.3.3.2.2. aqo_queries

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

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

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

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

Говоря подробнее, при включённом свойстве auto_tuning, если для нескольких последовательных выполнений запроса с выключенным use_aqo ошибка оценки количества строк остаётся достаточно малой и стабильной, aqo включает use_aqo.

Запросы с learn_aqo=true (все новые запросы) несколько раз выполняются с использованием aqo и без него, со стандартным планировщиком. Чем быстрее запрос выполняется с aqo, тем вероятнее, что модуль будет использоваться для следующих запросов. Если после нескольких выполнений окажется, что запросы с использованием aqo выполняются дольше, чем с использованием стандартного планировщика, aqo больше не будет использоваться для этого класса запросов: для auto_tuning, use_aqo и learn_aqo устанавливается значение off.

smart_timeoutПоказывает значение «умного» тайм-аута операторов для данного класса запросов.
count_increase_timeoutПоказывает, сколько раз увеличивался «умный» тайм-аут операторов для данного класса запросов.

F.3.3.2.3. aqo_data

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

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

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

  • 1 — указывает, что данные получены после нормального выполнения запроса

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

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

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

F.3.3.2.4. aqo_query_stat

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

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

ДанныеОписание
execution_time_with_aqoВремя выполнения запросов со включённым aqo.
execution_time_without_aqoВремя выполнения запросов с отключённым aqo.
planning_time_with_aqoВремя планирования запросов со включённым aqo.
planning_time_without_aqoВремя планирования запросов с отключённым aqo.
cardinality_error_with_aqoОшибка оценки количества строк в планах запросов, выбранных со включённым aqo.
cardinality_error_without_aqoОшибка оценки количества строк в планах запросов, выбранных с отключённым aqo.
executions_with_aqoЧисло запросов, выполненных со включённым aqo.
executions_without_aqoЧисло запросов, выполненных с отключённым aqo.

F.3.3.3. Функции

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

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

Важно

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

aqo_cleanup() → setof integer

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

aqo_enable_class (queryid bigint) → void

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

aqo_disable_class (queryid bigint) → void

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

aqo_drop_class (queryid bigint) → integer

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

aqo_reset() → bigint

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

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

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

aqo_query_texts_update (queryid bigint, query_text text) → boolean

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

aqo_query_stat_update (queryid bigint, execution_time_with_aqo double precision[], execution_time_without_aqo double precision[], planning_time_with_aqo double precision[], planning_time_without_aqo double precision[], cardinality_error_with_aqo double precision[], cardinality_error_without_aqo double precision[], executions_with_aqo bigint[], executions_without_aqo bigint[]) → boolean

Изменяет или добавляет запись в файл, на котором основано представление aqo_query_stat, для данного queryid.

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

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

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

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

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

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

aqo_execution_time (controlled boolean) → setof record

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

F.3.4. Примеры

Пример F.1. Обучение на запросе

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

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

postgres=# EXPLAIN (ANALYZE, SUMMARY OFF, TIMING OFF) select count(*) from score join course on score.cno=course.cno join student on score.sno=student.sno where degree<90 and test_preparation = 0;
                                                QUERY PLAN
----------------------------------------------------------------------------------------------------------
 Aggregate  (cost=308.28..308.29 rows=1 width=8) (actual rows=1 loops=1)
   AQO not used, fss=0
   ->  Hash Join  (cost=124.80..299.47 rows=3526 width=0) (actual rows=3649 loops=1)
         AQO not used, fss=2128507884
         Hash Cond: (score.sno = student.sno)
         ->  Hash Join  (cost=16.30..181.70 rows=3526 width=4) (actual rows=3649 loops=1)
               AQO not used, fss=-303037802
               Hash Cond: (score.cno = course.cno)
               ->  Seq Scan on score  (cost=0.00..156.00 rows=3526 width=8) (actual rows=3649 loops=1)
                     AQO not used, fss=-636613046
                     Filter: ((degree < 90) AND (test_preparation = 0))
                     Rows Removed by Filter: 1351
               ->  Hash  (cost=12.80..12.80 rows=280 width=4) (actual rows=10 loops=1)
                     Buckets: 1024  Batches: 1  Memory Usage: 9kB
                     ->  Seq Scan on course  (cost=0.00..12.80 rows=280 width=4) (actual rows=10 loops=1)
                           AQO not used, fss=-1076069505
         ->  Hash  (cost=71.00..71.00 rows=3000 width=4) (actual rows=3000 loops=1)
               Buckets: 4096  Batches: 1  Memory Usage: 138kB
               ->  Seq Scan on student  (cost=0.00..71.00 rows=3000 width=4) (actual rows=3000 loops=1)
                     AQO not used, fss=-1838231581
 Using aqo: true
 AQO mode: LEARN
 Query hash: -727505571757520766
 JOINS: 2
(24 rows)

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

         fs          |     fss     | nfeatures |                                      features                                      |       targets       | reliability |        oids
---------------------+-------------+-----------+------------------------------------------------------------------------------------+---------------------+-------------+---------------------
 -727505571757520766 |  2128507884 |         4 | {{-0.03438753143452488,-5.634789603169249,-0.3149847743198556,-8.006367567650246}} | {8.202208436436448} | {1}         | {16579,16555,16563}
 -727505571757520766 | -1076069505 |         0 |                                                                                    | {2.302585092994046} | {1}         | {16555}
 -727505571757520766 | -1838231581 |         0 |                                                                                    | {8.006367567650246} | {1}         | {16563}
 -727505571757520766 |  -303037802 |         3 | {{-0.03438753143452488,-5.634789603169249,-0.3149847743198556}}                    | {8.202208436436448} | {1}         | {16579,16555}
 -727505571757520766 |  -636613046 |         2 | {{-0.03438753143452488,-0.3149847743198556}}                                       | {8.202208436436448} | {1}         | {16579}
(6 rows)

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

postgres=# EXPLAIN (ANALYZE, SUMMARY OFF, TIMING OFF) select count(*) from score join course on score.cno=course.cno join student on score.sno=student.sno where degree<90 and test_preparation = 0;
                                               QUERY PLAN
---------------------------------------------------------------------------------------------------------
 Aggregate  (cost=305.86..305.87 rows=1 width=8) (actual rows=1 loops=1)
   AQO not used, fss=0
   ->  Hash Join  (cost=121.42..296.74 rows=3649 width=0) (actual rows=3649 loops=1)
         AQO: rows=3649, error=0%, fss=2128507884
         Hash Cond: (score.sno = student.sno)
         ->  Hash Join  (cost=12.93..178.65 rows=3649 width=4) (actual rows=3649 loops=1)
               AQO: rows=3649, error=0%, fss=-303037802
               Hash Cond: (score.cno = course.cno)
               ->  Seq Scan on score  (cost=0.00..156.00 rows=3649 width=8) (actual rows=3649 loops=1)
                     AQO: rows=3649, error=0%, fss=-636613046
                     Filter: ((degree < 90) AND (test_preparation = 0))
                     Rows Removed by Filter: 1351
               ->  Hash  (cost=12.80..12.80 rows=10 width=4) (actual rows=10 loops=1)
                     Buckets: 1024  Batches: 1  Memory Usage: 9kB
                     ->  Seq Scan on course  (cost=0.00..12.80 rows=10 width=4) (actual rows=10 loops=1)
                           AQO: rows=10, error=0%, fss=-1076069505
         ->  Hash  (cost=71.00..71.00 rows=3000 width=4) (actual rows=3000 loops=1)
               Buckets: 4096  Batches: 1  Memory Usage: 138kB
               ->  Seq Scan on student  (cost=0.00..71.00 rows=3000 width=4) (actual rows=3000 loops=1)
                     AQO: rows=3000, error=0%, fss=-1838231581
 Using aqo: true
 AQO mode: LEARN
 Query hash: -727505571757520766
 JOINS: 2
(24 rows)

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

         fs          |     fss     | nfeatures |                                      features                                      |       targets       | reliability |        oids
---------------------+-------------+-----------+------------------------------------------------------------------------------------+---------------------+-------------+---------------------
 -727505571757520766 |  2128507884 |         4 | {{-0.03438753143452488,-5.634789603169249,-0.3149847743198556,-8.006367567650246}} | {8.202208436436448} | {1}         | {16579,16555,16563}
 -727505571757520766 | -1076069505 |         0 |                                                                                    | {2.302585092994046} | {1}         | {16555}
 -727505571757520766 | -1838231581 |         0 |                                                                                    | {8.006367567650246} | {1}         | {16563}
 -727505571757520766 |  -303037802 |         3 | {{-0.03438753143452488,-5.634789603169249,-0.3149847743198556}}                    | {8.202208436436448} | {1}         | {16579,16555}
 -727505571757520766 |  -636613046 |         2 | {{-0.03438753143452488,-0.3149847743198556}}                                       | {8.202208436436448} | {1}         | {16579}
(6 rows)

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

    postgres=# EXPLAIN (ANALYZE, SUMMARY OFF, TIMING OFF) select count(*) from score join course on score.cno=course.cno join student on score.sno=student.sno where degree<80 and test_preparation = 0;
                                                   QUERY PLAN
---------------------------------------------------------------------------------------------------------
 Aggregate  (cost=305.86..305.87 rows=1 width=8) (actual rows=1 loops=1)
   AQO not used, fss=0
   ->  Hash Join  (cost=121.42..296.74 rows=3649 width=0) (actual rows=3551 loops=1)
         AQO: rows=3649, error=3%, fss=2128507884
         Hash Cond: (score.sno = student.sno)
         ->  Hash Join  (cost=12.93..178.65 rows=3649 width=4) (actual rows=3551 loops=1)
               AQO: rows=3649, error=3%, fss=-303037802
               Hash Cond: (score.cno = course.cno)
               ->  Seq Scan on score  (cost=0.00..156.00 rows=3649 width=8) (actual rows=3551 loops=1)
                     AQO: rows=3649, error=3%, fss=-636613046
                     Filter: ((degree < 80) AND (test_preparation = 0))
                     Rows Removed by Filter: 1449
               ->  Hash  (cost=12.80..12.80 rows=10 width=4) (actual rows=10 loops=1)
                     Buckets: 1024  Batches: 1  Memory Usage: 9kB
                     ->  Seq Scan on course  (cost=0.00..12.80 rows=10 width=4) (actual rows=10 loops=1)
                           AQO: rows=10, error=0%, fss=-1076069505
         ->  Hash  (cost=71.00..71.00 rows=3000 width=4) (actual rows=3000 loops=1)
               Buckets: 4096  Batches: 1  Memory Usage: 138kB
               ->  Seq Scan on student  (cost=0.00..71.00 rows=3000 width=4) (actual rows=3000 loops=1)
                     AQO: rows=3000, error=0%, fss=-1838231581
 Using aqo: true
 AQO mode: LEARN
 Query hash: -727505571757520766
 JOINS: 2
(24 rows)

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

         fs          |     fss     | nfeatures |                                                                               features                                                                               |                targets                | reliability |        oids
---------------------+-------------+-----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------+-------------+---------------------
 -727505571757520766 |  1141621836 |         0 |                                                                                                                                                                      | {0}                                   | {1}         | {16579,16555,16563}
 -727505571757520766 |  2128507884 |         4 | {{-0.030949078292235133,-5.634789603169249,-0.3149847743198556,-8.006367567650246},{-0.34221288089027607,-5.634789603169249,-0.3149847743198556,-8.006367567650246}} | {8.202208436436448,8.174984532943087} | {1,1}       | {16579,16555,16563}
 -727505571757520766 | -1076069505 |         0 |                                                                                                                                                                      | {2.302585092994046}                   | {1}         | {16555}
 -727505571757520766 | -1838231581 |         0 |                                                                                                                                                                      | {8.006367567650246}                   | {1}         | {16563}
 -727505571757520766 |  -303037802 |         3 | {{-0.030949078292235133,-5.634789603169249,-0.3149847743198556},{-0.34221288089027607,-5.634789603169249,-0.3149847743198556}}                                       | {8.202208436436448,8.174984532943087} | {1,1}       | {16579,16555}
 -727505571757520766 |  -636613046 |         2 | {{-0.030949078292235133,-0.3149847743198556},{-0.34221288089027607,-0.3149847743198556}}

Теперь в предсказании нет ошибок: 

postgres=# EXPLAIN (ANALYZE, SUMMARY OFF, TIMING OFF) select count(*) from score join course on score.cno=course.cno join student on score.sno=student.sno where degree<80 and test_preparation = 0;
                                               QUERY PLAN
---------------------------------------------------------------------------------------------------------
 Aggregate  (cost=305.10..305.11 rows=1 width=8) (actual rows=1 loops=1)
   AQO not used, fss=0
   ->  Hash Join  (cost=121.42..296.22 rows=3551 width=0) (actual rows=3551 loops=1)
         AQO: rows=3551, error=0%, fss=2128507884
         Hash Cond: (score.sno = student.sno)
         ->  Hash Join  (cost=12.93..178.39 rows=3551 width=4) (actual rows=3551 loops=1)
               AQO: rows=3551, error=0%, fss=-303037802
               Hash Cond: (score.cno = course.cno)
               ->  Seq Scan on score  (cost=0.00..156.00 rows=3551 width=8) (actual rows=3551 loops=1)
                     AQO: rows=3551, error=0%, fss=-636613046
                     Filter: ((degree < 80) AND (test_preparation = 0))
                     Rows Removed by Filter: 1449
               ->  Hash  (cost=12.80..12.80 rows=10 width=4) (actual rows=10 loops=1)
                     Buckets: 1024  Batches: 1  Memory Usage: 9kB
                     ->  Seq Scan on course  (cost=0.00..12.80 rows=10 width=4) (actual rows=10 loops=1)
                           AQO: rows=10, error=0%, fss=-1076069505
         ->  Hash  (cost=71.00..71.00 rows=3000 width=4) (actual rows=3000 loops=1)
               Buckets: 4096  Batches: 1  Memory Usage: 138kB
               ->  Seq Scan on student  (cost=0.00..71.00 rows=3000 width=4) (actual rows=3000 loops=1)
                     AQO: rows=3000, error=0%, fss=-1838231581
 Using aqo: true
 AQO mode: LEARN
 Query hash: -727505571757520766
 JOINS: 2
(24 rows)

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

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

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

select queryid, cardinality_error_with_aqo, cardinality_error_without_aqo,execution_time_with_aqo, execution_time_without_aqo, planning_time_with_aqo, planning_time_without_aqo from aqo_query_stat \gx
-[ RECORD 1 ]-----------------+------------------------------------------------------------------------------------------------------------
queryid                       | 8041624334006338922
cardinality_error_with_aqo    | {0.14932737556062836,0,0.507421202801325,0.00040469447777891077}
cardinality_error_without_aqo | {0.1493979460962751,0.018403615483185476}
execution_time_with_aqo       | {0.004760108,0.008743075,0.006608304,0.012392751}
execution_time_without_aqo    | {0.005775926,0.012730316}
planning_time_with_aqo        | {0.006927997,0.004247339,0.005005022,0.004169717}
planning_time_without_aqo     | {0.001783542,0.001706121}

Полученные данные относятся к запросу, рассмотренному в примере Пример F.1. Этот запрос выполнялся с каждым из параметров degree<80 и degree<90 по одному разу без aqo и по два раза с aqo. Видно, что с aqo погрешность оценки количества строк уменьшается до 0,0004, а минимальная погрешность оценки количества строк без aqo составляет 0,15. Кроме того, время выполнения с aqo меньше, чем без него. Таким образом, можно сделать вывод, что aqo хорошо обучается на этом запросе и предсказание можно использовать для этого класса запросов.


F.3.5. Автор

Олег Иванов

Пред. Наверх След.
F.2. amcheck Начало F.4. auth_delay

Чтобы сделать работу с сайтом удобнее, мы используем cookie и аналитический сервис «Яндекс.Метрика». Продолжая пользоваться сайтом, вы соглашаетесь с их использованием.