Support partition pruning at execution time

Existing partition pruning is only able to work at plan time, for query
quals that appear in the parsed query.  This is good but limiting, as
there can be parameters that appear later that can be usefully used to
further prune partitions.

This commit adds support for pruning subnodes of Append which cannot
possibly contain any matching tuples, during execution, by evaluating
Params to determine the minimum set of subnodes that can possibly match.
We support more than just simple Params in WHERE clauses. Support
additionally includes:

1. Parameterized Nested Loop Joins: The parameter from the outer side of the
   join can be used to determine the minimum set of inner side partitions to
   scan.

2. Initplans: Once an initplan has been executed we can then determine which
   partitions match the value from the initplan.

Partition pruning is performed in two ways.  When Params external to the plan
are found to match the partition key we attempt to prune away unneeded Append
subplans during the initialization of the executor.  This allows us to bypass
the initialization of non-matching subplans meaning they won't appear in the
EXPLAIN or EXPLAIN ANALYZE output.

For parameters whose value is only known during the actual execution
then the pruning of these subplans must wait.  Subplans which are
eliminated during this stage of pruning are still visible in the EXPLAIN
output.  In order to determine if pruning has actually taken place, the
EXPLAIN ANALYZE must be viewed.  If a certain Append subplan was never
executed due to the elimination of the partition then the execution
timing area will state "(never executed)".  Whereas, if, for example in
the case of parameterized nested loops, the number of loops stated in
the EXPLAIN ANALYZE output for certain subplans may appear lower than
others due to the subplan having been scanned fewer times.  This is due
to the list of matching subnodes having to be evaluated whenever a
parameter which was found to match the partition key changes.

This commit required some additional infrastructure that permits the
building of a data structure which is able to perform the translation of
the matching partition IDs, as returned by get_matching_partitions, into
the list index of a subpaths list, as exist in node types such as
Append, MergeAppend and ModifyTable.  This allows us to translate a list
of clauses into a Bitmapset of all the subpath indexes which must be
included to satisfy the clause list.

Author: David Rowley, based on an earlier effort by Beena Emerson
Reviewers: Amit Langote, Robert Haas, Amul Sul, Rajkumar Raghuwanshi,
Jesper Pedersen
Discussion: https://postgr.es/m/CAOG9ApE16ac-_VVZVvv0gePSgkg_BwYEV1NBqZFqDR2bBE0X0A@mail.gmail.com

2 files changed, 1479 insertions, 0 deletions

diff --git a/src/test/regress/expected/partition_prune.out b/src/test/regress/expected/partition_prune.out
index 69d541eff49..9762cc97482 100644
--- a/src/test/regress/expected/partition_prune.out
+++ b/src/test/regress/expected/partition_prune.out
@@ -1331,3 +1331,1138 @@ explain (costs off) select * from rparted_by_int2 where a > 100000000000000;
 (3 rows)
 
 drop table lp, coll_pruning, rlp, mc3p, mc2p, boolpart, rp, coll_pruning_multi, like_op_noprune, lparted_by_int2, rparted_by_int2;
