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

On Mon, 8 Sept 2025 at 22:05, Andrei Lepikhov <lepihov(at)gmail(dot)com> wrote:
> I guess they mentioned that the Postgres optimiser doesn't care about
> the order of Append's subplans. It is a little sad in some cases. The
> most critical case is when we have a limitation on the number of tuples
> returned. In this case, the optimiser could consider the following
> strategies:
> 1. Prefer scanning local partitions to foreign ones.
> 2. Pick first partitions with less startup costs and 'high probability'
> to obtain all necessary tuples from a minimum set of partitions.
>
> Postgres arranges clauses inside a long expression according to
> evaluation cost (see order_qual_clauses). So, why not do similar stuff
> for subplans?

This seems quite separate from what's being complained about here. It
might be beneficial to reconsider whether we should do some sort of
sorting on startup_subpaths inside add_paths_to_append_rel(). I
imagine that it might make some sense to sort that list so the path
with the cheapest startup cost is first, then put the remainder of the
list in order of cheapest total cost per tuple. I suspect that would
result in Foreign partitions being scanned last...

... 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().

> Also, I wonder if it would make sense to shuffle partitions a little and
> let backends scan partitions one-by-one in different orders just to
> reduce any sort of contention in case the queries don't fit the
> partitioning expression.

I don't follow this part. Are you proposing we randomise subpath list
order? What contention do you aim to fix?

David