Обсуждение: Query Performance Degradation Due to Partition Scan Order – PostgreSQL v17.6

On Mon, Sep 8, 2025 at 4:01 AM Vivek Gadge <vvkgadge56@gmail.com> wrote:
>
> Hi Team,
>
> We are currently experiencing performance issues related to partition scanning on a heavily used table in our
PostgreSQLv17.6 database. 
>
> The table is partitioned monthly (e.g., transactions_jan25, transactions_feb25, …, transactions_sept25). We’ve
observedthat PostgreSQL scans these partitions in the order they were attached (January through September). 
>
> This behavior is leading to inefficient query performance when accessing recent data (e.g., August or September), as
olderpartitions are being scanned first — adding unnecessary overhead. Since PostgreSQL does not provide a built-in
settingto prioritize scanning partitions in descending order, we’re exploring ways to address this. 
>
> Could you please advise on:
>
> How to optimize partition scanning so that recent partitions are scanned first, and
>
> Any recommended best practices or workarounds to improve performance in such scenarios.
>
> 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?

--
Best Wishes,
Ashutosh Bapat

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

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

On 8/9/2025 13:39, Vivek Gadge wrote:
> 
> For example, when a query runs on a partitioned table, PostgreSQL scans 
> partitions in the order they were created or attached to the parent 
> table. In our case (monthly partitions from January through September), 
> this means that queries looking for recent data (e.g., September) may 
> experience additional overhead. PostgreSQL evaluates the older 
> partitions first, checking their constraints and in some cases probing 
> their indexes, before reaching the later partitions that actually 
> contain the needed data.
> 
> As a result, while the query results are correct, the execution time 
> increases due to unnecessary work on irrelevant partitions. This 
> performance impact is more noticeable when the target partition is at 
> the end of the scan order and pruning cannot fully eliminate the earlier 
> partitions.
The case looks straightforward. But just to be sure that we are on the 
same page, could you provide a synthetic DB example and a query 
representing the exact problem you are going to resolve?

-- 
regards, Andrei Lepikhov

On Mon, Sep 8, 2025 at 5:09 PM Vivek Gadge <vvkgadge56@gmail.com> wrote:
>
>
> For example, when a query runs on a partitioned table, PostgreSQL scans partitions in the order they were created or
attachedto the parent table. In our case (monthly partitions from January through September), this means that queries
lookingfor recent data (e.g., September) may experience additional overhead. PostgreSQL evaluates the older partitions
first,checking their constraints and in some cases probing their indexes, before reaching the later partitions that
actuallycontain the needed data. 
>
> As a result, while the query results are correct, the execution time increases due to unnecessary work on irrelevant
partitions.This performance impact is more noticeable when the target partition is at the end of the scan order and
pruningcannot fully eliminate the earlier partitions. 
>

If you don't want data from certain partitions maybe you should add a
clause that will help partition pruning. If you need data from all
partitions, the order in which they are scanned doesn't matter, those
will be scanned either way.

If partitioning pruning isn't working for you, please report the exact
query. Please provide example queries anyway.

--
Best Wishes,
Ashutosh Bapat

On 8/9/2025 12:32, Adrien Nayrat wrote:
> 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:
> It reminds me of these threads :
Yes, partially. Actively using foreign tables and touching cheaper 
partitions first is crucial in queries with limits. Also, some cases may 
provide an implicit limit, like a merge join.

-- 
regards, Andrei Lepikhov

On Wed, 10 Sept 2025 at 09:32, Vivek Gadge <vvkgadge56@gmail.com> wrote:
> EXPLAIN ANALYZE VERBOSE
> SELECT m.txn_date, d.bank_ref
> FROM app.main m
> JOIN app.detail d ON m.txn_id = d.main_txn_id
> WHERE m.txn_id = 9999999999999999999;
>
>
> Both app.main and app.detail are range partitioned by month (e.g., main_202502, detail_202502, etc.) on a date
column,
>
> Attached below is the EXPLAIN ANALYZE VERBOSE output for the query. It shows that PostgreSQL is scanning all
partitionsin ascending order, even though the matching record is present in the main_202509 partition.
 