+--
+-- Test runtime partition pruning
+--
+create table ab (a int not null, b int not null) partition by list (a);
+create table ab_a2 partition of ab for values in(2) partition by list (b);
+create table ab_a2_b1 partition of ab_a2 for values in (1);
+create table ab_a2_b2 partition of ab_a2 for values in (2);
+create table ab_a2_b3 partition of ab_a2 for values in (3);
+create table ab_a1 partition of ab for values in(1) partition by list (b);
+create table ab_a1_b1 partition of ab_a1 for values in (1);
+create table ab_a1_b2 partition of ab_a1 for values in (2);
+create table ab_a1_b3 partition of ab_a1 for values in (3);
+create table ab_a3 partition of ab for values in(3) partition by list (b);
+create table ab_a3_b1 partition of ab_a3 for values in (1);
+create table ab_a3_b2 partition of ab_a3 for values in (2);
+create table ab_a3_b3 partition of ab_a3 for values in (3);
+prepare ab_q1 (int, int, int) as
+select * from ab where a between $1 and $2 and b <= $3;
+-- Execute query 5 times to allow choose_custom_plan
+-- to start considering a generic plan.
+execute ab_q1 (1, 8, 3);
+ a | b 
+---+---
+(0 rows)
+
+execute ab_q1 (1, 8, 3);
+ a | b 
+---+---
+(0 rows)
+
+execute ab_q1 (1, 8, 3);
+ a | b 
+---+---
+(0 rows)
+
+execute ab_q1 (1, 8, 3);
+ a | b 
+---+---
+(0 rows)
+
+execute ab_q1 (1, 8, 3);
+ a | b 
+---+---
+(0 rows)
+
+explain (analyze, costs off, summary off, timing off) execute ab_q1 (2, 2, 3);
+                       QUERY PLAN                        
+---------------------------------------------------------
+ Append (actual rows=0 loops=1)
+   Subplans Removed: 6
+   ->  Seq Scan on ab_a2_b1 (actual rows=0 loops=1)
+         Filter: ((a >= $1) AND (a <= $2) AND (b <= $3))
+   ->  Seq Scan on ab_a2_b2 (actual rows=0 loops=1)
+         Filter: ((a >= $1) AND (a <= $2) AND (b <= $3))
+   ->  Seq Scan on ab_a2_b3 (actual rows=0 loops=1)
+         Filter: ((a >= $1) AND (a <= $2) AND (b <= $3))
+(8 rows)
+
+explain (analyze, costs off, summary off, timing off) execute ab_q1 (1, 2, 3);
+                       QUERY PLAN                        
+---------------------------------------------------------
+ Append (actual rows=0 loops=1)
+   Subplans Removed: 3
+   ->  Seq Scan on ab_a1_b1 (actual rows=0 loops=1)
+         Filter: ((a >= $1) AND (a <= $2) AND (b <= $3))
+   ->  Seq Scan on ab_a1_b2 (actual rows=0 loops=1)
+         Filter: ((a >= $1) AND (a <= $2) AND (b <= $3))
+   ->  Seq Scan on ab_a1_b3 (actual rows=0 loops=1)
+         Filter: ((a >= $1) AND (a <= $2) AND (b <= $3))
+   ->  Seq Scan on ab_a2_b1 (actual rows=0 loops=1)
+         Filter: ((a >= $1) AND (a <= $2) AND (b <= $3))
+   ->  Seq Scan on ab_a2_b2 (actual rows=0 loops=1)
+         Filter: ((a >= $1) AND (a <= $2) AND (b <= $3))
+   ->  Seq Scan on ab_a2_b3 (actual rows=0 loops=1)
+         Filter: ((a >= $1) AND (a <= $2) AND (b <= $3))
+(14 rows)
+
+deallocate ab_q1;
+-- Runtime pruning after optimizer pruning
+prepare ab_q1 (int, int) as
+select a from ab where a between $1 and $2 and b < 3;
+-- Execute query 5 times to allow choose_custom_plan
+-- to start considering a generic plan.
+execute ab_q1 (1, 8);
+ a 
+---
+(0 rows)
+
+execute ab_q1 (1, 8);
+ a 
+---
+(0 rows)
+
+execute ab_q1 (1, 8);
+ a 
+---
+(0 rows)
+
+execute ab_q1 (1, 8);
+ a 
+---
+(0 rows)
+
+execute ab_q1 (1, 8);
+ a 
+---
+(0 rows)
+
+explain (analyze, costs off, summary off, timing off) execute ab_q1 (2, 2);
+                      QUERY PLAN                       
+-------------------------------------------------------
+ Append (actual rows=0 loops=1)
+   Subplans Removed: 4
+   ->  Seq Scan on ab_a2_b1 (actual rows=0 loops=1)
+         Filter: ((a >= $1) AND (a <= $2) AND (b < 3))
+   ->  Seq Scan on ab_a2_b2 (actual rows=0 loops=1)
+         Filter: ((a >= $1) AND (a <= $2) AND (b < 3))
+(6 rows)
+
+explain (analyze, costs off, summary off, timing off) execute ab_q1 (2, 4);
+                      QUERY PLAN                       
+-------------------------------------------------------
+ Append (actual rows=0 loops=1)
+   Subplans Removed: 2
+   ->  Seq Scan on ab_a2_b1 (actual rows=0 loops=1)
+         Filter: ((a >= $1) AND (a <= $2) AND (b < 3))
+   ->  Seq Scan on ab_a2_b2 (actual rows=0 loops=1)
+         Filter: ((a >= $1) AND (a <= $2) AND (b < 3))
+   ->  Seq Scan on ab_a3_b1 (actual rows=0 loops=1)
+         Filter: ((a >= $1) AND (a <= $2) AND (b < 3))
+   ->  Seq Scan on ab_a3_b2 (actual rows=0 loops=1)
+         Filter: ((a >= $1) AND (a <= $2) AND (b < 3))
+(10 rows)
+
+-- Ensure a mix of external and exec params work together at different
+-- levels of partitioning.
+prepare ab_q2 (int, int) as
+select a from ab where a between $1 and $2 and b < (select 3);
+execute ab_q2 (1, 8);
+ a 
+---
+(0 rows)
+
+execute ab_q2 (1, 8);
+ a 
+---
+(0 rows)
+
+execute ab_q2 (1, 8);
+ a 
+---
+(0 rows)
+
+execute ab_q2 (1, 8);
+ a 
+---
+(0 rows)
+
+execute ab_q2 (1, 8);
+ a 
+---
+(0 rows)
+
+explain (analyze, costs off, summary off, timing off) execute ab_q2 (2, 2);
+                       QUERY PLAN                       
+--------------------------------------------------------
+ Append (actual rows=0 loops=1)
+   InitPlan 1 (returns $0)
+     ->  Result (actual rows=1 loops=1)
+   Subplans Removed: 6
+   ->  Seq Scan on ab_a2_b1 (actual rows=0 loops=1)
+         Filter: ((a >= $1) AND (a <= $2) AND (b < $0))
+   ->  Seq Scan on ab_a2_b2 (actual rows=0 loops=1)
+         Filter: ((a >= $1) AND (a <= $2) AND (b < $0))
+   ->  Seq Scan on ab_a2_b3 (never executed)
+         Filter: ((a >= $1) AND (a <= $2) AND (b < $0))
+(10 rows)
+
+-- As above, but with swap the exec param to the first partition level
+prepare ab_q3 (int, int) as
+select a from ab where b between $1 and $2 and a < (select 3);
+execute ab_q3 (1, 8);
+ a 
+---
+(0 rows)
+
+execute ab_q3 (1, 8);
+ a 
+---
+(0 rows)
+
+execute ab_q3 (1, 8);
+ a 
+---
+(0 rows)
+
+execute ab_q3 (1, 8);
+ a 
+---
+(0 rows)
+
+execute ab_q3 (1, 8);
+ a 
+---
+(0 rows)
+
+explain (analyze, costs off, summary off, timing off) execute ab_q3 (2, 2);
+                       QUERY PLAN                       
+--------------------------------------------------------
+ Append (actual rows=0 loops=1)
+   InitPlan 1 (returns $0)
+     ->  Result (actual rows=1 loops=1)
+   Subplans Removed: 6
+   ->  Seq Scan on ab_a1_b2 (actual rows=0 loops=1)
+         Filter: ((b >= $1) AND (b <= $2) AND (a < $0))
+   ->  Seq Scan on ab_a2_b2 (actual rows=0 loops=1)
+         Filter: ((b >= $1) AND (b <= $2) AND (a < $0))
+   ->  Seq Scan on ab_a3_b2 (never executed)
+         Filter: ((b >= $1) AND (b <= $2) AND (a < $0))
+(10 rows)
+
+-- Parallel append
+prepare ab_q4 (int, int) as
+select avg(a) from ab where a between $1 and $2 and b < 4;
+-- Encourage use of parallel plans
+set parallel_setup_cost = 0;
+set parallel_tuple_cost = 0;
+set min_parallel_table_scan_size = 0;
+set max_parallel_workers_per_gather = 2;
+-- Execute query 5 times to allow choose_custom_plan
+-- to start considering a generic plan.
+execute ab_q4 (1, 8);
+ avg 
+-----
+    
+(1 row)
+
+execute ab_q4 (1, 8);
+ avg 
+-----
+    
+(1 row)
+
+execute ab_q4 (1, 8);
+ avg 
+-----
+    
+(1 row)
+
+execute ab_q4 (1, 8);
+ avg 
+-----
+    
+(1 row)
+
+execute ab_q4 (1, 8);
+ avg 
+-----
+    
+(1 row)
+
+explain (analyze, costs off, summary off, timing off) execute ab_q4 (2, 2);
+                                  QUERY PLAN                                   
+-------------------------------------------------------------------------------
+ Finalize Aggregate (actual rows=1 loops=1)
+   ->  Gather (actual rows=3 loops=1)
+         Workers Planned: 2
+         Workers Launched: 2
+         ->  Partial Aggregate (actual rows=1 loops=3)
+               ->  Parallel Append (actual rows=0 loops=3)
+                     Subplans Removed: 6
+                     ->  Parallel Seq Scan on ab_a2_b1 (actual rows=0 loops=1)
+                           Filter: ((a >= $1) AND (a <= $2) AND (b < 4))
+                     ->  Parallel Seq Scan on ab_a2_b2 (actual rows=0 loops=1)
+                           Filter: ((a >= $1) AND (a <= $2) AND (b < 4))
+                     ->  Parallel Seq Scan on ab_a2_b3 (actual rows=0 loops=1)
+                           Filter: ((a >= $1) AND (a <= $2) AND (b < 4))
+(13 rows)
+
+-- Test run-time pruning with IN lists.
+prepare ab_q5 (int, int, int) as
+select avg(a) from ab where a in($1,$2,$3) and b < 4;
+-- Execute query 5 times to allow choose_custom_plan
+-- to start considering a generic plan.
+execute ab_q5 (1, 2, 3);
+ avg 
+-----
+    
+(1 row)
+
+execute ab_q5 (1, 2, 3);
+ avg 
+-----
+    
+(1 row)
+
+execute ab_q5 (1, 2, 3);
+ avg 
+-----
+    
+(1 row)
+
+execute ab_q5 (1, 2, 3);
+ avg 
+-----
+    
+(1 row)
+
+execute ab_q5 (1, 2, 3);
+ avg 
+-----
+    
+(1 row)
+
+explain (analyze, costs off, summary off, timing off) execute ab_q5 (1, 1, 1);
+                                  QUERY PLAN                                   
+-------------------------------------------------------------------------------
+ Finalize Aggregate (actual rows=1 loops=1)
+   ->  Gather (actual rows=3 loops=1)
+         Workers Planned: 2
+         Workers Launched: 2
+         ->  Partial Aggregate (actual rows=1 loops=3)
+               ->  Parallel Append (actual rows=0 loops=3)
+                     Subplans Removed: 6
+                     ->  Parallel Seq Scan on ab_a1_b1 (actual rows=0 loops=1)
+                           Filter: ((b < 4) AND (a = ANY (ARRAY[$1, $2, $3])))
+                     ->  Parallel Seq Scan on ab_a1_b2 (actual rows=0 loops=1)
+                           Filter: ((b < 4) AND (a = ANY (ARRAY[$1, $2, $3])))
+                     ->  Parallel Seq Scan on ab_a1_b3 (actual rows=0 loops=1)
+                           Filter: ((b < 4) AND (a = ANY (ARRAY[$1, $2, $3])))
+(13 rows)
+
+explain (analyze, costs off, summary off, timing off) execute ab_q5 (2, 3, 3);
+                                  QUERY PLAN                                   
+-------------------------------------------------------------------------------
+ Finalize Aggregate (actual rows=1 loops=1)
+   ->  Gather (actual rows=3 loops=1)
+         Workers Planned: 2
+         Workers Launched: 2
+         ->  Partial Aggregate (actual rows=1 loops=3)
+               ->  Parallel Append (actual rows=0 loops=3)
+                     Subplans Removed: 3
+                     ->  Parallel Seq Scan on ab_a2_b1 (actual rows=0 loops=1)
+                           Filter: ((b < 4) AND (a = ANY (ARRAY[$1, $2, $3])))
+                     ->  Parallel Seq Scan on ab_a2_b2 (actual rows=0 loops=1)
+                           Filter: ((b < 4) AND (a = ANY (ARRAY[$1, $2, $3])))
+                     ->  Parallel Seq Scan on ab_a2_b3 (actual rows=0 loops=1)
+                           Filter: ((b < 4) AND (a = ANY (ARRAY[$1, $2, $3])))
+                     ->  Parallel Seq Scan on ab_a3_b1 (actual rows=0 loops=1)
+                           Filter: ((b < 4) AND (a = ANY (ARRAY[$1, $2, $3])))
+                     ->  Parallel Seq Scan on ab_a3_b2 (actual rows=0 loops=1)
+                           Filter: ((b < 4) AND (a = ANY (ARRAY[$1, $2, $3])))
+                     ->  Parallel Seq Scan on ab_a3_b3 (actual rows=0 loops=1)
+                           Filter: ((b < 4) AND (a = ANY (ARRAY[$1, $2, $3])))
+(19 rows)
+
+-- Try some params whose values do not belong to any partition.
+-- We'll still get a single subplan in this case, but it should not be scanned.
+explain (analyze, costs off, summary off, timing off) execute ab_q5 (33, 44, 55);
+                                  QUERY PLAN                                   
+-------------------------------------------------------------------------------
+ Finalize Aggregate (actual rows=1 loops=1)
+   ->  Gather (actual rows=3 loops=1)
+         Workers Planned: 2
+         Workers Launched: 2
+         ->  Partial Aggregate (actual rows=1 loops=3)
+               ->  Parallel Append (actual rows=0 loops=3)
+                     Subplans Removed: 8
+                     ->  Parallel Seq Scan on ab_a1_b1 (never executed)
+                           Filter: ((b < 4) AND (a = ANY (ARRAY[$1, $2, $3])))
+(9 rows)
+
+-- Test parallel Append with IN list and parameterized nested loops
+create table lprt_a (a int not null);
+-- Insert some values we won't find in ab
+insert into lprt_a select 0 from generate_series(1,100);
+-- and insert some values that we should find.
+insert into lprt_a values(1),(1);
+analyze lprt_a;
+create index ab_a2_b1_a_idx on ab_a2_b1 (a);
+create index ab_a2_b2_a_idx on ab_a2_b2 (a);
+create index ab_a2_b3_a_idx on ab_a2_b3 (a);
+create index ab_a1_b1_a_idx on ab_a1_b1 (a);
+create index ab_a1_b2_a_idx on ab_a1_b2 (a);
+create index ab_a1_b3_a_idx on ab_a1_b3 (a);
+create index ab_a3_b1_a_idx on ab_a3_b1 (a);
+create index ab_a3_b2_a_idx on ab_a3_b2 (a);
+create index ab_a3_b3_a_idx on ab_a3_b3 (a);
+set enable_hashjoin = 0;
+set enable_mergejoin = 0;
+prepare ab_q6 (int, int, int) as
+select avg(ab.a) from ab inner join lprt_a a on ab.a = a.a where a.a in($1,$2,$3);
+execute ab_q6 (1, 2, 3);
+ avg 
+-----
+    
+(1 row)
+
+execute ab_q6 (1, 2, 3);
+ avg 
+-----
+    
+(1 row)
+
+execute ab_q6 (1, 2, 3);
+ avg 
+-----
+    
+(1 row)
+
+execute ab_q6 (1, 2, 3);
+ avg 
+-----
+    
+(1 row)
+
+execute ab_q6 (1, 2, 3);
+ avg 
+-----
+    
+(1 row)
+
+explain (analyze, costs off, summary off, timing off) execute ab_q6 (0, 0, 1);
+                                               QUERY PLAN                                               
+--------------------------------------------------------------------------------------------------------
+ Finalize Aggregate (actual rows=1 loops=1)
+   ->  Gather (actual rows=2 loops=1)
+         Workers Planned: 1
+         Workers Launched: 1
+         ->  Partial Aggregate (actual rows=1 loops=2)
+               ->  Nested Loop (actual rows=0 loops=2)
+                     ->  Parallel Seq Scan on lprt_a a (actual rows=51 loops=2)
+                           Filter: (a = ANY ('{0,0,1}'::integer[]))
+                     ->  Append (actual rows=0 loops=102)
+                           ->  Index Only Scan using ab_a1_b1_a_idx on ab_a1_b1 (actual rows=0 loops=2)
+                                 Index Cond: (a = a.a)
+                                 Heap Fetches: 0
+                           ->  Index Only Scan using ab_a1_b2_a_idx on ab_a1_b2 (actual rows=0 loops=2)
+                                 Index Cond: (a = a.a)
+                                 Heap Fetches: 0
+                           ->  Index Only Scan using ab_a1_b3_a_idx on ab_a1_b3 (actual rows=0 loops=2)
+                                 Index Cond: (a = a.a)
+                                 Heap Fetches: 0
+                           ->  Index Only Scan using ab_a2_b1_a_idx on ab_a2_b1 (never executed)
+                                 Index Cond: (a = a.a)
+                                 Heap Fetches: 0
+                           ->  Index Only Scan using ab_a2_b2_a_idx on ab_a2_b2 (never executed)
+                                 Index Cond: (a = a.a)
+                                 Heap Fetches: 0
+                           ->  Index Only Scan using ab_a2_b3_a_idx on ab_a2_b3 (never executed)
+                                 Index Cond: (a = a.a)
+                                 Heap Fetches: 0
+                           ->  Index Only Scan using ab_a3_b1_a_idx on ab_a3_b1 (never executed)
+                                 Index Cond: (a = a.a)
+                                 Heap Fetches: 0
+                           ->  Index Only Scan using ab_a3_b2_a_idx on ab_a3_b2 (never executed)
+                                 Index Cond: (a = a.a)
+                                 Heap Fetches: 0
+                           ->  Index Only Scan using ab_a3_b3_a_idx on ab_a3_b3 (never executed)
+                                 Index Cond: (a = a.a)
+                                 Heap Fetches: 0
+(36 rows)
+
+insert into lprt_a values(3),(3);
+explain (analyze, costs off, summary off, timing off) execute ab_q6 (1, 0, 3);
+                                               QUERY PLAN                                               
+--------------------------------------------------------------------------------------------------------
+ Finalize Aggregate (actual rows=1 loops=1)
+   ->  Gather (actual rows=2 loops=1)
+         Workers Planned: 1
+         Workers Launched: 1
+         ->  Partial Aggregate (actual rows=1 loops=2)
+               ->  Nested Loop (actual rows=0 loops=2)
+                     ->  Parallel Seq Scan on lprt_a a (actual rows=52 loops=2)
+                           Filter: (a = ANY ('{1,0,3}'::integer[]))
+                     ->  Append (actual rows=0 loops=104)
+                           ->  Index Only Scan using ab_a1_b1_a_idx on ab_a1_b1 (actual rows=0 loops=2)
+                                 Index Cond: (a = a.a)
+                                 Heap Fetches: 0
+                           ->  Index Only Scan using ab_a1_b2_a_idx on ab_a1_b2 (actual rows=0 loops=2)
+                                 Index Cond: (a = a.a)
+                                 Heap Fetches: 0
+                           ->  Index Only Scan using ab_a1_b3_a_idx on ab_a1_b3 (actual rows=0 loops=2)
+                                 Index Cond: (a = a.a)
+                                 Heap Fetches: 0
+                           ->  Index Only Scan using ab_a2_b1_a_idx on ab_a2_b1 (never executed)
+                                 Index Cond: (a = a.a)
+                                 Heap Fetches: 0
+                           ->  Index Only Scan using ab_a2_b2_a_idx on ab_a2_b2 (never executed)
+                                 Index Cond: (a = a.a)
+                                 Heap Fetches: 0
+                           ->  Index Only Scan using ab_a2_b3_a_idx on ab_a2_b3 (never executed)
+                                 Index Cond: (a = a.a)
+                                 Heap Fetches: 0
+                           ->  Index Only Scan using ab_a3_b1_a_idx on ab_a3_b1 (actual rows=0 loops=2)
+                                 Index Cond: (a = a.a)
+                                 Heap Fetches: 0
+                           ->  Index Only Scan using ab_a3_b2_a_idx on ab_a3_b2 (actual rows=0 loops=2)
+                                 Index Cond: (a = a.a)
+                                 Heap Fetches: 0
+                           ->  Index Only Scan using ab_a3_b3_a_idx on ab_a3_b3 (actual rows=0 loops=2)
+                                 Index Cond: (a = a.a)
+                                 Heap Fetches: 0
+(36 rows)
+
+explain (analyze, costs off, summary off, timing off) execute ab_q6 (1, 0, 0);
+                                               QUERY PLAN                                               
+--------------------------------------------------------------------------------------------------------
+ Finalize Aggregate (actual rows=1 loops=1)
+   ->  Gather (actual rows=2 loops=1)
+         Workers Planned: 1
+         Workers Launched: 1
+         ->  Partial Aggregate (actual rows=1 loops=2)
+               ->  Nested Loop (actual rows=0 loops=2)
+                     ->  Parallel Seq Scan on lprt_a a (actual rows=51 loops=2)
+                           Filter: (a = ANY ('{1,0,0}'::integer[]))
+                           Rows Removed by Filter: 1
+                     ->  Append (actual rows=0 loops=102)
+                           ->  Index Only Scan using ab_a1_b1_a_idx on ab_a1_b1 (actual rows=0 loops=2)
+                                 Index Cond: (a = a.a)
+                                 Heap Fetches: 0
+                           ->  Index Only Scan using ab_a1_b2_a_idx on ab_a1_b2 (actual rows=0 loops=2)
+                                 Index Cond: (a = a.a)
+                                 Heap Fetches: 0
+                           ->  Index Only Scan using ab_a1_b3_a_idx on ab_a1_b3 (actual rows=0 loops=2)
+                                 Index Cond: (a = a.a)
+                                 Heap Fetches: 0
+                           ->  Index Only Scan using ab_a2_b1_a_idx on ab_a2_b1 (never executed)
+                                 Index Cond: (a = a.a)
+                                 Heap Fetches: 0
+                           ->  Index Only Scan using ab_a2_b2_a_idx on ab_a2_b2 (never executed)
+                                 Index Cond: (a = a.a)
+                                 Heap Fetches: 0
+                           ->  Index Only Scan using ab_a2_b3_a_idx on ab_a2_b3 (never executed)
+                                 Index Cond: (a = a.a)
+                                 Heap Fetches: 0
+                           ->  Index Only Scan using ab_a3_b1_a_idx on ab_a3_b1 (never executed)
+                                 Index Cond: (a = a.a)
+                                 Heap Fetches: 0
+                           ->  Index Only Scan using ab_a3_b2_a_idx on ab_a3_b2 (never executed)
+                                 Index Cond: (a = a.a)
+                                 Heap Fetches: 0
+                           ->  Index Only Scan using ab_a3_b3_a_idx on ab_a3_b3 (never executed)
+                                 Index Cond: (a = a.a)
+                                 Heap Fetches: 0
+(37 rows)
+
+delete from lprt_a where a = 1;
+explain (analyze, costs off, summary off, timing off) execute ab_q6 (1, 0, 0);
+                                           QUERY PLAN                                            
+-------------------------------------------------------------------------------------------------
+ Finalize Aggregate (actual rows=1 loops=1)
+   ->  Gather (actual rows=2 loops=1)
+         Workers Planned: 1
+         Workers Launched: 1
+         ->  Partial Aggregate (actual rows=1 loops=2)
+               ->  Nested Loop (actual rows=0 loops=2)
+                     ->  Parallel Seq Scan on lprt_a a (actual rows=50 loops=2)
+                           Filter: (a = ANY ('{1,0,0}'::integer[]))
+                           Rows Removed by Filter: 1
+                     ->  Append (actual rows=0 loops=100)
+                           ->  Index Only Scan using ab_a1_b1_a_idx on ab_a1_b1 (never executed)
+                                 Index Cond: (a = a.a)
+                                 Heap Fetches: 0
+                           ->  Index Only Scan using ab_a1_b2_a_idx on ab_a1_b2 (never executed)
+                                 Index Cond: (a = a.a)
+                                 Heap Fetches: 0
+                           ->  Index Only Scan using ab_a1_b3_a_idx on ab_a1_b3 (never executed)
+                                 Index Cond: (a = a.a)
+                                 Heap Fetches: 0
+                           ->  Index Only Scan using ab_a2_b1_a_idx on ab_a2_b1 (never executed)
+                                 Index Cond: (a = a.a)
+                                 Heap Fetches: 0
+                           ->  Index Only Scan using ab_a2_b2_a_idx on ab_a2_b2 (never executed)
+                                 Index Cond: (a = a.a)
+                                 Heap Fetches: 0
+                           ->  Index Only Scan using ab_a2_b3_a_idx on ab_a2_b3 (never executed)
+                                 Index Cond: (a = a.a)
+                                 Heap Fetches: 0
+                           ->  Index Only Scan using ab_a3_b1_a_idx on ab_a3_b1 (never executed)
+                                 Index Cond: (a = a.a)
+                                 Heap Fetches: 0
+                           ->  Index Only Scan using ab_a3_b2_a_idx on ab_a3_b2 (never executed)
+                                 Index Cond: (a = a.a)
+                                 Heap Fetches: 0
+                           ->  Index Only Scan using ab_a3_b3_a_idx on ab_a3_b3 (never executed)
+                                 Index Cond: (a = a.a)
+                                 Heap Fetches: 0
+(37 rows)
+
+reset enable_hashjoin;
+reset enable_mergejoin;
+reset parallel_setup_cost;
+reset parallel_tuple_cost;
+reset min_parallel_table_scan_size;
+reset max_parallel_workers_per_gather;
+-- Test run-time partition pruning with an initplan
+explain (analyze, costs off, summary off, timing off)
+select * from ab where a = (select max(a) from lprt_a) and b = (select max(a)-1 from lprt_a);
+                               QUERY PLAN                                
+-------------------------------------------------------------------------
+ Append (actual rows=0 loops=1)
+   InitPlan 1 (returns $0)
+     ->  Aggregate (actual rows=1 loops=1)
+           ->  Seq Scan on lprt_a (actual rows=102 loops=1)
+   InitPlan 2 (returns $1)
+     ->  Aggregate (actual rows=1 loops=1)
+           ->  Seq Scan on lprt_a lprt_a_1 (actual rows=102 loops=1)
+   ->  Bitmap Heap Scan on ab_a1_b1 (never executed)
+         Recheck Cond: (a = $0)
+         Filter: (b = $1)
+         ->  Bitmap Index Scan on ab_a1_b1_a_idx (never executed)
+               Index Cond: (a = $0)
+   ->  Bitmap Heap Scan on ab_a1_b2 (never executed)
+         Recheck Cond: (a = $0)
+         Filter: (b = $1)
+         ->  Bitmap Index Scan on ab_a1_b2_a_idx (never executed)
+               Index Cond: (a = $0)
+   ->  Bitmap Heap Scan on ab_a1_b3 (never executed)
+         Recheck Cond: (a = $0)
+         Filter: (b = $1)
+         ->  Bitmap Index Scan on ab_a1_b3_a_idx (never executed)
+               Index Cond: (a = $0)
+   ->  Bitmap Heap Scan on ab_a2_b1 (never executed)
+         Recheck Cond: (a = $0)
+         Filter: (b = $1)
+         ->  Bitmap Index Scan on ab_a2_b1_a_idx (never executed)
+               Index Cond: (a = $0)
+   ->  Bitmap Heap Scan on ab_a2_b2 (never executed)
+         Recheck Cond: (a = $0)
+         Filter: (b = $1)
+         ->  Bitmap Index Scan on ab_a2_b2_a_idx (never executed)
+               Index Cond: (a = $0)
+   ->  Bitmap Heap Scan on ab_a2_b3 (never executed)
+         Recheck Cond: (a = $0)
+         Filter: (b = $1)
+         ->  Bitmap Index Scan on ab_a2_b3_a_idx (never executed)
+               Index Cond: (a = $0)
+   ->  Bitmap Heap Scan on ab_a3_b1 (never executed)
+         Recheck Cond: (a = $0)
+         Filter: (b = $1)
+         ->  Bitmap Index Scan on ab_a3_b1_a_idx (never executed)
+               Index Cond: (a = $0)
+   ->  Bitmap Heap Scan on ab_a3_b2 (actual rows=0 loops=1)
+         Recheck Cond: (a = $0)
+         Filter: (b = $1)
+         ->  Bitmap Index Scan on ab_a3_b2_a_idx (actual rows=0 loops=1)
+               Index Cond: (a = $0)
+   ->  Bitmap Heap Scan on ab_a3_b3 (never executed)
+         Recheck Cond: (a = $0)
+         Filter: (b = $1)
+         ->  Bitmap Index Scan on ab_a3_b3_a_idx (never executed)
+               Index Cond: (a = $0)
+(52 rows)
+
+deallocate ab_q1;
+deallocate ab_q2;
+deallocate ab_q3;
+deallocate ab_q4;
+deallocate ab_q5;
+deallocate ab_q6;
+drop table ab, lprt_a;
+-- Join
+create table tbl1(col1 int);
+insert into tbl1 values (501), (505);
+-- Basic table
+create table tprt (col1 int) partition by range (col1);
+create table tprt_1 partition of tprt for values from (1) to (501);
+create table tprt_2 partition of tprt for values from (501) to (1001);
+create table tprt_3 partition of tprt for values from (1001) to (2001);
+create table tprt_4 partition of tprt for values from (2001) to (3001);
+create table tprt_5 partition of tprt for values from (3001) to (4001);
+create table tprt_6 partition of tprt for values from (4001) to (5001);
+create index tprt1_idx on tprt_1 (col1);
+create index tprt2_idx on tprt_2 (col1);
+create index tprt3_idx on tprt_3 (col1);
+create index tprt4_idx on tprt_4 (col1);
+create index tprt5_idx on tprt_5 (col1);
+create index tprt6_idx on tprt_6 (col1);
+insert into tprt values (10), (20), (501), (502), (505), (1001), (4500);
+set enable_hashjoin = off;
+set enable_mergejoin = off;
+explain (analyze, costs off, summary off, timing off)
+select * from tbl1 join tprt on tbl1.col1 > tprt.col1;
+                                  QUERY PLAN                                   
+-------------------------------------------------------------------------------
+ Nested Loop (actual rows=6 loops=1)
+   ->  Seq Scan on tbl1 (actual rows=2 loops=1)
+   ->  Append (actual rows=3 loops=2)
+         ->  Index Only Scan using tprt1_idx on tprt_1 (actual rows=2 loops=2)
+               Index Cond: (col1 < tbl1.col1)
+               Heap Fetches: 4
+         ->  Index Only Scan using tprt2_idx on tprt_2 (actual rows=2 loops=1)
+               Index Cond: (col1 < tbl1.col1)
+               Heap Fetches: 2
+         ->  Index Only Scan using tprt3_idx on tprt_3 (never executed)
+               Index Cond: (col1 < tbl1.col1)
+               Heap Fetches: 0
+         ->  Index Only Scan using tprt4_idx on tprt_4 (never executed)
+               Index Cond: (col1 < tbl1.col1)
+               Heap Fetches: 0
+         ->  Index Only Scan using tprt5_idx on tprt_5 (never executed)
+               Index Cond: (col1 < tbl1.col1)
+               Heap Fetches: 0
+         ->  Index Only Scan using tprt6_idx on tprt_6 (never executed)
+               Index Cond: (col1 < tbl1.col1)
+               Heap Fetches: 0
+(21 rows)
+
+explain (analyze, costs off, summary off, timing off)
+select * from tbl1 join tprt on tbl1.col1 = tprt.col1;
+                                  QUERY PLAN                                   
+-------------------------------------------------------------------------------
+ Nested Loop (actual rows=2 loops=1)
+   ->  Seq Scan on tbl1 (actual rows=2 loops=1)
+   ->  Append (actual rows=1 loops=2)
+         ->  Index Only Scan using tprt1_idx on tprt_1 (never executed)
+               Index Cond: (col1 = tbl1.col1)
+               Heap Fetches: 0
+         ->  Index Only Scan using tprt2_idx on tprt_2 (actual rows=1 loops=2)
+               Index Cond: (col1 = tbl1.col1)
+               Heap Fetches: 2
+         ->  Index Only Scan using tprt3_idx on tprt_3 (never executed)
+               Index Cond: (col1 = tbl1.col1)
+               Heap Fetches: 0
+         ->  Index Only Scan using tprt4_idx on tprt_4 (never executed)
+               Index Cond: (col1 = tbl1.col1)
+               Heap Fetches: 0
+         ->  Index Only Scan using tprt5_idx on tprt_5 (never executed)
+               Index Cond: (col1 = tbl1.col1)
+               Heap Fetches: 0
+         ->  Index Only Scan using tprt6_idx on tprt_6 (never executed)
+               Index Cond: (col1 = tbl1.col1)
+               Heap Fetches: 0
+(21 rows)
+
+select tbl1.col1, tprt.col1 from tbl1
+inner join tprt on tbl1.col1 > tprt.col1
+order by tbl1.col1, tprt.col1;
+ col1 | col1 
+------+------
+  501 |   10
+  501 |   20
+  505 |   10
+  505 |   20
+  505 |  501
+  505 |  502
+(6 rows)
+
+select tbl1.col1, tprt.col1 from tbl1
+inner join tprt on tbl1.col1 = tprt.col1
+order by tbl1.col1, tprt.col1;
+ col1 | col1 
+------+------
+  501 |  501
+  505 |  505
+(2 rows)
+
+-- Multiple partitions
+insert into tbl1 values (1001), (1010), (1011);
+explain (analyze, costs off, summary off, timing off)
+select * from tbl1 inner join tprt on tbl1.col1 > tprt.col1;
+                                  QUERY PLAN                                   
+-------------------------------------------------------------------------------
+ Nested Loop (actual rows=23 loops=1)
+   ->  Seq Scan on tbl1 (actual rows=5 loops=1)
+   ->  Append (actual rows=5 loops=5)
+         ->  Index Only Scan using tprt1_idx on tprt_1 (actual rows=2 loops=5)
+               Index Cond: (col1 < tbl1.col1)
+               Heap Fetches: 10
+         ->  Index Only Scan using tprt2_idx on tprt_2 (actual rows=3 loops=4)
+               Index Cond: (col1 < tbl1.col1)
+               Heap Fetches: 11
+         ->  Index Only Scan using tprt3_idx on tprt_3 (actual rows=1 loops=2)
+               Index Cond: (col1 < tbl1.col1)
+               Heap Fetches: 2
+         ->  Index Only Scan using tprt4_idx on tprt_4 (never executed)
+               Index Cond: (col1 < tbl1.col1)
+               Heap Fetches: 0
+         ->  Index Only Scan using tprt5_idx on tprt_5 (never executed)
+               Index Cond: (col1 < tbl1.col1)
+               Heap Fetches: 0
+         ->  Index Only Scan using tprt6_idx on tprt_6 (never executed)
+               Index Cond: (col1 < tbl1.col1)
+               Heap Fetches: 0
+(21 rows)
+
+explain (analyze, costs off, summary off, timing off)
+select * from tbl1 inner join tprt on tbl1.col1 = tprt.col1;
+                                  QUERY PLAN                                   
+-------------------------------------------------------------------------------
+ Nested Loop (actual rows=3 loops=1)
+   ->  Seq Scan on tbl1 (actual rows=5 loops=1)
+   ->  Append (actual rows=1 loops=5)
+         ->  Index Only Scan using tprt1_idx on tprt_1 (never executed)
+               Index Cond: (col1 = tbl1.col1)
+               Heap Fetches: 0
+         ->  Index Only Scan using tprt2_idx on tprt_2 (actual rows=1 loops=2)
+               Index Cond: (col1 = tbl1.col1)
+               Heap Fetches: 2
+         ->  Index Only Scan using tprt3_idx on tprt_3 (actual rows=0 loops=3)
+               Index Cond: (col1 = tbl1.col1)
+               Heap Fetches: 1
+         ->  Index Only Scan using tprt4_idx on tprt_4 (never executed)
+               Index Cond: (col1 = tbl1.col1)
+               Heap Fetches: 0
+         ->  Index Only Scan using tprt5_idx on tprt_5 (never executed)
+               Index Cond: (col1 = tbl1.col1)
+               Heap Fetches: 0
+         ->  Index Only Scan using tprt6_idx on tprt_6 (never executed)
+               Index Cond: (col1 = tbl1.col1)
+               Heap Fetches: 0
+(21 rows)
+
+select tbl1.col1, tprt.col1 from tbl1
+inner join tprt on tbl1.col1 > tprt.col1
+order by tbl1.col1, tprt.col1;
+ col1 | col1 
+------+------
+  501 |   10
+  501 |   20
+  505 |   10
+  505 |   20
+  505 |  501
+  505 |  502
+ 1001 |   10
+ 1001 |   20
+ 1001 |  501
+ 1001 |  502
+ 1001 |  505
+ 1010 |   10
+ 1010 |   20
+ 1010 |  501
+ 1010 |  502
+ 1010 |  505
+ 1010 | 1001
+ 1011 |   10
+ 1011 |   20
+ 1011 |  501
+ 1011 |  502
+ 1011 |  505
+ 1011 | 1001
+(23 rows)
+
+select tbl1.col1, tprt.col1 from tbl1
+inner join tprt on tbl1.col1 = tprt.col1
+order by tbl1.col1, tprt.col1;
+ col1 | col1 
+------+------
+  501 |  501
+  505 |  505
+ 1001 | 1001
+(3 rows)
+
+-- Last partition
+delete from tbl1;
+insert into tbl1 values (4400);
+explain (analyze, costs off, summary off, timing off)
+select * from tbl1 join tprt on tbl1.col1 < tprt.col1;
+                                  QUERY PLAN                                   
+-------------------------------------------------------------------------------
+ Nested Loop (actual rows=1 loops=1)
+   ->  Seq Scan on tbl1 (actual rows=1 loops=1)
+   ->  Append (actual rows=1 loops=1)
+         ->  Index Only Scan using tprt1_idx on tprt_1 (never executed)
+               Index Cond: (col1 > tbl1.col1)
+               Heap Fetches: 0
+         ->  Index Only Scan using tprt2_idx on tprt_2 (never executed)
+               Index Cond: (col1 > tbl1.col1)
+               Heap Fetches: 0
+         ->  Index Only Scan using tprt3_idx on tprt_3 (never executed)
+               Index Cond: (col1 > tbl1.col1)
+               Heap Fetches: 0
+         ->  Index Only Scan using tprt4_idx on tprt_4 (never executed)
+               Index Cond: (col1 > tbl1.col1)
+               Heap Fetches: 0
+         ->  Index Only Scan using tprt5_idx on tprt_5 (never executed)
+               Index Cond: (col1 > tbl1.col1)
+               Heap Fetches: 0
+         ->  Index Only Scan using tprt6_idx on tprt_6 (actual rows=1 loops=1)
+               Index Cond: (col1 > tbl1.col1)
+               Heap Fetches: 1
+(21 rows)
+
+select tbl1.col1, tprt.col1 from tbl1
+inner join tprt on tbl1.col1 < tprt.col1
+order by tbl1.col1, tprt.col1;
+ col1 | col1 
+------+------
+ 4400 | 4500
+(1 row)
+
+-- No matching partition
+delete from tbl1;
+insert into tbl1 values (10000);
+explain (analyze, costs off, summary off, timing off)
+select * from tbl1 join tprt on tbl1.col1 = tprt.col1;
+                               QUERY PLAN                               
+------------------------------------------------------------------------
+ Nested Loop (actual rows=0 loops=1)
+   ->  Seq Scan on tbl1 (actual rows=1 loops=1)
+   ->  Append (actual rows=0 loops=1)
+         ->  Index Only Scan using tprt1_idx on tprt_1 (never executed)
+               Index Cond: (col1 = tbl1.col1)
+               Heap Fetches: 0
+         ->  Index Only Scan using tprt2_idx on tprt_2 (never executed)
+               Index Cond: (col1 = tbl1.col1)
+               Heap Fetches: 0
+         ->  Index Only Scan using tprt3_idx on tprt_3 (never executed)
+               Index Cond: (col1 = tbl1.col1)
+               Heap Fetches: 0
+         ->  Index Only Scan using tprt4_idx on tprt_4 (never executed)
+               Index Cond: (col1 = tbl1.col1)
+               Heap Fetches: 0
+         ->  Index Only Scan using tprt5_idx on tprt_5 (never executed)
+               Index Cond: (col1 = tbl1.col1)
+               Heap Fetches: 0
+         ->  Index Only Scan using tprt6_idx on tprt_6 (never executed)
+               Index Cond: (col1 = tbl1.col1)
+               Heap Fetches: 0
+(21 rows)
+
+select tbl1.col1, tprt.col1 from tbl1
+inner join tprt on tbl1.col1 = tprt.col1
+order by tbl1.col1, tprt.col1;
+ col1 | col1 
+------+------
+(0 rows)
+
+drop table tbl1, tprt;
+-- Test with columns defined in varying orders between each level
+create table part_abc (a int not null, b int not null, c int not null) partition by list (a);
+create table part_bac (b int not null, a int not null, c int not null) partition by list (b);
+create table part_cab (c int not null, a int not null, b int not null) partition by list (c);
+create table part_abc_p1 (a int not null, b int not null, c int not null);
+alter table part_abc attach partition part_bac for values in(1);
+alter table part_bac attach partition part_cab for values in(2);
+alter table part_cab attach partition part_abc_p1 for values in(3);
+prepare part_abc_q1 (int, int, int) as
+select * from part_abc where a = $1 and b = $2 and c = $3;
+-- Execute query 5 times to allow choose_custom_plan
+-- to start considering a generic plan.
+execute part_abc_q1 (1, 2, 3);
+ a | b | c 
+---+---+---
+(0 rows)
+
+execute part_abc_q1 (1, 2, 3);
+ a | b | c 
+---+---+---
+(0 rows)
+
+execute part_abc_q1 (1, 2, 3);
+ a | b | c 
+---+---+---
+(0 rows)
+
+execute part_abc_q1 (1, 2, 3);
+ a | b | c 
+---+---+---
+(0 rows)
+
+execute part_abc_q1 (1, 2, 3);
+ a | b | c 
+---+---+---
+(0 rows)
+
+-- Single partition should be scanned.
+explain (analyze, costs off, summary off, timing off) execute part_abc_q1 (1, 2, 3);
+                      QUERY PLAN                       
+-------------------------------------------------------
+ Append (actual rows=0 loops=1)
+   ->  Seq Scan on part_abc_p1 (actual rows=0 loops=1)
+         Filter: ((a = $1) AND (b = $2) AND (c = $3))
+(3 rows)
+
+deallocate part_abc_q1;
+drop table part_abc;
+-- Ensure that an Append node properly handles a sub-partitioned table
+-- matching without any of its leaf partitions matching the clause.
+create table listp (a int, b int) partition by list (a);
+create table listp_1 partition of listp for values in(1) partition by list (b);
+create table listp_1_1 partition of listp_1 for values in(1);
+create table listp_2 partition of listp for values in(2) partition by list (b);
+create table listp_2_1 partition of listp_2 for values in(2);
+select * from listp where b = 1;
+ a | b 
+---+---
+(0 rows)
+
+-- Ensure that an Append node properly can handle selection of all first level
+-- partitions before finally detecting the correct set of 2nd level partitions
+-- which match the given parameter.
+prepare q1 (int,int) as select * from listp where b in ($1,$2);
+execute q1 (1,2);
+ a | b 
+---+---
+(0 rows)
+
+execute q1 (1,2);
+ a | b 
+---+---
+(0 rows)
+
+execute q1 (1,2);
+ a | b 
+---+---
+(0 rows)
+
+execute q1 (1,2);
+ a | b 
+---+---
+(0 rows)
+
+execute q1 (1,2);
+ a | b 
+---+---
+(0 rows)
+
+explain (analyze, costs off, summary off, timing off)  execute q1 (1,1);
+                     QUERY PLAN                      
+-----------------------------------------------------
+ Append (actual rows=0 loops=1)
+   Subplans Removed: 1
+   ->  Seq Scan on listp_1_1 (actual rows=0 loops=1)
+         Filter: (b = ANY (ARRAY[$1, $2]))
+(4 rows)
+
+explain (analyze, costs off, summary off, timing off)  execute q1 (2,2);
+                     QUERY PLAN                      
+-----------------------------------------------------
+ Append (actual rows=0 loops=1)
+   Subplans Removed: 1
+   ->  Seq Scan on listp_2_1 (actual rows=0 loops=1)
+         Filter: (b = ANY (ARRAY[$1, $2]))
+(4 rows)
+
+-- Try with no matching partitions. One subplan should remain in this case,
+-- but it shouldn't be executed.
+explain (analyze, costs off, summary off, timing off)  execute q1 (0,0);
+                  QUERY PLAN                  
+----------------------------------------------
+ Append (actual rows=0 loops=1)
+   Subplans Removed: 1
+   ->  Seq Scan on listp_1_1 (never executed)
+         Filter: (b = ANY (ARRAY[$1, $2]))
+(4 rows)
+
+deallocate q1;
+-- Test more complex cases where a not-equal condition further eliminates partitions.
+prepare q1 (int,int,int,int) as select * from listp where b in($1,$2) and $3 <> b and $4 <> b;
+execute q1 (1,2,3,4);
+ a | b 
+---+---
+(0 rows)
+
+execute q1 (1,2,3,4);
+ a | b 
+---+---
+(0 rows)
+
+execute q1 (1,2,3,4);
+ a | b 
+---+---
+(0 rows)
+
+execute q1 (1,2,3,4);
+ a | b 
+---+---
+(0 rows)
+
+execute q1 (1,2,3,4);
+ a | b 
+---+---
+(0 rows)
+
+-- Both partitions allowed by IN clause, but one disallowed by <> clause
+explain (analyze, costs off, summary off, timing off)  execute q1 (1,2,2,0);
+                               QUERY PLAN                                
+-------------------------------------------------------------------------
+ Append (actual rows=0 loops=1)
+   Subplans Removed: 1
+   ->  Seq Scan on listp_1_1 (actual rows=0 loops=1)
+         Filter: ((b = ANY (ARRAY[$1, $2])) AND ($3 <> b) AND ($4 <> b))
+(4 rows)
+
+-- Both partitions allowed by IN clause, then both excluded again by <> clauses.
+-- One subplan will remain in this case, but it should not be executed.
+explain (analyze, costs off, summary off, timing off)  execute q1 (1,2,2,1);
+                               QUERY PLAN                                
+-------------------------------------------------------------------------
+ Append (actual rows=0 loops=1)
+   Subplans Removed: 1
+   ->  Seq Scan on listp_1_1 (never executed)
+         Filter: ((b = ANY (ARRAY[$1, $2])) AND ($3 <> b) AND ($4 <> b))
+(4 rows)
+
+drop table listp;
+-- Ensure runtime pruning works with initplans params with boolean types
+create table boolvalues (value bool not null);
+insert into boolvalues values('t'),('f');
+create table boolp (a bool) partition by list (a);
+create table boolp_t partition of boolp for values in('t');
+create table boolp_f partition of boolp for values in('f');
+explain (analyze, costs off, summary off, timing off)
+select * from boolp where a = (select value from boolvalues where value);
+                       QUERY PLAN                       
+--------------------------------------------------------
+ Append (actual rows=0 loops=1)
+   InitPlan 1 (returns $0)
+     ->  Seq Scan on boolvalues (actual rows=1 loops=1)
+           Filter: value
+           Rows Removed by Filter: 1
+   ->  Seq Scan on boolp_f (never executed)
+         Filter: (a = $0)
+   ->  Seq Scan on boolp_t (actual rows=0 loops=1)
+         Filter: (a = $0)
+(9 rows)
+
+explain (analyze, costs off, summary off, timing off)
+select * from boolp where a = (select value from boolvalues where not value);
+                       QUERY PLAN                       
+--------------------------------------------------------
+ Append (actual rows=0 loops=1)
+   InitPlan 1 (returns $0)
+     ->  Seq Scan on boolvalues (actual rows=1 loops=1)
+           Filter: (NOT value)
+           Rows Removed by Filter: 1
+   ->  Seq Scan on boolp_f (actual rows=0 loops=1)
+         Filter: (a = $0)
+   ->  Seq Scan on boolp_t (never executed)
+         Filter: (a = $0)
+(9 rows)
+
+drop table boolp;
diff --git a/src/test/regress/sql/partition_prune.sql b/src/test/regress/sql/partition_prune.sql
index d5ca3cb702d..1d712b4adaf 100644
--- a/src/test/regress/sql/partition_prune.sql
+++ b/src/test/regress/sql/partition_prune.sql
@@ -237,3 +237,347 @@ create table rparted_by_int2_maxvalue partition of rparted_by_int2 for values fr
 explain (costs off) select * from rparted_by_int2 where a > 100000000000000;
 
 drop table lp, coll_pruning, rlp, mc3p, mc2p, boolpart, rp, coll_pruning_multi, like_op_noprune, lparted_by_int2, rparted_by_int2;
