Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id 03FA6B5D91F for ; Tue, 13 Sep 2011 21:24:42 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia, port 10024) with ESMTP id 50485-03 for ; Wed, 14 Sep 2011 00:24:35 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.8.0-rc2 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by mail.postgresql.org (Postfix) with ESMTP id 71D4DB5DC19 for ; Tue, 13 Sep 2011 21:24:35 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id p8E0OUGi016097; Tue, 13 Sep 2011 20:24:30 -0400 (EDT) To: Peter Geoghegan cc: Stefan Keller , pgsql-performance@postgresql.org Subject: Re: Hash index use presently(?) discouraged since 2005: revive or bury it? In-reply-to: References: Comments: In-reply-to Peter Geoghegan message dated "Wed, 14 Sep 2011 01:04:36 +0100" Date: Tue, 13 Sep 2011 20:24:30 -0400 Message-ID: <16096.1315959870@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-2.404 tagged_above=-10 required=5 tests=BAYES_00=-1.9, RP_MATCHES_RCVD=-0.504 X-Spam-Level: X-Archive-Number: 201109/200 X-Sequence-Number: 44976 Peter Geoghegan writes: > On 14 September 2011 00:04, Stefan Keller 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