Movatterモバイル変換

F.3. aqo

• intelligent — this mode auto-tunes your queries based on statistics collected per query class. See the description of theauto_tuning flag of theaqo_queries view for more details.

• 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 without auto-tuning queries.

• frozen — this mode reads the collected statistics for already known query classes but does not collect any new data. You can use this mode to reduce the impact ofaqo on query planning and execution.

• disabled — this mode disablesaqo for all queries, even for the known query classes. The collected statistics andaqo settings are saved and can be used in the future. You can use this mode to temporarily disableaqo without losing the collected statistics and configuration.

Note

You can view the current query plan using the standardPostgres ProEXPLAIN command with theANALYZE option. For details, see theSection 14.1.

aqo.mode (text)

Defines theaqo operation mode. Possible values are listed inSection F.3.1.1.

Default:controlled.

aqo.show_hash (boolean)

Show a hash value that is computed from a query tree and uniquely identifies the class of queries or class of plan nodes. Starting withPostgres Pro 14,aqo uses the native query ID to identify a query class for consistency with other extensions, such aspg_stat_statements. So, the query ID can be taken from theQuery Identifier field inEXPLAIN ANALYZE output of a query.

Default:off.

aqo.show_details (boolean)

Add some details toEXPLAIN output of a query, such as the prediction or feature-subspace hash, and show some additionalaqo-specific on-screen information.

Default:off.

aqo.join_threshold (integer)

Ignore queries that contain smaller number of joins, which means that statistics for such queries will not be collected.

Default:3.

aqo.statement_timeout (integer)

Defines the initial value of thesmart statement timeout, in milliseconds, which is needed to limit the execution time when manually trainingaqo on special queries with a poor cardinality forecast.aqo can dynamically change the value of the smart statement timeout during this training. When the cardinality estimation error on nodes exceeds 0.1, the value ofaqo.statement_timeout is automatically incremented exponentially, but remains not greater thanstatement_timeout.

Default:0.

aqo.force_collect_stat (boolean)

Gather statistics on query executions even in thedisabled mode. Although no predictions are made, some overhead will be added.

Default:off.

aqo.dsm_size_max (integer)

Defines the maximum size of dynamic shared memory, in MB, thataqo can allocate to store learning data. When this number is exceeded, an attempt to load theaqo_data view will fail with“out of memory” error.

Default:100.

aqo.fs_max_items (integer)

Defines the maximum number of feature spaces thataqo can operate with. When this number is exceeded, learning on new query classes will no longer occur, and they will not appear in the views accordingly.

Default:10000.

aqo.fss_max_items (integer)

Defines the maximum number of feature subspaces thataqo can operate with. When this number is exceeded, the selectivity and cardinality for new query plan nodes will no longer be collected, and new feature subspaces will not appear in theaqo_data view accordingly.

Default:100000.

aqo.wide_search (boolean)

Enables searching neighbors with the same feature subspace among different query classes.

Default:off.

aqo.querytext_max_size (integer)

Defines the maximum size of the query in theaqo_query_texts view.

Default:1000.

aqo.min_neighbors_for_predicting (integer)

Defines the minimum number of neighbors needed for the cardinality prediction. If there are fewer of them,aqo will not make any prediction.

Default:3.

aqo.predict_with_few_neighbors (boolean)

Enablesaqo to make predictions with fewer neighbors than were found.

Default:on.

Theaqo_query_texts view classifies all the query classes processed byaqo. For each query class, the view shows the text of the first analyzed query of this class.

Theaqo_queries view shows optimization settings for different query classes.

Theaqo_data view shows machine learning data for cardinality estimation refinement. To forget all the collected statistics for a particular query class, you can delete all rows fromaqo_data with the correspondingfs.

Theaqo_query_stat view shows statistics on query execution, by query class. Theaqo extension uses this data when theauto_tuning option is enabled for a particular query class.

Consider optimization of a query usingaqo.

When the query is executed for the first time, it is missing in tables underlyingaqo views. So there is no data for predicting withaqo for each plan node, and“AQO not used” lines appear in theEXPLAIN output:

