Re: [PERFORM] "Hash index" vs. "b-tree index" (PostgreSQL

Neil Conway <neilc(at)samurai(dot)com> writes:
> Greg Stark wrote:
>> What if the hash index stored *only* the hash code?

> Attached is a WIP patch that implements this.

Performance?

> I'm posting mainly because I wasn't sure what to do to avoid false
> positives in the case of hash collisions. In the hash AM code it is
> somewhat awkward to fetch the pointed-to heap tuple and recheck the
> scankey.[1] I just did the first thing that came to mind -- I marked all
> the hash AM opclasses as "lossy", so the index qual is rechecked. This
> works, but suggestions for a better way to do things would be welcome.

AFAICS that's the *only* way to do it.

I disagree completely with the idea of forcing this behavior for all
datatypes. It could only be sensible for fairly wide values; you don't
save enough to justify the lossiness otherwise.

It would be interesting to look into whether it could be driven on a
per-opclass basis. Then you could have, eg, "text_lossy_hash_ops"
as a non-default opclass the DBA could select if he wanted this
behavior. (The code could perhaps use the amopreqcheck flag to tell
it which way to behave.) If that seems unworkable, I'd prefer to see us
set this up as a new index AM type, which would share a lot of code with
the old.

[ BTW, posting patches to pgsql-general seems pretty off-topic. ]

regards, tom lane