DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=message-id:date:from:to:subject:cc:in-reply-to:mime-version
	:content-type:references;
	b=Pfus/7xnb1aZTAnHucWvm3sAwIjp63glkYRjAFiSkTZYD5Zl1bppn5eflJgNb0ZTsj
	RKhE6cqgwvEigwrbqVvH4mXhsNunY7rB6xR/yKPDXDMdz+Zv4qkC/FqtY/tn1ES7P9kd
	J2V9T8w/RbQehMPFVbAF9vIYPG1hfB5+mSUOE=
Message-ID: <34d269d40809092004q10b5da87r6fa3fe86926476ff@mail.gmail.com>
Date: Tue, 9 Sep 2008 21:04:58 -0600
From: "Alex Hunsaker" <badalex@gmail.com>
To: "Tom Lane" <tgl@sss.pgh.pa.us>
Subject: Re: hash index improving v3
Cc: "Xiao Meng" <mx.cogito@gmail.com>,
 "Zdenek Kotala" <Zdenek.Kotala@sun.com>,
	pgsql-patches@postgresql.org, "Jonah H. Harris" <jonah.harris@gmail.com>
In-Reply-To: <34d269d40809091823t204e252fl246443be71aed9ff@mail.gmail.com>
MIME-Version: 1.0
Content-Type: multipart/mixed;
	boundary="----=_Part_3050_15884634.1221015898101"
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>

------=_Part_3050_15884634.1221015898101
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

On Tue, Sep 9, 2008 at 7:23 PM, Alex Hunsaker <badalex@gmail.com> wrote:
> BTW Im still planning on doing a wide vs narrow test... sometime... :)

narrow: (exactly the same as what I just did in the other post)
create table test_hash(num int8);
insert into test_hash (num) select generate_series(1, 2000000);
create index test_hash_num_idx on test_hash using hash (num);

pgbench -c1 -n -t100000 -f bench_index.sql
cvs head: tps = 7345.69432
v5:           tps = 7526.290462


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

------=_Part_3050_15884634.1221015898101
Content-Type: application/x-perl; name=gen.pl
Content-Transfer-Encoding: base64
X-Attachment-Id: f_fkxd67aj0
Content-Disposition: attachment; filename=gen.pl

dXNlIHN0cmljdDsKdXNlIHdhcm5pbmdzOwoKbXkgQGNoYXJzID0gJ0EnLi4nWic7Cgpmb3IgbXkg
JHggKDAuLjIwMDAwMDApCnsKCW15ICRzdHIgPSAnJyB4IDEwMDsKCglmb3IgbXkgJHkgKDAuLjEw
MCkKCXsKCQkkc3RyIC49ICRjaGFyc1tyYW5kIEBjaGFyc107Cgl9CgoJcHJpbnQgIiRzdHJcbiI7
Cn0K
------=_Part_3050_15884634.1221015898101--