Re: Inheritance question

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