Skip site navigation (1) Skip section navigation (2)

Re: hash index improving v3

From: "Alex Hunsaker" <badalex(at)gmail(dot)com>
To: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: "Xiao Meng" <mx(dot)cogito(at)gmail(dot)com>, "Zdenek Kotala" <Zdenek(dot)Kotala(at)sun(dot)com>, pgsql-patches(at)postgresql(dot)org, "Jonah H(dot) Harris" <jonah(dot)harris(at)gmail(dot)com>
Subject: Re: hash index improving v3
Date: 2008-09-07 02:23:05
Message-ID: 34d269d40809061923k4c731481gf580d46d259defb0@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-patches
On Sat, Sep 6, 2008 at 1:09 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>For the convenience of anyone intending to test, here is an updated
>patch against CVS HEAD that incorporates Alex's fix.

Here are the results for a table containing 1 million entries that
will generate hash collisions.  It paints a bad picture for the patch
but then again im not sure how relevant the issue is.  For example
yesterday I imported a table with 10 million collisions and the create
index is still running (now at about ~18 hours).  Maybe we should warn
if there are lots of collisions when creating the index and suggest
you use a btree? Anyway here are the results.

./pgbench -c1 -n -t10 -f bench_createindex.sql
cvs head: tps = 0.002169
v5          : tps = 0.002196

pgbench -c1 -n -t1000 -f bench_bitmap.sql
cvs head: tps = 24.011871
v5:           tps = 2.543123

pgbench -c1 -n -t1000 -f bench_index.sql
cvs head: tps = 51.614502
v5:           tps = 3.205542

pgbench -c1 -n -t1000 -f bench_seqscan.sql
cvs head: tps = 8.553318
v5:           tps = 9.836091

Table created via:
create table test_hash (num int8);
./hash | psql -c 'copy test_hash from stdin;'

Attachment: hash.c
Description: text/x-csrc (208 bytes)
Attachment: int8collide.patch
Description: application/octet-stream (381 bytes)
Attachment: bench_seqscan.sql
Description: application/octet-stream (104 bytes)
Attachment: bench_index.sql
Description: application/octet-stream (102 bytes)
Attachment: bench_create.sql
Description: application/octet-stream (101 bytes)

In response to

Responses

pgsql-hackers by date

Next:From: Alex HunsakerDate: 2008-09-07 02:24:33
Subject: Re: hash index improving v3
Previous:From: Bruce MomjianDate: 2008-09-07 02:09:17
Subject: Re: Synchronous Log Shipping Replication

pgsql-patches by date

Next:From: Alex HunsakerDate: 2008-09-07 02:24:33
Subject: Re: hash index improving v3
Previous:From: David RowleyDate: 2008-09-07 02:03:57
Subject: Re: [PATCHES] TODO item: Implement Boyer-Moore searching (First time hacker)

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group