postgres=# EXPLAIN (ANALYZE, SUMMARY OFF, TIMING OFF) select count(*) from score join course on score.cno=course.cno join student on score.sno=student.sno where degree<90 and test_preparation = 0;                                                QUERY PLAN---------------------------------------------------------------------------------------------------------- Aggregate  (cost=308.28..308.29 rows=1 width=8) (actual rows=1 loops=1)   AQO not used, fss=0   ->  Hash Join  (cost=124.80..299.47 rows=3526 width=0) (actual rows=3649 loops=1)         AQO not used, fss=2128507884         Hash Cond: (score.sno = student.sno)         ->  Hash Join  (cost=16.30..181.70 rows=3526 width=4) (actual rows=3649 loops=1)               AQO not used, fss=-303037802               Hash Cond: (score.cno = course.cno)               ->  Seq Scan on score  (cost=0.00..156.00 rows=3526 width=8) (actual rows=3649 loops=1)                     AQO not used, fss=-636613046                     Filter: ((degree < 90) AND (test_preparation = 0))                     Rows Removed by Filter: 1351               ->  Hash  (cost=12.80..12.80 rows=280 width=4) (actual rows=10 loops=1)                     Buckets: 1024  Batches: 1  Memory Usage: 9kB                     ->  Seq Scan on course  (cost=0.00..12.80 rows=280 width=4) (actual rows=10 loops=1)                           AQO not used, fss=-1076069505         ->  Hash  (cost=71.00..71.00 rows=3000 width=4) (actual rows=3000 loops=1)               Buckets: 4096  Batches: 1  Memory Usage: 138kB               ->  Seq Scan on student  (cost=0.00..71.00 rows=3000 width=4) (actual rows=3000 loops=1)                     AQO not used, fss=-1838231581 Using aqo: true AQO mode: LEARN Query hash: -727505571757520766 JOINS: 2(24 rows)

If there is no information on a certain node in theaqo_data view,aqo will add the appropriate record there for future learning and predictions except for nodes withfss=0 in theEXPLAIN output. As each offeatures andtargets in theaqo_data view is a logarithm to basee, to get the actual value, raisee to this power. For example: exp(0):

         fs          |     fss     | nfeatures |                                      features                                      |       targets       | reliability |        oids---------------------+-------------+-----------+------------------------------------------------------------------------------------+---------------------+-------------+--------------------- -727505571757520766 |  2128507884 |         4 | {{-0.03438753143452488,-5.634789603169249,-0.3149847743198556,-8.006367567650246}} | {8.202208436436448} | {1}         | {16579,16555,16563} -727505571757520766 | -1076069505 |         0 |                                                                                    | {2.302585092994046} | {1}         | {16555} -727505571757520766 | -1838231581 |         0 |                                                                                    | {8.006367567650246} | {1}         | {16563} -727505571757520766 |  -303037802 |         3 | {{-0.03438753143452488,-5.634789603169249,-0.3149847743198556}}                    | {8.202208436436448} | {1}         | {16579,16555} -727505571757520766 |  -636613046 |         2 | {{-0.03438753143452488,-0.3149847743198556}}                                       | {8.202208436436448} | {1}         | {16579}(6 rows)

When the query is executed for the second time,aqo recognizes the query and makes a prediction. Pay attention to the cardinality predicted byaqo and the value ofaqo error (“error=0%”).

postgres=# EXPLAIN (ANALYZE, SUMMARY OFF, TIMING OFF) select count(*) from score join course on score.cno=course.cno join student on score.sno=student.sno where degree<90 and test_preparation = 0;                                               QUERY PLAN--------------------------------------------------------------------------------------------------------- Aggregate  (cost=305.86..305.87 rows=1 width=8) (actual rows=1 loops=1)   AQO not used, fss=0   ->  Hash Join  (cost=121.42..296.74 rows=3649 width=0) (actual rows=3649 loops=1)         AQO: rows=3649, error=0%, fss=2128507884         Hash Cond: (score.sno = student.sno)         ->  Hash Join  (cost=12.93..178.65 rows=3649 width=4) (actual rows=3649 loops=1)               AQO: rows=3649, error=0%, fss=-303037802               Hash Cond: (score.cno = course.cno)               ->  Seq Scan on score  (cost=0.00..156.00 rows=3649 width=8) (actual rows=3649 loops=1)                     AQO: rows=3649, error=0%, fss=-636613046                     Filter: ((degree < 90) AND (test_preparation = 0))                     Rows Removed by Filter: 1351               ->  Hash  (cost=12.80..12.80 rows=10 width=4) (actual rows=10 loops=1)                     Buckets: 1024  Batches: 1  Memory Usage: 9kB                     ->  Seq Scan on course  (cost=0.00..12.80 rows=10 width=4) (actual rows=10 loops=1)                           AQO: rows=10, error=0%, fss=-1076069505         ->  Hash  (cost=71.00..71.00 rows=3000 width=4) (actual rows=3000 loops=1)               Buckets: 4096  Batches: 1  Memory Usage: 138kB               ->  Seq Scan on student  (cost=0.00..71.00 rows=3000 width=4) (actual rows=3000 loops=1)                     AQO: rows=3000, error=0%, fss=-1838231581 Using aqo: true AQO mode: LEARN Query hash: -727505571757520766 JOINS: 2(24 rows)

