Hacker News new | ask | show | jobs
by snaky 3187 days ago
> We can see here that the hash index performs better than the btree index and the performance difference is in the range of 10 to 22%. In some other workloads we have seen a better performance like with hash index on varchar columns and even in the community, it has been reported that there is performance improvement in the range of 40-60% when hash indexes are used for unique index columns.

the link is from the post http://amitkapila16.blogspot.com/2017/03/hash-indexes-are-fa...
1 comments
slagfart 3187 days ago
Don't forget that the performance improvement of hash over b-tree gets better for wide keys - the wider the key, the better the improvement, as the key width is irrelevant to the hash index.

Based on the width of the key, the row count and the amount of memory available, there might be instances where a disk-hit gets replaced with an in-memory cache, which is awesome for spinning-disk based systems.

link
snaky 3187 days ago
> the wider the key, the better the improvement

Good point, I think it would be nice to add it to the docs.

Maybe with part numbers of goods as an example - in typical OLTP DB they are variable width strings, mostly read-heavy, unique (not as constraint but as the matter of fact) and searched by almost constantly.

link