undefined | Better HN

0 pointsanarazel8y ago0 comments

> Finally, hash indexes always require that the found row be confirmed in the data table, even for simple existence queries, since the keys themselves aren't stored in the hash table. (This is why hash indexes can't be UNIQUE.)

I don't think that's really true - even for btree indexes the heap is accessed to check row visibility. That's necessary because postgres doesn't store visibility information in indexes. There's imo no really big problem making hash indexes support uniqueness - it's "just" work.

0 comments

colanderman8y ago

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...

anarazelOP8y ago

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...

colanderman8y ago

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

j / k navigate · click thread line to collapse

0 pointsanarazel8y ago0 comments

0 comments

colanderman8y ago

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

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...

anarazelOP8y ago

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

colanderman8y ago

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

j / k navigate · click thread line to collapse