CODEWITHSUNDEEP
sqlpostgresqlindexingrdbms
J.J. Beam
J.J. Beam

Reputation: 3059

What is the usecase of hash index?

When to use hash index?

I know the theory of hashing: when search something by hash it's not comparable on < or >, it can only be equal or not. So in opposite to BTree cannot perform divide and conquer searching, rather try to be precise. But I cannot understand when it's useful?

Upvotes: 5

Views: 4221

Answers (3)

Victor
Victor

Reputation: 1003

Hash indexes are useful for equality queries, i.e. when you look for an specific element in your table (for example, WHERE id=1). In this case hash indexes are theoretically faster.

On the other hand, if you need to search also for ranges of elements (for example, WHERE id>1 and id<10), then hash indexes are useless, you need btrees.

Upvotes: 4

jjanes
jjanes

Reputation: 44167

In PostgreSQL, btree indexes have a hard limit on the size of index entries at around 1/3 of the blocksize. So if even 0.00001% of your table rows might have values longer than the limit, you have a problem. Hash indexes avoid this limitation.

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269763

Hash indexes are faster than b-tree indexes -- at least in theory. A b-tree index requires O(log n) effort to search. A hash index is closer to constant time.

Given the improvement in performance, they are sometimes useful. Often inequalities and sorting is not needed.

Upvotes: 7

Related Questions