Peter Geoghegan <peter@xxxxxxxxxxxxxxx> writes: > On 14 September 2011 00:04, Stefan Keller <sfkeller@xxxxxxxxx> 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 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
