Re: Multi field hash indexes

Ocie Mitchell wrote:

> I was playing around with my latest compile and tried to make a unique

> index on two columns, using a hash method.  Both of these (more than
> one column and unique) are currently not allowed for hash indexes.
>
> I thought about this for a bit and realized that making a NESTED hash
> index (index on a and b also serves as an index on a) would be a
> trick, but allowing the unique clause should not be a problem.

It can be complicated (especially for extensible hashing) but the
theory
for this seems to be on in the database handbooks under the name of
'segmented hash' or some like fancy name.

The trick is to hash each field separately and then have a concatenation

of the hash values.

so assuming that for fields (a,b,c) values (120, 'friday', 3.1415927)
hash
to 'aa', 'bb', 'cc' the hash value of the whole tuple will be 'aabbcc'

then the index can be used not only when selecting  = a,b,c but also
when
selecting on _any_ of the fields in this index. For example when
selecting
for b='friday' one would examine only buckets where the middle is 'bb'

> Therefore, I would like to try implementing unique constraints on hash

> indexes.  Has this come up before?  Are there any reasons not to
> support this?  As far as I understand, specifying an index method is
> non standard (above and beyond standard) to begin with.

While you are at it could you please comment if the GIST indexes are
used or
at least easily usable?

I have asked this question once in each two months for about a year now,
but
it seems that noone is in the position to answer it.

Cheers,

Hannu Krosing