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

On 9/8/25 12:05 PM, Andrei Lepikhov wrote:
> 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.
> 

It reminds me of these threads :


Make the optimiser aware of partitions ordering : 
https://www.postgresql.org/message-id/2401607.SfZhPQhbS4@ronan_laptop

Allow ordered partition scans in more cases :
https://www.postgresql.org/message-id/CAApHDvojKdBR3MR59JXmaCYbyHB6Q_5qPRU+dy93En8wm+XiDA@mail.gmail.com

Ordered Partitioned Table Scans :
https://www.postgresql.org/message-id/CAKJS1f-hAqhPLRk_RaSFTgYxd=Tz5hA7kQ2h4-DhJufQk8TGuw@mail.gmail.com

Regards

-- 
Adrien NAYRAT