Postgres Pro Enterprise : Documentation: 16: 76.1. How It Works : Postgres Professional

76.1. How It Works
Prev	Up	Chapter 76. Real-Time Query Replanning	Home	Next

76.1. How It Works #

76.1.1. Replan Triggers
76.1.2. Viewing Replanning Details
76.1.3. Example

If during execution of a query, a replan trigger fired, replanning of this query starts. A partially executed query is bypassed and an attempt is made to collect information to assist further replanning. If no useful information is found, execution of the query starts from the beginning, but without activated replan triggers. The information on the query statement and node cardinalities is saved. The information on nodes is saved for:

Non-parameterized nodes
Parameterized nodes whose actual cardinality is larger than the one used by the planner

Replanning continues while the trigger condition is met or the configured maximum number of replanning attempts is not exceeded or until new information is no longer found. The maximum number of replanning attempts is defined by the replan_max_attempts configuration parameter.

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

76.1.1. Replan Triggers #

The following triggers can interrupt a query and launch replanning:

Query execution time: the trigger fires when the query runs longer than the value of the replan_query_execution_time 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 replan_overrun_limit configuration parameter.
Backend memory consumption: the trigger fires when memory consumption of a backend exceeds the value of the replan_memory_limit configuration parameter and the number of processed node tuples trigger fired.

76.1.2. Viewing Replanning Details #

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

Replan Active — whether a replan trigger was active during the query execution.
Table Entries — number of tables in the query, including subqueries.
Controlled Statements — number of statements that were replanned during execution of the query.
Replanning Attempts — total number of the query reruns/replanning attempts.
Total Time Elapsed — total time of the query execution including the time of all reruns/replanning attempts. This property is only displayed if the query was reoptimized and rerun at least once.
Final Run Planning Time — planning time during last replanning 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.

76.1.3. Example #

Example 76.1. Using Replanning

The following example illustrates replanning:

  DROP TABLE IF EXISTS replan_test;
  CREATE TABLE replan_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 replan_test SELECT 1, x, x FROM generate_series(1, 1000000) as x;

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

  set replan_show_signature = true;

  -- Replanning is disabled
  SET replan_enable = false;
  EXPLAIN ANALYZE
  SELECT FROM replan_test t1, replan_test t2, replan_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;

  -- Replanning is enabled with node tuples underestimation trigger
  SET replan_enable = true;
  SET replan_overrun_limit = 2;
  EXPLAIN ANALYZE
  SELECT FROM replan_test t1, replan_test t2, replan_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 replanning:

                                                                      QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=14.30..45.18 rows=1 width=0) (actual time=73.677..6848.136 rows=20196 loops=1)
   ->  Nested Loop  (cost=13.87..36.72 rows=1 width=4) (actual time=73.654..6792.731 rows=10098 loops=1)
         ->  Index Scan using replan_test_y_idx on replan_test t1  (cost=0.43..19.25 rows=1 width=8) (actual time=0.014..0.271 rows=198 loops=1)
               Index Cond: (y < 100)
               Filter: (z < 100)
         ->  Bitmap Heap Scan on replan_test t2  (cost=13.44..17.46 rows=1 width=4) (actual time=34.278..34.295 rows=51 loops=198)
               Recheck Cond: ((y < 100) AND (t1.x = x))
               Filter: (z < 100)
               Heap Blocks: exact=298
               ->  BitmapAnd  (cost=13.44..13.44 rows=1 width=0) (actual time=34.268..34.268 rows=0 loops=198)
                     ->  Bitmap Index Scan on replan_test_y_idx  (cost=0.00..6.08 rows=221 width=0) (actual time=0.016..0.016 rows=198 loops=198)
                           Index Cond: (y < 100)
                     ->  Bitmap Index Scan on replan_test_x_idx  (cost=0.00..7.11 rows=357 width=0) (actual time=34.248..34.248 rows=505052 loops=198)
                           Index Cond: (x = t1.x)
   ->  Index Scan using replan_test_y_idx on replan_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.722 ms
 Execution Time: 6849.539 ms
(19 rows)

Output with replanning:

                                                                      QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------
 Merge Join  (cost=1.28..720.49 rows=20196 width=0) (actual time=0.038..30.495 rows=20196 loops=1)
   NodeSign: 16399548477598347697
   Merge Cond: (t1.y = t3.y)
   ->  Nested Loop  (cost=0.85..624.08 rows=9998 width=4) (actual time=0.027..8.577 rows=10098 loops=1)
         NodeSign: 6806589677965256871
         Join Filter: (t1.x = t2.x)
         Rows Removed by Join Filter: 29106
         ->  Index Scan using replan_test_y_idx on replan_test t1  (cost=0.43..19.25 rows=197 width=8) (actual time=0.014..0.180 rows=198 loops=1)
               NodeSign: 17234727896600901988
               Index Cond: (y < 100)
               Filter: (z < 100)
         ->  Materialize  (cost=0.43..20.24 rows=198 width=4) (actual time=0.000..0.013 rows=198 loops=198)
               NodeSign: subordinate
               ->  Index Scan using replan_test_y_idx on replan_test t2  (cost=0.43..19.25 rows=198 width=4) (actual time=0.010..0.180 rows=198 loops=1)
                     NodeSign: 8157818216567004834
                     Index Cond: (y < 100)
                     Filter: (z < 100)
   ->  Index Scan using replan_test_y_idx on replan_test t3  (cost=0.43..19.25 rows=19996 width=4) (actual time=0.009..15.417 rows=20096 loops=1)
         NodeSign: 12097487659300777104
         Index Cond: (y < 100)
         Filter: (z < 100)
 Planning Time: 252.050 ms
 Execution Time: 32.169 ms
 Replan Active: true
 Table Entries: 3
 Controlled Statements: 1
 Replanning Attempts: 3
 Final Run Planning Time: 0.612 ms
 Total Time Elapsed: 284.219 ms
(29 rows)

Prev	Up	Next
Chapter 76. Real-Time Query Replanning	Home	Chapter 77. Backup Manifest Format

epub pdf