Re: Partitioned Tables and ORDER BY

Joe Uhl wrote:
> This seems like a pretty major weakness in PostgreSQL partitioning. I
> have essentially settled on not being able to do queries against the
> parent table when I want to order the results. Going to have to use a
> Hibernate interceptor or something similar to rewrite the statements so
> they hit specific partitions, will be working on this in the coming week.
>
> This weakness is a bummer though as it makes partitions a lot less
> useful. Having to hit specific child tables by name isn't much
> different than just creating separate tables and not using partitions at
> all.

I wonder if the "offset 0" trick would work here? I was told (for a different question) that the planner can't merge levels if there's an offset or limit on a subquery. So you might be able to do something like this:

select ... from (select ... offset 0) as foo order by ...

In other words, put your primary query as a sub-select without the sort criterion, with the "offset 0" as a sort of roadblock that the planner can't get past. Then the outer select does the sorting, without affecting the plan for the inner select.

Craig