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?
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.
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
