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

2011/9/14 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:
> (...) 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.

How much of work (in man days) do you estimate would this mean for
someone who can program but has to learn PG internals first?

Stefan

2011/9/14 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
> 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
>