inherits index
I have a parent table that is inherited by a couple others... Would it be best to set up an index on the children tables column that is inherited by the parent, or should I index the parent column that is inherited by the children? The children tables are the ones called frequently, not the parent. I just want to help the planner to use the most effective way by setting it up properly for parsing, especially if these tables get really big.
Thanks, Matt
---------------------------------
Yahoo! Photos
Ring in the New Year with Photo Calendars. Add photos, events, holidays, whatever.
My understanding is that you will need to set up the indexes on the child
table in order to do any good for queries on the child table. Also, you have
foreign key (an other, like primary key, I think) constraints are not
inherited and have to be repeated on each table.
Think of it like this: table inheritance in PostgreSQL is not much of a
convenience for defining your schema as it doesn't provide much except for
inheritance of columns. To me it's most useful for certain types of queries
over the shared attributes of similar classes of entities, where you can
query the base table rather than constructing clumsy unions over the common
columns (or having one-to-one relationships, making querying the "base"
attributes easy, but requiring joins for everything dealing with "child"
entities).
--
Scott Ribe
scott_ribe@killerbytes.com
http://www.killerbytes.com/
(303) 665-7007 voice