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: | CAFcOn293Rzi7cZYWBT8tjxCtNX5pYdbknANXgf9mWfBizK8Frg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
The doc at http://www.postgresql.org/docs/current/interactive/indexes-types.html
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
discouraged."
I found a thread here
http://archives.postgresql.org/pgsql-general/2005-05/msg00370.php
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 http://www.postgresql.org/docs/7.2/static/indexes-types.html )
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
http://en.wikipedia.org/wiki/Hash_table seems to be a success.
Are there any plans to give hash index another chance (or to bury it
with a reason)?
Stefan
From | Date | Subject | |
---|---|---|---|
Next Message | Samuel Gendler | 2011-09-13 23:27:26 | Re: raid array seek performance |
Previous Message | Kevin Grittner | 2011-09-13 21:13:00 | Re: Migrated from 8.3 to 9.0 - need to update config (re-post) |