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

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