Re: Save Hash Indexes

On Fri, Nov 1, 2013 at 8:52 AM, Daniel Farina <daniel(at)heroku(dot)com> wrote:
> On Fri, Nov 1, 2013 at 6:31 AM, Dimitri Fontaine <dimitri(at)2ndquadrant(dot)fr> wrote:
>> Also, talking with Peter Geoghegan, it's unclear that there's a use case
>> where a hash index would be faster than a btree index over the hash
>> function.
>>
>> Comments?
>
> I haven't met a single Heroku user that has stumbled into this
> landmine. Normally I am very weary of such landmine laden features,
> but this one is there and doesn't seem to have detonated at any point.
> I guess the obscure nature of those indexes and the stern warning in
> the documentation has sufficed to discourage their use.
>
> Given that experience, I think foreclosing fixing hash indexes is
> premature, and doesn't seem to be hurting inexperienced users of this
> stripe. Maybe there are yet other reasons to argue the subject,
> though...it's not like the current user base relies on the behavior
> as-is either, having seemingly steered clear just about altogether.

In addendum, though, some users *have* done the hash-function + btree
trick to make keys smaller. They tend to resort to full blown
cryptographic hashes and assume/enforce non-collision, but it's
somewhat awkward and finicky. So while perhaps commandeering the
'hash' index type is a bit over-aggressive, making that use case work
better would probably carry positive impact.

I can say most of my indexes over text are *never* used for range
queries, so hashing them down one would think would be great. The
fiddliness of applying expression indexes over all that forecloses
getting the benefits that might be possible, there: only certain heavy
workloads will receive that level of care.