Re: Inherited indexes.

On Sun, Oct 02, 2005 at 09:46:07PM -0400, Tom Lane wrote:
> Fredrik Olsson <fredrik(dot)olsson(at)treyst(dot)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(at)pervasive(dot)com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461