From: Vitalii Tymchyshyn <tivv00@gmail.com>
To: Robert Klemme <shortcutter@googlemail.com>
Cc: Merlin Moncure <mmoncure@gmail.com>
Cc: Stefan Keller <sfkeller@gmail.com>
Cc: Jeff Janes <jeff.janes@gmail.com>
Cc: Peter Geoghegan <peter@2ndquadrant.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Hash index use presently(?) discouraged since 2005: revive or bury it?
Date: Mon, 19 Sep 2011 18:28:57 +0300
Message-ID: <4E775FB9.6050704@gmail.com> (raw)
In-Reply-To: <CAM9pMnNUoOnLye5iPqGu15K43Nus=xtCL_rRW5LRt3UJ3vqDQQ@mail.gmail.com>
References: <CAFcOn293Rzi7cZYWBT8tjxCtNX5pYdbknANXgf9mWfBizK8Frg@mail.gmail.com>
<CAEYLb_Udue8_XSE9K0KMqKBE_62yurv1bY4Wc+qJoGykZ_rXtA@mail.gmail.com>
<CAMkU=1x7EsbLh8oxYtXKWzDx5HUDxQ2Vb4sFDCVY0FE8rnttSg@mail.gmail.com>
<CAHyXU0wPJkYhQLioswNRe6YmXcC_JKZmDxXsb1jcLHo8+BuJkw@mail.gmail.com>
<CAFcOn2_NnrzquFQ+YPw2vh=ioC+KXcE1NdEJZWG4O7pmY1Eu3Q@mail.gmail.com>
<CAHyXU0yExA3sBDy1o1vJ57g5pTV5MQ_Z7HXhBo9Pv0+Zg-moHg@mail.gmail.com>
<CAM9pMnNUoOnLye5iPqGu15K43Nus=xtCL_rRW5LRt3UJ3vqDQQ@mail.gmail.com>
19.09.11 18:19, Robert Klemme написав(ла):
> On Mon, Sep 19, 2011 at 4:04 PM, Merlin Moncure<mmoncure@gmail.com> wrote:
>>
>> Postgres's hash index implementation used to be pretty horrible -- it
>> stored the pre-hashed datum in the index which, while making it easier
>> to do certain things, made it horribly slow, and, for all intents and
>> purposes, useless. Somewhat recently,a lot of work was put in to fix
>> that -- the index now packs the hash code only which made it
>> competitive with btree and superior for larger keys. However, certain
>> technical limitations like lack of WAL logging and uniqueness hold
>> hash indexing back from being used like it really should be. In cases
>> where I really *do* need hash indexing, I do it in userland.
>>
>> create table foo
>> (
>> a_long_field text;
>> );
>> create index on foo(hash(a_long_field));
>>
>> select * from foo where hash(a_long_field) = hash(some_value) and
>> a_long_field = some_value;
>>
>> This technique works fine -- the main disadvantage is that enforcing
>> uniqueness is a PITA but since the standard index doesn't support it
>> either it's no great loss. I also have the option of getting
>> 'uniqueness' and being able to skip the equality operation if I
>> sacrifice some performance and choose a strong digest. Until the hash
>> index issues are worked out, I submit that this remains the go-to
>> method to do this.
> Is this approach (storing the hash code in a btree) really faster than
> a regular btree index on "a_long_field"? And if so, for which kind of
> data and load?
Actually sometimes the field in [potentially] so long, you can't use
regular b-tree because it won't fit in the page. Say, it is "text" type.
If you will create regular index, you will actually limit column value
size to few KB. I am using md5(text) indexes in this case coupled with
rather ugly queries (see above). Native support would be nice.
Best regards, Vitalii Tymchyshyn.
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: tivv00@gmail.com, shortcutter@googlemail.com, mmoncure@gmail.com, sfkeller@gmail.com, jeff.janes@gmail.com, peter@2ndquadrant.com
Subject: Re: Hash index use presently(?) discouraged since 2005: revive or bury it?
In-Reply-To: <4E775FB9.6050704@gmail.com>
* 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