On 15/10/2025 14:35, David Rowley wrote:
On Wed, 15 Oct 2025 at 22:26, Andrei Lepikhov <[email protected]> wrote:
Or is this a case of that you want to also consider Seq Scan on hp0 ->
Sort if it's cheaper than Index Scan on hp0_a_idx just in case that's
enough to make Merge Append cheap enough to beat Append -> Sort?
I spent some time reviewing original user complaints. However, after
switching employers, I no longer have direct access to the reports :(((
- it was the main benefit of working for the company, which has massive
migrations from Oracle and SQL Server.
I recall the problem raised with multiple foreign partitions, where
MergeAppend by X is a preferable strategy (due to the need for ORDER BY
X, or MergeJoin, etc). For some partitions, IndexScan(X) fetches too
many tuples from disk. In this case, IndexScan(Y) + Sort (X) drastically
improves the situation. That's why we proposed to look into the
cheaper_total path + sort, not only the path that fits pathkeys.
Additionally, this patch revealed an issue with the cost model: there is
no significant difference between a single massive Sort and multiple
sorts followed by MergeAppend. Our experiments show that it is incorrect
(one Sort operator demonstrates more efficacy) and may be corrected.
Do you mean "no significant difference [in the costings] between"?
Yes>
Not sure if I follow you here. You've said "one Sort operator
demonstrates more efficacy", do you mean Sort atop of Append is
better? If so, why does the patch try to encourage plans with Merge
Append with many Sorts?
Sort-Append definitely better than MergeAppend-IndexScan.
This patch just reveals the issue that current cost model doesn't differ
these two strategies. In the corner case it triggers a suboptimal plan.
--
regards, Andrei Lepikhov,
pgEdge
