On Mon, Oct 19, 2009 at 6:58 AM, Joe Uhl <joeuhl@xxxxxxxxx> 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
This definitely sounds like a workable approach. I am doing something
a little similar on the insert/update side to trick hibernate into
writing data correctly into partitioned tables when it only knows about
the parent.
For anyone else hitting this issue and using hibernate my solution on
the select side ended up being session-specific hibernate interceptors
that rewrite the from clause after hibernate prepares the statement.
This seems to be working alright especially since in our case the code,
while not aware of DB partitioning, has the context necessary to select
the right partition under the hood.
Thankfully we haven't yet had queries that need to hit multiple
partitions so this works okay without too much logic for now. I
suppose if I needed to go multi-partition on single queries and wanted
to continue down the hibernate interceptor path I could get more
creative with the from clause rewriting and start using UNIONs, or
switch to a Postgres-level solution like you are describing.
|
