Message-ID: <4E775FB9.6050704@gmail.com>
Date: Mon, 19 Sep 2011 18:28:57 +0300
From: Vitalii Tymchyshyn <tivv00@gmail.com>
User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US;
	rv:1.9.2.18) Gecko/20110617 Lightning/1.0b2 Thunderbird/3.1.11
MIME-Version: 1.0
To: Robert Klemme <shortcutter@googlemail.com>
CC: Merlin Moncure <mmoncure@gmail.com>, Stefan Keller <sfkeller@gmail.com>,
	Jeff Janes <jeff.janes@gmail.com>,
	Peter Geoghegan <peter@2ndquadrant.com>, pgsql-performance@postgresql.org
Subject: Re: Hash index use presently(?) discouraged since 2005:
	revive or bury it?
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>
In-Reply-To: 
 <CAM9pMnNUoOnLye5iPqGu15K43Nus=xtCL_rRW5LRt3UJ3vqDQQ@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit

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.