PostgreSQL HASH index

Question

Hi people,

Does anyone know a situation where a PostgreSQL HASH should be used instead of a B-TREE for it seems to me that these things are a trap. They are take way more time to CREATE or maintain than a B-TREE (at least 10 times more), they also take more space (for one of my table.columns, a B-TREE takes up 240 MB, while a HASH would take 4 GB) and I seem to have understood from my googling, that they do not SELECT faster than B-TREEs; yet the HASH may have recently been optimized or google was wrong.

Anyway, I wanted you guy's opinions and experiences. If these HASHs are evil, people should know.

Thanks
Also: what about MySQL's HASHs?

Answer 1

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 collumn in question is never intended to be scanned comparatively with < or > commands.

Hashes are O(1) complexity, B-Trees are O(nlogn) complexity ( iirc ) , ergo, for large tables with unique entries, fetching an ITEM="foo" will be the most efficient way of looking it up.

This is especially practical when these unique fields are used on a join condition.