2 files changed, 148 insertions, 0 deletions
diff --git a/src/test/regress/expected/partition_join.out b/src/test/regress/expected/partition_join.out
index b45a590b945..585e7243752 100644
--- a/src/test/regress/expected/partition_join.out
+++ b/src/test/regress/expected/partition_join.out
@@ -2166,6 +2166,110 @@ SELECT t1.a, t1.c, t2.b, t2.c FROM prt1_n t1 FULL JOIN prt1 t2 ON (t1.c = t2.c);
 (10 rows)
 
 --
+-- Test some other plan types in a partitionwise join (unfortunately,
+-- we need larger tables to get the planner to choose these plan types)
+--
+create temp table prtx1 (a integer, b integer, c integer)
+  partition by range (a);
+create temp table prtx1_1 partition of prtx1 for values from (1) to (11);
+create temp table prtx1_2 partition of prtx1 for values from (11) to (21);
+create temp table prtx1_3 partition of prtx1 for values from (21) to (31);
+create temp table prtx2 (a integer, b integer, c integer)
+  partition by range (a);
+create temp table prtx2_1 partition of prtx2 for values from (1) to (11);
+create temp table prtx2_2 partition of prtx2 for values from (11) to (21);
+create temp table prtx2_3 partition of prtx2 for values from (21) to (31);
+insert into prtx1 select 1 + i%30, i, i
+  from generate_series(1,1000) i;
+insert into prtx2 select 1 + i%30, i, i
+  from generate_series(1,500) i, generate_series(1,10) j;
+create index on prtx2 (b);
+create index on prtx2 (c);
+analyze prtx1;
+analyze prtx2;
+explain (costs off)
+select * from prtx1
+where not exists (select 1 from prtx2
+                  where prtx2.a=prtx1.a and prtx2.b=prtx1.b and prtx2.c=123)
+  and a<20 and c=120;
+                         QUERY PLAN                          
+-------------------------------------------------------------
+ Append
+   ->  Nested Loop Anti Join
+         ->  Seq Scan on prtx1_1
+               Filter: ((a < 20) AND (c = 120))
+         ->  Bitmap Heap Scan on prtx2_1
+               Recheck Cond: ((b = prtx1_1.b) AND (c = 123))
+               Filter: (a = prtx1_1.a)
+               ->  BitmapAnd
+                     ->  Bitmap Index Scan on prtx2_1_b_idx
+                           Index Cond: (b = prtx1_1.b)
+                     ->  Bitmap Index Scan on prtx2_1_c_idx
+                           Index Cond: (c = 123)
+   ->  Nested Loop Anti Join
+         ->  Seq Scan on prtx1_2
+               Filter: ((a < 20) AND (c = 120))
+         ->  Bitmap Heap Scan on prtx2_2
+               Recheck Cond: ((b = prtx1_2.b) AND (c = 123))
+               Filter: (a = prtx1_2.a)
+               ->  BitmapAnd
+                     ->  Bitmap Index Scan on prtx2_2_b_idx
+                           Index Cond: (b = prtx1_2.b)
+                     ->  Bitmap Index Scan on prtx2_2_c_idx
+                           Index Cond: (c = 123)
+(23 rows)
+
+select * from prtx1
+where not exists (select 1 from prtx2
+                  where prtx2.a=prtx1.a and prtx2.b=prtx1.b and prtx2.c=123)
+  and a<20 and c=120;
+ a |  b  |  c  
+---+-----+-----
+ 1 | 120 | 120
+(1 row)
+
+explain (costs off)
+select * from prtx1
+where not exists (select 1 from prtx2
+                  where prtx2.a=prtx1.a and (prtx2.b=prtx1.b+1 or prtx2.c=99))
+  and a<20 and c=91;
+                           QUERY PLAN                            
+-----------------------------------------------------------------
+ Append
+   ->  Nested Loop Anti Join
+         ->  Seq Scan on prtx1_1
+               Filter: ((a < 20) AND (c = 91))
+         ->  Bitmap Heap Scan on prtx2_1
+               Recheck Cond: ((b = (prtx1_1.b + 1)) OR (c = 99))
+               Filter: (a = prtx1_1.a)
+               ->  BitmapOr
+                     ->  Bitmap Index Scan on prtx2_1_b_idx
+                           Index Cond: (b = (prtx1_1.b + 1))
+                     ->  Bitmap Index Scan on prtx2_1_c_idx
+                           Index Cond: (c = 99)
+   ->  Nested Loop Anti Join
+         ->  Seq Scan on prtx1_2
+               Filter: ((a < 20) AND (c = 91))
+         ->  Bitmap Heap Scan on prtx2_2
+               Recheck Cond: ((b = (prtx1_2.b + 1)) OR (c = 99))
+               Filter: (a = prtx1_2.a)
+               ->  BitmapOr
+                     ->  Bitmap Index Scan on prtx2_2_b_idx
+                           Index Cond: (b = (prtx1_2.b + 1))
+                     ->  Bitmap Index Scan on prtx2_2_c_idx
+                           Index Cond: (c = 99)
+(23 rows)
+
+select * from prtx1
+where not exists (select 1 from prtx2
+                  where prtx2.a=prtx1.a and (prtx2.b=prtx1.b+1 or prtx2.c=99))
+  and a<20 and c=91;
+ a | b  | c  
+---+----+----
+ 2 | 91 | 91
+(1 row)
+
+--
 -- Test advanced partition-matching algorithm for partitioned join
 --
 -- Tests for range-partitioned tables
