From 3d3a81fc24b07f04df81d898f6b4e85c553ef604 Mon Sep 17 00:00:00 2001 From: Alexander Korotkov Date: Sun, 18 May 2025 23:49:50 +0300 Subject: [PATCH] Fix tuple_fraction calculation in generate_orderedappend_paths() 6b94e7a6da adjusted generate_orderedappend_paths() to consider fractional paths. However, it didn't manage to interpret the tuple_fraction value correctly. According to the header comment of grouping_planner(), the tuple_fraction >= 1 specifies the absolute number of expected tuples. That number must be divided by the expected total number of tuples to get the actual fraction. Even though this is a bug fix, we don't backpatch it. The risks of the side effects of plan changes on stable branches are too high. Reported-by: Andrei Lepikhov Discussion: https://postgr.es/m/3ca271fa-ca5c-458c-8934-eb148622b270%40gmail.com Author: Andrei Lepikhov Reviewed-by: Junwang Zhao Reviewed-by: Alvaro Herrera Reviewed-by: Tom Lane --- src/backend/optimizer/path/allpaths.c | 12 +++++++++++- src/test/regress/expected/partition_join.out | 18 ++++++++++++++++++ src/test/regress/sql/partition_join.sql | 3 +++ 3 files changed, 32 insertions(+), 1 deletion(-) diff --git a/src/backend/optimizer/path/allpaths.c b/src/backend/optimizer/path/allpaths.c index 905250b3325..6cc6966b060 100644 --- a/src/backend/optimizer/path/allpaths.c +++ b/src/backend/optimizer/path/allpaths.c @@ -1891,7 +1891,17 @@ generate_orderedappend_paths(PlannerInfo *root, RelOptInfo *rel, */ if (root->tuple_fraction > 0) { - double path_fraction = (1.0 / root->tuple_fraction); + double path_fraction = root->tuple_fraction; + + /* + * Merge Append considers only live children relations. Dummy + * relations must be filtered out before. + */ + Assert(childrel->rows > 0); + + /* Convert absolute limit to a path fraction */ + if (path_fraction >= 1.0) + path_fraction /= childrel->rows; cheapest_fractional = get_cheapest_fractional_path_for_pathkeys(childrel->pathlist, diff --git a/src/test/regress/expected/partition_join.out b/src/test/regress/expected/partition_join.out index 6101c8c7cf1..d5368186caa 100644 --- a/src/test/regress/expected/partition_join.out +++ b/src/test/regress/expected/partition_join.out @@ -5260,6 +5260,24 @@ SELECT x.id, y.id FROM fract_t x LEFT JOIN fract_t y USING (id) ORDER BY x.id DE Index Cond: (id = x_2.id) (11 rows) +EXPLAIN (COSTS OFF) -- Should use NestLoop with parameterised inner scan +SELECT x.id, y.id FROM fract_t x LEFT JOIN fract_t y USING (id) +ORDER BY x.id DESC LIMIT 2; + QUERY PLAN +-------------------------------------------------------------------------------- + Limit + -> Merge Append + Sort Key: x.id DESC + -> Nested Loop Left Join + -> Index Only Scan Backward using fract_t0_pkey on fract_t0 x_1 + -> Index Only Scan using fract_t0_pkey on fract_t0 y_1 + Index Cond: (id = x_1.id) + -> Nested Loop Left Join + -> Index Only Scan Backward using fract_t1_pkey on fract_t1 x_2 + -> Index Only Scan using fract_t1_pkey on fract_t1 y_2 + Index Cond: (id = x_2.id) +(11 rows) + -- -- Test Append's fractional paths -- diff --git a/src/test/regress/sql/partition_join.sql b/src/test/regress/sql/partition_join.sql index b76c5451001..30f15ee9acb 100644 --- a/src/test/regress/sql/partition_join.sql +++ b/src/test/regress/sql/partition_join.sql @@ -1224,6 +1224,9 @@ SELECT x.id, y.id FROM fract_t x LEFT JOIN fract_t y USING (id) ORDER BY x.id AS 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; +EXPLAIN (COSTS OFF) -- Should use NestLoop with parameterised inner scan +SELECT x.id, y.id FROM fract_t x LEFT JOIN fract_t y USING (id) +ORDER BY x.id DESC LIMIT 2; -- -- Test Append's fractional paths -- 2.30.2