[colanderman]

The heap need only be accessed if the visibility bitmap indicates it ought to be. (Otherwise index-only scans would be pretty useless.)

"Visibility information is not stored in index entries, only in heap entries; so at first glance it would seem that every row retrieval would require a heap access anyway. And this is indeed the case, if the table row has been modified recently. However, for seldom-changing data there is a way around this problem. PostgreSQL tracks, for each page in a table's heap, whether all rows stored in that page are old enough to be visible to all current and future transactions." [1]

But you are right regarding UNIQUE. No reason it couldn't be implemented for hash indexes.

[1] https://www.postgresql.org/docs/9.6/static/indexes-index-onl...

[anarazel]

I think you might have misunderstood what I meant - I was talking about the index insertion case, to verify uniqueness. And that indeed checks the heap regardless of the VM bit. Check _bt_doinsert() invocation of _bt_check_unique() and the latter's content. Therefore there's no fundamental problem with having to do the same in hash insertion cases, even if more heap fetches are likely to be necessary.

https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f...

[colanderman]

Ah gotcha, I thought you were making two separate comments. Makes sense.