F.3. aqo

The aqo module is a Postgres Pro Enterprise extension for cost-based query optimization. Using machine learning methods, more precisely, a modification of the k-NN algorithm, aqo improves cardinality estimation, which can optimize execution plans and, consequently, speed up query execution.

The aqo module can collect statistics on all the executed queries, excluding the queries that access system relations. The collected statistics is classified by query class. If the queries differ in their constants only, they belong to the same class. For each query class, aqo stores the cardinality quality, planning time, execution time, and execution statistics for machine learning. Based on this data, aqo builds a new query plan and uses it for the next query of the same class. aqo test runs have shown significant performance improvements for complex queries.

Important

Query optimization using the aqo module is not supported on standby.

F.3.1. Installation and Setup

The aqo extension is included into Postgres Pro Enterprise. Once you have Postgres Pro Enterprise installed, complete the following steps to enable aqo:

  1. Add aqo to the shared_preload_libraries parameter in the postgresql.conf file:

    shared_preload_libraries = 'aqo'
    

    The aqo library must be preloaded at the server startup, since adaptive query optimization needs to be enabled per cluster. Otherwise, aqo will only be used for the session in which you created the aqo extension.

  2. Create the aqo extension using the following query:

    CREATE EXTENSION aqo;
    

Once the extension is created, you can start optimizing queries.

To disable aqo at the cluster level and remove all the collected statistics, run:

DROP EXTENSION aqo;

F.3.1.1. Configuration

By default, aqo does not affect query performance. To enable adaptive query optimization for your database, add the aqo.mode variable to your postgresql.conf file and reload the cluster. Depending on your database usage model, you can choose between the following modes:

  • intelligent — this mode auto-tunes your queries based on statistics collected per query class.

  • forced — this mode collects statistics for all queries altogether without any classification.

  • controlled — this mode uses the default planner for all new queries, but continues using the previously specified planning settings for already known query classes, if any.

  • learn — this mode collects statistics on all the executed queries and updates the data for query classes.

  • frozen — this mode reads the collected statistics for already known query classes but doesn't collect any new data. You can use this mode to reduce the impact of aqo on query planning and execution.

  • disabled — this mode disables aqo for all queries, even for the known query classes. You can use this mode to temporarily disable aqo without losing the collected statistics and configuration.

To dynamically change the aqo settings in your current session, run the following command:

SET aqo.mode = 'mode';

where mode is the name of the operation mode to use.

Important

The intelligent mode of aqo may not work well if the queries in your workload are of multiple different classes. In this case, you can try resetting the mode to controlled.

F.3.2. Usage

F.3.2.1. Choosing Operation Mode for Query Optimization

If you often run queries of the same class, for example, your application limits the number of possible query classes, you can use the intelligent mode to improve planning for these queries. In this mode, aqo analyzes each query execution and stores statistics. Statistics on queries of different classes is stored separately. If performance is not improved after 50 iterations, the aqo extension falls back to the default query planner.

Note

You can view the current query plan using the standard Postgres Pro EXPLAIN command with the ANALYZE option. For details, see the Section 14.1.

Since the intelligent mode tries to learn separately for different query classes, aqo may fail to provide performance improvements if the classes of the queries in the workload are constantly changing. For such dynamic workloads, reset the aqo extension to the controlled mode, or try using the forced mode.

In the forced mode, aqo does not classify the collected statistics by query classes and tries to optimize all queries together. This mode can help you optimize workloads with multiple different query classes, and it consumes less memory than the intelligent mode. However, since the forced mode lacks intelligent tuning, performance may decrease for some queries. If you see performance issues in this mode, switch aqo to the controlled mode.

In the controlled mode, aqo does not collect statistics for new query classes, so they will not be optimized. For known query classes, aqo will continue collecting statistics and using optimized planning algorithms.

The learn mode collects statistics from all the executed queries and updates the data for query classes. This mode is similar to the intelligent mode, except that it doesn't provide intelligent tuning.

If you want to reduce the impact of aqo on query planning and execution, you can use it in the frozen mode. In this mode, aqo only reads the collected statistics for already known query classes but doesn't collect any new data.

If you want to fully disable aqo, you can switch aqo to the disabled mode. In this case, the default planner is used for all queries, but the collected statistics and aqo settings are saved and can be used in the future.

F.3.2.2. Fine-Tuning aqo

You must have superuser rights to access aqo tables and configure advanced query settings.

When run in the intelligent mode, aqo assigns a unique hash value to each query class to separate the collected statistics. If you switch to the forced mode, the statistics for all untracked query classes is stored in a common query class with hash 0. You can view all the processed query classes and their corresponding hash values in the aqo_query_texts table:

SELECT * FROM aqo_query_texts;

Each query class has its own optimization settings. These settings are stored in the aqo_queries table:

SELECT * FROM aqo_queries;

For each query class, the following settings are available:

  • query_hash stores the hash value that uniquely identifies the query class.

  • learn_aqo enables statistics collection for this query class.

  • use_aqo enables aqo cardinality prediction for the next execution of this query class.

  • fspace_hash is a unique identifier of the separate space in which the statistics for this query class is collected. By default, fspace_hash is equal to query_hash.

  • auto_tuning shows whether aqo tries to change other settings for the given query. By default, auto-tuning is enabled in the intelligent mode.

