Re: Hash Indexes

On Wed, Sep 21, 2016 at 12:44 PM, Geoff Winkless <pgsqladmin(at)geoff(dot)dj>
wrote:

> On 21 September 2016 at 13:29, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> > I'd be curious what benefits people expect to get.
>
> An edge case I came across the other day was a unique index on a large
> string: postgresql popped up and told me that I couldn't insert a
> value into the field because the BTREE-index-based constraint wouldn't
> support the size of string, and that I should use a HASH index
> instead. Which, of course, I can't, because it's fairly clearly
> deprecated in the documentation...
>

Yes, this large string issue is why I argued against removing hash indexes
the last couple times people proposed removing them. I'd rather be able to
use something that gets the job done, even if it is deprecated.

You could use btree indexes over hashes of the strings. But then you would
have to rewrite all your queries to inject an additional qualification,
something like:

Where value = 'really long string' and md5(value)=md5('really long string').

Alas, it still wouldn't support unique indexes. I don't think you can even
use an excluding constraint, because you would have to exclude on the hash
value alone, not the original value, and so it would also forbid
false-positive collisions.

There has been discussion to make btree-over-hash just work without needing
to rewrite the queries, but discussions aren't patches...

Cheers,

Jeff