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 <[EMAIL PROTECTED]> http://svana.org/kleptog/
> Those who make peaceful revolution impossible will make violent revolution
> inevitable.
> -- John F Kennedy
signature.asc
Description: Digital signature