In case of an error, values offeatures andtargets must change, but as there was no error above, they did not change.

         fs          |     fss     | nfeatures |                                      features                                      |       targets       | reliability |        oids---------------------+-------------+-----------+------------------------------------------------------------------------------------+---------------------+-------------+--------------------- -727505571757520766 |  2128507884 |         4 | {{-0.03438753143452488,-5.634789603169249,-0.3149847743198556,-8.006367567650246}} | {8.202208436436448} | {1}         | {16579,16555,16563} -727505571757520766 | -1076069505 |         0 |                                                                                    | {2.302585092994046} | {1}         | {16555} -727505571757520766 | -1838231581 |         0 |                                                                                    | {8.006367567650246} | {1}         | {16563} -727505571757520766 |  -303037802 |         3 | {{-0.03438753143452488,-5.634789603169249,-0.3149847743198556}}                    | {8.202208436436448} | {1}         | {16579,16555} -727505571757520766 |  -636613046 |         2 | {{-0.03438753143452488,-0.3149847743198556}}                                       | {8.202208436436448} | {1}         | {16579}(6 rows)

Let's change a constant in the query, and you will notice that the prediction is made with an error:

    postgres=# EXPLAIN (ANALYZE, SUMMARY OFF, TIMING OFF) select count(*) from score join course on score.cno=course.cno join student on score.sno=student.sno where degree<80 and test_preparation = 0;                                                   QUERY PLAN--------------------------------------------------------------------------------------------------------- Aggregate  (cost=305.86..305.87 rows=1 width=8) (actual rows=1 loops=1)   AQO not used, fss=0   ->  Hash Join  (cost=121.42..296.74 rows=3649 width=0) (actual rows=3551 loops=1)         AQO: rows=3649, error=3%, fss=2128507884         Hash Cond: (score.sno = student.sno)         ->  Hash Join  (cost=12.93..178.65 rows=3649 width=4) (actual rows=3551 loops=1)               AQO: rows=3649, error=3%, fss=-303037802               Hash Cond: (score.cno = course.cno)               ->  Seq Scan on score  (cost=0.00..156.00 rows=3649 width=8) (actual rows=3551 loops=1)                     AQO: rows=3649, error=3%, fss=-636613046                     Filter: ((degree < 80) AND (test_preparation = 0))                     Rows Removed by Filter: 1449               ->  Hash  (cost=12.80..12.80 rows=10 width=4) (actual rows=10 loops=1)                     Buckets: 1024  Batches: 1  Memory Usage: 9kB                     ->  Seq Scan on course  (cost=0.00..12.80 rows=10 width=4) (actual rows=10 loops=1)                           AQO: rows=10, error=0%, fss=-1076069505         ->  Hash  (cost=71.00..71.00 rows=3000 width=4) (actual rows=3000 loops=1)               Buckets: 4096  Batches: 1  Memory Usage: 138kB               ->  Seq Scan on student  (cost=0.00..71.00 rows=3000 width=4) (actual rows=3000 loops=1)                     AQO: rows=3000, error=0%, fss=-1838231581 Using aqo: true AQO mode: LEARN Query hash: -727505571757520766 JOINS: 2(24 rows)

