F.2. 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.2.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
:
Add
aqo
to the shared_preload_libraries parameter in thepostgresql.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 theaqo
extension.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.2.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 disablesaqo
for all queries, even for the known query types. You can use this mode to temporarily disableaqo
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.2.2. Usage
F.2.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.2.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
enablesaqo
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 toquery_hash
.auto_tuning
shows whetheraqo
tries to change other settings for the given query. By default, auto-tuning is enabled in theintelligent
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.2.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
andcontrolled
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 theforced
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.2.2.2.
F.2.3. Reference
F.2.3.1. Configuration Variables
F.2.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.2.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.2.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.2.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.2.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.2.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.2.4. Authors
Oleg Ivanov <o.ivanov@postgrespro.ru>
, Postgres Professional, Moscow, Russia