You can manually change these settings to adjust optimization for a particular query class. For example:

 -- Add a new query class into the aqo_queries table:

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

 -- Disable auto_tuning, enable both learn_aqo and use_aqo 
 -- for this query class:

UPDATE aqo_queries SET use_aqo=true, learn_aqo=true, auto_tuning=false 
  WHERE query_hash = (SELECT query_hash from aqo_query_texts 
  WHERE query_text LIKE 'SELECT * FROM a, b WHERE a.id=b.id;');

 -- Run EXPLAIN ANALYZE until the plan changes:

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

 -- Disable learning to stop statistics collection 
 -- and use the optimized plan:

UPDATE aqo_queries SET learn_aqo=false 
  WHERE query_hash = (SELECT query_hash from aqo_query_texts 
  WHERE query_text LIKE 'SELECT * FROM a, b WHERE a.id=b.id;');

If your data or query distribution is rapidly changing, learning new statistics will take longer than usual. In this case, obsolete statistics may affect performance. To speed up aqo learning, reset the statistics. To remove all the collected machine learning statistics, run the following command:

DELETE FROM aqo_data;

Alternatively, you can specify a particular query class to reset by providing its hash value in the fspace_hash option. For example:

DELETE FROM aqo_data WHERE fspace_hash = (SELECT fspace_hash FROM aqo_queries 
  WHERE query_hash = (SELECT query_hash from aqo_query_texts 
  WHERE query_text LIKE 'SELECT * FROM a, b WHERE a.id=b.id;'));

To stop intelligent tuning for a particular query class, disable the auto_tuning setting:

UPDATE aqo_queries SET auto_tuning=false WHERE query_hash = 'hash';

where hash is the hash value for this query class. As a result, aqo disables automatic changing of the learn_aqo and use_aqo settings.

To disable further learning for a particular query class, use the following command:

UPDATE aqo_queries SET learn_aqo=false WHERE query_hash = 'hash';

where hash is the hash value for this query class.

To fully disable aqo for all queries and use the default PostgreSQL query planner, run:

UPDATE aqo_queries SET use_aqo=false, learn_aqo=false, auto_tuning=false;

F.3.3. Reference

F.3.3.1. Configuration Variables

F.3.3.1.1. aqo.mode

Defines aqo operation mode.

Table F.2. aqo.mode Options

OptionDescription
intelligentAuto-tunes your queries based on statistics collected per query class.
forcedCollects statistics for all queries altogether without any classification.
controlledDefault. Uses the default planner for all new queries, but can reuse the collected statistics for already known query classes, if any.
learnCollects statistics on all the executed queries and updates the data for query classes.
frozenReads the collected statistics for already known query classes but doesn't collect any new data in order to reduce the impact of aqo on query planning and execution.
disabledFully disables aqo for all queries. The collected statistics and aqo settings are saved and can be used in the future.

F.3.3.2. Tables

Important

You can manually change optimization settings in the aqo_queries table. You may also modify other tables, but only if you understand the logic of adaptive query optimization.

F.3.3.2.1. aqo_query_texts Table

The aqo_query_texts table classifies all the query classes processed by aqo. For each query class, the table contains the text of the first analyzed query of this class.

Table F.3. aqo_query_texts Table

Column NameDescription
query_hashStores the hash value that uniquely identifies the query class.
query_textProvides the text of the first analyzed query of the given class.

F.3.3.2.2. aqo_queries Table

The aqo_queries table stores optimization settings for different query classes.

Table F.4. aqo_queries Table

SettingDescription
query_hashStores the hash value that uniquely identifies the query class.
learn_aqoEnables statistics collection for this query class.
use_aqoEnables aqo cardinality prediction for the next execution of this query class. If cost estimation model is incomplete, this may slow down query execution.
fspace_hashProvides a unique identifier of the separate space in which the statistics for this query class is collected. By default, fspace_hash is equal to query_hash. You can change this setting to a different query_hash to optimize different query classes together. It may decrease the amount of memory for models and even improve query execution performance. However, changing this setting may cause unexpected aqo behavior, so make sure to use it only if you know what you are doing.
auto_tuningShows whether aqo tries to tune other settings for the given query. By default, auto-tuning is enabled in the intelligent mode. In other modes, new queries are not appended to aqo_queries automatically. You can change this behavior by setting the auto_tuning variable to true.

F.3.3.2.3. aqo_data Table

The aqo_data table contains machine learning data for cardinality estimation refinement. To forget all the collected statistics for a particular query class, you can delete all rows from aqo_data with the corresponding fspace_hash.

F.3.3.2.4. aqo_query_stat Table

The aqo_query_stat table stores statistics on query execution, by query class. The aqo extension uses this data when the auto_tuning option is enabled for a particular query class.

Table F.5. aqo_query_stat Table

DataDescription
execution_time_with_aqoExecution time for queries run with aqo enabled.
execution_time_without_aqoExecution time for queries run with aqo disabled.
planning_time_with_aqoPlanning time for queries run with aqo enabled.
planning_time_without_aqoPlanning time for queries run with aqo disabled.
cardinality_error_with_aqoCardinality estimation error in the selected query plans with aqo enabled.
cardinality_error_without_aqoCardinality estimation error in the selected query plans with aqo disabled.
executions_with_aqoNumber of queries run with aqo enabled.
executions_without_aqoNumber of queries run with aqo disabled.

F.3.4. Author

Oleg Ivanov