I think you might be under the false impression that the executor is
able to stop searching for the next row to join once it finds the
first matching row.  If that were the case, then yes, it might be
faster to somehow scan the partition where the matching row exists
first as then you could forego scanning the remainder.   PostgreSQL
does have some ability to do some of this with the "Unique Join"
functionality, but it does not apply to this case since there's no
proof that only a single row can match (this would require some sort
of Unique Index, which cannot exist in your case since we don't have
global indexes that exist over all partitions).

If you're able to patch PostgreSQL and recompile, try the attached
patch away from your production environment to check if there are any
gains to scanning the partitions in another order. I didn't bother to
change all the append paths that were generated, but I think I've
reversed the order of at least the one the planner is using in your
case.

It's probably true that there are some cases (when the row order does
not matter) where scanning partitions that are more present in shared
buffers first would be better as that means using buffers perhaps
before you evict them to make way for the buffers of some other
(normally unused) partition, but I don't believe buffer eviction comes
into effect for your case since you're basically just scanning an
index which doesn't contain any value for the search key. That
requires very few buffers.

A secondary thought here is that perhaps your partitioning strategy
needs revision. What's the reason you opted to partition by date? Does
that benefit some other queries better? Or is it just a case that it
works well for some data retention policy? IMO, partitioning by range
of the transaction ID would fit much better for this particular query.

David

On Mon, 8 Sept 2025 at 22:05, Andrei Lepikhov <lepihov@gmail.com> wrote:
> 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?

This seems quite separate from what's being complained about here. It
might be beneficial to reconsider whether we should do some sort of
sorting on startup_subpaths inside add_paths_to_append_rel(). I
imagine that it might make some sense to sort that list so the path
with the cheapest startup cost is first, then put the remainder of the
list in order of cheapest total cost per tuple. I suspect that would
result in Foreign partitions being scanned last...

... 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().

> 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?

David

On Wed, Sep 10, 2025 at 4:27 AM David Rowley <dgrowleyml@gmail.com> wrote:
>
> On Mon, 8 Sept 2025 at 22:05, Andrei Lepikhov <lepihov@gmail.com> wrote:
> > 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?
>
> This seems quite separate from what's being complained about here. It
> might be beneficial to reconsider whether we should do some sort of
> sorting on startup_subpaths inside add_paths_to_append_rel(). I
> imagine that it might make some sense to sort that list so the path
> with the cheapest startup cost is first, then put the remainder of the
> list in order of cheapest total cost per tuple. I suspect that would
> result in Foreign partitions being scanned last...

If there's LIMIT without ORDER BY, we could order the list of subpaths
by the number of rows in descending order or cost per row in ascending
order. That way there are more chances of scanning fewer partitions
quicker.

--
Best Wishes,
Ashutosh Bapat

On Wed, 10 Sept 2025 at 16:26, Ashutosh Bapat
<ashutosh.bapat.oss@gmail.com> wrote:
>
> On Wed, Sep 10, 2025 at 4:27 AM David Rowley <dgrowleyml@gmail.com> wrote:
> > This seems quite separate from what's being complained about here. It
> > might be beneficial to reconsider whether we should do some sort of
> > sorting on startup_subpaths inside add_paths_to_append_rel(). I
> > imagine that it might make some sense to sort that list so the path
> > with the cheapest startup cost is first, then put the remainder of the
> > list in order of cheapest total cost per tuple. I suspect that would
> > result in Foreign partitions being scanned last...
>
> If there's LIMIT without ORDER BY, we could order the list of subpaths
> by the number of rows in descending order or cost per row in ascending
> order. That way there are more chances of scanning fewer partitions
> quicker.

Wouldn't that amount to favouring scanning some large foreign
partition over a smaller local partition? My interpretation of
Andrei's "Prefer scanning local partitions to foreign ones" statement
is that was what we shouldn't be doing!

David

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

