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

On 10/9/2025 00:57, David Rowley wrote:
>> 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.
Maybe. I didn't see the reproduction script, which made it hard for me
to understand the origin of the problem clearly.>
> ... 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.>
>> 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?I have no specific case for now. From the top of my mind, it may help to
prevent flushing out some partitions from the shared buffers...

--
regards, Andrei Lepikhov