Re: Inherited indexes.

On Sun, Oct 02, 2005 at 09:46:07PM -0400, Tom Lane wrote:
> Fredrik Olsson <fredrik.olsson@treyst.se> writes:
> > To allow indexes to be inherited so unique, foreign keys and such works 
> > properly with inheritance has been on the todo for quite some time. I 
> > thought that most probably it is a very non trivial thing, perhaps 
> > completely rethinking how indexes are done.
> 
> Yup, you're right.  There are a couple of major problems, to my mind:
> 
> 1. A cross-table index would need to store a table OID as well as the
> existing block/offset information in order to tell you what an entry is
> pointing at.  An extra 4 bytes per index entry (8 bytes if MAXALIGN is
> 8) is a lot of overhead, so you'd not want to pay that all the time.
> Which means two index tuple header formats to support, which looks
> painful.  How can that be handled cleanly and efficiently?

Wouldn't it make more sense to use a smaller pointer to a table of OIDs
that that index covers? I don't know off-hand how much padding there
currently is in index tuples, but hopefully this would allow bringing
the space usage under control for common cases involving less than a few
dozen tables.

Another possibility is optimizing for the special case of indexing on a
partitioning key. In this case, index values would be very localized to
one table, so just storing the table info on each index page (or
something similar) would work well.
-- 
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461