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

On 8/9/2025 13:39, Vivek Gadge wrote:
>
> 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.
The case looks straightforward. But just to be sure that we are on the
same page, could you provide a synthetic DB example and a query
representing the exact problem you are going to resolve?

--
regards, Andrei Lepikhov