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.