Job Order Benchmark (AQO v. 1.2)
- shared_preload_libraries = 'aqo'
- min_parallel_table_scan_size = 0
- min_parallel_index_scan_size = 0
AQO was trained in learn mode on each of 113 queries. In this version some additional instruments for AQO convergence efficiency was added. It allowed to learn AQO on each query only to achieving the convergence, but no more than 100 iterations.
In this test we assumed that convergence has achieved if 7 last executions of the query has cardinality error less than 0.1. This strong criteria allows us to be sure that planner is not able to offer an alternative plan.
The number of iterations that was required in this test to achieve the convergence criteria is shown in the figure below.
Bash script was used for the learning could be found here. It can use platform-specific paths or commands and placed here only for the technique demonstration.
During the test AQO was used in frozen mode. Bash script of the test procedure can be found here. It is placed only for the technique demonstration too.
The test results are shown in the graph below in comparison with previous version (1.1). Here Speedup denotes execution time of PostgreSQL with disabled AQO extension divided by execution time with enabled AQO extension after training; This link contains source data for the query graph.
Some statistics on speedup:
- Execution time of 64 queries was improved by 10% or more (up to 6.2 times).
- Execution time of 48 queries was not changed.
- Execution time of one query (11d) was degraded by 30%. Query plans in json format can be found here for the case without AQO and here with AQO cardinality estimations.