+
+
+--
+-- Test runtime partition pruning
+--
+create table ab (a int not null, b int not null) partition by list (a);
+create table ab_a2 partition of ab for values in(2) partition by list (b);
+create table ab_a2_b1 partition of ab_a2 for values in (1);
+create table ab_a2_b2 partition of ab_a2 for values in (2);
+create table ab_a2_b3 partition of ab_a2 for values in (3);
+create table ab_a1 partition of ab for values in(1) partition by list (b);
+create table ab_a1_b1 partition of ab_a1 for values in (1);
+create table ab_a1_b2 partition of ab_a1 for values in (2);
+create table ab_a1_b3 partition of ab_a1 for values in (3);
+create table ab_a3 partition of ab for values in(3) partition by list (b);
+create table ab_a3_b1 partition of ab_a3 for values in (1);
+create table ab_a3_b2 partition of ab_a3 for values in (2);
+create table ab_a3_b3 partition of ab_a3 for values in (3);
+
+prepare ab_q1 (int, int, int) as
+select * from ab where a between $1 and $2 and b <= $3;
+
+-- Execute query 5 times to allow choose_custom_plan
+-- to start considering a generic plan.
+execute ab_q1 (1, 8, 3);
+execute ab_q1 (1, 8, 3);
+execute ab_q1 (1, 8, 3);
+execute ab_q1 (1, 8, 3);
+execute ab_q1 (1, 8, 3);
+
+explain (analyze, costs off, summary off, timing off) execute ab_q1 (2, 2, 3);
+explain (analyze, costs off, summary off, timing off) execute ab_q1 (1, 2, 3);
+
+deallocate ab_q1;
+
+-- Runtime pruning after optimizer pruning
+prepare ab_q1 (int, int) as
+select a from ab where a between $1 and $2 and b < 3;
+
+-- Execute query 5 times to allow choose_custom_plan
+-- to start considering a generic plan.
+execute ab_q1 (1, 8);
+execute ab_q1 (1, 8);
+execute ab_q1 (1, 8);
+execute ab_q1 (1, 8);
+execute ab_q1 (1, 8);
+
+explain (analyze, costs off, summary off, timing off) execute ab_q1 (2, 2);
+explain (analyze, costs off, summary off, timing off) execute ab_q1 (2, 4);
+
+-- Ensure a mix of external and exec params work together at different
+-- levels of partitioning.
+prepare ab_q2 (int, int) as
+select a from ab where a between $1 and $2 and b < (select 3);
+
+execute ab_q2 (1, 8);
+execute ab_q2 (1, 8);
+execute ab_q2 (1, 8);
+execute ab_q2 (1, 8);
+execute ab_q2 (1, 8);
+
+explain (analyze, costs off, summary off, timing off) execute ab_q2 (2, 2);
+
+-- As above, but with swap the exec param to the first partition level
+prepare ab_q3 (int, int) as
+select a from ab where b between $1 and $2 and a < (select 3);
+
+execute ab_q3 (1, 8);
+execute ab_q3 (1, 8);
+execute ab_q3 (1, 8);
+execute ab_q3 (1, 8);
+execute ab_q3 (1, 8);
+
+explain (analyze, costs off, summary off, timing off) execute ab_q3 (2, 2);
+
+-- Parallel append
+prepare ab_q4 (int, int) as
+select avg(a) from ab where a between $1 and $2 and b < 4;
+
+-- Encourage use of parallel plans
+set parallel_setup_cost = 0;
+set parallel_tuple_cost = 0;
+set min_parallel_table_scan_size = 0;
+set max_parallel_workers_per_gather = 2;
+
+-- Execute query 5 times to allow choose_custom_plan
+-- to start considering a generic plan.
+execute ab_q4 (1, 8);
+execute ab_q4 (1, 8);
+execute ab_q4 (1, 8);
+execute ab_q4 (1, 8);
+execute ab_q4 (1, 8);
+
+explain (analyze, costs off, summary off, timing off) execute ab_q4 (2, 2);
+
+-- Test run-time pruning with IN lists.
+prepare ab_q5 (int, int, int) as
+select avg(a) from ab where a in($1,$2,$3) and b < 4;
+
+-- Execute query 5 times to allow choose_custom_plan
+-- to start considering a generic plan.
+execute ab_q5 (1, 2, 3);
+execute ab_q5 (1, 2, 3);
+execute ab_q5 (1, 2, 3);
+execute ab_q5 (1, 2, 3);
+execute ab_q5 (1, 2, 3);
+
+explain (analyze, costs off, summary off, timing off) execute ab_q5 (1, 1, 1);
+explain (analyze, costs off, summary off, timing off) execute ab_q5 (2, 3, 3);
+
+-- Try some params whose values do not belong to any partition.
+-- We'll still get a single subplan in this case, but it should not be scanned.
+explain (analyze, costs off, summary off, timing off) execute ab_q5 (33, 44, 55);
+
+-- Test parallel Append with IN list and parameterized nested loops
+create table lprt_a (a int not null);
+-- Insert some values we won't find in ab
+insert into lprt_a select 0 from generate_series(1,100);
+
+-- and insert some values that we should find.
+insert into lprt_a values(1),(1);
+
+analyze lprt_a;
+
+create index ab_a2_b1_a_idx on ab_a2_b1 (a);
+create index ab_a2_b2_a_idx on ab_a2_b2 (a);
+create index ab_a2_b3_a_idx on ab_a2_b3 (a);
+create index ab_a1_b1_a_idx on ab_a1_b1 (a);
+create index ab_a1_b2_a_idx on ab_a1_b2 (a);
+create index ab_a1_b3_a_idx on ab_a1_b3 (a);
+create index ab_a3_b1_a_idx on ab_a3_b1 (a);
+create index ab_a3_b2_a_idx on ab_a3_b2 (a);
+create index ab_a3_b3_a_idx on ab_a3_b3 (a);
+
+set enable_hashjoin = 0;
+set enable_mergejoin = 0;
+
+prepare ab_q6 (int, int, int) as
+select avg(ab.a) from ab inner join lprt_a a on ab.a = a.a where a.a in($1,$2,$3);
+execute ab_q6 (1, 2, 3);
+execute ab_q6 (1, 2, 3);
+execute ab_q6 (1, 2, 3);
+execute ab_q6 (1, 2, 3);
+execute ab_q6 (1, 2, 3);
+
+explain (analyze, costs off, summary off, timing off) execute ab_q6 (0, 0, 1);
+
+insert into lprt_a values(3),(3);
+
+explain (analyze, costs off, summary off, timing off) execute ab_q6 (1, 0, 3);
+explain (analyze, costs off, summary off, timing off) execute ab_q6 (1, 0, 0);
+
+delete from lprt_a where a = 1;
+
+explain (analyze, costs off, summary off, timing off) execute ab_q6 (1, 0, 0);
+
+reset enable_hashjoin;
+reset enable_mergejoin;
+reset parallel_setup_cost;
+reset parallel_tuple_cost;
+reset min_parallel_table_scan_size;
+reset max_parallel_workers_per_gather;
+
+-- Test run-time partition pruning with an initplan
+explain (analyze, costs off, summary off, timing off)
+select * from ab where a = (select max(a) from lprt_a) and b = (select max(a)-1 from lprt_a);
+
+deallocate ab_q1;
+deallocate ab_q2;
+deallocate ab_q3;
+deallocate ab_q4;
+deallocate ab_q5;
+deallocate ab_q6;
+
+drop table ab, lprt_a;
+
+-- Join
+create table tbl1(col1 int);
+insert into tbl1 values (501), (505);
+
+-- Basic table
+create table tprt (col1 int) partition by range (col1);
+create table tprt_1 partition of tprt for values from (1) to (501);
+create table tprt_2 partition of tprt for values from (501) to (1001);
+create table tprt_3 partition of tprt for values from (1001) to (2001);
+create table tprt_4 partition of tprt for values from (2001) to (3001);
+create table tprt_5 partition of tprt for values from (3001) to (4001);
+create table tprt_6 partition of tprt for values from (4001) to (5001);
+
+create index tprt1_idx on tprt_1 (col1);
+create index tprt2_idx on tprt_2 (col1);
+create index tprt3_idx on tprt_3 (col1);
+create index tprt4_idx on tprt_4 (col1);
+create index tprt5_idx on tprt_5 (col1);
+create index tprt6_idx on tprt_6 (col1);
+
+insert into tprt values (10), (20), (501), (502), (505), (1001), (4500);
+
+set enable_hashjoin = off;
+set enable_mergejoin = off;
+
+explain (analyze, costs off, summary off, timing off)
+select * from tbl1 join tprt on tbl1.col1 > tprt.col1;
+
+explain (analyze, costs off, summary off, timing off)
+select * from tbl1 join tprt on tbl1.col1 = tprt.col1;
+
+select tbl1.col1, tprt.col1 from tbl1
+inner join tprt on tbl1.col1 > tprt.col1
+order by tbl1.col1, tprt.col1;
+
+select tbl1.col1, tprt.col1 from tbl1
+inner join tprt on tbl1.col1 = tprt.col1
+order by tbl1.col1, tprt.col1;
+
+-- Multiple partitions
+insert into tbl1 values (1001), (1010), (1011);
+explain (analyze, costs off, summary off, timing off)
+select * from tbl1 inner join tprt on tbl1.col1 > tprt.col1;
+
+explain (analyze, costs off, summary off, timing off)
+select * from tbl1 inner join tprt on tbl1.col1 = tprt.col1;
+
+select tbl1.col1, tprt.col1 from tbl1
+inner join tprt on tbl1.col1 > tprt.col1
+order by tbl1.col1, tprt.col1;
+
+select tbl1.col1, tprt.col1 from tbl1
+inner join tprt on tbl1.col1 = tprt.col1
+order by tbl1.col1, tprt.col1;
+
+-- Last partition
+delete from tbl1;
+insert into tbl1 values (4400);
+explain (analyze, costs off, summary off, timing off)
+select * from tbl1 join tprt on tbl1.col1 < tprt.col1;
+
+select tbl1.col1, tprt.col1 from tbl1
+inner join tprt on tbl1.col1 < tprt.col1
+order by tbl1.col1, tprt.col1;
+
+-- No matching partition
+delete from tbl1;
+insert into tbl1 values (10000);
+explain (analyze, costs off, summary off, timing off)
+select * from tbl1 join tprt on tbl1.col1 = tprt.col1;
+
+select tbl1.col1, tprt.col1 from tbl1
+inner join tprt on tbl1.col1 = tprt.col1
+order by tbl1.col1, tprt.col1;
+
+drop table tbl1, tprt;
+
+-- Test with columns defined in varying orders between each level
+create table part_abc (a int not null, b int not null, c int not null) partition by list (a);
+create table part_bac (b int not null, a int not null, c int not null) partition by list (b);
+create table part_cab (c int not null, a int not null, b int not null) partition by list (c);
+create table part_abc_p1 (a int not null, b int not null, c int not null);
+
+alter table part_abc attach partition part_bac for values in(1);
+alter table part_bac attach partition part_cab for values in(2);
+alter table part_cab attach partition part_abc_p1 for values in(3);
+
+prepare part_abc_q1 (int, int, int) as
+select * from part_abc where a = $1 and b = $2 and c = $3;
+
+-- Execute query 5 times to allow choose_custom_plan
+-- to start considering a generic plan.
+execute part_abc_q1 (1, 2, 3);
+execute part_abc_q1 (1, 2, 3);
+execute part_abc_q1 (1, 2, 3);
+execute part_abc_q1 (1, 2, 3);
+execute part_abc_q1 (1, 2, 3);
+
+-- Single partition should be scanned.
+explain (analyze, costs off, summary off, timing off) execute part_abc_q1 (1, 2, 3);
+
+deallocate part_abc_q1;
+
+drop table part_abc;
+
+-- Ensure that an Append node properly handles a sub-partitioned table
+-- matching without any of its leaf partitions matching the clause.
+create table listp (a int, b int) partition by list (a);
+create table listp_1 partition of listp for values in(1) partition by list (b);
+create table listp_1_1 partition of listp_1 for values in(1);
+create table listp_2 partition of listp for values in(2) partition by list (b);
+create table listp_2_1 partition of listp_2 for values in(2);
+select * from listp where b = 1;
+
+-- Ensure that an Append node properly can handle selection of all first level
+-- partitions before finally detecting the correct set of 2nd level partitions
+-- which match the given parameter.
+prepare q1 (int,int) as select * from listp where b in ($1,$2);
+
+execute q1 (1,2);
+execute q1 (1,2);
+execute q1 (1,2);
+execute q1 (1,2);
+execute q1 (1,2);
+
+explain (analyze, costs off, summary off, timing off)  execute q1 (1,1);
+
+explain (analyze, costs off, summary off, timing off)  execute q1 (2,2);
+
+-- Try with no matching partitions. One subplan should remain in this case,
+-- but it shouldn't be executed.
+explain (analyze, costs off, summary off, timing off)  execute q1 (0,0);
+
+deallocate q1;
+
+-- Test more complex cases where a not-equal condition further eliminates partitions.
+prepare q1 (int,int,int,int) as select * from listp where b in($1,$2) and $3 <> b and $4 <> b;
+
+execute q1 (1,2,3,4);
+execute q1 (1,2,3,4);
+execute q1 (1,2,3,4);
+execute q1 (1,2,3,4);
+execute q1 (1,2,3,4);
+
+-- Both partitions allowed by IN clause, but one disallowed by <> clause
+explain (analyze, costs off, summary off, timing off)  execute q1 (1,2,2,0);
+
+-- Both partitions allowed by IN clause, then both excluded again by <> clauses.
+-- One subplan will remain in this case, but it should not be executed.
+explain (analyze, costs off, summary off, timing off)  execute q1 (1,2,2,1);
+
+drop table listp;
+
+-- Ensure runtime pruning works with initplans params with boolean types
+create table boolvalues (value bool not null);
+insert into boolvalues values('t'),('f');
+
+create table boolp (a bool) partition by list (a);
+create table boolp_t partition of boolp for values in('t');
+create table boolp_f partition of boolp for values in('f');
+
+explain (analyze, costs off, summary off, timing off)
+select * from boolp where a = (select value from boolvalues where value);
+
+explain (analyze, costs off, summary off, timing off)
+select * from boolp where a = (select value from boolvalues where not value);
+
+drop table boolp;