Hash vs B-tree index
For now on PostgreSQL we use hash index for index with Equality usage with a single column. This is because hash indexes are supposed to be faster to update O(1) and faster in SELECT query. But it is not always the case, when the cardinality of the indexed column is low, the size of the hash index is much bigger than the equivalent B-tree this is because each row is stored with the hash and B-tree has deduplication.
So I think that we should use B-tree index for Equality usage with a single column if the cardinality is not high.
For that I propose to add a cardinality property to the index Usage which describe the cardinality of the expression with the available values being low
, normal
(default) and high
.
A typical use case is the index on the state field which has a low
cardinality so the B-tree contains only a few nodes compare to all rows for the hash.
References:
- https://www.postgresql.org/docs/current/hash-index.html
- https://www.postgresql.org/docs/current/btree.html
- https://evgeniydemin.medium.com/postgresql-indexes-hash-vs-b-tree-84b4f6aa6d61
- https://amitkapila16.blogspot.com/2017/03/hash-indexes-are-faster-than-btree.html
- https://thwack.solarwinds.com/groups/data-driven/b/blog/posts/an-introduction-to-b-tree-and-hash-indexes-in-postgresql