F.2. aqo — оптимизация запросов по стоимости выполнения #
Модуль aqo представляет собой расширение Postgres Pro Standard для оптимизации запросов по стоимости выполнения. Используя методы машинного обучения, а точнее модификацию алгоритма k-NN, aqo улучшает оценку количества строк, что может способствовать выбору лучшего плана и, как следствие, ускорению запросов.
F.2.1. Описание #
Модуль aqo может собирать статистику по всем выполняемым запросам, за исключением запросов, обращающихся к системным отношениям. Если запросы различаются только константами, они считаются относящимися к одному классу. Для каждого класса модуль aqo сохраняет для машинного обучения качество оценки количества строк, время планирования, время и статистику выполнения. На основе этих данных aqo строит новый план выполнения и использует его для следующего запроса того же класса. В тестах aqo показал значительное увеличение производительности для сложных запросов.
Модуль aqo сохраняет все данные обучения (aqo_data), запросы (aqo_query_texts), параметры запросов (aqo_queries) и статистику выполнения запросов (aqo_query_stat) в файлах. При запуске aqo эти данные загружаются в разделяемую память. Вы можете обращаться к данным aqo, используя функции и представления.
При включённом параметре aqo.advanced и запуске aqo в режиме intelligent
или learn
каждому классу запросов для его идентификации и разделения собранной статистики присваивается уникальное хеш-значение, вычисляемое на основе дерева запросов. Если aqo.advanced
отключён, статистика для всех неотслеживаемых классов запросов хранится в общем классе запросов с хешем 0.
С каждым классом запросов связано отдельное пространство, называемое пространством признаков, в котором собирается статистика для данного класса запросов. Для идентификации этого пространства признаков используется хеш-значение (fs
), которое обычно совпадает с идентификатором запроса. С каждым пространством признаков связаны подпространства признаков, в которых собирается информация об избирательности и количестве строк для каждого узла плана запроса. Для идентификации каждого подпространства также используется хеш-значение (fss
).
Параметры оптимизации для каждого запроса хранятся в представлении aqo_queries.
F.2.1.1. Ограничения #
В настоящее время расширение aqo имеет следующие ограничения:
Оптимизация запросов с использованием aqo не поддерживается на ведомых серверах.
Оптимизация запросов с использованием aqo не поддерживается для запросов, обращающихся только к временным объектам.
Оптимизация запросов с использованием aqo не поддерживается для запросов, содержащих функции
IMMUTABLE
.Модуль aqo не собирает статистику по репликам, поскольку они доступны только для чтения. Однако он может использовать статистику выполнения запросов с ведущего сервера при работе с физической репликой.
Режимы
learn
иintelligent
не должны работать на уровне кластера с запросами, имеющими динамически генерируемую структуру, поскольку в этих режимах сохраняются все идентификаторы классов запросов, которые различны для всех таких запросов. Тем не менее могут использоваться динамически генерируемые константы.
F.2.2. Установка и подготовка #
Расширение aqo включено в состав Postgres Pro Standard. Установив Postgres Pro Standard, выполните следующие действия, чтобы подготовить aqo к работе:
Добавьте
aqo
в параметр shared_preload_libraries в файлеpostgresql.conf
:shared_preload_libraries = 'aqo'
Библиотеку aqo нужно предварительно загрузить при запуске сервера, так как адаптивная оптимизация запросов должна быть включена для всего кластера.
Создайте расширение aqo, выполнив следующий запрос:
CREATE EXTENSION aqo;
Когда расширение будет создано, вы можете приступить к оптимизации запросов.
Чтобы отключить aqo в текущей базе данных, выполните:
DROP EXTENSION aqo;
Чтобы отключить aqo на уровне кластера, выполните следующее:
ALTER SYSTEM SET aqo.enable = off; SELECT pg_reload_conf();
Чтобы удалить все данные из хранилища aqo, включая собранную статистику, вызовите функцию aqo_reset()
. Чтобы удалить данные из текущей базы данных, выполните
SELECT aqo_reset();
Чтобы удалить все данные из хранилища aqo, выполните
SELECT aqo_reset(NULL);
Чтобы расширение aqo не загружалось при перезапуске сервера, удалите строку
shared_preload_libraries = 'aqo'
из файла postgresql.conf
.
Важно
Чтобы избежать ошибок во время физической репликации при переносе данных aqo с ведущего сервера на реплику, убедитесь, что на серверах установлены одинаковые версии модуля. Если установлены разные версии aqo, необходимо задать значение off
для параметра aqo.wal_rw на обоих серверах, однако в таком случае репликация выполняться не будет.
F.2.2.1. Конфигурирование #
Поведение aqo в основном регулируется параметрами конфигурации aqo.enable, aqo.mode и aqo.advanced. Значения этих параметров по умолчанию позволяют начать обучение aqo в основном режиме, как только для параметра aqo.enable
устанавливается значение on
.
Чтобы динамически изменить в текущем сеансе любой из этих параметров, например режим, выполните следующую команду:
SET aqo.mode = 'mode';
Здесь режим
— название режима работы, который будет использоваться.
F.2.3. Использование #
F.2.3.1. Использование основного режима aqo #
По умолчанию параметр aqo.advanced отключён. При этом устанавливается рекомендуемый основной режим, в котором статистика собирается для узлов плана (определяемых fss
), а собранные данные машинного обучения используются для исправления погрешностей оценки количества строк для всех запросов, план которых содержит определённый узел плана. Как только для параметра aqo.enable устанавливается значение on
, aqo начинает обучение. Следует несколько раз выполнить запросы, которые необходимо оптимизировать, пока план не станет достаточно хорошим, и изменить значение параметра aqo.mode на frozen
. Чтобы применить данные машинного обучения на уровне экземпляра сервера, выполните следующие команды:
ALTER SYSTEM SET aqo.mode = frozen; ALTER SYSTEM SET aqo.enable = on; SELECT pg_reload_conf();
Данные машинного обучения будут применяться не только к запросам, на которых обучался модуль aqo, но также ко всем запросам с планом, содержащим узлы, по которым собиралась статистика. Чтобы данные машинного обучения не влияли на другие запросы, установите для параметра aqo.advanced значение on
, и статистика будет собираться для отдельных запросов. За подробностями обратитесь к Подразделу F.2.3.2.
F.2.3.2. Выбор режима работы для расширенной оптимизации запросов #
Если часто выполняются однотипные запросы, например, в приложении ограничено число возможных классов запросов, можно включить параметр aqo.advanced и использовать интеллектуальный режим (intelligent
) для улучшения планирования таких запросов. В этом режиме aqo анализирует выполнение каждого запроса и собирает статистику. При этом статистика по разным классам запросов собирается отдельно. Если производительность не увеличивается после 50 итераций, расширение aqo отключается, и планирование выполняется стандартным планировщиком запросов.
Примечание
Можно посмотреть текущий план запроса, воспользовавшись стандартной командой Postgres Pro EXPLAIN
с указанием ANALYZE
. За подробностями обратитесь к Разделу 14.1.
Так как в режиме intelligent
различные классы запросов анализируются отдельно, aqo может не улучшить производительность, если запросы в рабочей нагрузке относятся к нескольким разным классам или постоянно меняются. Для такого профиля нагрузки стоит перевести aqo в режим controlled
или попробовать отключить параметр aqo.advanced
.
Когда включён параметр aqo.advanced
, в режиме controlled
расширение aqo не собирает статистику для новых классов запросов, поэтому они не будут оптимизированы, но для известных классов запросов aqo продолжит собирать статистику и использовать оптимизированные алгоритмы планирования. Поэтому следует использовать режим controlled
только после обучения aqo в режиме learn
или intelligent
.
После того, как aqo прошло обучение, рекомендуется использовать режим controlled
для рабочей среды. Чтобы aqo работало в этом режиме на уровне всего производственного кластера, выполните
ALTER SYSTEM SET aqo.mode = 'controlled'; SELECT pg_reload_conf();
При включённом параметре aqo.advanced
расширение в режиме learn
собирает статистику по всем выполненным запросам и обновляет данные для классов запросов. Этот режим аналогичен режиму intelligent
, за исключением возможности интеллектуальной настройки. Его не рекомендуется использовать постоянно для всего кластера, поскольку он пытается оптимизировать aqo для каждого класса запросов, даже для тех, которым это не нужно, и может привести к ненужным вычислительным издержкам и снижению производительности.
Для рабочей нагрузки с динамически генерируемой структурой запросов используйте режим learn
с отключённым параметром. Общее улучшение производительности не гарантируется. Поскольку в этом режиме отсутствует интеллектуальная настройка, производительность некоторых запросов может даже снизиться, однако он больше подходит для динамически меняющейся нагрузки и потребляет меньше памяти, чем режим intelligent
.
Параметр конфигурации aqo.delta_rows
может быть полезен в случае с данными, которые могут существенно изменяться между запросами. Этот параметр позволяет aqo делать предсказания на основе оценки количества строк планировщика. Например, планировщик оценивает меньшее число строк при удалении данных из таблицы. Расширение aqo может использовать эту обновлённую информацию, чтобы предсказать меньшее число строк без необходимости дополнительного обучения.
Если вы хотите уменьшить влияние aqo на время планирования и исполнения запросов, вы можете использовать режим frozen
. В этом режиме aqo только читает собранную статистику, но новые данные не собираются.
F.2.3.3. Тонкая настройка aqo #
Для обращения к представлениям aqo и изменения расширенных свойств запросов необходимо иметь права суперпользователя.
Все обработанные классы запросов и соответствующие хеш-значения можно увидеть в представлении aqo_query_texts:
SELECT * FROM aqo_query_texts;
Чтобы узнать класс (то есть хеш) запроса и режим aqo, включите переменные среды aqo.show_hash
(boolean
) (boolean) и aqo.show_details
(boolean
) (boolean) и выполните запрос. Вывод будет примерно следующим:
... Planning Time: 23.538 ms ... Execution Time: 249813.875 ms ... Using aqo: true AQO mode: LEARN AQO advanced: OFF ... Query hash: -2439501042637610315
Разные классы запросов имеют собственные свойства оптимизации. Эти свойства отображаются в представлении aqo_queries:
SELECT * FROM aqo_queries;
Можно вручную изменять эти свойства, чтобы скорректировать оптимизацию для определённого класса запросов. Например:
-- Добавление нового класса запросов в представление aqo_queries: SET aqo.advanced='on'; SET aqo.mode='intelligent'; SELECT * FROM a, b WHERE a.id=b.id; SET aqo.mode='controlled'; -- Отключение автонастройки, включение learn_aqo и use_aqo -- для данного класса запросов: SELECT count(*) FROM aqo_queries, LATERAL aqo_queries_update(queryid, NULL, NULL, true, true, false) WHERE queryid = (SELECT queryid FROM aqo_query_texts WHERE query_text LIKE 'SELECT * FROM a, b WHERE a.id=b.id;'); -- Запуск EXPLAIN ANALYZE и наблюдение изменённого плана: EXPLAIN ANALYZE SELECT * FROM a, b WHERE a.id=b.id; EXPLAIN ANALYZE SELECT * FROM a, b WHERE a.id=b.id; -- Отключение обучения для прекращения сбора статистики и -- начала использования оптимизированного плана: SELECT count(*) FROM aqo_queries, LATERAL aqo_queries_update(queryid, NULL, NULL, false, true, false) WHERE queryid = (SELECT queryid FROM aqo_query_texts WHERE query_text LIKE 'SELECT * FROM a, b WHERE a.id=b.id;');
Чтобы предотвратить интеллектуальную настройку для определённого класса запросов, отключите свойство auto_tuning
:
SELECT count(*) FROM aqo_queries, LATERAL aqo_queries_update(queryid, NULL, NULL, NULL, NULL, false) WHERE queryid = 'hash';
Здесь хеш
— это значение хеша для данного класса запросов. В результате aqo не будет автоматически менять свойства learn_aqo
и use_aqo
.
Чтобы отключить дальнейшее обучение для некоторого класса запросов, выполните следующую команду:
SELECT count(*) FROM aqo_queries, LATERAL aqo_queries_update(queryid, NULL, NULL, false, NULL, false) WHERE queryid = 'hash';
Здесь хеш
— это значение хеша для данного класса запросов.
Чтобы полностью отключить aqo для всех запросов и использовать стандартный планировщик Postgres Pro, выполните:
SELECT count(*) FROM aqo_queries, LATERAL aqo_disable_class(queryid, NULL) WHERE queryid <> 0;
Чтобы временно отключить aqo для всех запросов в текущем сеансе или на уровне всего кластера, но не удалять и не изменять собранную статистику и параметры, отключите параметр aqo.enable, как указано ниже:
SET aqo.enable = 'off';
или
ALTER SYSTEM SET aqo.enable = 'off'
F.2.3.4. Режим «песочницы» #
Вы можете экспериментировать с aqo, не затрагивая его основную базу знаний. Для этого выполните команду
SET aqo.sandbox = ON;
Она включит режим «песочницы», в котором aqo будет работать в изолированной среде. Однако если включить aqo.sandbox в разных сеансах SQL, они будут использовать одни и те же данные.
Данные, полученные в режиме «песочницы», не реплицируются. Но режим «песочницы» можно использовать на ведомом сервере. Более того, единственный способ обучить aqo на ведомом сервере — включить режим «песочницы» при включённой репликации, то есть при значении true для aqo.wal_rw
. Без режима «песочницы» aqo будет работать на ведомом сервере так, как будто aqo.mode
= FROZEN
, то есть сможет использовать существующую базу знаний, но не сможет её обновлять или расширять.
F.2.4. Справка #
F.2.4.1. Параметры конфигурации #
aqo.enable
(boolean
) #Определяет состояние расширения aqo. Если установлено значение
off
, aqo не работает, за исключением случаев, когда параметр aqo.force_collect_stat =on
.По умолчанию:
off
(выкл.).aqo.mode
(text
) #Устанавливает режим работы aqo и определяет, как расширение будет обрабатывать новые запросы. Возможные значения:
intelligent
— расширение сохраняет новые запросы с включённымauto_tuning
. За дополнительными сведениями обратитесь к описанию представления aqo_queries. В данном режиме aqo может отключиться для запроса в случае снижения средней производительности. Этот режим работает таким образом, только если параметр aqo.advanced =on
, в противном случае работает аналогично режимуlearn
.learn
— расширение собирает статистику по всем выполненным запросам, обучается и делает предсказания на основе этой статистики.controlled
— расширение только обучается и делает предсказания для известных запросов.frozen
— расширение делает предсказания для известных запросов, но не обучается ни на каких запросах.
По умолчанию:
learn
.aqo.advanced
(boolean
) #Включает расширенную процедуру обучения, которая сохраняет статистику обучения отдельно для каждого класса запроса. Также позволяет настраивать параметры
use_aqo
иlearn_aqo
в представлении aqo_queries. После тонкой настройки параметры запроса в представленииaqo_query
продолжают работать при выключенном параметреaqo.advanced
.По умолчанию:
off
(выкл.).aqo.force_collect_stat
(boolean
) #Собирать статистику выполнения запросов во всех режимах aqo даже при
aqo.enable
=off
.По умолчанию:
off
(выкл.).aqo.show_details
(boolean
) #Добавлять некоторые детали в вывод команды
EXPLAIN
запроса, такие как предсказание или хеш подпространства признаков, и отображать некоторую дополнительную информацию, специфичную для aqo.По умолчанию:
on
(вкл.).aqo.show_hash
(boolean
) #Показывать хеш-значение, однозначно идентифицирующее класс запросов или класс узлов плана. Расширение aqo использует в качестве идентификатора класса запроса идентификатор самого Postgres Pro, чтобы обеспечить согласованность с другими расширениями, такими как pg_stat_statements. Таким образом, идентификатор запроса можно получить из поля
Query hash
в выводе командыEXPLAIN ANALYZE
.По умолчанию:
on
(вкл.).aqo.join_threshold
(integer
) #Игнорировать запросы, содержащие количество соединений меньше указанного, то есть статистика для таких запросов не собирается.
По умолчанию:
0
(запросы не игнорируются).aqo.learn_statement_timeout
(boolean
) #Обучаться на планах запросов, прерванных по тайм-ауту оператора.
По умолчанию:
off
(выкл.).aqo.statement_timeout
(integer
) #Определяет начальное значение так называемого «умного» тайм-аута операторов в миллисекундах, который необходим для ограничения времени выполнения при ручном обучении aqo на специальных запросах с неудовлетворительным прогнозом количества строк. Расширение aqo может динамически изменять умный тайм-аут операторов во время этого обучения. Когда погрешность оценки количества строк на узлах превышает 0.1, значение
aqo.statement_timeout
автоматически увеличивается экспоненциально, но не превышает statement_timeout.По умолчанию:
0
.aqo.wide_search
(boolean
) #Включает поиск соседей с одним и тем же подпространством признаков среди разных классов запросов. Работает, только если параметр aqo.advanced =
on
.По умолчанию:
off
(выкл.).aqo.min_neighbors_for_predicting
(integer
) #Определяет, сколько выборок, собранных при предыдущих выполнениях запроса, будет использоваться для прогнозирования оценки количества строк в следующий раз. Если количество выборок меньше заданного значения, aqo не будет делать предсказаний. Слишком большое значение может повлиять на производительность, а слишком маленькое — снизить качество предсказания.
По умолчанию:
3
.aqo.predict_with_few_neighbors
(boolean
) #Позволяет aqo делать предсказания с меньшим количеством соседей, чем указано в параметре aqo.min_neighbors_for_predicting. Если установлено значение
off
, aqo обучается, но не делает предсказания до тех пор, пока счётчик выполнений запроса с разными константами не достигнет 3 (по умолчанию дляaqo.min_neighbors_for_predicting
).По умолчанию:
on
(вкл.).aqo.fs_max_items
(integer
) #Определяет максимальное количество пространств признаков, с которыми может работать aqo. При превышении этого количества обучение на новых классах запросов прекратится, и они не будут отображаться в представлениях. Задать этот параметр можно только при запуске сервера.
По умолчанию:
10000
.aqo.fss_max_items
(integer
) #Определяет максимальное количество подпространств признаков, с которыми может работать aqo. При превышении этого количества данные об избирательности и предсказание количества строк для новых узлов плана запроса больше не будут собираться и новые подпространства признаков не будут отображаться в представлении aqo_data. Задать этот параметр можно только при запуске сервера.
По умолчанию:
100000
.aqo.querytext_max_size
(integer
) #Определяет максимальный размер запроса в представлении aqo_query_texts. Задать этот параметр можно только при запуске сервера.
По умолчанию:
1000
.aqo.dsm_size_max
(integer
) #Определяет максимальный размер динамической разделяемой памяти в мегабайтах, которую модуль aqo может выделить для хранения данных обучения и текстов запросов. Если установленное значение меньше объёма сохранённых данных aqo, сервер не запустится. Задать этот параметр можно только при запуске сервера.
По умолчанию:
100
.aqo.wal_rw
(boolean
) #Включает физическую репликацию и обеспечивает полное восстановление данных aqo после сбоя. При значении
off
на ведущем сервере данные на реплику не передаются. При значенииoff
на реплике данные, передаваемые с ведущего сервера, игнорируются. В таком случае при сбое сервера данные могут быть восстановлены только до последней контрольной точки. Этот параметр можно задать только при запуске сервера.По умолчанию:
on
(вкл.).aqo.sandbox
(boolean
) #Позволяет резервировать отдельную область в общей памяти для использования ведущим или ведомым узлом, что позволяет собирать и использовать статистику с данными в этой области памяти. Если включён на ведомом узле, расширение использует отдельную область общей памяти, которая не реплицируется на ведомый сервер. Изменение значения этого параметра сбрасывает кеш aqo. Изменить этот параметр могут только суперпользователи.
По умолчанию:
off
(выкл.).aqo.delta_rows
(boolean
) #Включает механизм обучения, при котором aqo корректирует оценку количества строк планировщика своими предсказаниями. Если параметр выключен, aqo использует собственные предсказания.
По умолчанию:
off
(выкл.).
F.2.4.2. Представления #
F.2.4.2.1. aqo_query_texts
#
В представлении aqo_query_texts
классифицируются все классы запросов, обрабатываемые aqo. Для каждого класса запросов в представлении отображается текст первого проанализированного запроса этого класса.
Таблица F.1. Представление aqo_query_texts
Имя столбца | Описание |
---|---|
queryid | Уникальный идентификатор класса запросов. |
dbid | Идентификатор базы данных. |
query_text | Текст первого проанализированного запроса данного класса. Длина текста запроса ограничивается параметром aqo.querytext_max_size. |
F.2.4.2.2. aqo_queries
#
В представлении aqo_queries
отображаются свойства оптимизации для разных классов запросов. Один запрос, выполненный в двух разных базах данных, сохраняется дважды с одинаковым идентификатором запроса (queryid
).
Таблица F.2. Представление aqo_queries
Свойство | Описание |
---|---|
queryid | Уникальный идентификатор класса запросов. |
dbid | Идентификатор базы данных, в которой выполнялся запрос. |
fs | Уникальный идентификатор (хеш) пространства признаков, в котором собирается статистика для данного класса запросов. По умолчанию используется queryid . Можно вручную установить одно и то же значение fs для разных классов запросов, особенно если они похожи. |
learn_aqo | Показывает, включён ли сбор статистики для данного класса запросов. |
use_aqo | Показывает, включено ли предсказание количества строк средствами aqo для следующего выполнения данного класса запросов. |
auto_tuning | Показывает, может ли aqo динамически изменять параметры При включённом свойстве Запросы с |
smart_timeout | Значение «умного» тайм-аута операторов для данного класса запросов. Начальное значение такого тайм-аута для любого запроса определяется параметром конфигурации statement_timeout. |
count_increase_timeout | Показывает, сколько раз увеличивался «умный» тайм-аут операторов для данного класса запросов. |
F.2.4.2.3. aqo_data
#
В представлении aqo_data
отображаются данные машинного обучения для уточнения оценки количества строк. Количество строк ограничено параметром aqo.fss_max_items. Чтобы сбросить всю собранную статистику для определённого класса запросов, можно удалить из представления aqo_data
все строки с соответствующим fs
.
Таблица F.3. Представление aqo_data
Данные | Описание |
---|---|
fs | Идентификатор (хеш) пространства признаков. |
fss | Идентификатор (хеш) подпространства признаков. |
dbid | Идентификатор базы данных. |
delta_rows | Если true, aqo делает предсказания на основе оценки планировщика; в противном случае false . |
nfeatures | Размер подпространства признаков для узла плана запроса. |
features | Логарифм избирательности, на котором основано предсказание количества строк. |
targets | Логарифм количества строк для узла плана запроса. |
reliability | Уровень достоверности статистики обучения:
|
oids | Список идентификаторов таблиц, которые участвовали в предсказании для этого узла. |
F.2.4.2.4. aqo_query_stat
#
В представлении aqo_query_stat
отображается статистика выполнения запросов, группируемая по классам запросов. Расширение aqo использует эти данные, когда для определённого класса запросов включено свойство auto_tuning
.
Таблица F.4. Представление aqo_query_stat
Данные | Описание |
---|---|
queryid | Уникальный идентификатор класса запросов. |
dbid | Идентификатор базы данных. |
execution_time_with_aqo | Массив значений времени выполнения запросов со включённым aqo. |
execution_time_without_aqo | Массив значений времени выполнения запросов с отключённым aqo. |
planning_time_with_aqo | Массив значений времени планирования запросов со включённым aqo. |
planning_time_without_aqo | Массив значений времени планирования запросов с отключённым aqo. |
cardinality_error_with_aqo | Массив ошибок оценки количества строк со включённым aqo в планах выбранных запросов. |
cardinality_error_without_aqo | Массив ошибок оценки количества строк с отключённым aqo в планах выбранных запросов. |
executions_with_aqo | Число запросов, выполненных со включённым aqo. |
executions_without_aqo | Число запросов, выполненных с отключённым aqo. |
F.2.4.3. Функции #
Модуль aqo добавляет несколько функций в каталог Postgres Pro.
F.2.4.3.1. Функции управления хранилищем #
Важно
Функции aqo_queries_update
, aqo_query_texts_update
, aqo_query_stat_update
, aqo_data_update
и aqo_data_delete
изменяют файлы данных, на которых основаны соответствующие представления aqo. Поэтому вызывайте эти функции только в том случае, если вы понимаете логику адаптивной оптимизации запросов.
aqo_cleanup
() →setof integer
Удаляет данные, относящиеся к классам запросов, которые связаны (возможно частично) с удалёнными отношениями. Возвращает количество удалённых пространств признаков (классов) и подпространств признаков. Игнорирует удаление других объектов.
aqo_enable_class
(queryid
bigint
,dbid
oid
) →void
Устанавливает для
learn_aqo
,use_aqo
иauto_tuning
(только в режимеintelligent
) значение true для класса запросов с указаннымиqueryid
иdbid
. Можно использовать дляdbid
значение NULL вместо идентификатора текущей базы данных.aqo_disable_class
(queryid
bigint
,dbid
oid
) →void
Устанавливает для
learn_aqo
,use_aqo
иauto_tuning
значение false для класса запросов с указаннымиqueryid
иdbid
. Можно использовать дляdbid
значение NULL вместо идентификатора текущей базы данных.aqo_drop_class
(queryid
bigint
,dbid
oid
) →integer
Удаляет все данные, относящиеся к заданному классу запросов и базе данных, из хранилища aqo. Для параметра
dbid
можно задать значение NULL вместо идентификатора текущей базы данных. Возвращает количество записей, удалённых из хранилища aqo.aqo_reset
(dbid
oid
) →bigint
Удаляет записи из указанной базы данных: данные машинного обучения, тексты запросов, статистику и свойства классов запросов. Если параметр
dbid
не указан, данные удаляются из текущей базы данных. Еслиdbid
имеет значение NULL, удаляются все записи из хранилища aqo. Возвращает количество удалённых записей.aqo_queries_update
(queryid
bigint
,dbid
oid
,fs
bigint
,learn_aqo
boolean
,use_aqo
boolean
,auto_tuning
boolean
) →boolean
Изменяет или вставляет запись в файл данных, лежащий в основе представления aqo_queries, для указанных
queryid
иdbid
. Можно использовать дляdbid
значение NULL вместо идентификатора текущей базы данных. Значения NULL для остальных параметров означают, что их следует оставить без изменений. Обратите внимание, что записи с нулевым значениемqueryid
илиdbid
не могут быть изменены. Возвращаетfalse
в случае ошибки иtrue
в противном случае.aqo_query_texts_update
(queryid
bigint
,dbid
oid
,query_text
text
) →boolean
Изменяет или вставляет запись в файл данных, лежащий в основе представления aqo_query_texts, для указанных
queryid
иdbid
. Можно использовать дляdbid
значение NULL вместо идентификатора текущей базы данных. Значения NULL для остальных параметров означают, что их следует оставить без изменений. Обратите внимание, что записи с нулевым значениемqueryid
илиdbid
не могут быть изменены. Возвращаетfalse
в случае ошибки иtrue
в противном случае.aqo_query_stat_update
(queryid
bigint
,dbid
oid
,execution_time_with_aqo
double precision[]
,execution_time_without_aqo
double precision[]
,planning_time_with_aqo
double precision[]
,planning_time_without_aqo
double precision[]
,cardinality_error_with_aqo
double precision[]
,cardinality_error_without_aqo
double precision[]
,executions_with_aqo
bigint[]
,executions_without_aqo
bigint[]
) →boolean
Изменяет или вставляет запись в файл данных, лежащий в основе представления aqo_query_stat, для указанных
queryid
иdbid
. Можно использовать дляdbid
значение NULL вместо идентификатора текущей базы данных. Возвращаетfalse
в случае ошибки иtrue
в противном случае.aqo_data_update
(fs
bigint
,fss
integer
,dbid
oid
,delta_rows
boolean
,nfeatures
integer
,features
double precision[][]
,targets
double precision[]
,reliability
double precision[]
,oids
oid[]
) →boolean
Изменяет или вставляет запись в файл данных, лежащий в основе представления aqo_data, для указанных
fs
,fss
иdbid
. Можно использовать дляdbid
значение NULL вместо идентификатора текущей базы данных. Если дляdelta_rows
задать NULL, будет использовано значение параметра конфигурацииaqo.delta_rows
. Возвращаетfalse
в случае ошибки иtrue
в противном случае.aqo_data_delete
(fs
bigint
,fss
integer
,dbid
oid
,delta_rows
boolean
) →boolean
Удаляет запись из файла данных, лежащего в основе представления aqo_data, для указанных
fs
,fss
иdbid
. Можно использовать дляdbid
значение NULL вместо идентификатора текущей базы данных. Если не указать параметрdelta_rows
или задать для него NULL, будет использовано значение параметра конфигурацииaqo.delta_rows
. Возвращаетfalse
в случае ошибки иtrue
в противном случае.
F.2.4.3.2. Функции управления памятью #
aqo_memory_usage
() →setof record
Отображает выделенные и использованные размеры контекстов памяти и хеш-таблиц aqo. Возвращает таблицу со следующими столбцами:
name
Краткое описание контекста памяти или хеш-таблицы
allocated_size
Общий размер выделенной памяти
used_size
Размер текущей используемой памяти
F.2.4.3.3. Функции для аналитики #
aqo_cardinality_error
(controlled
boolean
) →setof record
Показывает ошибку оценки количества строк для последнего выполнения запроса. Если
controlled
имеет значениеtrue
, показывает запросы, выполненные с включённым aqo. Еслиcontrolled
имеет значениеfalse
, показывает запросы, выполненные с отключённым aqo, но имеющие накопленную статистику. Возвращает таблицу со следующими столбцами:num
Порядковый номер
queryid
Уникальный идентификатор класса запросов
dbid
Идентификатор базы данных
fs
Идентификатор пространства признаков, обычно равен нулю или
queryid
.error
Погрешность aqo, рассчитываемая на узлах планов запросов
nexecs
Количество выполнений запросов, связанных с данным
queryid
aqo_execution_time
(controlled
boolean
) →setof record
Показывает время выполнения запросов. Если
controlled
имеет значение true, показывает время выполнения последнего запроса с включённым aqo. Еслиcontrolled
имеет значение false, возвращает среднее время выполнения запроса для всех записанных в журнал выполнений с отключённым aqo. Время выполнения без aqo можно собрать, если параметр aqo.mode =intelligent
или параметр aqo.force_collect_stat =on
. Возвращает таблицу со следующими столбцами:num
Порядковый номер
queryid
Уникальный идентификатор класса запросов
dbid
Идентификатор базы данных
fs
Идентификатор пространства признаков, обычно равен нулю или
queryid
.exec_time
Если
controlled
имеет значение true, показывает время выполнения последнего запроса с включённым aqo, в противном случае — среднее время выполнения запроса для всех записанных в журнал выполнений с отключённым aqo.nexecs
Количество выполнений запросов, связанных с данным
queryid
F.2.5. Примеры #
Пример F.1. Обучение на запросе (Основной режим)
Рассмотрим оптимизацию запроса с использованием расширения aqo.
Когда запрос выполняется в первый раз, его нет в таблицах, лежащих в основе представлений aqo. Таким образом, данных для предсказания aqo для каждого узла плана нет, и в выводе EXPLAIN
появляются строки «AQO not used»:
demo=# EXPLAIN (ANALYZE, SUMMARY OFF, TIMING OFF) SELECT bp.* FROM ticket_flights tf JOIN flights f ON f.flight_id = tf.flight_id JOIN boarding_passes bp ON bp.ticket_no = tf.ticket_no AND bp.flight_id = tf.flight_id WHERE f.scheduled_departure > '2017-08-15 15:00:00+00'; QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------- Hash Join (cost=63796.33..140286.73 rows=477728 width=25) (actual rows=2215 loops=1) AQO not used, fss=8598194613120045129 Hash Cond: ((bp.flight_id = f.flight_id) AND (bp.ticket_no = tf.ticket_no)) -> Seq Scan on boarding_passes bp (cost=0.00..32894.95 rows=1894295 width=25) (actual rows=1894295 loops=1) AQO not used, fss=1362775811343989307 -> Hash (cost=51379.43..51379.43 rows=595260 width=22) (actual rows=468255 loops=1) Buckets: 131072 Batches: 8 Memory Usage: 4223kB -> Hash Join (cost=1859.80..51379.43 rows=595260 width=22) (actual rows=468255 loops=1) AQO not used, fss=-7651474063207585780 Hash Cond: (tf.flight_id = f.flight_id) -> Seq Scan on ticket_flights tf (cost=0.00..43323.35 rows=2360335 width=18) (actual rows=2360335 loops=1) AQO not used, fss=-6410966714754547713 -> Hash (cost=1652.80..1652.80 rows=16560 width=4) (actual rows=16340 loops=1) Buckets: 32768 Batches: 1 Memory Usage: 831kB -> Seq Scan on flights f (cost=0.00..1652.80 rows=16560 width=4) (actual rows=16340 loops=1) AQO not used, fss=-1289471166524579716 Filter: (scheduled_departure > '2017-08-15 22:00:00+07'::timestamp with time zone) Rows Removed by Filter: 49324 Using aqo: true AQO mode: LEARN AQO advanced: OFF Query hash: 402936317627943257 JOINS: 2 (23 rows)
Если в представлении aqo_data нет информации об определённом узле, aqo добавит в него соответствующую запись для дальнейшего изучения и предсказания, за исключением узлов с fss=0
в выводе EXPLAIN
. Поскольку значения в полях features
и targets
в представлении aqo_data
являются логарифмом по основанию e
, чтобы получить фактическое значение, возведите e
в соответствующую степень. Например: exp(7.703007682479236):
demo=# select * from aqo_data; fs | fss | dbid | nfeatures | features | targets | reliability | oids ----+----------------------+-------+-----------+---------------------------------------------+----------------------+-------------+--------------------- 0 | 1362775811343989307 | 16429 | 0 | | {14.454357295615447} | {1} | {16452} 0 | -6410966714754547713 | 16429 | 0 | | {14.674314116080508} | {1} | {16479} 0 | -1289471166524579716 | 16429 | 1 | {{-1.3775704575284085}} | {9.701371368413994} | {1} | {16458} 0 | 8598194613120045129 | 16429 | 2 | {{-13.492416828684513,-1.3775704575284085}} | {7.703007682479236} | {1} | {16479,16458,16452} 0 | -7651474063207585780 | 16429 | 2 | {{-11.092306109090387,-1.3775704575284085}} | {13.056768298305919} | {1} | {16479,16458} (5 rows)
При повторном выполнении запроса aqo распознаёт его и делает предсказание. Обратите внимание на оценку количества строк, предсказанную aqo, и значение погрешности aqo («error=0%»).
demo=# EXPLAIN (ANALYZE, SUMMARY OFF, TIMING OFF) SELECT bp.* FROM ticket_flights tf JOIN flights f ON f.flight_id = tf.flight_id JOIN boarding_passes bp ON bp.ticket_no = tf.ticket_no AND bp.flight_id = tf.flight_id WHERE f.scheduled_departure > '2017-08-15 15:00:00+00'; QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------- Hash Join (cost=61144.50..136890.86 rows=2215 width=25) (actual rows=2215 loops=1) AQO: rows=2215, error=0%, fss=8598194613120045129 Hash Cond: ((bp.flight_id = f.flight_id) AND (bp.ticket_no = tf.ticket_no)) -> Seq Scan on boarding_passes bp (cost=0.00..32894.95 rows=1894295 width=25) (actual rows=1894295 loops=1) AQO: rows=1894295, error=0%, fss=1362775811343989307 -> Hash (cost=51376.68..51376.68 rows=468255 width=22) (actual rows=468255 loops=1) Buckets: 131072 Batches: 4 Memory Usage: 7438kB -> Hash Join (cost=1857.05..51376.68 rows=468255 width=22) (actual rows=468255 loops=1) AQO: rows=468255, error=0%, fss=-7651474063207585780 Hash Cond: (tf.flight_id = f.flight_id) -> Seq Scan on ticket_flights tf (cost=0.00..43323.35 rows=2360335 width=18) (actual rows=2360335 loops=1) AQO: rows=2360335, error=0%, fss=-6410966714754547713 -> Hash (cost=1652.80..1652.80 rows=16340 width=4) (actual rows=16340 loops=1) Buckets: 16384 Batches: 1 Memory Usage: 703kB -> Seq Scan on flights f (cost=0.00..1652.80 rows=16340 width=4) (actual rows=16340 loops=1) AQO: rows=16340, error=0%, fss=-1289471166524579716 Filter: (scheduled_departure > '2017-08-15 22:00:00+07'::timestamp with time zone) Rows Removed by Filter: 49324 Using aqo: true AQO mode: LEARN AQO advanced: OFF Query hash: 402936317627943257 JOINS: 2 (23 rows)
Изменив константу в запросе, можно заметить, что предсказание сделано с ошибкой:
demo=# EXPLAIN (ANALYZE, SUMMARY OFF, TIMING OFF) SELECT bp.* FROM ticket_flights tf JOIN flights f ON f.flight_id = tf.flight_id JOIN boarding_passes bp ON bp.ticket_no = tf.ticket_no AND bp.flight_id = tf.flight_id WHERE f.scheduled_departure > '2017-08-10 15:00:00+00'; QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------- Hash Join (cost=61144.50..136890.86 rows=2215 width=25) (actual rows=111397 loops=1) AQO: rows=2215, error=-4929%, fss=8598194613120045129 Hash Cond: ((bp.flight_id = f.flight_id) AND (bp.ticket_no = tf.ticket_no)) -> Seq Scan on boarding_passes bp (cost=0.00..32894.95 rows=1894295 width=25) (actual rows=1894295 loops=1) AQO: rows=1894295, error=0%, fss=1362775811343989307 -> Hash (cost=51376.68..51376.68 rows=468255 width=22) (actual rows=577437 loops=1) Buckets: 131072 (originally 131072) Batches: 8 (originally 4) Memory Usage: 7169kB -> Hash Join (cost=1857.05..51376.68 rows=468255 width=22) (actual rows=577437 loops=1) AQO: rows=468255, error=-23%, fss=-7651474063207585780 Hash Cond: (tf.flight_id = f.flight_id) -> Seq Scan on ticket_flights tf (cost=0.00..43323.35 rows=2360335 width=18) (actual rows=2360335 loops=1) AQO: rows=2360335, error=0%, fss=-6410966714754547713 -> Hash (cost=1652.80..1652.80 rows=16340 width=4) (actual rows=19040 loops=1) Buckets: 32768 (originally 16384) Batches: 1 (originally 1) Memory Usage: 926kB -> Seq Scan on flights f (cost=0.00..1652.80 rows=16340 width=4) (actual rows=19040 loops=1) AQO: rows=16340, error=-17%, fss=-1289471166524579716 Filter: (scheduled_departure > '2017-08-10 22:00:00+07'::timestamp with time zone) Rows Removed by Filter: 46624 Using aqo: true AQO mode: LEARN AQO advanced: OFF Query hash: 402936317627943257 JOINS: 2 (23 rows)
Однако вместо пересчёта полей features
и targets
, aqo добавил новые значения избирательности и оценки количества строк для этого запроса в aqo_data
:
demo=# select * from aqo_data; fs | fss | dbid | nfeatures | features | targets | reliability | oids ----+----------------------+-------+-----------+---------------------------------------------------------------------------------------+-----------------------------------------+-------------+------------ --------- 0 | 1362775811343989307 | 16429 | 0 | | {14.454357295615447} | {1} | {16452} 0 | -6410966714754547713 | 16429 | 0 | | {14.674314116080508} | {1} | {16479} 0 | -1289471166524579716 | 16429 | 1 | {{-1.3775704575284085},{-1.2287385463463019}} | {9.701371368413994,9.854297308345357} | {1,1} | {16458} 0 | 8598194613120045129 | 16429 | 2 | {{-13.492416828684513,-1.3775704575284085},{-13.492416828684513,-1.2287385463463019}} | {7.703007682479236,11.620855676130656} | {1,1} | {16479,1645 8,16452} 0 | -7651474063207585780 | 16429 | 2 | {{-11.092306109090387,-1.3775704575284085},{-11.092306109090387,-1.2287385463463019}} | {13.056768298305919,13.266354624518149} | {1,1} | {16479,1645 8} (5 rows)
Теперь в предсказании есть небольшая погрешность примерно в 3%, которая может объясняться погрешностью вычислений:
demo=# EXPLAIN (ANALYZE, SUMMARY OFF, TIMING OFF) SELECT bp.* FROM ticket_flights tf JOIN flights f ON f.flight_id = tf.flight_id JOIN boarding_passes bp ON bp.ticket_no = tf.ticket_no AND bp.flight_id = tf.flight_id WHERE f.scheduled_departure > '2017-08-10 15:00:00+00'; QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------- Hash Join (cost=63438.72..139820.11 rows=108541 width=25) (actual rows=111397 loops=1) AQO: rows=108541, error=-3%, fss=8598194613120045129 Hash Cond: ((bp.flight_id = f.flight_id) AND (bp.ticket_no = tf.ticket_no)) -> Seq Scan on boarding_passes bp (cost=0.00..32894.95 rows=1894295 width=25) (actual rows=1894295 loops=1) AQO: rows=1894295, error=0%, fss=1362775811343989307 -> Hash (cost=51410.19..51410.19 rows=576635 width=22) (actual rows=577437 loops=1) Buckets: 131072 Batches: 8 Memory Usage: 4966kB -> Hash Join (cost=1890.56..51410.19 rows=576635 width=22) (actual rows=577437 loops=1) AQO: rows=576635, error=-0%, fss=-7651474063207585780 Hash Cond: (tf.flight_id = f.flight_id) -> Seq Scan on ticket_flights tf (cost=0.00..43323.35 rows=2360335 width=18) (actual rows=2360335 loops=1) AQO: rows=2360335, error=0%, fss=-6410966714754547713 -> Hash (cost=1652.80..1652.80 rows=19021 width=4) (actual rows=19040 loops=1) Buckets: 32768 Batches: 1 Memory Usage: 926kB -> Seq Scan on flights f (cost=0.00..1652.80 rows=19021 width=4) (actual rows=19040 loops=1) AQO: rows=19021, error=-0%, fss=-1289471166524579716 Filter: (scheduled_departure > '2017-08-10 22:00:00+07'::timestamp with time zone) Rows Removed by Filter: 46624 Using aqo: true AQO mode: LEARN AQO advanced: OFF Query hash: 402936317627943257 JOINS: 2 (23 rows)
Можно изменить запрос, добавив некоторую таблицу в список JOIN
. В этом случае aqo будет прогнозировать оценку количества строк на узлах, использовавшихся для обучения.
demo=# EXPLAIN (ANALYZE, SUMMARY OFF, TIMING OFF) SELECT t.* FROM ticket_flights tf JOIN flights f ON f.flight_id = tf.flight_id JOIN boarding_passes bp ON bp.ticket_no = tf.ticket_no AND bp.flight_id = tf.flight_id JOIN tickets t ON t.ticket_no = tf.ticket_no WHERE f.scheduled_departure > '2017-08-15 15:00:00+00'; QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------- Nested Loop (cost=61158.91..134296.78 rows=2273 width=104) (actual rows=2215 loops=1) AQO not used, fss=-8581941154270057231 -> Hash Join (cost=61158.48..133208.83 rows=2273 width=28) (actual rows=2215 loops=1) AQO: rows=2273, error=3%, fss=8598194613120045129 Hash Cond: ((bp.flight_id = f.flight_id) AND (bp.ticket_no = tf.ticket_no)) -> Seq Scan on boarding_passes bp (cost=0.00..32894.95 rows=1894295 width=18) (actual rows=1894295 loops=1) AQO: rows=1894295, error=0%, fss=1362775811343989307 -> Hash (cost=51376.89..51376.89 rows=468906 width=22) (actual rows=468255 loops=1) Buckets: 131072 Batches: 4 Memory Usage: 7438kB -> Hash Join (cost=1857.26..51376.89 rows=468906 width=22) (actual rows=468255 loops=1) AQO: rows=468906, error=0%, fss=-7651474063207585780 Hash Cond: (tf.flight_id = f.flight_id) -> Seq Scan on ticket_flights tf (cost=0.00..43323.35 rows=2360335 width=18) (actual rows=2360335 loops=1) AQO: rows=2360335, error=0%, fss=-6410966714754547713 -> Hash (cost=1652.80..1652.80 rows=16357 width=4) (actual rows=16340 loops=1) Buckets: 16384 Batches: 1 Memory Usage: 703kB -> Seq Scan on flights f (cost=0.00..1652.80 rows=16357 width=4) (actual rows=16340 loops=1) AQO: rows=16357, error=0%, fss=-1289471166524579716 Filter: (scheduled_departure > '2017-08-15 22:00:00+07'::timestamp with time zone) Rows Removed by Filter: 49324 -> Index Scan using tickets_pkey on tickets t (cost=0.42..0.48 rows=1 width=104) (actual rows=1 loops=2215) AQO not used, fss=2731022528523952664 Index Cond: (ticket_no = bp.ticket_no) Using aqo: true AQO mode: LEARN AQO advanced: OFF Query hash: 7809046947949890015 JOINS: 3 (28 rows)
Пример F.2. Использование представления aqo_query_stat
В представлении aqo_query_stat
отображается статистика времени планирования запросов, времени выполнения запросов и ошибок оценки количества строк. На основании этих данных вы можете принимать решения об использовании предсказаний aqo для различных классов запросов.
Обратимся к представлению aqo_query_stats
:
demo=# SELECT * FROM aqo_query_stat \gx -[ RECORD 1 ]-----------------+--------------------------------------------------------------- queryid | 7809046947949890015 dbid | 16429 execution_time_with_aqo | {1.039218233,0.925258453,0.831166925,0.779602353} execution_time_without_aqo | {1.022052611,0.936486619} planning_time_with_aqo | {0.003305339,0.002129048,0.002538877,0.002142972} planning_time_without_aqo | {0.000767553,0.000711208} cardinality_error_with_aqo | {0.4854215265638894,0,1.1711726076352047,0.007732205169478082} cardinality_error_without_aqo | {0.4854215265638894,1.571562511977072} executions_with_aqo | 4 executions_without_aqo | 2
Полученные данные относятся к запросу, рассмотренному в примере Пример F.1. Этот запрос выполнялся с каждым из параметров f.scheduled_departure > '2017-08-10 15:00:00+00'
и f.scheduled_departure > '2017-08-15 15:00:00+00'
по одному разу без aqo и по два раза с aqo. Видно, что с aqo погрешность оценки количества строк уменьшается до 0,0077, а минимальная погрешность оценки количества строк без aqo составляет 0,4854. Кроме того, время выполнения с aqo меньше, чем без него. Таким образом, можно сделать вывод, что aqo хорошо обучается на этом запросе и предсказание можно использовать для этого класса запросов.
Пример F.3. Использование расширенного режима aqo
Расширенный режим позволяет более гибко управлять расширением aqo. При включении данного режима, то есть
demo=# SET aqo.advanced = on;
aqo будет собирать данные машинного обучения отдельно для каждого выполняемого запроса. Например:
demo=# EXPLAIN (ANALYZE, SUMMARY OFF, TIMING OFF) SELECT bp.* FROM ticket_flights tf JOIN flights f ON f.flight_id = tf.flight_id JOIN boarding_passes bp ON bp.ticket_no = tf.ticket_no AND bp.flight_id = tf.flight_id WHERE f.scheduled_departure > '2017-08-15 15:00:00+00'; QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------- Hash Join (cost=63796.33..140286.73 rows=477728 width=25) (actual rows=2215 loops=1) AQO not used, fss=8598194613120045129 Hash Cond: ((bp.flight_id = f.flight_id) AND (bp.ticket_no = tf.ticket_no)) -> Seq Scan on boarding_passes bp (cost=0.00..32894.95 rows=1894295 width=25) (actual rows=1894295 loops=1) AQO not used, fss=1362775811343989307 -> Hash (cost=51379.43..51379.43 rows=595260 width=22) (actual rows=468255 loops=1) Buckets: 131072 Batches: 8 Memory Usage: 4223kB -> Hash Join (cost=1859.80..51379.43 rows=595260 width=22) (actual rows=468255 loops=1) AQO not used, fss=-7651474063207585780 Hash Cond: (tf.flight_id = f.flight_id) -> Seq Scan on ticket_flights tf (cost=0.00..43323.35 rows=2360335 width=18) (actual rows=2360335 loops=1) AQO not used, fss=-6410966714754547713 -> Hash (cost=1652.80..1652.80 rows=16560 width=4) (actual rows=16340 loops=1) Buckets: 32768 Batches: 1 Memory Usage: 831kB -> Seq Scan on flights f (cost=0.00..1652.80 rows=16560 width=4) (actual rows=16340 loops=1) AQO not used, fss=-1289471166524579716 Filter: (scheduled_departure > '2017-08-15 22:00:00+07'::timestamp with time zone) Rows Removed by Filter: 49324 Using aqo: true AQO mode: LEARN AQO advanced: ON Query hash: 402936317627943257 JOINS: 2 (23 rows)
Теперь этот запрос хранится в aqo_data с ненулевым fs
(по умолчанию fs
равен хешу запроса):
demo=# SELECT * FROM aqo_data; fs | fss | dbid | nfeatures | features | targets | reliability | oids --------------------+----------------------+-------+-----------+---------------------------------------------+----------------------+-------------+--------------------- 402936317627943257 | -6410966714754547713 | 16429 | 0 | | {14.674314116080508} | {1} | {16479} 402936317627943257 | 1362775811343989307 | 16429 | 0 | | {14.454357295615447} | {1} | {16452} 402936317627943257 | -1289471166524579716 | 16429 | 1 | {{-1.3775704575284085}} | {9.701371368413994} | {1} | {16458} 402936317627943257 | 8598194613120045129 | 16429 | 2 | {{-13.492416828684513,-1.3775704575284085}} | {7.703007682479236} | {1} | {16479,16458,16452} 402936317627943257 | -7651474063207585780 | 16429 | 2 | {{-11.092306109090387,-1.3775704575284085}} | {13.056768298305919} | {1} | {16479,16458} (5 rows)
Можно настроить несколько параметров только для данного запроса. Это значения learn_aqo
, use_aqo
и auto_tuning
в представлении aqo_queries:
demo=# SELECT * FROM aqo_queries; queryid | dbid | fs | learn_aqo | use_aqo | auto_tuning | smart_timeout | count_increase_timeout --------------------+-------+--------------------+-----------+---------+-------------+---------------+------------------------ 402936317627943257 | 16429 | 402936317627943257 | t | t | f | 0 | 0 0 | 0 | 0 | f | f | f | 0 | 0 (2 rows)
Зададим для use_aqo
значение false
:
demo=# SELECT aqo_queries_update(402936317627943257, NULL, NULL, NULL, false, NULL); aqo_queries_update -------------------- t (1 row)
Теперь изменим константу в запросе:
demo=# EXPLAIN (ANALYZE, SUMMARY OFF, TIMING OFF) SELECT bp.* FROM ticket_flights tf JOIN flights f ON f.flight_id = tf.flight_id JOIN boarding_passes bp ON bp.ticket_no = tf.ticket_no AND bp.flight_id = tf.flight_id WHERE f.scheduled_departure > '2017-08-10 15:00:00+00'; QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------- Hash Join (cost=65822.16..142872.60 rows=554378 width=25) (actual rows=111397 loops=1) AQO not used, fss=0 Hash Cond: ((bp.flight_id = f.flight_id) AND (bp.ticket_no = tf.ticket_no)) -> Seq Scan on boarding_passes bp (cost=0.00..32894.95 rows=1894295 width=25) (actual rows=1894295 loops=1) AQO not used, fss=0 -> Hash (cost=51412.64..51412.64 rows=690768 width=22) (actual rows=577437 loops=1) Buckets: 131072 Batches: 8 Memory Usage: 4966kB -> Hash Join (cost=1893.01..51412.64 rows=690768 width=22) (actual rows=577437 loops=1) AQO not used, fss=0 Hash Cond: (tf.flight_id = f.flight_id) -> Seq Scan on ticket_flights tf (cost=0.00..43323.35 rows=2360335 width=18) (actual rows=2360335 loops=1) AQO not used, fss=0 -> Hash (cost=1652.80..1652.80 rows=19217 width=4) (actual rows=19040 loops=1) Buckets: 32768 Batches: 1 Memory Usage: 926kB -> Seq Scan on flights f (cost=0.00..1652.80 rows=19217 width=4) (actual rows=19040 loops=1) AQO not used, fss=0 Filter: (scheduled_departure > '2017-08-10 22:00:00+07'::timestamp with time zone) Rows Removed by Filter: 46624 Using aqo: false AQO mode: LEARN AQO advanced: ON Query hash: 402936317627943257 JOINS: 2 (23 rows)
aqo не использовался для этого запроса, но в представлении aqo_data
появились новые данные:
demo=# SELECT * FROM aqo_data; fs | fss | dbid | nfeatures | features | targets | reliability | oids --------------------+----------------------+-------+-----------+---------------------------------------------------------------------------------------+-----------------------------------------+-------------+--------------------- 402936317627943257 | -6410966714754547713 | 16429 | 0 | | {14.674314116080508} | {1} | {16479} 402936317627943257 | 1362775811343989307 | 16429 | 0 | | {14.454357295615447} | {1} | {16452} 402936317627943257 | -1289471166524579716 | 16429 | 1 | {{-1.3775704575284085},{-1.2287385463463019}} | {9.701371368413994,9.854297308345357} | {1,1} | {16458} 402936317627943257 | 8598194613120045129 | 16429 | 2 | {{-13.492416828684513,-1.3775704575284085},{-13.492416828684513,-1.2287385463463019}} | {7.703007682479236,11.620855676130656} | {1,1} | {16479,16458,16452} 402936317627943257 | -7651474063207585780 | 16429 | 2 | {{-11.092306109090387,-1.3775704575284085},{-11.092306109090387,-1.2287385463463019}} | {13.056768298305919,13.266354624518149} | {1,1} | {16479,16458} (5 rows)
Установленное значение параметра use_aqo
не относится к другим запросам. После выполнения другого запроса дважды видно, что aqo обучается на нём и делает для него предсказание:
EXPLAIN (ANALYZE, SUMMARY OFF, TIMING OFF) SELECT t.* FROM ticket_flights tf JOIN flights f ON f.flight_id = tf.flight_id JOIN boarding_passes bp ON bp.ticket_no = tf.ticket_no AND bp.flight_id = tf.flight_id JOIN tickets t ON t.ticket_no = tf.ticket_no WHERE f.scheduled_departure > '2017-08-15 15:00:00+00'; QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------- Nested Loop (cost=61144.93..134251.05 rows=2215 width=104) (actual rows=2215 loops=1) AQO: rows=2215, error=0%, fss=-8581941154270057231 -> Hash Join (cost=61144.50..133190.86 rows=2215 width=28) (actual rows=2215 loops=1) AQO: rows=2215, error=0%, fss=8598194613120045129 Hash Cond: ((bp.flight_id = f.flight_id) AND (bp.ticket_no = tf.ticket_no)) -> Seq Scan on boarding_passes bp (cost=0.00..32894.95 rows=1894295 width=18) (actual rows=1894295 loops=1) AQO: rows=1894295, error=0%, fss=1362775811343989307 -> Hash (cost=51376.68..51376.68 rows=468255 width=22) (actual rows=468255 loops=1) Buckets: 131072 Batches: 4 Memory Usage: 7438kB -> Hash Join (cost=1857.05..51376.68 rows=468255 width=22) (actual rows=468255 loops=1) AQO: rows=468255, error=0%, fss=-7651474063207585780 Hash Cond: (tf.flight_id = f.flight_id) -> Seq Scan on ticket_flights tf (cost=0.00..43323.35 rows=2360335 width=18) (actual rows=2360335 loops=1) AQO: rows=2360335, error=0%, fss=-6410966714754547713 -> Hash (cost=1652.80..1652.80 rows=16340 width=4) (actual rows=16340 loops=1) Buckets: 16384 Batches: 1 Memory Usage: 703kB -> Seq Scan on flights f (cost=0.00..1652.80 rows=16340 width=4) (actual rows=16340 loops=1) AQO: rows=16340, error=0%, fss=-1289471166524579716 Filter: (scheduled_departure > '2017-08-15 22:00:00+07'::timestamp with time zone) Rows Removed by Filter: 49324 -> Index Scan using tickets_pkey on tickets t (cost=0.42..0.48 rows=1 width=104) (actual rows=1 loops=2215) AQO not used, fss=2731022528523952664 Index Cond: (ticket_no = bp.ticket_no) Using aqo: true AQO mode: LEARN AQO advanced: ON Query hash: 7809046947949890015 JOINS: 3 (28 rows)
Пример F.4. Использование режима «песочницы»
SET aqo.sandbox = ON; SET aqo.enable = ON; SET aqo.advanced = OFF; -- Очистка базы знаний песочницы, не затрагивающая основные данные SELECT aqo_reset(); EXPLAIN (ANALYZE, SUMMARY OFF, TIMING OFF) SELECT t.* FROM ticket_flights tf JOIN flights f ON f.flight_id = tf.flight_id JOIN boarding_passes bp ON bp.ticket_no = tf.ticket_no AND bp.flight_id = tf.flight_id JOIN tickets t ON t.ticket_no = tf.ticket_no WHERE f.scheduled_departure > '2017-08-15 15:00:00+00'; -- Выполнение предыдущего запроса, пока планы не стабилизируются. ... -- Копирование данных, полученных из песочницы с aqo.advanced = OFF CREATE TABLE aqo_data_sandbox AS SELECT * FROM aqo_data; SET aqo.sandbox = OFF; SELECT aqo_data_update (fs, fss, dbid, nfeatures, features, targets, reliability, oids) FROM aqo_data_sandbox WHERE fs = 0; DROP TABLE aqo_data_sandbox;
F.2.6. Автор #
Олег Иванов