Dynamic Partition Pruning in PG 11

dyn-pruning.md

Following tables are used in the examples below:

create table foo (a int);
insert into foo values (2), (3);

create table p (a int) partition by list (a);
create index on p (a);
create table p1 partition of p for values in (1);
create table p2 partition of p for values in (2);
create table p3 partition of p for values in (3);

Dynamic pruning may occur if a query contains comparison of the partition key to a "parameter" whose value is only known during execution. There are two types of execution-time parameters. A query may contain any combination of parameters, type 1 or 2 or both.

Parameters whose value is fixed for the whole execution

If query contains this type of parameter, then pruning can occur before executing the plan (actually, when initializing the plan) and if pruning is successful, it is shown using Subplans Removed: in the EXPLAIN output.

Example:

prepare example_query as select * from p where a = $1;

In the above query, $1 is this type of parameter.

explain (costs off, timing off, analyze) execute example_query (1);
                                     QUERY PLAN

─────────────────────────────────────────────────────────────────────────────────────
 Append
   Subplans Removed: 2
   ->  Bitmap Heap Scan on p1
         Recheck Cond: (a = $1)
         ->  Bitmap Index Scan on p1_a_idx
               Index Cond: (a = $1)
 Planning Time: 0.019 ms
 Execution Time: 0.163 ms
(8 rows)

$1 is assigned the value of 1 and it's fixed for the entire execution of that query.

(Note that you'll need to run the above query at least 5 times before planner switches to a generic plan. It's only then that you can see dynamic pruning in action. I'm running PG 12 development version, where I set plan_cache_mode for plan cache to always return a generic plan and easily see dynamic pruning in action.)

Parameters whose value may change during execution

If query contains this type of parameter, then pruning occurs every time its value changes. If a partition is pruned every time, then its subplan is showed as (never executed) in the EXPLAIN output.

Example:

select * from foo f where exists (select * from p where a = f.a);

Here f.a is the parameter for scanning p. The above query is actually implemented using a join between foo and p. If p is the inner table, then we can use dynamic pruning using a = f.a condition where f.a is the parameter. Pruning is executed for every value of f.a, that is, for all rows of foo.

explain (costs off, timing off, analyze) select * from foo f where exists
(select * from p where a = f.a);
                               QUERY PLAN
─────────────────────────────────────────────────────────────────────────
 Nested Loop Semi Join (actual rows=0 loops=1)
   ->  Seq Scan on foo f (actual rows=2 loops=1)
   ->  Append (actual rows=0 loops=2)
         ->  Bitmap Heap Scan on p1 (never executed)
               Recheck Cond: (a = f.a)
               ->  Bitmap Index Scan on p1_a_idx (never executed)
                     Index Cond: (a = f.a)
         ->  Bitmap Heap Scan on p2 (actual rows=0 loops=1)
               Recheck Cond: (a = f.a)
               ->  Bitmap Index Scan on p2_a_idx (actual rows=0 loops=1)
                     Index Cond: (a = f.a)
         ->  Bitmap Heap Scan on p3 (actual rows=0 loops=1)
               Recheck Cond: (a = f.a)
               ->  Bitmap Index Scan on p3_a_idx (actual rows=0 loops=1)
                     Index Cond: (a = f.a)
 Planning Time: 3.293 ms
 Execution Time: 0.397 ms
(17 rows)

Here p1's subplan is never executed, because foo contains only 2 and 3.

Example with both types of dynamic pruning in the same query

It's also possible for a query to contain both types of parameters, such as in the following example.

prepare example_query as
select * from foo f where exists (select * from p where a = f.a and a <> $1);

Above query contains both types of parameters: = f.a and <> $1.

explain (costs off, timing off, analyze) execute example_query (2);
                               QUERY PLAN
─────────────────────────────────────────────────────────────────────────
 Nested Loop Semi Join (actual rows=0 loops=1)
   ->  Seq Scan on foo f (actual rows=2 loops=1)
   ->  Append (actual rows=0 loops=2)
         Subplans Removed: 1
         ->  Bitmap Heap Scan on p1 (never executed)
               Recheck Cond: (a = f.a)
               Filter: (a <> $1)
               ->  Bitmap Index Scan on p1_a_idx (never executed)
                     Index Cond: (a = f.a)
         ->  Bitmap Heap Scan on p3 (actual rows=0 loops=1)
               Recheck Cond: (a = f.a)
               Filter: (a <> $1)
               ->  Bitmap Index Scan on p3_a_idx (actual rows=0 loops=1)
                     Index Cond: (a = f.a)
 Planning Time: 4.189 ms
 Execution Time: 0.331 ms
(16 rows)

Note that there are both Sublans Removed: and (never executed) in this case.:

Sublans Removed: exists because of the parameter $1. When it is executed with 2, partition p2's subplan is removed, because the condition is a <> $1.

(never executed) is shown for p1, because the parameter f.a never becomes 1, because foo only contain 2 and 3, so p1's subplan is never executed.