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