Re: Hash Indexes

On Tue, Jan 08, 2008 at 01:49:53AM +1100, Naz Gassiep wrote:
> 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?

Well, they do in the sense that it does the equivalent of:

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

But hash indexes can't be used for uniqueness checks, so if you need to
stop the same value being entered twice, hash indexes are way out.

The fact that hash indexes don't survive a DB crash is probably your
biggest concern.

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Those who make peaceful revolution impossible will make violent revolution inevitable.
> -- John F Kennedy