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