4

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!

1

Hash indexes do not currently support unique indexes, and so not PK or FK either. Maybe in a future version...

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.