Рассмотрим оптимизацию запроса с использованием расширения aqo.

Когда запрос выполняется в первый раз, его нет в таблицах, лежащих в основе представлений aqo. Таким образом, данных для предсказания aqo для каждого узла плана нет, и в выводе EXPLAIN появляются строки «AQO not used»:

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)

Если в представлении aqo_data нет информации об определённом узле, aqo добавит в него соответствующую запись для дальнейшего изучения и предсказания, за исключением узлов с fss=0 в выводе EXPLAIN. Поскольку значения в полях features и targets в представлении aqo_data являются логарифмом по основанию e, чтобы получить фактическое значение, возведите e в соответствующую степень. Например: 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)
 

Когда запрос выполняется во второй раз, aqo распознаёт запрос и делает предсказание. Обратите внимание на оценку количества строк, предсказанную aqo, и значение ошибки aqo («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)

В случае ошибки значения полей features и targets должны измениться, но поскольку ошибки выше не было, они не изменились.

         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)
  

Изменив константу в запросе, можно заметить, что предсказание сделано с ошибкой:

    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)
  

Однако вместо пересчёта полей features и targets, aqo добавил новые значения избирательности и оценки количества строк для этого запроса в aqo_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}}

Теперь в предсказании нет ошибок:

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)

В представлении aqo_query_stat отображается статистика времени планирования запросов, времени выполнения запросов и ошибок оценки количества строк. На основании этих данных вы можете принимать решения об использовании предсказаний aqo для различных классов запросов.

Обратимся к представлению aqo_query_stats:

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                       | 8041624334006338922
cardinality_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}

Полученные данные относятся к запросу, рассмотренному в примере Пример F.1. Этот запрос выполнялся с каждым из параметров degree<80 и degree<90 по одному разу без aqo и по два раза с aqo. Видно, что с aqo погрешность оценки количества строк уменьшается до 0,0004, а минимальная погрешность оценки количества строк без aqo составляет 0,15. Кроме того, время выполнения с aqo меньше, чем без него. Таким образом, можно сделать вывод, что aqo хорошо обучается на этом запросе и предсказание можно использовать для этого класса запросов.