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@svana.org> http://svana.org/kleptog/
> Those who make peaceful revolution impossible will make violent revolution inevitable.
> -- John F Kennedy