On Jan 16, 2009, at 9:49 AM, Glyn Astill wrote:
Hi chaps,
I've got a question about inheritance here, and I think I may have
gotten the wrong end of the stick as to how it works, or at least
when to use it.
What I intended to do was have a schema "audit" with an empty set of
tables in it, then each quarter restore our audit data into schemas
such as "audit_Q1_2009" etc. Then alter the tables in the
audit_Q1_2009 schema to inherit the audit schema, etc and so on for
audit_Q2_2009.
This appears to work so the audit schema appears as if it contains
everything in the other schemas.
However this isn't very efficient as soon as I try to order the
data, even with only one table getting inherited it does a sort
rather than using the index on the child table.
Is this because the inheritance works like a view, and it basically
has to build the view before ordering it?
Pretty much. Inheritance works essentially like UNION ALL and for
UNION queries the sort doesn't happen until the entire result set has
been processed and even with processing on child table you're really
processing 2 tables, the child and parent. Think about it like this,
for any given index that you may order by on the child tables there's
no guarantee that you could do the same with all of the child tables,
append the results and still have a correctly ordered result set.
*We* may know that the tables have a logical ordering such that when
processed in a certain order, sorting each along the way, the results
from each child could be appended and maintain ordering of the results
but the planner has no idea of anything like that.
Erik Jones, Database Administrator
Engine Yard
Support, Scalability, Reliability
866.518.9273 x 260
Location: US/Pacific
IRC: mage2k
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
