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:

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 optimization mode to use.

If you often run queries of the same type, for example, your application limits the number of possible query types, 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 types is stored separately. If performance is not improved after 50 iterations, the aqo extension falls back to the default query planner.

Since the intelligent mode tries to learn separately for different query types, aqo may fail to provide performance improvements if the types 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 types and tries to optimize all queries together. This mode can help you optimize workloads with multiple different query types, 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 types, so they will not be optimized. For known query types, aqo will continue collecting statistics and using optimized planning algorithms.

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.

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 type to separate the collected statistics. If you switch to the forced mode, the statistics for all untracked query types is stored in a common query type with hash 0. You can view all the processed query types and their corresponding hash values in the aqo_query_texts table:

SELECT * FROM aqo_query_texts;

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

SELECT * FROM aqo_queries;

For each query type, the following settings are available:

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

 -- Add a new query type 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 type:

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 type 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 type, 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 type. As a result, aqo disables automatic changing of the learn_aqo and use_aqo settings.

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

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

where hash is the hash value for this query type.

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;

In master-standby configurations, aqo statistics on standby is inherited from master in read-only mode. Standby queries do not update aqo statistics, regardless of the current aqo settings. If you would like to update the statistics on standby for one or more query types, you must run these queries on master and synchronize the standby.

You can define whether to use aqo statistics for standby queries independent of the master configuration, toggling aqo modes as required. By default, aqo optimizes queries on standby as follows:

If you fine-tuned optimization settings for specific query types on master, these settings override the default aqo behavior, unless you choose the disabled mode. For details, see Section F.2.2.2.