Re: hash index improving v3

On Sat, Sep 6, 2008 at 1:09 PM, Tom Lane <[email protected]> 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;'


-- 
Sent via pgsql-patches mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-patches

*** a/src/backend/access/hash/hashfunc.c
--- b/src/backend/access/hash/hashfunc.c
***************
*** 62,70 **** hashint8(PG_FUNCTION_ARGS)
  #ifndef INT64_IS_BUSTED
  	int64		val = PG_GETARG_INT64(0);
  	uint32		lohalf = (uint32) val;
- 	uint32		hihalf = (uint32) (val >> 32);
- 
- 	lohalf ^= (val >= 0) ? hihalf : ~hihalf;
  
  	return hash_uint32(lohalf);
  #else
--- 62,67 ----

#include <stdio.h>
#include <stdlib.h>
#include <limits.h>

int main(void)
{
	unsigned long y = 0;
	unsigned cnt = 0;

	while(cnt < 1000000)
	{
		y += UINT_MAX;
		y += 1;

		printf("%ld\n", y);

		cnt++;
	}
}