| From: | Andrei Lepikhov <lepihov(at)gmail(dot)com> |
|---|---|
| To: | Ashutosh Bapat <ashutosh(dot)bapat(dot)oss(at)gmail(dot)com>, Vivek Gadge <vvkgadge56(at)gmail(dot)com> |
| Cc: | pgsql-hackers(at)lists(dot)postgresql(dot)org |
| Subject: | Re: Query Performance Degradation Due to Partition Scan Order – PostgreSQL v17.6 |
| Date: | 2025-09-08 10:05:03 |
| Message-ID: | 404fa7e8-6490-4aff-8f2a-2d15a13433e0@gmail.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-hackers |
On 8/9/2025 11:47, Ashutosh Bapat wrote:
> On Mon, Sep 8, 2025 at 4:01 AM Vivek Gadge <vvkgadge56(at)gmail(dot)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
| From | Date | Subject | |
|---|---|---|---|
| Next Message | David Geier | 2025-09-08 10:08:59 | Re: Use merge-based matching for MCVs in eqjoinsel |
| Previous Message | Richard Guo | 2025-09-08 09:51:33 | Re: plan shape work |