Re: [HACKERS] Re: Multi field hash indexes

ocie(at)paracel(dot)com wrote:

> Hannu Krosing wrote:
> >
> > 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'
> >
>
> HMM, this doesn't feel right. If I have an index on four int4s
> a,b,c,d and I only know d, then it seems like searching for these in
> the hash table could be as much work, or more work than a table scan.

I was assuming that the hash table scan would be cheaper than the table scan

> > > 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?
>
> What are GIST indexes?

Some kind of generalised binary tree indexes that should make it easy to
define additional indexing strategies.

There is a directory access/gist in src/backend, and they are briefly
mentioned in the PostgreSQL programmers guide.

They seem to be offspring of some independent (of postgres) Berkeley project.

Hannu