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, <[email protected]> wrote: > On Mon, Sep 8, 2025 at 4:01 AM Vivek Gadge <[email protected]> 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 >
