Unfortunately the current PostgreSQL optimizer/executor doesn't do as much as it could when joining records from subtables partitioned with inherits clauses.
If you have a query with a predicate that can be pushed down to a subtable index then you're probably ok. However if you are doing something more intensive like an unqualified aggregation then you'll be in for a disappointment since the executor doesn't know how to preserve ordering when it scans the subtables. It will always give you a hash join or a merge join involving an intermediate sort even when the sort could be avoided by using an index scan of the subtables with something like a priority queue.
This can be worked around by doing your own incremental aggregations over the subtables and combining them, but it's a little bit of a pain.
Comments
I've always been a fan of PostgreSQL. The one bit that appealed to me was its erstwhile ability to 'shard', or partition data across tables.
http://enfranchisedmind.com/blog/2006/11/04/postgres-for-the...
Any comments?
Unfortunately the current PostgreSQL optimizer/executor doesn't do as much as it could when joining records from subtables partitioned with inherits clauses.
If you have a query with a predicate that can be pushed down to a subtable index then you're probably ok. However if you are doing something more intensive like an unqualified aggregation then you'll be in for a disappointment since the executor doesn't know how to preserve ordering when it scans the subtables. It will always give you a hash join or a merge join involving an intermediate sort even when the sort could be avoided by using an index scan of the subtables with something like a priority queue.
This can be worked around by doing your own incremental aggregations over the subtables and combining them, but it's a little bit of a pain.
If you haven't seen it yet, check this presentation about partitioning with PostgreSQL:
http://images.omniti.net/omniti.com/talks/partitions-public....