Re: Hash Indexes

On Mon, Sep 19, 2016 at 05:50:13PM +1200, Mark Kirkwood wrote:
> >I'm rather unenthused about having a hash index implementation that's
> >mildly better in some corner cases, but otherwise doesn't have much
> >benefit. That'll mean we'll have to step up our user education a lot,
> >and we'll have to maintain something for little benefit.
>
> While I see the point of what you are saying here, I recall all previous
> discussions about has indexes tended to go a bit like this:
>
> - until WAL logging of hash indexes is written it is not worthwhile trying
> to make improvements to them
> - WAL logging will be a lot of work, patches 1st please
>
> Now someone has done that work, and we seem to be objecting that because
> they are not improved then the patches are (maybe) not worthwhile. I think
> that is - essentially - somewhat unfair.

My understanding of hash indexes is that they'd be good for indexing
random(esque) data (such as UUIDs or, well, hashes like shaX). If so
then I've got a DB that'll be rather big that is the very embodiment
of such a use case. It indexes such data for equality comparisons
and runs on SELECT, INSERT and, eventually, DELETE.

Lack of WAL and that big warning in the docs is why I haven't used it.

Given the above, many lamentations from me that it wont be available
for 9.6. :( When 10.0 comes I'd probably go to the bother of re-indexing
with hash indexes.

Andrew