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

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.

Thanks.

On Mon, 8 Sept, 2025, 3:17 pm Ashutosh Bapat, <ashutosh(dot)bapat(dot)oss(at)gmail(dot)com>
wrote:

> On Mon, Sep 8, 2025 at 4:01 AM Vivek Gadge <vvkgadge56(at)gmail(dot)com> wrote:
> >
> > Hi Team,
> >
> > We are currently experiencing performance issues related to partition
> scanning on a heavily used table in our PostgreSQL v17.6 database.
> >
> > The table is partitioned monthly (e.g., transactions_jan25,
> transactions_feb25, …, transactions_sept25). We’ve observed that 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 older partitions are being
> scanned first — adding unnecessary overhead. Since PostgreSQL does not
> provide a built-in setting to 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
>