Re: Hash Indexes

On Tue, Jan 08, 2008 at 01:49:53AM +1100, Naz Gassiep wrote:
> >You could always do something like:
> >
> >CREATE INDEX foo ON table((md5(textcol)));
> >
> >Then it will get used in queries like:
> >SELECT * FROM table WHERE md5(textcol) = md5('text');
>
> That's exactly what I was considering doing, however there is always the
> change of a hash collision. Yes, this is a very remote chance, however
> the ramifications of a collision under those circumstances is
> potentially catastrophic.

You could make it a UNIQUE index (i.e. CREATE UNIQUE INDEX and the rest
like above) if you wanted, or you could perform the query as:

SELECT * FROM table
WHERE md5(textcol) = md5('text')
AND textcol = 'text';

this should use the index to do the initial lookup and then filter out
colliding entries.

> I would assume that hash indexes have inbuilt mechanisms for collision
> checking before returning the row as a match. Am I correct in this
> assumption?

The above isn't using hash indexes in any way. You're creating a b-tree
index on top of the md5-hash of a column. The only index type that
support uniqueness constraints at the moment are b-tree indexes[1].

Sam

[1] http://www.postgresql.org/docs/current/static/sql-createindex.html#AEN47593