## 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 type. If the queries differ in their constants only, they belong to the same type. For each type, `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 type. `aqo` test runs have shown significant performance improvements for complex queries.

### F.3.1. Installation and Setup

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

1. When setting up your server, add `aqo` to the `shared_preload_libraries` variable 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 type.

- `forced` — this mode tries to optimize all new queries together, regardless of the query type.

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

- `disabled` — this mode disables `aqo` for all queries, even for the known query types. 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 optimization mode to use.

### Important

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

### F.3.2. Usage

#### F.3.2.1. Choosing Query Optimization Modes

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.

### 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 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.

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

- `query_hash` stores the hash value that uniquely identifies the query type.

- `learn_aqo` enables statistics collection for this query type.

- `use_aqo` enables `aqo` cardinality prediction for the next execution of this query type.

- `fspace_hash` is a unique identifier of the separate space in which the statistics for this query type 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 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;

#### F.3.2.3. Using aqo on Standby

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:

- In `intelligent` and `controlled` modes, `aqo` statistics is only used for the already known query types.

- In the `forced` mode, `aqo` tries to optimize the unknown queries based on the common statistics collected in the `forced` mode on master, if any. If the common statistics is not found, `aqo` falls back to the default query planner. All the known query types continue using the available statistics.

- The `disabled` mode switches off the collected statistics completely, relying on the default query planner for all queries.

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.3.2.2.

### F.3.3. Reference

#### F.3.3.1. Configuration Variables

##### F.3.3.1.1. `aqo.mode`

Defines `aqo` optimization modes.

Table F.3. `aqo.mode` Options

Option | Description
--- | ---
`intelligent` | Auto-tunes your queries based on statistics collected per query type.
`forced` | Optimizes all queries together, regardless of the query type.
`controlled` | Default. Uses the default planner for all new queries, but can reuse the collected statistics for already known query types, if any.
`disabled` | Fully 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. Avoid modifying other tables as it may lead to unexpected results.

##### F.3.3.2.1. `aqo_query_texts` Table

The `aqo_query_texts` table classifies all the query types processed by `aqo`.

Table F.4. `aqo_query_texts` Table

Column Name | Description
--- | ---
`query_hash` | Stores the hash value that uniquely identifies the query type.
`query_text` | Provides a sample query of the given type.

##### F.3.3.2.2. `aqo_queries` Table

The `aqo_queries` table stores optimization settings for different query types.

Table F.5. `aqo_queries` Table

Setting | Description
--- | ---
`query_hash` | Stores the hash value that uniquely identifies the query type.
`learn_aqo` | Enables statistics collection for this query type.
`use_aqo` | Enables `aqo` cardinality prediction for the next execution of this query type. If cost estimation model is incomplete, this may slow down query execution.
`fspace_hash` | Provides a unique identifier of the separate space in which the statistics for this query type 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 types 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_tuning` | Shows whether `aqo` tries to tune other settings for the given query. By default, auto-tuning in 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 type, 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 type. The `aqo` extension uses this data when the `auto_tuning` option is enabled for a particular query type.

Table F.6. `aqo_query_stat` Table

Data | Description
--- | ---
`execution_time_with_aqo` | Execution time for queries run with `aqo` enabled.
`execution_time_without_aqo` | Execution time for queries run with `aqo` disabled.
`planning_time_with_aqo` | Planning time for queries run with `aqo` enabled.
`planning_time_without_aqo` | Planning time for queries run with `aqo` disabled.
`cardinality_error_with_aqo` | Cardinality estimation error in the selected query plans with `aqo` enabled.
`cardinality_error_without_aqo` | Cardinality estimation error in the selected query plans with `aqo` disabled.
`executions_with_aqo` | Number of queries run with `aqo` enabled.
`executions_without_aqo` | Number of queries run with `aqo` disabled.

### F.3.4. Authors

Oleg Ivanov `<o.ivanov@postgrespro.ru>`, Postgres Professional, Moscow, Russia