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 <[email protected]> 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+x...@mail.gmail.com
Ordered Partitioned Table Scans :
https://www.postgresql.org/message-id/CAKJS1f-hAqhPLRk_RaSFTgYxd=tz5ha7kq2h4-dhjufqk8t...@mail.gmail.com
Regards
--
Adrien NAYRAT
