Re: Hash Indexes

<div dir="ltr"><div class="gmail_extra"><div class="gmail_quote">On Wed, Sep 21, 2016 at 12:44 PM, Geoff Winkless <span
dir="ltr"><<ahref="mailto:pgsqladmin@geoff.dj" target="_blank">pgsqladmin@geoff.dj</a>></span> wrote:<br
/><blockquoteclass="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><span
class="">On21 September 2016 at 13:29, Robert Haas <<a
href="mailto:robertmhaas@gmail.com">robertmhaas@gmail.com</a>>wrote:<br /> > I'd be curious what benefits people
expectto get.<br /><br /></span>An edge case I came across the other day was a unique index on a large<br /> string:
postgresqlpopped up and told me that I couldn't insert a<br /> value into the field because the BTREE-index-based
constraintwouldn't<br /> support the size of string, and that I should use a HASH index<br /> instead. Which, of
course,I can't, because it's fairly clearly<br /> deprecated in the documentation...<br /></blockquote></div><br
/></div><divclass="gmail_extra">Yes, this large string issue is why I argued against removing hash indexes the last
coupletimes people proposed removing them.  I'd rather be able to use something that gets the job done, even if it is
deprecated.</div><divclass="gmail_extra"><br /></div><div class="gmail_extra">You could use btree indexes over hashes
ofthe strings.  But then you would have to rewrite all your queries to inject an additional qualification, something
like:</div><divclass="gmail_extra"><br /></div><div class="gmail_extra">Where value = 'really long string' and
md5(value)=md5('reallylong string').</div><div class="gmail_extra"><br /></div><div class="gmail_extra">Alas, it still
wouldn'tsupport unique indexes.  I don't think you can even use an excluding constraint, because you would have to
excludeon the hash value alone, not the original value, and so it would also forbid false-positive
collisions.</div><divclass="gmail_extra"><br /></div><div class="gmail_extra">There has been discussion to make
btree-over-hashjust work without needing to rewrite the queries, but discussions aren't patches...</div><div
class="gmail_extra"><br/></div><div class="gmail_extra">Cheers,</div><div class="gmail_extra"><br /></div><div
class="gmail_extra">Jeff</div></div>