Re: MergeAppend could consider sorting cheapest child path

On 15/10/2025 14:35, David Rowley wrote:
> On Wed, 15 Oct 2025 at 22:26, Andrei Lepikhov <lepihov(at)gmail(dot)com> 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