Re: Hash Indexes

On Sat, Jan 05, 2008 at 03:20:54AM +1100, Naz Gassiep wrote:
> Hi there,
> I am creating functionality where there are blocks of text that are
> being stored in the DB and that need to be searched for. No like or
> pattern matching, just a plain old WHERE clause. Obviously, hash indexes
> would be best here, however I've been warned away from PG's hash
> implementation.

Why are hash indexes "obviously" best? In an ideal world with a good
implementation maybe, but postgresql b-trees are really quite good.

> Would it be better to manually create a column storing
> the hashes (maintained with a CHECK (md5(textcol) = hashcol) type
> constraint) and generate the hashes in the app?

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');

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