From: Tom Lane <tgl@sss.pgh.pa.us>
To: Peter Geoghegan <peter@2ndquadrant.com>
Cc: Stefan Keller <sfkeller@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Hash index use presently(?) discouraged since 2005: revive or bury it?
Date: Tue, 13 Sep 2011 20:24:30 -0400
Message-ID: <16096.1315959870@sss.pgh.pa.us> (raw)
In-Reply-To: <CAEYLb_Udue8_XSE9K0KMqKBE_62yurv1bY4Wc+qJoGykZ_rXtA@mail.gmail.com>
References: <CAFcOn293Rzi7cZYWBT8tjxCtNX5pYdbknANXgf9mWfBizK8Frg@mail.gmail.com>
<CAEYLb_Udue8_XSE9K0KMqKBE_62yurv1bY4Wc+qJoGykZ_rXtA@mail.gmail.com>
Peter Geoghegan <peter@2ndquadrant.com> writes:
> On 14 September 2011 00:04, Stefan Keller <sfkeller@gmail.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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, peter@2ndquadrant.com, sfkeller@gmail.com
Subject: Re: Hash index use presently(?) discouraged since 2005: revive or bury it?
In-Reply-To: <16096.1315959870@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox