Re: hash index improving v3

Alex Hunsaker napsal(a):
> On Thu, Sep 4, 2008 at 9:48 PM, Alex Hunsaker <badalex(at)gmail(dot)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