Postgres Pro Enterprise : Documentation: 17: 68.1. How It Works : Postgres Professional

68.1. How It Works
Prev	Up	Chapter 68. Adaptive Query Execution	Home	Next

68.1. How It Works #

68.1.1. AQE Triggers
68.1.2. Viewing AQE Details
68.1.3. Example

If an AQE trigger fires during query execution, the partially executed query is paused, and AQE attempts to reoptimize it. Information about the query statement, node cardinalities of non-parameterized nodes and parameterized nodes with actual cardinalities larger than predicted is used to choose a new plan and is stored for further reoptimizations. Then the query is rerun using the new plan. AQE triggers may be disabled for future reoptimizations of the same query in the following situations:

No new information was gathered during execution.
The newly generated plan matches a plan that was already tried.
The maximum number of reruns, set by the aqe_max_reruns configuration parameter, was reached.

In such cases, the query execution continues without interruption.

If an AQE trigger is activated, a query that meets the trigger condition is executed in an implicitly created subtransaction. In case of rerunning, this helps in cleaning up after the previous execution and releasing system resources, such as memory or disk space.

68.1.1. AQE Triggers #

The following triggers can interrupt a query and launch AQE:

Query execution time: the trigger fires when the query runs longer than the value of the aqe_sql_execution_time_trigger configuration parameter.
Number of processed node tuples: the trigger fires when this number exceeds the number normally expected by the planner, which is multiplied by the value of the aqe_rows_underestimation_rate_trigger configuration parameter.
Backend memory consumption: the trigger fires when memory consumption of a backend exceeds the value of the aqe_backend_memory_used_trigger configuration parameter and the number of processed node tuples trigger fired.

68.1.2. Viewing AQE Details #

If aqe_show_details is on, information related to AQE is included in the EXPLAIN ANALYZE output. The SUMMARY section additionally includes the following characteristics:

AQE Active — whether a AQE trigger was active during the query execution.
Table Entries — number of tables in the query, including subqueries.
Controlled Statements — number of statements that were reoptimized during execution of the query.
AQE Reruns — total number of the query reruns.
Total Time Elapsed — total time of the query execution including the time of all reruns. This property is only displayed if the query was reoptimized and rerun at least once.
Final Run Planning Time — planning time during last AQE reoptimization. This property is only displayed if the query was reoptimized and rerun at least once.

For each plan node, the following characteristics are included:

NodeSign — a 64-bit signature (hash) of the node that uniquely identifies the node of the query plan. Not all nodes are signed, but only those where errors of the optimizer estimation model can cause errors in searching the optimal query plan.
Cardinality — cardinality of the plan node achieved in previous executions of the query. Only available with the VERBOSE parameter.
Groups Number — number of groups computed for this plan node from previous executions of the query. Only available with the VERBOSE parameter.

68.1.3. Example #

Example 68.1. Using AQE

The following example illustrates AQE:

DROP TABLE IF EXISTS aqe_test;
CREATE TABLE aqe_test WITH (autovacuum_enabled=off) AS
SELECT x as x, x as y, x as z from generate_series(1, 100000) as x;
INSERT INTO aqe_test SELECT 1, x, x FROM generate_series(1, 1000000) as x;

CREATE INDEX ON aqe_test(x);
CREATE INDEX ON aqe_test(y);
ANALYZE;

-- AQE is disabled
SET aqe_enable = false;
EXPLAIN ANALYZE
SELECT FROM aqe_test t1, aqe_test t2, aqe_test t3
WHERE t1.x = t2.x and
t1.y = t3.y and
t1.y < 100 and t1.z < 100 and
t2.y < 100 and t2.z < 100 and
t3.y < 100 and t3.z < 100;

-- AQE is enabled with node tuples underestimation trigger
SET aqe_enable = true;
SET aqe_rows_underestimation_rate_trigger = 2;
EXPLAIN ANALYZE
SELECT FROM aqe_test t1, aqe_test t2, aqe_test t3
WHERE t1.x = t2.x and
t1.y = t3.y and
t1.y < 100 and t1.z < 100 and
t2.y < 100 and t2.z < 100 and
t3.y < 100 and t3.z < 100;

