v4-0004-Temporary-hack-to-unbreak-partitionwise-join-cont.patch

From 744b99293955e0a138134db22ddf4d30ef63b1f3 Mon Sep 17 00:00:00 2001
From: Robert Haas <rhaas@postgresql.org>
Date: Wed, 29 Oct 2025 15:17:46 -0400
Subject: [PATCH v4 4/6] Temporary hack to unbreak partitionwise join control.

Resetting the pathlist and partial pathlist to NIL when the
topmost scan/join rel is a partitioned joinrel is incorrect. The issue
was originally reported by Ashutosh Bapat here:

http://postgr.es/m/CAExHW5toze58+jL-454J3ty11sqJyU13Sz5rJPQZDmASwZgWiA@mail.gmail.com

I failed to understand Ashutosh's explanation until I hit the problem
myself, so here's my attempt to re-explain what he had said, just in
case you find my explanation any clearer:

http://postgr.es/m/CA%2BTgmoZvBD%2B5vyQruXBVXW74FMgWxE%3DO4K4rCrCtEELWNj-MLA%40mail.gmail.com

As subsequent discussion on that thread indicates, it is unclear
exactly what the right fix for this problem is, and at least as of
this writing, it is even more unclear how to adjust the test cases
that break. What I've done here is just accept all the changes to the
regression test outputs, which is almost certainly the wrong idea,
especially since I've also added no comments.

This is just a temporary hack to make it possible to test this patch
set, because without this, PARTITIONWISE() advice can't be used to
suppress a partitionwise join, because all of the alternatives get
eliminated regardless of cost.
---
 src/backend/optimizer/plan/planner.c         |   4 +-
 src/test/regress/expected/partition_join.out | 172 ++++++++-----------
 src/test/regress/expected/subselect.out      |  41 ++---
 3 files changed, 91 insertions(+), 126 deletions(-)

diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.c
index eb62794aecd..8b1ab847f39 100644
--- a/src/backend/optimizer/plan/planner.c
+++ b/src/backend/optimizer/plan/planner.c
@@ -7927,7 +7927,7 @@ apply_scanjoin_target_to_paths(PlannerInfo *root,
 	 * generate_useful_gather_paths to add path(s) to the main list, and
 	 * finally zap the partial pathlist.
 	 */
-	if (rel_is_partitioned)
+	if (rel_is_partitioned && IS_SIMPLE_REL(rel))
 		rel->pathlist = NIL;
 
 	/*
@@ -7953,7 +7953,7 @@ apply_scanjoin_target_to_paths(PlannerInfo *root,
 	}
 
 	/* Finish dropping old paths for a partitioned rel, per comment above */
-	if (rel_is_partitioned)
+	if (rel_is_partitioned && IS_SIMPLE_REL(rel))
 		rel->partial_pathlist = NIL;
 
 	/* Extract SRF-free scan/join target. */
diff --git a/src/test/regress/expected/partition_join.out b/src/test/regress/expected/partition_join.out
index 713828be335..3e34f05ba62 100644
--- a/src/test/regress/expected/partition_join.out
+++ b/src/test/regress/expected/partition_join.out
@@ -65,31 +65,24 @@ SELECT t1.a, t1.c, t2.b, t2.c FROM prt1 t1, prt2 t2 WHERE t1.a = t2.b AND t1.b =
 -- inner join with partially-redundant join clauses
 EXPLAIN (COSTS OFF)
 SELECT t1.a, t1.c, t2.b, t2.c FROM prt1 t1, prt2 t2 WHERE t1.a = t2.a AND t1.a = t2.b ORDER BY t1.a, t2.b;
-                          QUERY PLAN                           
----------------------------------------------------------------
- Sort
-   Sort Key: t1.a
+                       QUERY PLAN                        
+---------------------------------------------------------
+ Merge Join
+   Merge Cond: (t1.a = t2.a)
    ->  Append
-         ->  Merge Join
-               Merge Cond: (t1_1.a = t2_1.a)
-               ->  Index Scan using iprt1_p1_a on prt1_p1 t1_1
-               ->  Sort
-                     Sort Key: t2_1.b
-                     ->  Seq Scan on prt2_p1 t2_1
-                           Filter: (a = b)
-         ->  Hash Join
-               Hash Cond: (t1_2.a = t2_2.a)
-               ->  Seq Scan on prt1_p2 t1_2
-               ->  Hash
-                     ->  Seq Scan on prt2_p2 t2_2
-                           Filter: (a = b)
-         ->  Hash Join
-               Hash Cond: (t1_3.a = t2_3.a)
-               ->  Seq Scan on prt1_p3 t1_3
-               ->  Hash
-                     ->  Seq Scan on prt2_p3 t2_3
-                           Filter: (a = b)
-(22 rows)
+         ->  Index Scan using iprt1_p1_a on prt1_p1 t1_1
+         ->  Index Scan using iprt1_p2_a on prt1_p2 t1_2
+         ->  Index Scan using iprt1_p3_a on prt1_p3 t1_3
+   ->  Sort
+         Sort Key: t2.b
+         ->  Append
+               ->  Seq Scan on prt2_p1 t2_1
+                     Filter: (a = b)
+               ->  Seq Scan on prt2_p2 t2_2
+                     Filter: (a = b)
+               ->  Seq Scan on prt2_p3 t2_3
+                     Filter: (a = b)
+(15 rows)
 
 SELECT t1.a, t1.c, t2.b, t2.c FROM prt1 t1, prt2 t2 WHERE t1.a = t2.a AND t1.a = t2.b ORDER BY t1.a, t2.b;
  a  |  c   | b  |  c   
@@ -1249,56 +1242,50 @@ SET enable_hashjoin TO off;
 SET enable_nestloop TO off;
 EXPLAIN (COSTS OFF)
 SELECT t1.* FROM prt1 t1 WHERE t1.a IN (SELECT t1.b FROM prt2 t1 WHERE t1.b IN (SELECT (t1.a + t1.b)/2 FROM prt1_e t1 WHERE t1.c = 0)) AND t1.b = 0 ORDER BY t1.a;
-                            QUERY PLAN                            
-------------------------------------------------------------------
- Merge Append
-   Sort Key: t1.a
-   ->  Merge Semi Join
-         Merge Cond: (t1_3.a = t1_6.b)
-         ->  Sort
-               Sort Key: t1_3.a
+                               QUERY PLAN                               
+------------------------------------------------------------------------
+ Merge Join
+   Merge Cond: (t1.a = t1_1.b)
+   ->  Sort
+         Sort Key: t1.a
+         ->  Append
                ->  Seq Scan on prt1_p1 t1_3
                      Filter: (b = 0)
-         ->  Merge Semi Join
-               Merge Cond: (t1_6.b = (((t1_9.a + t1_9.b) / 2)))
-               ->  Sort
-                     Sort Key: t1_6.b
-                     ->  Seq Scan on prt2_p1 t1_6
-               ->  Sort
-                     Sort Key: (((t1_9.a + t1_9.b) / 2))
-                     ->  Seq Scan on prt1_e_p1 t1_9
-                           Filter: (c = 0)
-   ->  Merge Semi Join
-         Merge Cond: (t1_4.a = t1_7.b)
-         ->  Sort
-               Sort Key: t1_4.a
                ->  Seq Scan on prt1_p2 t1_4
                      Filter: (b = 0)
-         ->  Merge Semi Join
-               Merge Cond: (t1_7.b = (((t1_10.a + t1_10.b) / 2)))
-               ->  Sort
-                     Sort Key: t1_7.b
-                     ->  Seq Scan on prt2_p2 t1_7
-               ->  Sort
-                     Sort Key: (((t1_10.a + t1_10.b) / 2))
-                     ->  Seq Scan on prt1_e_p2 t1_10
-                           Filter: (c = 0)
-   ->  Merge Semi Join
-         Merge Cond: (t1_5.a = t1_8.b)
-         ->  Sort
-               Sort Key: t1_5.a
                ->  Seq Scan on prt1_p3 t1_5
                      Filter: (b = 0)
-         ->  Merge Semi Join
-               Merge Cond: (t1_8.b = (((t1_11.a + t1_11.b) / 2)))
-               ->  Sort
-                     Sort Key: t1_8.b
-                     ->  Seq Scan on prt2_p3 t1_8
-               ->  Sort
-                     Sort Key: (((t1_11.a + t1_11.b) / 2))
-                     ->  Seq Scan on prt1_e_p3 t1_11
-                           Filter: (c = 0)
-(47 rows)
+   ->  Unique
+         ->  Merge Append
+               Sort Key: t1_1.b
+               ->  Merge Semi Join
+                     Merge Cond: (t1_6.b = (((t1_9.a + t1_9.b) / 2)))
+                     ->  Sort
+                           Sort Key: t1_6.b
+                           ->  Seq Scan on prt2_p1 t1_6
+                     ->  Sort
+                           Sort Key: (((t1_9.a + t1_9.b) / 2))
+                           ->  Seq Scan on prt1_e_p1 t1_9
+                                 Filter: (c = 0)
+               ->  Merge Semi Join
+                     Merge Cond: (t1_7.b = (((t1_10.a + t1_10.b) / 2)))
+                     ->  Sort
+                           Sort Key: t1_7.b
+                           ->  Seq Scan on prt2_p2 t1_7
+                     ->  Sort
+                           Sort Key: (((t1_10.a + t1_10.b) / 2))
+                           ->  Seq Scan on prt1_e_p2 t1_10
+                                 Filter: (c = 0)
+               ->  Merge Semi Join
+                     Merge Cond: (t1_8.b = (((t1_11.a + t1_11.b) / 2)))
+                     ->  Sort
+                           Sort Key: t1_8.b
+                           ->  Seq Scan on prt2_p3 t1_8
+                     ->  Sort
+                           Sort Key: (((t1_11.a + t1_11.b) / 2))
+                           ->  Seq Scan on prt1_e_p3 t1_11
+                                 Filter: (c = 0)
+(41 rows)
 
 SELECT t1.* FROM prt1 t1 WHERE t1.a IN (SELECT t1.b FROM prt2 t1 WHERE t1.b IN (SELECT (t1.a + t1.b)/2 FROM prt1_e t1 WHERE t1.c = 0)) AND t1.b = 0 ORDER BY t1.a;
   a  | b |  c   
@@ -4923,32 +4910,27 @@ ANALYZE plt3_adv;
 -- '0001' of that partition
 EXPLAIN (COSTS OFF)
 SELECT t1.a, t1.c, t2.a, t2.c, t3.a, t3.c FROM (plt1_adv t1 LEFT JOIN plt2_adv t2 ON (t1.c = t2.c)) FULL JOIN plt3_adv 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;
-                                          QUERY PLAN                                           
------------------------------------------------------------------------------------------------
+                                     QUERY PLAN                                      
+-------------------------------------------------------------------------------------
  Sort
    Sort Key: t1.c, t1.a, t2.a, t3.a
-   ->  Append
-         ->  Hash Full Join
-               Hash Cond: (t1_1.c = t3_1.c)
-               Filter: (((COALESCE(t1_1.a, 0) % 5) <> 3) AND ((COALESCE(t1_1.a, 0) % 5) <> 4))
-               ->  Hash Left Join
-                     Hash Cond: (t1_1.c = t2_1.c)
+   ->  Hash Full Join
+         Hash Cond: (t1.c = t3.c)
+         Filter: (((COALESCE(t1.a, 0) % 5) <> 3) AND ((COALESCE(t1.a, 0) % 5) <> 4))
+         ->  Hash Left Join
+               Hash Cond: (t1.c = t2.c)
+               ->  Append
                      ->  Seq Scan on plt1_adv_p1 t1_1
-                     ->  Hash
-                           ->  Seq Scan on plt2_adv_p1 t2_1
-               ->  Hash
-                     ->  Seq Scan on plt3_adv_p1 t3_1
-         ->  Hash Full Join
-               Hash Cond: (t1_2.c = t3_2.c)
-               Filter: (((COALESCE(t1_2.a, 0) % 5) <> 3) AND ((COALESCE(t1_2.a, 0) % 5) <> 4))
-               ->  Hash Left Join
-                     Hash Cond: (t1_2.c = t2_2.c)
                      ->  Seq Scan on plt1_adv_p2 t1_2
-                     ->  Hash
-                           ->  Seq Scan on plt2_adv_p2 t2_2
                ->  Hash
+                     ->  Append
+                           ->  Seq Scan on plt2_adv_p1 t2_1
+                           ->  Seq Scan on plt2_adv_p2 t2_2
+         ->  Hash
+               ->  Append
+                     ->  Seq Scan on plt3_adv_p1 t3_1
                      ->  Seq Scan on plt3_adv_p2 t3_2
-(23 rows)
+(18 rows)
 
 SELECT t1.a, t1.c, t2.a, t2.c, t3.a, t3.c FROM (plt1_adv t1 LEFT JOIN plt2_adv t2 ON (t1.c = t2.c)) FULL JOIN plt3_adv 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;
  a  |  c   | a  |  c   | a  |  c   
@@ -5240,17 +5222,15 @@ SELECT x.id, y.id FROM fract_t x LEFT JOIN fract_t y USING (id) ORDER BY x.id AS
                               QUERY PLAN                               
 -----------------------------------------------------------------------
  Limit
-   ->  Merge Append
-         Sort Key: x.id
-         ->  Merge Left Join
-               Merge Cond: (x_1.id = y_1.id)
+   ->  Merge Left Join
+         Merge Cond: (x.id = y.id)
+         ->  Append
                ->  Index Only Scan using fract_t0_pkey on fract_t0 x_1
-               ->  Index Only Scan using fract_t0_pkey on fract_t0 y_1
-         ->  Merge Left Join
-               Merge Cond: (x_2.id = y_2.id)
                ->  Index Only Scan using fract_t1_pkey on fract_t1 x_2
+         ->  Append
+               ->  Index Only Scan using fract_t0_pkey on fract_t0 y_1
                ->  Index Only Scan using fract_t1_pkey on fract_t1 y_2
-(11 rows)
+(9 rows)
 
 EXPLAIN (COSTS OFF)
 SELECT x.id, y.id FROM fract_t x LEFT JOIN fract_t y USING (id) ORDER BY x.id DESC LIMIT 10;
diff --git a/src/test/regress/expected/subselect.out b/src/test/regress/expected/subselect.out
index cf6b32d1173..8549601e3bc 100644
--- a/src/test/regress/expected/subselect.out
+++ b/src/test/regress/expected/subselect.out
@@ -850,10 +850,11 @@ where (t1.a, t2.a) in (select a, a from unique_tbl_p t3)
 order by t1.a, t2.a;
                                            QUERY PLAN                                           
 ------------------------------------------------------------------------------------------------
- Merge Append
-   Sort Key: t1.a
-   ->  Nested Loop
-         Output: t1_1.a, t1_1.b, t2_1.a, t2_1.b
+ Merge Join
+   Output: t1.a, t1.b, t2.a, t2.b
+   Merge Cond: (t1.a = t2.a)
+   ->  Merge Append
+         Sort Key: t1.a
          ->  Nested Loop
                Output: t1_1.a, t1_1.b, t3_1.a
                ->  Unique
@@ -863,15 +864,6 @@ order by t1.a, t2.a;
                ->  Index Scan using unique_tbl_p1_a_idx on public.unique_tbl_p1 t1_1
                      Output: t1_1.a, t1_1.b
                      Index Cond: (t1_1.a = t3_1.a)
-         ->  Memoize
-               Output: t2_1.a, t2_1.b
-               Cache Key: t1_1.a
-               Cache Mode: logical
-               ->  Index Scan using unique_tbl_p1_a_idx on public.unique_tbl_p1 t2_1
-                     Output: t2_1.a, t2_1.b
-                     Index Cond: (t2_1.a = t1_1.a)
-   ->  Nested Loop
-         Output: t1_2.a, t1_2.b, t2_2.a, t2_2.b
          ->  Nested Loop
                Output: t1_2.a, t1_2.b, t3_2.a
                ->  Unique
@@ -881,15 +873,6 @@ order by t1.a, t2.a;
                ->  Index Scan using unique_tbl_p2_a_idx on public.unique_tbl_p2 t1_2
                      Output: t1_2.a, t1_2.b
                      Index Cond: (t1_2.a = t3_2.a)
-         ->  Memoize
-               Output: t2_2.a, t2_2.b
-               Cache Key: t1_2.a
-               Cache Mode: logical
-               ->  Index Scan using unique_tbl_p2_a_idx on public.unique_tbl_p2 t2_2
-                     Output: t2_2.a, t2_2.b
-                     Index Cond: (t2_2.a = t1_2.a)
-   ->  Nested Loop
-         Output: t1_3.a, t1_3.b, t2_3.a, t2_3.b
          ->  Nested Loop
                Output: t1_3.a, t1_3.b, t3_3.a
                ->  Unique
@@ -902,14 +885,16 @@ order by t1.a, t2.a;
                ->  Index Scan using unique_tbl_p3_a_idx on public.unique_tbl_p3 t1_3
                      Output: t1_3.a, t1_3.b
                      Index Cond: (t1_3.a = t3_3.a)
-         ->  Memoize
-               Output: t2_3.a, t2_3.b
-               Cache Key: t1_3.a
-               Cache Mode: logical
+   ->  Materialize
+         Output: t2.a, t2.b
+         ->  Append
+               ->  Index Scan using unique_tbl_p1_a_idx on public.unique_tbl_p1 t2_1
+                     Output: t2_1.a, t2_1.b
+               ->  Index Scan using unique_tbl_p2_a_idx on public.unique_tbl_p2 t2_2
+                     Output: t2_2.a, t2_2.b
                ->  Index Scan using unique_tbl_p3_a_idx on public.unique_tbl_p3 t2_3
                      Output: t2_3.a, t2_3.b
-                     Index Cond: (t2_3.a = t1_3.a)
-(59 rows)
+(44 rows)
 
 reset enable_partitionwise_join;
 drop table unique_tbl_p;
-- 
2.51.0