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
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)
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
selecting on _any_ of the fields in this index. For example when
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
at least easily usable?
I have asked this question once in each two months for about a year now,
it seems that noone is in the position to answer it.
pgsql-hackers by date
|Next:||From: Pedro J. Lobo||Date: 1998-03-17 08:42:07|
|Subject: Re: [HACKERS] Alpha initdb fixed!|
|Previous:||From: The Hermit Hacker||Date: 1998-03-17 07:40:44|
|Subject: Re: [HACKERS] Re: [PATCHES] patches for 6.2.1p6|