On Mon, Oct 19, 2009 at 6:58 AM, Joe Uhl <joeuhl@gmail.com> wrote:
> I have a similar, recent thread titled Partitioned Tables and ORDER BY with
> a decent break down. I think I am hitting the same issue Michal is.
>
> Essentially doing a SELECT against the parent with appropriate constraint
> columns in the WHERE clause is very fast (uses index scans against correct
> child table only) but the moment you add an ORDER BY it seems to be merging
> the parent (an empty table) and the child, sorting the results, and
> sequential scanning. So it does still scan only the appropriate child table
> in the end but indexes are useless.
>
> Unfortunately the only workaround I can come up with is to query the
> partitioned child tables directly. In my case the partitions are rather
> large so the timing difference is 522ms versus 149865ms.
These questions are all solvable depending on what you define
'solution' as. I would at this point be thinking in terms of wrapping
the query in a function using dynamic sql in plpgsql...using some ad
hoc method of determining which children to hit and awkwardly looping
them and enforcing limit, ordering, etc at that level. Yes, it sucks,
but it only has to be done for classes of queries constraint exclusion
can't handle and you will only handle a couple of cases most likely.
For this reason, when I set up my partitioning strategies, I always
try to divide the data such that you rarely if ever, have to fire
queries that have to touch multiple partitions simultaneously.
merlin