Incorrect calculation of path fraction value in MergeAppend
Andrei Lepikhov <lepihov@gmail.com>
From: Andrei Lepikhov <lepihov@gmail.com>
To: "pgsql-bugs@lists.postgresql.org" <pgsql-bugs@lists.postgresql.org>,
Tomas Vondra <tomas@vondra.me>
Date: 2025-04-29T11:14:39Z
Lists: pgsql-bugs
Commits
Same data as JSON:
GET /api/v1/messages/:b64id/commits
the thread's linked commits as JSON, with link sources.
API reference →
-
Fix tuple_fraction calculation in generate_orderedappend_paths()
- 3d3a81fc24b0 18.0 landed
-
Consider fractional paths in generate_orderedappend_paths
- 6b94e7a6da2f 15.0 cited
Attachments
- v0-0001-Fix-tuple_fraction-calculation-for-merge-append-f.patch (text/x-patch) patch v0-0001
Hi, Commit 6b94e7a has added a "fractional" strategy to the merge join path search scope. But as I see it incorrectly calculates tuple_fraction value. Let's see one of the regression tests' queries: 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; It uses NestedLoop with parameterised inner scan as a subplan of MergeAppend. Overall cost is: Limit (cost=1.11..4.86 rows=10 width=16) Let's reduce the LIMIT a little: 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 2; There, the query plan uses plain NestLoop node and overall cost significantly increases: Limit (cost=0.56..30.70 rows=2 width=16) The origin problem lies in the following code line: path_fraction = (1.0 / root->tuple_fraction); Building startup, total and fractional MergeAppends, the optimiser puts off the 'fractional' way because it has built for a 50% limit (uses MergeJoin with high startup cost) and chooses a startup-optimal path (built upon plain NestLoops) that is better than the total one. Instead, the path_fraction value must be corrected when root->tuple_fraction contains the absolute value of tuples. See the patch in the attachment. I report this issue into the bugs mailing list because it might cause some 'enigmatic' performance slowdowns I have heard about (but have no proofs) and may need back-patching. -- regards, Andrei Lepikhov