However, instead of recalculatingfeatures andtargets,aqo added new values of selectivity and cardinality for this query toaqo_data:

         fs          |     fss     | nfeatures |                                                                               features                                                                               |                targets                | reliability |        oids---------------------+-------------+-----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------+-------------+--------------------- -727505571757520766 |  1141621836 |         0 |                                                                                                                                                                      | {0}                                   | {1}         | {16579,16555,16563} -727505571757520766 |  2128507884 |         4 | {{-0.030949078292235133,-5.634789603169249,-0.3149847743198556,-8.006367567650246},{-0.34221288089027607,-5.634789603169249,-0.3149847743198556,-8.006367567650246}} | {8.202208436436448,8.174984532943087} | {1,1}       | {16579,16555,16563} -727505571757520766 | -1076069505 |         0 |                                                                                                                                                                      | {2.302585092994046}                   | {1}         | {16555} -727505571757520766 | -1838231581 |         0 |                                                                                                                                                                      | {8.006367567650246}                   | {1}         | {16563} -727505571757520766 |  -303037802 |         3 | {{-0.030949078292235133,-5.634789603169249,-0.3149847743198556},{-0.34221288089027607,-5.634789603169249,-0.3149847743198556}}                                       | {8.202208436436448,8.174984532943087} | {1,1}       | {16579,16555} -727505571757520766 |  -636613046 |         2 | {{-0.030949078292235133,-0.3149847743198556},{-0.34221288089027607,-0.3149847743198556}}

Now the prediction has no error:

postgres=# EXPLAIN (ANALYZE, SUMMARY OFF, TIMING OFF) select count(*) from score join course on score.cno=course.cno join student on score.sno=student.sno where degree<80 and test_preparation = 0;                                               QUERY PLAN--------------------------------------------------------------------------------------------------------- Aggregate  (cost=305.10..305.11 rows=1 width=8) (actual rows=1 loops=1)   AQO not used, fss=0   ->  Hash Join  (cost=121.42..296.22 rows=3551 width=0) (actual rows=3551 loops=1)         AQO: rows=3551, error=0%, fss=2128507884         Hash Cond: (score.sno = student.sno)         ->  Hash Join  (cost=12.93..178.39 rows=3551 width=4) (actual rows=3551 loops=1)               AQO: rows=3551, error=0%, fss=-303037802               Hash Cond: (score.cno = course.cno)               ->  Seq Scan on score  (cost=0.00..156.00 rows=3551 width=8) (actual rows=3551 loops=1)                     AQO: rows=3551, error=0%, fss=-636613046                     Filter: ((degree < 80) AND (test_preparation = 0))                     Rows Removed by Filter: 1449               ->  Hash  (cost=12.80..12.80 rows=10 width=4) (actual rows=10 loops=1)                     Buckets: 1024  Batches: 1  Memory Usage: 9kB                     ->  Seq Scan on course  (cost=0.00..12.80 rows=10 width=4) (actual rows=10 loops=1)                           AQO: rows=10, error=0%, fss=-1076069505         ->  Hash  (cost=71.00..71.00 rows=3000 width=4) (actual rows=3000 loops=1)               Buckets: 4096  Batches: 1  Memory Usage: 138kB               ->  Seq Scan on student  (cost=0.00..71.00 rows=3000 width=4) (actual rows=3000 loops=1)                     AQO: rows=3000, error=0%, fss=-1838231581 Using aqo: true AQO mode: LEARN Query hash: -727505571757520766 JOINS: 2(24 rows)

Theaqo_query_stats view shows statistics on the query planning time, query execution time and cardinality error. Based on this data you can make a decision whether to useaqo predictions for different query classes.

Let's query theaqo_query_stats view:

select queryid, cardinality_error_with_aqo, cardinality_error_without_aqo,execution_time_with_aqo, execution_time_without_aqo, planning_time_with_aqo, planning_time_without_aqo from aqo_query_stat \gx-[ RECORD 1 ]-----------------+------------------------------------------------------------------------------------------------------------queryid                       | 8041624334006338922cardinality_error_with_aqo    | {0.14932737556062836,0,0.507421202801325,0.00040469447777891077}cardinality_error_without_aqo | {0.1493979460962751,0.018403615483185476}execution_time_with_aqo       | {0.004760108,0.008743075,0.006608304,0.012392751}execution_time_without_aqo    | {0.005775926,0.012730316}planning_time_with_aqo        | {0.006927997,0.004247339,0.005005022,0.004169717}planning_time_without_aqo     | {0.001783542,0.001706121}

The retrieved data is for the query fromExample F.1, which was executed once withoutaqo for each of the parametersdegree<80 anddegree<90 and twice withaqo for each of these parameters. It is clear that withaqo, the cardinality error decreases to 0.0004, while the minimum cardinality error withoutaqo is 0.15. Besides, the execution time withaqo is lower than without it. So the conclusion is thataqo learns well on this query, and the prediction can be used for this query class.