Thread: Inheritance question

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
datainto 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
doesa 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?

For example in audit_Q1_2009 the table at_price has an index on trigger_id

SEE=# explain select * from audit.at_price order by trigger_id limit 100;
                                         QUERY PLAN
--------------------------------------------------------------------------------------------
 Limit  (cost=100095726.71..100095726.96 rows=100 width=820)
   ->  Sort  (cost=100095726.71..100098424.83 rows=1079251 width=820)
         Sort Key: audit.at_price.trigger_id
         ->  Result  (cost=0.00..54478.51 rows=1079251 width=820)
               ->  Append  (cost=0.00..54478.51 rows=1079251 width=820)
                     ->  Seq Scan on at_price  (cost=0.00..10.90 rows=90 width=820)
                     ->  Seq Scan on at_price  (cost=0.00..54467.61 rows=1079161 width=280)


SEE=# explain select * from "audit_Q1_2009".at_price order by trigger_id limit 100;
                                           QUERY PLAN
------------------------------------------------------------------------------------------------
 Limit  (cost=0.00..7.37 rows=100 width=280)
   ->  Index Scan using at_price_pkey on at_price  (cost=0.00..79537.33 rows=1079161 width=280)
(2 rows)


Any suggestions would be appreciated.

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