Hash indexes

[Mladen Gogala]

PostgreSQL has hash indexes, very similar to Oracle bitmap indexes.
Oracle warns the application designers against using them in the OLTP
applications because of the locking. Namely, locking a row would lock all
the rows which hash to the same hash value as the original row.
My question is whether the same thing applies to the PostgreSQL hash
indexes? In the documentation page, I found the following:

http://www.postgresql.org/docs/8.4/interactive/indexes-types.html

CREATE INDEX name ON table USING hash (column);

Note: Hash index operations are not presently WAL-logged, so hash
indexes might need to be rebuilt with REINDEX after a database crash. For
this reason, hash index use is presently discouraged.

My question is whether someone here has played with the hash indexes? Any
words of caution or blissful experiences? Thanks.

--
http://mgogala.byethost5.com

[Thomas Kellerer]

Mladen Gogala, 14.12.2009 22:42:

The only reason in Oracle to use a bitmap index is - as far as I know - to enable Oracle to use more than one index for retrieval. Postgres can do this with regular indexes anyway, so from that perspective there is no reason to use hash indexes.

I think I recall some messages on the mailing lists that the dev team sees the hash index as a second-class citizen, mainly because it gives no advantage over a regular index. But I'm not 100% if memory serves me well here.

Better ask that question on the mailing list.

Thomas