Output without AQE:

                                                                      QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=14.29..44.44 rows=1 width=0) (actual time=77.493..6778.074 rows=20196 loops=1)
   ->  Nested Loop  (cost=13.86..35.98 rows=1 width=4) (actual time=77.472..6722.165 rows=10098 loops=1)
         ->  Index Scan using aqe_test_y_idx on aqe_test t1  (cost=0.43..18.52 rows=1 width=8) (actual time=0.015..0.271 rows=198 loops=1)
               Index Cond: (y < 100)
               Filter: (z < 100)
         ->  Bitmap Heap Scan on aqe_test t2  (cost=13.43..17.45 rows=1 width=4) (actual time=33.924..33.940 rows=51 loops=198)
               Recheck Cond: ((y < 100) AND (t1.x = x))
               Filter: (z < 100)
               Heap Blocks: exact=298
               ->  BitmapAnd  (cost=13.43..13.43 rows=1 width=0) (actual time=33.917..33.917 rows=0 loops=198)
                     ->  Bitmap Index Scan on aqe_test_y_idx  (cost=0.00..5.97 rows=206 width=0) (actual time=0.016..0.016 rows=198 loops=198)
                           Index Cond: (y < 100)
                     ->  Bitmap Index Scan on aqe_test_x_idx  (cost=0.00..7.21 rows=371 width=0) (actual time=33.898..33.898 rows=505052 loops=198)
                           Index Cond: (x = t1.x)
   ->  Index Scan using aqe_test_y_idx on aqe_test t3  (cost=0.43..8.45 rows=1 width=4) (actual time=0.004..0.005 rows=2 loops=10098)
         Index Cond: ((y = t1.y) AND (y < 100))
         Filter: (z < 100)
 Planning Time: 0.804 ms
 Execution Time: 6779.473 ms
(19 rows)

Output with AQE:

                                                                      QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------
 Merge Join  (cost=1.28..718.25 rows=20196 width=0) (actual time=0.040..31.452 rows=20196 loops=1)
   NodeSign: 13451128626708613266
   Merge Cond: (t1.y = t3.y)
   ->  Nested Loop  (cost=0.85..622.63 rows=9998 width=4) (actual time=0.029..8.957 rows=10098 loops=1)
         NodeSign: 3624220923067346440
         Join Filter: (t1.x = t2.x)
         Rows Removed by Join Filter: 29106
         ->  Index Scan using aqe_test_y_idx on aqe_test t1  (cost=0.43..18.52 rows=197 width=8) (actual time=0.014..0.181 rows=198 loops=1)
               NodeSign: 5973269849475827818
               Index Cond: (y < 100)
               Filter: (z < 100)
         ->  Materialize  (cost=0.43..19.51 rows=198 width=4) (actual time=0.000..0.013 rows=198 loops=198)
               NodeSign: subordinate
               ->  Index Scan using aqe_test_y_idx on aqe_test t2  (cost=0.43..18.52 rows=198 width=4) (actual time=0.010..0.182 rows=198 loops=1)
                     NodeSign: 480658296932687531
                     Index Cond: (y < 100)
                     Filter: (z < 100)
   ->  Index Scan using aqe_test_y_idx on aqe_test t3  (cost=0.43..18.52 rows=19996 width=4) (actual time=0.010..16.026 rows=20096 loops=1)
         NodeSign: 12407239917974993641
         Index Cond: (y < 100)
         Filter: (z < 100)
 Planning Time: 214.610 ms
 Execution Time: 33.130 ms
 AQE Active: true
 Table Entries: 3
 Controlled Statements: 1
 AQE Reruns: 3
 Final Run Planning Time: 0.591 ms
 Total Time Elapsed: 247.740 ms
(29 rows)

Prev	Up	Next
Chapter 68. Adaptive Query Execution	Home	Chapter 69. Backup Manifest Format

epub pdf