I read this not so recent article about Hash Index performance on PostgreSQL, where the author writes:
...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% to 60% when hash indexes are used for unique index columns.
Assuming that your PostgreSQL is at leat version 10 (to have the index WAL-logged) and (as usually happens) you only have equality comparisons on PK and FK columns, my question is:
Shouldn’t be Hash index preferred to B-Tree index on PK and FK column, due to the (slightly) better performance and for the smaller memory footprint?
While I’m writing, I’m asking if the IN operator can use that kind of index, since it might be the only one other operator (other than =) usually involved in lookup queries on those kind of columns, and if not, this may respond to my answer (negetively).
Thank you!