diff --git a/src/test/regress/sql/partition_join.sql b/src/test/regress/sql/partition_join.sql
index 2a15362b1f8..73606c86e51 100644
--- a/src/test/regress/sql/partition_join.sql
+++ b/src/test/regress/sql/partition_join.sql
@@ -463,6 +463,50 @@ SELECT t1.a, t1.c, t2.b, t2.c FROM prt1_n t1 JOIN prt2_n t2 ON (t1.c = t2.c) JOI
 EXPLAIN (COSTS OFF)
 SELECT t1.a, t1.c, t2.b, t2.c FROM prt1_n t1 FULL JOIN prt1 t2 ON (t1.c = t2.c);
 
+--
+-- Test some other plan types in a partitionwise join (unfortunately,
+-- we need larger tables to get the planner to choose these plan types)
+--
+create temp table prtx1 (a integer, b integer, c integer)
+  partition by range (a);
+create temp table prtx1_1 partition of prtx1 for values from (1) to (11);
+create temp table prtx1_2 partition of prtx1 for values from (11) to (21);
+create temp table prtx1_3 partition of prtx1 for values from (21) to (31);
+create temp table prtx2 (a integer, b integer, c integer)
+  partition by range (a);
+create temp table prtx2_1 partition of prtx2 for values from (1) to (11);
+create temp table prtx2_2 partition of prtx2 for values from (11) to (21);
+create temp table prtx2_3 partition of prtx2 for values from (21) to (31);
+insert into prtx1 select 1 + i%30, i, i
+  from generate_series(1,1000) i;
+insert into prtx2 select 1 + i%30, i, i
+  from generate_series(1,500) i, generate_series(1,10) j;
+create index on prtx2 (b);
+create index on prtx2 (c);
+analyze prtx1;
+analyze prtx2;
+
+explain (costs off)
+select * from prtx1
+where not exists (select 1 from prtx2
+                  where prtx2.a=prtx1.a and prtx2.b=prtx1.b and prtx2.c=123)
+  and a<20 and c=120;
+
+select * from prtx1
+where not exists (select 1 from prtx2
+                  where prtx2.a=prtx1.a and prtx2.b=prtx1.b and prtx2.c=123)
+  and a<20 and c=120;
+
+explain (costs off)
+select * from prtx1
+where not exists (select 1 from prtx2
+                  where prtx2.a=prtx1.a and (prtx2.b=prtx1.b+1 or prtx2.c=99))
+  and a<20 and c=91;
+
+select * from prtx1
+where not exists (select 1 from prtx2
+                  where prtx2.a=prtx1.a and (prtx2.b=prtx1.b+1 or prtx2.c=99))
+  and a<20 and c=91;
 
 --
 -- Test advanced partition-matching algorithm for partitioned join