From 4acb0e8fadbc74f691aa6becea727dc197cca068 Mon Sep 17 00:00:00 2001 From: Robert Haas Date: Wed, 29 Oct 2025 15:17:46 -0400 Subject: [PATCH] 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 eb62794aec..8b1ab847f3 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 713828be33..3e34f05ba6 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 cf6b32d117..8549601e3b 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.39.5