On Wed, Sep 10, 2025 at 11:50 AM David Rowley <dgrowleyml@gmail.com> wrote:
>
> On Wed, 10 Sept 2025 at 16:26, Ashutosh Bapat
> <ashutosh.bapat.oss@gmail.com> wrote:
> >
> > On Wed, Sep 10, 2025 at 4:27 AM David Rowley <dgrowleyml@gmail.com> wrote:
> > > This seems quite separate from what's being complained about here. It
> > > might be beneficial to reconsider whether we should do some sort of
> > > sorting on startup_subpaths inside add_paths_to_append_rel(). I
> > > imagine that it might make some sense to sort that list so the path
> > > with the cheapest startup cost is first, then put the remainder of the
> > > list in order of cheapest total cost per tuple. I suspect that would
> > > result in Foreign partitions being scanned last...
> >
> > If there's LIMIT without ORDER BY, we could order the list of subpaths
> > by the number of rows in descending order or cost per row in ascending
> > order. That way there are more chances of scanning fewer partitions
> > quicker.
>
> Wouldn't that amount to favouring scanning some large foreign
> partition over a smaller local partition? My interpretation of
> Andrei's "Prefer scanning local partitions to foreign ones" statement
> is that was what we shouldn't be doing!

Generally foreign scans will have a higher cost, including startup
cost. So subpaths with local scans will be preferred. But in case
there's a foreign subpath with a lower cost than local subpath, I
think foreign scan should be preferred.

--
Best Wishes,
Ashutosh Bapat

On Wed, 10 Sept 2025 at 19:41, Andrei Lepikhov <lepihov@gmail.com> wrote:
>
> On 10/9/2025 00:57, David Rowley wrote:
> > ... 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.>

Cursors and nested loop semi joins are the only thing that come to
mind for me.  I don't see how there could be a Merge Join on an
unordered Append path. Merge Join inputs need to be ordered (and have
PathKeys).

David

On Wed, 10 Sept 2025 at 21:18, Ashutosh Bapat
<ashutosh.bapat.oss@gmail.com> wrote:
> > > If there's LIMIT without ORDER BY, we could order the list of subpaths
> > > by the number of rows in descending order or cost per row in ascending
> > > order. That way there are more chances of scanning fewer partitions
> > > quicker.
> >
> > Wouldn't that amount to favouring scanning some large foreign
> > partition over a smaller local partition? My interpretation of
> > Andrei's "Prefer scanning local partitions to foreign ones" statement
> > is that was what we shouldn't be doing!
>
> Generally foreign scans will have a higher cost, including startup
> cost. So subpaths with local scans will be preferred. But in case
> there's a foreign subpath with a lower cost than local subpath, I
> think foreign scan should be preferred.

I'm still stuck on why to you'd want to sort on the number of rows in
descending order. What does that have to do with foreign scans?

Otherwise, if the foreign scan comes out cheaper per row, then doing
those first sounds ok to me. It's all about cost per row in my view.
Foreign or local scan has no relevance to what I was proposing.

David

On Mon, Sep 15, 2025 at 3:19 AM David Rowley <dgrowleyml@gmail.com> wrote:
>
> On Wed, 10 Sept 2025 at 21:18, Ashutosh Bapat
> <ashutosh.bapat.oss@gmail.com> wrote:
> > > > If there's LIMIT without ORDER BY, we could order the list of subpaths
> > > > by the number of rows in descending order or cost per row in ascending
> > > > order. That way there are more chances of scanning fewer partitions
> > > > quicker.
> > >
> > > Wouldn't that amount to favouring scanning some large foreign
> > > partition over a smaller local partition? My interpretation of
> > > Andrei's "Prefer scanning local partitions to foreign ones" statement
> > > is that was what we shouldn't be doing!
> >
> > Generally foreign scans will have a higher cost, including startup
> > cost. So subpaths with local scans will be preferred. But in case
> > there's a foreign subpath with a lower cost than local subpath, I
> > think foreign scan should be preferred.
>
> I'm still stuck on why to you'd want to sort on the number of rows in
> descending order. What does that have to do with foreign scans?
>
> Otherwise, if the foreign scan comes out cheaper per row, then doing
> those first sounds ok to me. It's all about cost per row in my view.
> Foreign or local scan has no relevance to what I was proposing.

I agree that cost per row is a better parameter to order the subpaths.
I am fine if we go ahead with that.

Descending ordering by number rows definitely assures there are fewer
subplans to execute, so saving any startup and initial work on rest of
the subpaths, if there are many. If we order by cost per row, we may
end up starting and cleaning many cheaper subpaths which may take
longer in practice compared to starting and cleaning up fewer
subpaths.

--
Best Wishes,
Ashutosh Bapat