From 7a09f8629fff51727f9f59ed7bb57fcc8a452bed Mon Sep 17 00:00:00 2001 From: Ashutosh Bapat Date: Mon, 15 Apr 2024 12:03:01 +0530 Subject: [PATCH] Fix partitioned join case in apply_scanjoin_target_to_paths() apply_scanjoin_target_to_paths() assumes that the cheapest paths for all partitioned relations can be computed by Append'ing corresponding paths from the partitions. This is not true for partitioned join relations. Partitionwise join paths may not be optimal always. Hence do not wipe out all existing paths only when the relation is simple before computing paths with new target in that function. Ashutosh Bapat, initial investigation by Jacob Wartak --- src/backend/optimizer/plan/planner.c | 4 +- src/test/regress/expected/partition_join.out | 81 ++++++++++++++------ src/test/regress/sql/partition_join.sql | 14 +++- 3 files changed, 69 insertions(+), 30 deletions(-) diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.c index bd4e4ceeca..5d17c61f3b 100644 --- a/src/backend/optimizer/plan/planner.c +++ b/src/backend/optimizer/plan/planner.c @@ -7099,7 +7099,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; /* @@ -7125,7 +7125,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 8b179fa60d..639ce38c80 100644 --- a/src/test/regress/expected/partition_join.out +++ b/src/test/regress/expected/partition_join.out @@ -4721,38 +4721,35 @@ CREATE TABLE plt3_adv_p1 PARTITION OF plt3_adv FOR VALUES IN ('0001'); CREATE TABLE plt3_adv_p2 PARTITION OF plt3_adv FOR VALUES IN ('0003', '0004'); INSERT INTO plt3_adv SELECT i, i, to_char(i % 5, 'FM0000') FROM generate_series(0, 24) i WHERE i % 5 IN (1, 3, 4); ANALYZE plt3_adv; --- This tests that when merging partitions from plt1_adv and plt2_adv in --- merge_list_bounds(), process_outer_partition() returns an already-assigned --- merged partition when re-called with plt1_adv_p1 for the second list value --- '0001' of that partition +-- Both the queries test that when merging partitions from plt1_adv and +-- plt2_adv in merge_list_bounds(), process_outer_partition() returns an +-- already-assigned merged partition when re-called with plt1_adv_p1 for the +-- second list value '0001' of that partition. But the first query doesn't end +-- up using partitionwise join since it's more costly whereas the second one +-- use partitionwise join. 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 @@ -4814,6 +4811,42 @@ SELECT t1.a, t1.c, t2.a, t2.c, t3.a, t3.c FROM (plt1_adv t1 LEFT JOIN plt2_adv t 22 | 0002 | 22 | 0002 | | (55 rows) +EXPLAIN (COSTS OFF) +SELECT t1.a, t1.c, t2.a, t2.c FROM (plt1_adv t1 LEFT JOIN plt2_adv t2 ON (t1.c = t2.c)) WHERE coalesce(t1.a, t2.a) IN (3, 4) ORDER BY t1.c, t1.a, t2.a; + QUERY PLAN +----------------------------------------------------------------------------- + Sort + Sort Key: t1.c, t1.a, t2.a + -> Append + -> Hash Left Join + Hash Cond: (t1_1.c = t2_1.c) + Filter: (COALESCE(t1_1.a, t2_1.a) = ANY ('{3,4}'::integer[])) + -> Seq Scan on plt1_adv_p1 t1_1 + -> Hash + -> Seq Scan on plt2_adv_p1 t2_1 + -> Hash Left Join + Hash Cond: (t1_2.c = t2_2.c) + Filter: (COALESCE(t1_2.a, t2_2.a) = ANY ('{3,4}'::integer[])) + -> Seq Scan on plt1_adv_p2 t1_2 + -> Hash + -> Seq Scan on plt2_adv_p2 t2_2 +(15 rows) + +SELECT t1.a, t1.c, t2.a, t2.c FROM (plt1_adv t1 LEFT JOIN plt2_adv t2 ON (t1.c = t2.c)) WHERE coalesce(t1.a, t2.a) IN (3, 4) ORDER BY t1.c, t1.a, t2.a; + a | c | a | c +---+------+----+------ + 3 | 0003 | 3 | 0003 + 3 | 0003 | 8 | 0003 + 3 | 0003 | 13 | 0003 + 3 | 0003 | 18 | 0003 + 3 | 0003 | 23 | 0003 + 4 | 0004 | 4 | 0004 + 4 | 0004 | 9 | 0004 + 4 | 0004 | 14 | 0004 + 4 | 0004 | 19 | 0004 + 4 | 0004 | 24 | 0004 +(10 rows) + DROP TABLE plt1_adv; DROP TABLE plt2_adv; DROP TABLE plt3_adv; diff --git a/src/test/regress/sql/partition_join.sql b/src/test/regress/sql/partition_join.sql index 04d7adb8e5..bf3a139ab2 100644 --- a/src/test/regress/sql/partition_join.sql +++ b/src/test/regress/sql/partition_join.sql @@ -1135,14 +1135,20 @@ CREATE TABLE plt3_adv_p2 PARTITION OF plt3_adv FOR VALUES IN ('0003', '0004'); INSERT INTO plt3_adv SELECT i, i, to_char(i % 5, 'FM0000') FROM generate_series(0, 24) i WHERE i % 5 IN (1, 3, 4); ANALYZE plt3_adv; --- This tests that when merging partitions from plt1_adv and plt2_adv in --- merge_list_bounds(), process_outer_partition() returns an already-assigned --- merged partition when re-called with plt1_adv_p1 for the second list value --- '0001' of that partition +-- Both the queries test that when merging partitions from plt1_adv and +-- plt2_adv in merge_list_bounds(), process_outer_partition() returns an +-- already-assigned merged partition when re-called with plt1_adv_p1 for the +-- second list value '0001' of that partition. But the first query doesn't end +-- up using partitionwise join since it's more costly whereas the second one +-- use partitionwise join. 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; 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; +EXPLAIN (COSTS OFF) +SELECT t1.a, t1.c, t2.a, t2.c FROM (plt1_adv t1 LEFT JOIN plt2_adv t2 ON (t1.c = t2.c)) WHERE coalesce(t1.a, t2.a) IN (3, 4) ORDER BY t1.c, t1.a, t2.a; +SELECT t1.a, t1.c, t2.a, t2.c FROM (plt1_adv t1 LEFT JOIN plt2_adv t2 ON (t1.c = t2.c)) WHERE coalesce(t1.a, t2.a) IN (3, 4) ORDER BY t1.c, t1.a, t2.a; + DROP TABLE plt1_adv; DROP TABLE plt2_adv; DROP TABLE plt3_adv; -- 2.34.1