Thread: Hash indexes

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.

Mladen Gogala Sr. Oracle DBA 1500 Broadway New York, NY 10036 (212) 329-5251 www.vmsinfo.com The Leader in Integrated Media Intelligence Solutions

On Tue, Dec 15, 2009 at 11:48 AM, Mladen Gogala
<mladen.gogala@vmsinfo.com> wrote:

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

I can't give experiences either way, but I've remember reading a post
to the pg_general mailing regarding this subject a couple of years
ago.  Basically the advice was, since there the performance
differences between B-tree and Hash are negligible, why bother using
Hash indexes.

I cant find the post, but here is one that is a little more recent:

http://archives.postgresql.org/pgsql-hackers/2009-10/msg00284.php

--
Regards,
Richard Broersma Jr.

Visit the Los Angeles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug