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

On 8/9/2025 11:47, Ashutosh Bapat wrote:
> On Mon, Sep 8, 2025 at 4:01 AM Vivek Gadge <vvkgadge56@gmail.com> wrote:
>> Looking forward to your guidance.
>>
>> Thank you
>>
> 
> Can you please describe how the query performance is affected because
> of the order in which partitions are scanned?
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?

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.

-- 
regards, Andrei Lepikhov