Re: Query Performance Degradation Due to Partition Scan Order – PostgreSQL v17.6

On Wed, 10 Sept 2025 at 19:41, Andrei Lepikhov <lepihov(at)gmail(dot)com> wrote:
>
> On 10/9/2025 00:57, David Rowley wrote:
> > ... However, it's not all that clear to me how often someone would
> > have a LIMIT without an ORDER BY, as effectively there's nothing there
> > to determine which rows your query returns, and there's no flexibility
> > to change which subpaths are first in Append/MergeAppend paths created
> > in generate_orderedappend_paths().

> Of course, it should be applied to an Append without pathkeys.
> However, I still recall user cases where the subtree scan is stopped,
> even without any limit, simply because MergeJoin has reached the end of
> the inner/outer subtree or in the case of semi- or anti-joins. I wonder
> if other cases may exist.>

Cursors and nested loop semi joins are the only thing that come to
mind for me. I don't see how there could be a Merge Join on an
unordered Append path. Merge Join inputs need to be ordered (and have
PathKeys).

David