> Why are hash indexes "obviously" best? In an ideal world with a good
> implementation maybe, but postgresql b-trees are really quite good.
>
Because doing normal queries on a table where there are large text
blocks is unlikely to be a good idea. E.g.,:
SELECT * FROM table WHERE textcol = 'a 4kb block of text';
> 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. Think a user being delivered text that
contains confidential and sensitive material as opposed to the latest
memo about the cleaning of toilets.
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?
Best regards,
- Naz.