Re: MergeAppend could consider sorting cheapest child path

On Wed, 15 Oct 2025 at 19:45, Richard Guo <guofenglinux@gmail.com> wrote:
> I also noticed the hack you added to avoid using MergeAppend+Sort when
> none of the chosen subpaths are ordered.  It seems to me that this
> contradicts the idea of this patch.  If MergeAppend+Sort is indeed a
> better plan, why wouldn't it apply in cases where no chosen subpaths
> are ordered?

FWIW, I've not really followed this closely, but from the parts I have
read it seems the patch could cause a Sort -> unsorted path to be used
over a path that's already correctly sorted. This reminds me of a
patch I proposed in [1] and then subsequently decided it was a bad
idea in [2] because of concerns of having too many Sorts in a single
plan. Sort only calls tuplesort_end() at executor shutdown, so that
means possibly using up to work_mem per sort node. If you have 1000x
Sort nodes, then that's up to 1000x work_mem. Since the planner
doesn't have any abilities to consider the overall memory consumption,
I thought it was a bad idea due to increased OOM risk. If I'm not
mistaken it looks like this could suffer from the same problem.

David

[1] https://postgr.es/m/CAApHDvojKdBR3MR59JXmaCYbyHB6Q_5qPRU%2Bdy93En8wm%2BXiDA%40mail.gmail.com
[2] https://postgr.es/m/CAApHDvohAZLQSW4AiHUKmLGNuHYbi0pves%2B9_9ik3cAYevc2GQ%40mail.gmail.com