Date: Fri, 05 Sep 2008 08:43:14 +0200
From: Zdenek Kotala <Zdenek.Kotala@Sun.COM>
Subject: Re: hash index improving v3
In-reply-to: <34d269d40809042332l7a1ac5e9t8b1dded2b951dcd2@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: <48C0D502.80809@sun.com>
MIME-version: 1.0
Content-type: text/plain; format=flowed; charset=ISO-8859-1
Content-transfer-encoding: 7BIT
References: <ded849dd0808171846g2c6c65adub942bd2510a6c94f@mail.gmail.com>
	<48BFDA8D.3080506@sun.com> <29987.1220560114@sss.pgh.pa.us>
	<7627.1220569888@sss.pgh.pa.us>
	<34d269d40809041742r6a0703dbgfbdd012f7f19de2e@mail.gmail.com>
	<10995.1220577198@sss.pgh.pa.us> <11525.1220579153@sss.pgh.pa.us>
	<34d269d40809041854g355a8f68ga3a037a69e3d9fcb@mail.gmail.com>
	<11962.1220581027@sss.pgh.pa.us>
	<34d269d40809042048m3b532a5ej7a9588f63a788d0c@mail.gmail.com>
	<34d269d40809042332l7a1ac5e9t8b1dded2b951dcd2@mail.gmail.com>
User-Agent: Thunderbird 2.0.0.16 (X11/20080811)

Alex Hunsaker napsal(a):
> On Thu, Sep 4, 2008 at 9:48 PM, Alex Hunsaker <badalex@gmail.com> wrote:
> 
> Ok here are the results:
> 
> (data generated from the c program before)
> select count(1) from test_hash;
>    count
> -----------
>  100000011
> 
> create index test_hash_num_idx on test_hash using hash (num);
> CVS: Time: 698065.180 ms
> patch: Time: 565982.099 ms
> 
> ./pgbench -c 1 -t 100000 -n -f bench.sql
> bench.sql
> select count(1) from test_hash where num = 110034304728896610;
> 
> CVS: tps = 7232.375875 (excluding connections establishing)
> patch: tps = 7913.700150 (excluding connections establishing)
> 
> EXPLAIN ANALYZE select count(1) from test_hash where num = 110034304728896610;
>                                                              QUERY
> PLAN
> ------------------------------------------------------------------------------------------------------------------------------------
>  Aggregate  (cost=29.24..29.25 rows=1 width=0) (actual
> time=0.066..0.067 rows=1 loops=1)
>    ->  Index Scan using test_hash_num_idx on test_hash
> (cost=0.00..29.24 rows=1 width=0) (actual time=0.051..0.054 rows=1
> loops=1)
>          Index Cond: (num = 110034304728896610::bigint)
>  Total runtime: 0.153 ms
> 
> 
> Oddly the index sizes were the same (4096 MB) is that to be expected?

I think yes, because haskey is uint32. You save space only if you use hash for 
example on varchar attribute.

		Zdenek