amitlan/dyn-pruning.md

## dyn-pruning.md

      
    Raw
  

              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.
No results found