Skip site navigation (1) Skip section navigation (2)

Hash index use presently(?) discouraged since 2005: revive or bury it?

From: Stefan Keller <sfkeller(at)gmail(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Hash index use presently(?) discouraged since 2005: revive or bury it?
Date: 2011-09-13 23:04:27
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-performance
The doc at
says: "Caution: Hash index operations are not presently WAL-logged, so
hash indexes might need to be rebuilt with REINDEX after a database
crash. They are also not replicated over streaming or file-based
replication. For these reasons, hash index use is presently

I found a thread here
about <<"Hash index" vs. "b-tree index" (PostgreSQL 8.0)>> mentioning
some issues, like they
* are not faster than B-trees even for = comparisons
* aren't WAL safe
* have poor concurrency (require coarser locks),
* are significantly slower than creating a b+-tree index.

In fact these statements seem to rely on the docs back in version 7.2
(see )

Has this been verified on a recent release? I can't believe that hash
performs so bad over all these points. Theory tells me otherwise and seems to be a success.

Are there any plans to give hash index another chance (or to bury it
with a reason)?



pgsql-performance by date

Next:From: Samuel GendlerDate: 2011-09-13 23:27:26
Subject: Re: raid array seek performance
Previous:From: Kevin GrittnerDate: 2011-09-13 21:13:00
Subject: Re: Migrated from 8.3 to 9.0 - need to update config (re-post)

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group