v31-0004-Consider-pruned-partitions.patch

diff --git a/src/backend/partitioning/partbounds.c b/src/backend/partitioning/partbounds.c
index 43c4e03dde..3c7b6030c0 100644
--- a/src/backend/partitioning/partbounds.c
+++ b/src/backend/partitioning/partbounds.c
@@ -136,6 +136,7 @@ static PartitionBoundInfo partition_list_bounds_merge(FmgrInfo *partsupfunc, Oid
 							List **outer_parts, List **inner_parts);
 static void init_partition_map(RelOptInfo *rel, PartitionMap *map);
 static void free_partition_map(PartitionMap *map);
+static bool is_dummy_partition(RelOptInfo *rel, int part_index);
 static int map_and_merge_partitions(PartitionMap *outer_map, PartitionMap *inner_map,
 						 int outer_part, int inner_part, int *next_index);
 static int merge_partition_with_dummy(PartitionMap *map, int index,
@@ -171,8 +172,15 @@ static PartitionBoundInfo build_merged_partition_bounds(char strategy,
 							  List *merged_datums, List *merged_indexes,
 							  List *merged_contents, int null_index,
 							  int default_index);
-static int get_range_partition(PartitionBoundInfo bi, int *lb_pos,
-					PartitionRangeBound *lb, PartitionRangeBound *ub);
+static int get_range_partition(RelOptInfo *rel,
+							   PartitionBoundInfo bi,
+							   int *lb_index,
+							   PartitionRangeBound *lb,
+							   PartitionRangeBound *ub);
+static int get_range_partition_internal(PartitionBoundInfo bi,
+										int *lb_pos,
+										PartitionRangeBound *lb,
+										PartitionRangeBound *ub);
 static bool compare_range_partitions(int partnatts, FmgrInfo *partsupfuncs,
 						 Oid *partcollations,
 						 PartitionRangeBound *outer_lb,
@@ -3186,14 +3194,37 @@ partition_bounds_merge(int partnatts,
 
 /*
  * get_range_partition
- *		Returns the index of the range partition with the given lower bound
+ *		Returns the next non-dummy partition of a given range-partitioned
+ *		relation
  *
- * *lb and *ub are set to the lower and upper bounds of the range partition
+ * *lb and *ub are set to the lower and upper bounds of that partition
  * respectively, and *lb_index is advanced to the next lower bound, if any.
  */
 static int
-get_range_partition(PartitionBoundInfo bi, int *lb_index,
-					PartitionRangeBound *lb, PartitionRangeBound *ub)
+get_range_partition(RelOptInfo *rel,
+					PartitionBoundInfo bi,
+					int *lb_index,
+					PartitionRangeBound *lb,
+					PartitionRangeBound *ub)
+{
+	int			part_index;
+
+	Assert(bi->strategy == PARTITION_STRATEGY_RANGE);
+
+	do {
+		part_index = get_range_partition_internal(bi, lb_index, lb, ub);
+		if (part_index == -1)
+			return -1;
+	} while (is_dummy_partition(rel, part_index));
+
+	return part_index;
+}
+
+static int
+get_range_partition_internal(PartitionBoundInfo bi,
+							 int *lb_index,
+							 PartitionRangeBound *lb,
+							 PartitionRangeBound *ub)
 {
 	/* Return the index as -1 if we've exhausted all the lower bounds. */
 	if (*lb_index >= bi->ndatums)
@@ -3456,6 +3487,15 @@ partition_range_bounds_merge(int partnatts, FmgrInfo *partsupfuncs,
 	init_partition_map(outer_rel, &outer_map);
 	init_partition_map(inner_rel, &inner_map);
 
+	/*
+	 * If the default partitions (if any) have been proven empty, deem them
+	 * non-existent.
+	 */
+	if (outer_has_default && is_dummy_partition(outer_rel, outer_default))
+		outer_has_default = false;
+	if (inner_has_default && is_dummy_partition(inner_rel, inner_default))
+		inner_has_default = false;
+
 	/*
 	 * Merge the ranges (partitions) from both sides. Every iteration compares
 	 * a pair of ranges, one from each side, advancing to the next range from
@@ -3466,9 +3506,9 @@ partition_range_bounds_merge(int partnatts, FmgrInfo *partsupfuncs,
 	 * datum in PartitionBoundInfo::datums of that side.
 	 */
 	outer_lb_index = inner_lb_index = 0;
-	outer_part = get_range_partition(outer_bi, &outer_lb_index,
+	outer_part = get_range_partition(outer_rel, outer_bi, &outer_lb_index,
 									 &outer_lb, &outer_ub);
-	inner_part = get_range_partition(inner_bi, &inner_lb_index,
+	inner_part = get_range_partition(inner_rel, inner_bi, &inner_lb_index,
 									 &inner_lb, &inner_ub);
 	while (outer_part >= 0 || inner_part >= 0)
 	{
@@ -3544,9 +3584,9 @@ partition_range_bounds_merge(int partnatts, FmgrInfo *partsupfuncs,
 			save_inner_ub = inner_ub;
 
 			/* Move to the next pair of partitions. */
-			outer_part = get_range_partition(outer_bi, &outer_lb_index,
+			outer_part = get_range_partition(outer_rel, outer_bi, &outer_lb_index,
 											 &outer_lb, &outer_ub);
-			inner_part = get_range_partition(inner_bi, &inner_lb_index,
+			inner_part = get_range_partition(inner_rel, inner_bi, &inner_lb_index,
 											 &inner_lb, &inner_ub);
 
 			/*
@@ -3598,7 +3638,7 @@ partition_range_bounds_merge(int partnatts, FmgrInfo *partsupfuncs,
 			}
 
 			/* Move to the next partition on the outer side. */
-			outer_part = get_range_partition(outer_bi, &outer_lb_index,
+			outer_part = get_range_partition(outer_rel, outer_bi, &outer_lb_index,
 											 &outer_lb, &outer_ub);
 		}
 		else
@@ -3625,7 +3665,7 @@ partition_range_bounds_merge(int partnatts, FmgrInfo *partsupfuncs,
 			}
 
 			/* Move to the next partition on the inner side. */
-			inner_part = get_range_partition(inner_bi, &inner_lb_index,
+			inner_part = get_range_partition(inner_rel, inner_bi, &inner_lb_index,
 											 &inner_lb, &inner_ub);
 		}
 
@@ -3726,6 +3766,15 @@ partition_list_bounds_merge(FmgrInfo *partsupfunc, Oid *partcollation,
 	init_partition_map(outer_rel, &outer_map);
 	init_partition_map(inner_rel, &inner_map);
 
+	/*
+	 * If the default partitions (if any) have been proven empty, deem them
+	 * non-existent.
+	 */
+	if (outer_has_default && is_dummy_partition(outer_rel, outer_default))
+		outer_has_default = false;
+	if (inner_has_default && is_dummy_partition(inner_rel, inner_default))
+		inner_has_default = false;
+
 	/*
 	 * Merge the list value datums from both sides. Every iteration compares a
 	 * pair of datums, one from each side, advancing to the next datum from the
@@ -3738,10 +3787,39 @@ partition_list_bounds_merge(FmgrInfo *partsupfunc, Oid *partcollation,
 	{
 		Datum	   *merged_datum = NULL;
 		int			merged_index = -1;
+		int			o_index = -1;
+		int			i_index = -1;
 		Datum	   *odatums;
 		Datum	   *idatums;
 		int			cmpval;
 
+		if (cnto < outer_bi->ndatums)
+		{
+			/*
+			 * If the partition on the outer side has been proven empty, ignore
+			 * it and move to the next datum on the outer side.
+			 */
+			o_index = outer_bi->indexes[cnto];
+			if (is_dummy_partition(outer_rel, o_index))
+			{
+				cnto++;
+				continue;
+			}
+		}
+		if (cnti < inner_bi->ndatums)
+		{
+			/*
+			 * If the partition on the inner side has been proven empty, ignore
+			 * it and move to the next datum on the inner side.
+			 */
+			i_index = inner_bi->indexes[cnti];
+			if (is_dummy_partition(inner_rel, i_index))
+			{
+				cnti++;
+				continue;
+			}
+		}
+
 		/* Get the list datums of the next pair of partitions. */
 		odatums = cnto < outer_bi->ndatums ? outer_bi->datums[cnto] : NULL;
 		idatums = cnti < inner_bi->ndatums ? inner_bi->datums[cnti] : NULL;
@@ -3770,9 +3848,6 @@ partition_list_bounds_merge(FmgrInfo *partsupfunc, Oid *partcollation,
 
 		if (cmpval == 0)
 		{
-			int			o_index = outer_bi->indexes[cnto];
-			int			i_index = inner_bi->indexes[cnti];
-
 			/*
 			 * Datums match. Rows on either side with these datums as partition
 			 * key value will join and will be part of the partition of the
@@ -3797,14 +3872,13 @@ partition_list_bounds_merge(FmgrInfo *partsupfunc, Oid *partcollation,
 		else if (cmpval < 0)
 		{
 			Assert(cnto < outer_bi->ndatums);
+			Assert(o_index >= 0);
 
 			/* A datum missing from the inner side. */
 			merged_datum = odatums;
 
 			if (inner_has_default || IS_OUTER_JOIN(jointype))
 			{
-				int			o_index = outer_bi->indexes[cnto];
-
 				Assert(o_index >= 0);
 				merged_index = process_outer_partition(&outer_map,
 													   &inner_map,
@@ -3826,14 +3900,13 @@ partition_list_bounds_merge(FmgrInfo *partsupfunc, Oid *partcollation,
 		{
 			Assert(cmpval > 0);
 			Assert(cnti < inner_bi->ndatums);
+			Assert(i_index >= 0);
 
 			/* A datum missing from the outer side. */
 			merged_datum = idatums;
 
 			if (outer_has_default || jointype == JOIN_FULL)
 			{
-				int			i_index = inner_bi->indexes[cnti];
-
 				Assert(i_index >= 0);
 				merged_index = process_inner_partition(&outer_map,
 													   &inner_map,
@@ -3863,6 +3936,17 @@ partition_list_bounds_merge(FmgrInfo *partsupfunc, Oid *partcollation,
 		}
 	}
 
+	/*
+	 * If the NULL partitions (if any) have been proven empty, deem them
+	 * non-existent.
+	 */
+	if (outer_has_null &&
+		is_dummy_partition(outer_rel, outer_bi->null_index))
+		outer_has_null = false;
+	if (inner_has_null &&
+		is_dummy_partition(inner_rel, inner_bi->null_index))
+		inner_has_null = false;
+
 	/* Merge null partitions if any. */
 	if (outer_has_null || inner_has_null)
 		merge_null_partitions(&outer_map, &inner_map,
@@ -3950,6 +4034,21 @@ free_partition_map(PartitionMap *map)
 	pfree(map->old_indexes);
 }
 
+/*
+ * is_dummy_partition --- has partition been proven empty?
+ */
+static bool
+is_dummy_partition(RelOptInfo *rel, int part_index)
+{
+	RelOptInfo *part_rel;
+
+	Assert(part_index >= 0);
+	part_rel = rel->part_rels[part_index];
+	if (part_rel == NULL || IS_DUMMY_REL(part_rel))
+		return true;
+	return false;
+}
+
 /*
  * map_and_merge_partitions
  *
diff --git a/src/test/regress/expected/partition_join.out b/src/test/regress/expected/partition_join.out
index 924cb99863..d3f4e50878 100644
--- a/src/test/regress/expected/partition_join.out
+++ b/src/test/regress/expected/partition_join.out
@@ -2872,6 +2872,98 @@ SELECT t1.a, t1.c, t2.b, t2.c, t3.a, t3.c FROM prt1_ad t1 LEFT JOIN prt2_ad t2 O
  275 | 0275 | 275 | 0275 | 275 | 0275
 (8 rows)
 
+DROP TABLE prt1_ad;
+DROP TABLE prt2_ad;
+DROP TABLE prt3_ad;
+-- Test interaction of partitioned join with partition pruning
+CREATE TABLE prt1_ad (a int, b int, c varchar) PARTITION BY RANGE (a);
+CREATE TABLE prt1_ad_p1 PARTITION OF prt1_ad FOR VALUES FROM (100) TO (200);
+CREATE TABLE prt1_ad_p2 PARTITION OF prt1_ad FOR VALUES FROM (200) TO (300);
+CREATE TABLE prt1_ad_p3 PARTITION OF prt1_ad FOR VALUES FROM (300) TO (400);
+CREATE INDEX prt1_ad_a_idx on prt1_ad (a);
+INSERT INTO prt1_ad SELECT i, i % 25, to_char(i, 'FM0000') FROM generate_series(100, 399) i;
+ANALYZE prt1_ad;
+CREATE TABLE prt2_ad (a int, b int, c varchar) PARTITION BY RANGE (b);
+CREATE TABLE prt2_ad_p1 PARTITION OF prt2_ad FOR VALUES FROM (100) TO (200);
+CREATE TABLE prt2_ad_p2 PARTITION OF prt2_ad FOR VALUES FROM (200) TO (400);
+CREATE INDEX prt2_ad_b_idx on prt2_ad (b);
+INSERT INTO prt2_ad SELECT i % 25, i, to_char(i, 'FM0000') FROM generate_series(100, 399) i;
+ANALYZE prt2_ad;
+EXPLAIN (COSTS OFF)
+SELECT t1.a, t1.c, t2.b, t2.c FROM prt1_ad t1 INNER JOIN prt2_ad t2 ON (t1.a = t2.b) WHERE t1.a < 300 AND t1.b = 0 ORDER BY t1.a, t2.b;
+                        QUERY PLAN                         
+-----------------------------------------------------------
+ Sort
+   Sort Key: t1.a
+   ->  Append
+         ->  Hash Join
+               Hash Cond: (t2_1.b = t1_1.a)
+               ->  Seq Scan on prt2_ad_p1 t2_1
+               ->  Hash
+                     ->  Seq Scan on prt1_ad_p1 t1_1
+                           Filter: ((a < 300) AND (b = 0))
+         ->  Hash Join
+               Hash Cond: (t2_2.b = t1_2.a)
+               ->  Seq Scan on prt2_ad_p2 t2_2
+               ->  Hash
+                     ->  Seq Scan on prt1_ad_p2 t1_2
+                           Filter: ((a < 300) AND (b = 0))
+(15 rows)
+
+SELECT t1.a, t1.c, t2.b, t2.c FROM prt1_ad t1 INNER JOIN prt2_ad t2 ON (t1.a = t2.b) WHERE t1.a < 300 AND t1.b = 0 ORDER BY t1.a, t2.b;
+  a  |  c   |  b  |  c   
+-----+------+-----+------
+ 100 | 0100 | 100 | 0100
+ 125 | 0125 | 125 | 0125
+ 150 | 0150 | 150 | 0150
+ 175 | 0175 | 175 | 0175
+ 200 | 0200 | 200 | 0200
+ 225 | 0225 | 225 | 0225
+ 250 | 0250 | 250 | 0250
+ 275 | 0275 | 275 | 0275
+(8 rows)
+
+DROP TABLE prt1_ad_p3;
+CREATE TABLE prt1_ad_default PARTITION OF prt1_ad DEFAULT;
+ANALYZE prt1_ad;
+CREATE TABLE prt2_ad_default PARTITION OF prt2_ad DEFAULT;
+ANALYZE prt2_ad;
+EXPLAIN (COSTS OFF)
+SELECT t1.a, t1.c, t2.b, t2.c FROM prt1_ad t1 INNER JOIN prt2_ad t2 ON (t1.a = t2.b) WHERE t1.a >= 100 AND t1.a < 300 AND t1.b = 0 ORDER BY t1.a, t2.b;
+                                QUERY PLAN                                
+--------------------------------------------------------------------------
+ Sort
+   Sort Key: t1.a
+   ->  Append
+         ->  Hash Join
+               Hash Cond: (t2_1.b = t1_1.a)
+               ->  Seq Scan on prt2_ad_p1 t2_1
+               ->  Hash
+                     ->  Seq Scan on prt1_ad_p1 t1_1
+                           Filter: ((a >= 100) AND (a < 300) AND (b = 0))
+         ->  Hash Join
+               Hash Cond: (t2_2.b = t1_2.a)
+               ->  Seq Scan on prt2_ad_p2 t2_2
+               ->  Hash
+                     ->  Seq Scan on prt1_ad_p2 t1_2
+                           Filter: ((a >= 100) AND (a < 300) AND (b = 0))
+(15 rows)
+
+SELECT t1.a, t1.c, t2.b, t2.c FROM prt1_ad t1 INNER JOIN prt2_ad t2 ON (t1.a = t2.b) WHERE t1.a >= 100 AND t1.a < 300 AND t1.b = 0 ORDER BY t1.a, t2.b;
+  a  |  c   |  b  |  c   
+-----+------+-----+------
+ 100 | 0100 | 100 | 0100
+ 125 | 0125 | 125 | 0125
+ 150 | 0150 | 150 | 0150
+ 175 | 0175 | 175 | 0175
+ 200 | 0200 | 200 | 0200
+ 225 | 0225 | 225 | 0225
+ 250 | 0250 | 250 | 0250
+ 275 | 0275 | 275 | 0275
+(8 rows)
+
+DROP TABLE prt1_ad;
+DROP TABLE prt2_ad;
 -- Tests for list-partitioned tables
 CREATE TABLE plt1_ad (a int, b int, c text) PARTITION BY LIST (c);
 CREATE TABLE plt1_ad_p1 PARTITION OF plt1_ad FOR VALUES IN ('0001', '0003');
@@ -4168,3 +4260,131 @@ SELECT t1.a, t1.c, t2.a, t2.c, t3.a, t3.c FROM (plt1_ad t1 LEFT JOIN plt2_ad t2
  22 | 0002 | 22 | 0002 |    | 
 (55 rows)
 
+DROP TABLE plt1_ad;
+DROP TABLE plt2_ad;
+DROP TABLE plt3_ad;
+-- Test interaction of partitioned join with partition pruning
+CREATE TABLE plt1_ad (a int, b int, c text) PARTITION BY LIST (c);
+CREATE TABLE plt1_ad_p1 PARTITION OF plt1_ad FOR VALUES IN ('0001');
+CREATE TABLE plt1_ad_p2 PARTITION OF plt1_ad FOR VALUES IN ('0002');
+CREATE TABLE plt1_ad_p3 PARTITION OF plt1_ad FOR VALUES IN ('0003');
+CREATE TABLE plt1_ad_p4 PARTITION OF plt1_ad FOR VALUES IN (NULL, '0004', '0005');
+INSERT INTO plt1_ad SELECT i, i, to_char(i % 10, 'FM0000') FROM generate_series(1, 299) i WHERE i % 10 IN (1, 2, 3, 4, 5);
+INSERT INTO plt1_ad VALUES (-1, -1, NULL);
+ANALYZE plt1_ad;
+CREATE TABLE plt2_ad (a int, b int, c text) PARTITION BY LIST (c);
+CREATE TABLE plt2_ad_p1 PARTITION OF plt2_ad FOR VALUES IN ('0001', '0002');
+CREATE TABLE plt2_ad_p2 PARTITION OF plt2_ad FOR VALUES IN ('0003');
+CREATE TABLE plt2_ad_p3 PARTITION OF plt2_ad FOR VALUES IN (NULL);
+CREATE TABLE plt2_ad_p4 PARTITION OF plt2_ad FOR VALUES IN ('0004', '0005');
+INSERT INTO plt2_ad SELECT i, i, to_char(i % 10, 'FM0000') FROM generate_series(1, 299) i WHERE i % 10 IN (1, 2, 3, 4, 5);
+INSERT INTO plt2_ad VALUES (-1, -1, NULL);
+ANALYZE plt2_ad;
+EXPLAIN (COSTS OFF)
+SELECT t1.a, t1.c, t2.a, t2.c FROM plt1_ad t1 INNER JOIN plt2_ad t2 ON (t1.a = t2.a AND t1.c = t2.c) WHERE t1.c IN ('0003', '0004', '0005') AND t1.b < 10 AND t2.c IS NOT NULL ORDER BY t1.a;
+                                       QUERY PLAN                                        
+-----------------------------------------------------------------------------------------
+ Sort
+   Sort Key: t1.a
+   ->  Append
+         ->  Hash Join
+               Hash Cond: ((t2_1.a = t1_1.a) AND (t2_1.c = t1_1.c))
+               ->  Seq Scan on plt2_ad_p2 t2_1
+                     Filter: (c IS NOT NULL)
+               ->  Hash
+                     ->  Seq Scan on plt1_ad_p3 t1_1
+                           Filter: ((b < 10) AND (c = ANY ('{0003,0004,0005}'::text[])))
+         ->  Hash Join
+               Hash Cond: ((t2_2.a = t1_2.a) AND (t2_2.c = t1_2.c))
+               ->  Seq Scan on plt2_ad_p4 t2_2
+                     Filter: (c IS NOT NULL)
+               ->  Hash
+                     ->  Seq Scan on plt1_ad_p4 t1_2
+                           Filter: ((b < 10) AND (c = ANY ('{0003,0004,0005}'::text[])))
+(17 rows)
+
+SELECT t1.a, t1.c, t2.a, t2.c FROM plt1_ad t1 INNER JOIN plt2_ad t2 ON (t1.a = t2.a AND t1.c = t2.c) WHERE t1.c IN ('0003', '0004', '0005') AND t1.b < 10 AND t2.c IS NOT NULL ORDER BY t1.a;
+ a |  c   | a |  c   
+---+------+---+------
+ 3 | 0003 | 3 | 0003
+ 4 | 0004 | 4 | 0004
+ 5 | 0005 | 5 | 0005
+(3 rows)
+
+EXPLAIN (COSTS OFF)
+SELECT t1.a, t1.c, t2.a, t2.c FROM plt1_ad t1 LEFT JOIN plt2_ad t2 ON (t1.a = t2.a AND t1.c = t2.c) WHERE t1.c IS NULL AND t1.b < 10 ORDER BY t1.a;
+                       QUERY PLAN                       
+--------------------------------------------------------
+ Sort
+   Sort Key: t1.a
+   ->  Hash Right Join
+         Hash Cond: ((t2.a = t1.a) AND (t2.c = t1.c))
+         ->  Seq Scan on plt2_ad_p4 t2
+         ->  Hash
+               ->  Seq Scan on plt1_ad_p4 t1
+                     Filter: ((c IS NULL) AND (b < 10))
+(8 rows)
+
+SELECT t1.a, t1.c, t2.a, t2.c FROM plt1_ad t1 LEFT JOIN plt2_ad t2 ON (t1.a = t2.a AND t1.c = t2.c) WHERE t1.c IS NULL AND t1.b < 10 ORDER BY t1.a;
+ a  | c | a | c 
+----+---+---+---
+ -1 |   |   | 
+(1 row)
+
+CREATE TABLE plt1_ad_default PARTITION OF plt1_ad DEFAULT;
+ANALYZE plt1_ad;
+CREATE TABLE plt2_ad_default PARTITION OF plt2_ad DEFAULT;
+ANALYZE plt2_ad;
+EXPLAIN (COSTS OFF)
+SELECT t1.a, t1.c, t2.a, t2.c FROM plt1_ad t1 INNER JOIN plt2_ad t2 ON (t1.a = t2.a AND t1.c = t2.c) WHERE t1.c IN ('0003', '0004', '0005') AND t1.b < 10 AND t2.c IS NOT NULL ORDER BY t1.a;
+                                       QUERY PLAN                                        
+-----------------------------------------------------------------------------------------
+ Sort
+   Sort Key: t1.a
+   ->  Append
+         ->  Hash Join
+               Hash Cond: ((t2_1.a = t1_1.a) AND (t2_1.c = t1_1.c))
+               ->  Seq Scan on plt2_ad_p2 t2_1
+                     Filter: (c IS NOT NULL)
+               ->  Hash
+                     ->  Seq Scan on plt1_ad_p3 t1_1
+                           Filter: ((b < 10) AND (c = ANY ('{0003,0004,0005}'::text[])))
+         ->  Hash Join
+               Hash Cond: ((t2_2.a = t1_2.a) AND (t2_2.c = t1_2.c))
+               ->  Seq Scan on plt2_ad_p4 t2_2
+                     Filter: (c IS NOT NULL)
+               ->  Hash
+                     ->  Seq Scan on plt1_ad_p4 t1_2
+                           Filter: ((b < 10) AND (c = ANY ('{0003,0004,0005}'::text[])))
+(17 rows)
+
+SELECT t1.a, t1.c, t2.a, t2.c FROM plt1_ad t1 INNER JOIN plt2_ad t2 ON (t1.a = t2.a AND t1.c = t2.c) WHERE t1.c IN ('0003', '0004', '0005') AND t1.b < 10 AND t2.c IS NOT NULL ORDER BY t1.a;
+ a |  c   | a |  c   
+---+------+---+------
+ 3 | 0003 | 3 | 0003
+ 4 | 0004 | 4 | 0004
+ 5 | 0005 | 5 | 0005
+(3 rows)
+
+EXPLAIN (COSTS OFF)
+SELECT t1.a, t1.c, t2.a, t2.c FROM plt1_ad t1 LEFT JOIN plt2_ad t2 ON (t1.a = t2.a AND t1.c = t2.c) WHERE t1.c IS NULL AND t1.b < 10 ORDER BY t1.a;
+                       QUERY PLAN                       
+--------------------------------------------------------
+ Sort
+   Sort Key: t1.a
+   ->  Hash Right Join
+         Hash Cond: ((t2.a = t1.a) AND (t2.c = t1.c))
+         ->  Seq Scan on plt2_ad_p4 t2
+         ->  Hash
+               ->  Seq Scan on plt1_ad_p4 t1
+                     Filter: ((c IS NULL) AND (b < 10))
+(8 rows)
+
+SELECT t1.a, t1.c, t2.a, t2.c FROM plt1_ad t1 LEFT JOIN plt2_ad t2 ON (t1.a = t2.a AND t1.c = t2.c) WHERE t1.c IS NULL AND t1.b < 10 ORDER BY t1.a;
+ a  | c | a | c 
+----+---+---+---
+ -1 |   |   | 
+(1 row)
+
+DROP TABLE plt1_ad;
+DROP TABLE plt2_ad;
diff --git a/src/test/regress/sql/partition_join.sql b/src/test/regress/sql/partition_join.sql
index 418ffa117f..069ec7ca5d 100644
--- a/src/test/regress/sql/partition_join.sql
+++ b/src/test/regress/sql/partition_join.sql
@@ -631,6 +631,44 @@ EXPLAIN (COSTS OFF)
 SELECT t1.a, t1.c, t2.b, t2.c, t3.a, t3.c FROM prt1_ad t1 LEFT JOIN prt2_ad t2 ON (t1.a = t2.b) LEFT JOIN prt3_ad t3 ON (t1.a = t3.a) WHERE t1.b = 0 ORDER BY t1.a, t2.b, t3.a;
 SELECT t1.a, t1.c, t2.b, t2.c, t3.a, t3.c FROM prt1_ad t1 LEFT JOIN prt2_ad t2 ON (t1.a = t2.b) LEFT JOIN prt3_ad t3 ON (t1.a = t3.a) WHERE t1.b = 0 ORDER BY t1.a, t2.b, t3.a;
 
+DROP TABLE prt1_ad;
+DROP TABLE prt2_ad;
+DROP TABLE prt3_ad;
+
+-- Test interaction of partitioned join with partition pruning
+
+CREATE TABLE prt1_ad (a int, b int, c varchar) PARTITION BY RANGE (a);
+CREATE TABLE prt1_ad_p1 PARTITION OF prt1_ad FOR VALUES FROM (100) TO (200);
+CREATE TABLE prt1_ad_p2 PARTITION OF prt1_ad FOR VALUES FROM (200) TO (300);
+CREATE TABLE prt1_ad_p3 PARTITION OF prt1_ad FOR VALUES FROM (300) TO (400);
+CREATE INDEX prt1_ad_a_idx on prt1_ad (a);
+INSERT INTO prt1_ad SELECT i, i % 25, to_char(i, 'FM0000') FROM generate_series(100, 399) i;
+ANALYZE prt1_ad;
+
+CREATE TABLE prt2_ad (a int, b int, c varchar) PARTITION BY RANGE (b);
+CREATE TABLE prt2_ad_p1 PARTITION OF prt2_ad FOR VALUES FROM (100) TO (200);
+CREATE TABLE prt2_ad_p2 PARTITION OF prt2_ad FOR VALUES FROM (200) TO (400);
+CREATE INDEX prt2_ad_b_idx on prt2_ad (b);
+INSERT INTO prt2_ad SELECT i % 25, i, to_char(i, 'FM0000') FROM generate_series(100, 399) i;
+ANALYZE prt2_ad;
+
+EXPLAIN (COSTS OFF)
+SELECT t1.a, t1.c, t2.b, t2.c FROM prt1_ad t1 INNER JOIN prt2_ad t2 ON (t1.a = t2.b) WHERE t1.a < 300 AND t1.b = 0 ORDER BY t1.a, t2.b;
+SELECT t1.a, t1.c, t2.b, t2.c FROM prt1_ad t1 INNER JOIN prt2_ad t2 ON (t1.a = t2.b) WHERE t1.a < 300 AND t1.b = 0 ORDER BY t1.a, t2.b;
+
+DROP TABLE prt1_ad_p3;
+CREATE TABLE prt1_ad_default PARTITION OF prt1_ad DEFAULT;
+ANALYZE prt1_ad;
+
+CREATE TABLE prt2_ad_default PARTITION OF prt2_ad DEFAULT;
+ANALYZE prt2_ad;
+
+EXPLAIN (COSTS OFF)
+SELECT t1.a, t1.c, t2.b, t2.c FROM prt1_ad t1 INNER JOIN prt2_ad t2 ON (t1.a = t2.b) WHERE t1.a >= 100 AND t1.a < 300 AND t1.b = 0 ORDER BY t1.a, t2.b;
+SELECT t1.a, t1.c, t2.b, t2.c FROM prt1_ad t1 INNER JOIN prt2_ad t2 ON (t1.a = t2.b) WHERE t1.a >= 100 AND t1.a < 300 AND t1.b = 0 ORDER BY t1.a, t2.b;
+
+DROP TABLE prt1_ad;
+DROP TABLE prt2_ad;
 
 -- Tests for list-partitioned tables
 CREATE TABLE plt1_ad (a int, b int, c text) PARTITION BY LIST (c);
@@ -954,3 +992,52 @@ ANALYZE plt3_ad;
 EXPLAIN (COSTS OFF)
 SELECT t1.a, t1.c, t2.a, t2.c, t3.a, t3.c FROM (plt1_ad t1 LEFT JOIN plt2_ad t2 ON (t1.c = t2.c)) FULL JOIN plt3_ad t3 ON (t1.c = t3.c) WHERE coalesce(t1.a, 0) % 5 != 3 AND coalesce(t1.a, 0) % 5 != 4 ORDER BY t1.c, t1.a, t2.a, t3.a;
 SELECT t1.a, t1.c, t2.a, t2.c, t3.a, t3.c FROM (plt1_ad t1 LEFT JOIN plt2_ad t2 ON (t1.c = t2.c)) FULL JOIN plt3_ad t3 ON (t1.c = t3.c) WHERE coalesce(t1.a, 0) % 5 != 3 AND coalesce(t1.a, 0) % 5 != 4 ORDER BY t1.c, t1.a, t2.a, t3.a;
+
+DROP TABLE plt1_ad;
+DROP TABLE plt2_ad;
+DROP TABLE plt3_ad;
+
+-- Test interaction of partitioned join with partition pruning
+
+CREATE TABLE plt1_ad (a int, b int, c text) PARTITION BY LIST (c);
+CREATE TABLE plt1_ad_p1 PARTITION OF plt1_ad FOR VALUES IN ('0001');
+CREATE TABLE plt1_ad_p2 PARTITION OF plt1_ad FOR VALUES IN ('0002');
+CREATE TABLE plt1_ad_p3 PARTITION OF plt1_ad FOR VALUES IN ('0003');
+CREATE TABLE plt1_ad_p4 PARTITION OF plt1_ad FOR VALUES IN (NULL, '0004', '0005');
+INSERT INTO plt1_ad SELECT i, i, to_char(i % 10, 'FM0000') FROM generate_series(1, 299) i WHERE i % 10 IN (1, 2, 3, 4, 5);
+INSERT INTO plt1_ad VALUES (-1, -1, NULL);
+ANALYZE plt1_ad;
+
+CREATE TABLE plt2_ad (a int, b int, c text) PARTITION BY LIST (c);
+CREATE TABLE plt2_ad_p1 PARTITION OF plt2_ad FOR VALUES IN ('0001', '0002');
+CREATE TABLE plt2_ad_p2 PARTITION OF plt2_ad FOR VALUES IN ('0003');
+CREATE TABLE plt2_ad_p3 PARTITION OF plt2_ad FOR VALUES IN (NULL);
+CREATE TABLE plt2_ad_p4 PARTITION OF plt2_ad FOR VALUES IN ('0004', '0005');
+INSERT INTO plt2_ad SELECT i, i, to_char(i % 10, 'FM0000') FROM generate_series(1, 299) i WHERE i % 10 IN (1, 2, 3, 4, 5);
+INSERT INTO plt2_ad VALUES (-1, -1, NULL);
+ANALYZE plt2_ad;
+
+EXPLAIN (COSTS OFF)
+SELECT t1.a, t1.c, t2.a, t2.c FROM plt1_ad t1 INNER JOIN plt2_ad t2 ON (t1.a = t2.a AND t1.c = t2.c) WHERE t1.c IN ('0003', '0004', '0005') AND t1.b < 10 AND t2.c IS NOT NULL ORDER BY t1.a;
+SELECT t1.a, t1.c, t2.a, t2.c FROM plt1_ad t1 INNER JOIN plt2_ad t2 ON (t1.a = t2.a AND t1.c = t2.c) WHERE t1.c IN ('0003', '0004', '0005') AND t1.b < 10 AND t2.c IS NOT NULL ORDER BY t1.a;
+
+EXPLAIN (COSTS OFF)
+SELECT t1.a, t1.c, t2.a, t2.c FROM plt1_ad t1 LEFT JOIN plt2_ad t2 ON (t1.a = t2.a AND t1.c = t2.c) WHERE t1.c IS NULL AND t1.b < 10 ORDER BY t1.a;
+SELECT t1.a, t1.c, t2.a, t2.c FROM plt1_ad t1 LEFT JOIN plt2_ad t2 ON (t1.a = t2.a AND t1.c = t2.c) WHERE t1.c IS NULL AND t1.b < 10 ORDER BY t1.a;
+
+CREATE TABLE plt1_ad_default PARTITION OF plt1_ad DEFAULT;
+ANALYZE plt1_ad;
+
+CREATE TABLE plt2_ad_default PARTITION OF plt2_ad DEFAULT;
+ANALYZE plt2_ad;
+
+EXPLAIN (COSTS OFF)
+SELECT t1.a, t1.c, t2.a, t2.c FROM plt1_ad t1 INNER JOIN plt2_ad t2 ON (t1.a = t2.a AND t1.c = t2.c) WHERE t1.c IN ('0003', '0004', '0005') AND t1.b < 10 AND t2.c IS NOT NULL ORDER BY t1.a;
+SELECT t1.a, t1.c, t2.a, t2.c FROM plt1_ad t1 INNER JOIN plt2_ad t2 ON (t1.a = t2.a AND t1.c = t2.c) WHERE t1.c IN ('0003', '0004', '0005') AND t1.b < 10 AND t2.c IS NOT NULL ORDER BY t1.a;
+
+EXPLAIN (COSTS OFF)
+SELECT t1.a, t1.c, t2.a, t2.c FROM plt1_ad t1 LEFT JOIN plt2_ad t2 ON (t1.a = t2.a AND t1.c = t2.c) WHERE t1.c IS NULL AND t1.b < 10 ORDER BY t1.a;
+SELECT t1.a, t1.c, t2.a, t2.c FROM plt1_ad t1 LEFT JOIN plt2_ad t2 ON (t1.a = t2.a AND t1.c = t2.c) WHERE t1.c IS NULL AND t1.b < 10 ORDER BY t1.a;
+
+DROP TABLE plt1_ad;
+DROP TABLE plt2_ad;