Date: Wed, 10 Sep 2008 16:47:21 +0200
From: Zdenek Kotala <Zdenek.Kotala@Sun.COM>
Subject: Re: hash index improving v3
In-reply-to: <34d269d40809092004q10b5da87r6fa3fe86926476ff@mail.gmail.com>
To: Alex Hunsaker <badalex@gmail.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>, Xiao Meng <mx.cogito@gmail.com>,
	pgsql-patches@postgresql.org, "Jonah H. Harris" <jonah.harris@gmail.com>
Message-id: <48C7DDF9.6020507@sun.com>
MIME-version: 1.0
Content-type: text/plain; format=flowed; charset=ISO-8859-2
Content-transfer-encoding: 7BIT
References: <10995.1220577198@sss.pgh.pa.us> <11962.1220581027@sss.pgh.pa.us>
	<34d269d40809042048m3b532a5ej7a9588f63a788d0c@mail.gmail.com>
	<34d269d40809050912u33f2afc8mb90e316bc465136e@mail.gmail.com>
	<34d269d40809051321w2ed16037od0373a4a29c311f1@mail.gmail.com>
	<34d269d40809052249h59750f83v4b19a50d0555580@mail.gmail.com>
	<22574.1220728178@sss.pgh.pa.us>
	<34d269d40809061923k4c731481gf580d46d259defb0@mail.gmail.com>
	<20080909134839.GC6714@it.is.rice.edu>
	<34d269d40809091823t204e252fl246443be71aed9ff@mail.gmail.com>
	<34d269d40809092004q10b5da87r6fa3fe86926476ff@mail.gmail.com>
User-Agent: Thunderbird 2.0.0.14 (X11/20080630)

Alex Hunsaker napsal(a):

> 
> wide:
> # NOTE not on the same machine as the "narrow" test was run!
> 
> # spit out 2, 000, 000 random 100 length strings
> perl gen.pl > data.sql
> create table test_hash (wide text);
> copy test_hash from './data.sql';
> create index test_hash_num_idx on test_hash using hash (wide);
> 
> bench.sql:
> select a.wide from test_hash as a inner join test_hash as b on b.wide
> = a.wide where a.wide =
> 'BJNORSLMITGKHJCWDBLKLYRSJTVPTYXZJPWNBKXGHYFNDHRAKNFMDHRMUXLDXNTRBJMTHPGPBFJZPAENZXDHAHCUSCJTUPUXWCXUH';
> 
> # ^ that string is in data.sql
> 
> # 3 runs each
> pgbench -c1 -n -t100000 -f bench.sql
> cvs head: tps = 5073.463498, 5110.620923, 4955.347610
> v5:           tps = 5870.681336, 5740.007837, 5699.002942

What locale did you use? It would be nice to have also comparing between 
C and any UTF8 locale. I think we should see big benefit when non C 
locale is used.

		Thanks Zdenek