Re: Hash Indexes

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

I suggest you look at the tsearch stuff instead.

> 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?

I think you should avoid any assumptions about the hash index implementation
in PostgreSQL. The general consensus seems to be that the code has a number
of problems. Most importantly, hash index operations are _not_ currently
WAL-logged, which means you probably need to REINDEX in the event of a
database crash. I don't know whether the collision issues are present in
hash indexes, though.

A