PostgreSQL HASH index PostgreSQL HASH index postgresql postgresql

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