Fast But Narrow
A hash index stores keys in a hash table, hashing each key to a bucket. For an exact equality lookup it is excellent, offering near constant time access regardless of table size. But that speed comes from discarding order.
What It Cannot Do
Because hashing scatters keys with no relationship between adjacent values, a hash index cannot serve:
- Range queries such as greater than or between, since neighbours are spread randomly.
- Sorting, because there is no traversable order.
- Prefix or leftmost prefix matching on composite keys.
It also only works on the whole key; a partial key gives a different hash.
Other Pitfalls
- Collisions force chaining or probing, degrading performance when the hash distributes poorly.
- A growing table may trigger an expensive rehash to resize the bucket array.
- Memory hash indexes lose their contents on restart and must be rebuilt.
For these reasons B trees remain the default, with hash indexes reserved for pure equality heavy access patterns.
Key idea
A hash index gives near constant time equality lookups but cannot support range queries, sorting, or prefix matching because hashing destroys key order.