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

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.

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 does not 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 does not 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.

You must have superuser rights to access aqo views 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 view:

SELECT * FROM aqo_query_texts;

Each query class has an associated separate space, called feature space, in which the statistics for this query class is collected. Each feature space has associated feature subspaces, where the information about selectivity and cardinality for each query plan node is collected.

Each query class has its own optimization settings. These settings are shown in the aqo_queries view:

SELECT * FROM aqo_queries;

For each query class, the following settings are available:

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 view:

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:

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;');

 -- 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:

 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;');

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

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');

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:

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');

where hash is the hash value for this query class.

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

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);

aqo adds several functions to Postgres Pro catalog.