Thread

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

Andrei Lepikhov <lepihov@gmail.com> — 2025-09-08T13:27:27Z

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