PostgreSQL HASH index
Hashes are faster than B-Trees for cases where you have a known key value, especially a known unique value.
Hashes should be used if the column in question is never intended to be scanned comparatively with <
or >
commands.
Hashes are O(1)
complexity, B-Trees are O(log n)
complexity ( iirc ) , ergo, for large tables with unique entries, fetching an ITEM="foo"
, they will be the most efficient way of looking it up.
This is especially practical when these unique fields are used on a join condition.
It's better to use a Hash index for text columns that are searched using = operator only. For example a URL column which needs to be indexed for lookups.
A Hash index is approximately 30% the size of a B-Tree index for something like a URL.
The reduced size allows PostgreSQL to use it's cache memory (Aka, shared_buffers) more efficiently.
As http://www.postgresql.org/docs/9.2/static/sql-createindex.html point Hash index are still not WAL-safe; which means that they are not 100% reliable for crashes (index has to be reconstructed and wrong response could happen on replications). Check also http://www.postgresql.org/docs/9.1/static/wal-intro.html