Re: Making hash indexes worthwhile

Jeff Janes <jeff(dot)janes(at)gmail(dot)com> writes:
> I see that the docs were recently changed from discouraging hash
> indexes both because they were no known uses in which they were
> meaningfully better than btree, and because they aren't recoverable;
> to now just discouraging them because they are not recoverable. Does
> that mean there are now uses in which hash indexes are provably better
> than btree if one is willing to overlook the lack of recoverability?
> If so, what are those situations?

One reason is that you can index values that are too big for btrees;
since hash indexes now store only the hash codes, they don't have a hard
length limit on the underlying value. I'm not sure how useful that
really is in practice, but it's at least an argument for considering
them in certain cases.

> I've played around a bit with hash indexes, and it seems to me that
> making them generally worthwhile will take (at least) reducing or
> entirely doing away with the heavy-wait locks.

Concurrency is really the least of the issues for hash indexes. So far
it's not clear that they're fast enough even in sequential use ...

regards, tom lane