User Interface (v1.5)

GUCs

• aqo.mode - defines how AQO works with incoming queries. Possible modes: DISABLED, CONTROLLED, LEARN, INTELLIGENT and FROZEN.
• aqo.show_hash - hash value computed by a query tree which have a sense of a class of queries or class of plan nodes. Starting from PostgreSQL 14 AQO uses native queryid as a query class on purpose of interconnection with another extensions, such as pg_stat_statements. So, QueryId can be taken from 'Query Identifier' field in EXPLAIN ANALYZE of a query.
• aqo.show_details - adds some details into EXPLAIN of a query, such as prediction or feature subspace hash. Also, shows some additional on screen information related to AQO specifics.
• aqo.join_threshold - ignore queries which embodies lesser number of joins.
• aqo.learn_statement_timeout - AQO will use current state of a plan tree to improve future predictions if query is interrupted with statement timeout.
• aqo.force_collect_stat - allow AQO to gather statistics on query executions even in DISABLED mode. Although AQO doesn't make any predictions, some additional overhead will be added.
• aqo.wide_search - enable searching neighbors with the same fss among different query classes.
• aqo.fs_max_items - maximum number of feature spaces that AQO can operate with.
• aqo.fss_max_items - maximum number of feature subspaces that AQO can operate with.
• aqo.querytext_max_size - maximum size of query text, which is saved in aqo_query_texts.
• aqo.min_neighbors_for_predicting - minimum number of neighbors is used for cardinality prediction: if there are fewer of them, AQO won't make prediction.
• aqo.predict_with_few_neighbors - enable to make predictions with fewer neighbors than were found.
• aqo.statement_timeout - the initial value of smart statement timeout is for manual training of AQO on complex queries.
• aqo.dsm_size_max - maximum size of dynamic shared memory which AQO could allocate to store learning data.

Functions

AQO adds into postgres catalog several additional functions.

Storage management

• aqo_cleanup() - remove data related to a query classes which [partly] linked with removed relations. Returns a number of removed feature spaces (classes) and feature subspaces. Insensitive to removing of another objects.
• aqo_enable_query(queryid) - set learn_aqo, use_aqo and auto_tuning to true related to given query class.
• aqo_disable_query(queryid) - set learn_aqo, use_aqo and auto_tuning to false related to given query class.
• aqo_drop_class(queryid) - remove all data from the AQO storage related to given query class.
• aqo_reset() - remove data from the AQO storage (ML data, query texts, statistics, query class preferences).
• aqo_queries_update() - for specific queryid allow to manually change some class settings: feature space ID, learn or not AQO on this class, use AQO for cardinality predictions of queries, included in this class, use autotuning or not. NULL value means 'Stay the same'.

Analytics

• aqo_cardinality_error(controlled) - show cardinality error for each query class. If controlled == true, show the error of last execution under control of AQO. With controlled == false returns average cardinality error among all logged executions made without control of AQO.
• aqo_execution_time(controlled) - show execution time for each query class. Semantics of controlled parameter see in description of aqo_cardinality_error routine.

AQO VIEWs

• aqo_data() - show raw learning data
• aqo_queries() - show per class settings
• aqo_query_stat() - show statistics (under control of AQO or without, if aqo.force_collect_stat is on)
• aqo_query_texts() - show text of first query which caused the query class creation.