Re: Hash index use presently(?) discouraged since 2005: revive or bury it?

Peter Geoghegan <peter(at)2ndquadrant(dot)com> writes:
> On 14 September 2011 00:04, Stefan Keller <sfkeller(at)gmail(dot)com> wrote:
>> Has this been verified on a recent release? I can't believe that hash
>> performs so bad over all these points. Theory tells me otherwise and
>> http://en.wikipedia.org/wiki/Hash_table seems to be a success.

> Hash indexes have been improved since 2005 - their performance was
> improved quite a bit in 9.0. Here's a more recent analysis:

> http://www.depesz.com/index.php/2010/06/28/should-you-use-hash-index/

Yeah, looking into the git logs shows several separate major changes
committed during 2008, including storing only the hash code not the
whole indexed value (big win on wide values, and lets you index values
larger than one index page, which doesn't work in btree). I think that
the current state of affairs is still what depesz said, namely that
there might be cases where they'd be a win to use, except the lack of
WAL support is a killer. I imagine somebody will step up and do that
eventually.

The big picture though is that we're not going to remove hash indexes,
even if they're nearly useless in themselves, because hash index
opclasses provide the foundation for the system's knowledge of how to
do the datatype-specific hashing needed for hash joins and hash
aggregation. And those things *are* big wins, even if hash indexes
themselves never become so.

regards, tom lane