To: "Simon Riggs" <simon@2ndquadrant.com>
cc: "Balazs Nagy" <bnagy@thenewpush.com>, pgsql-bugs@postgresql.org,
	pgsql-performance@postgresql.org
Subject: Re: [PERFORM] BUG #2737: hash indexing large tablefails,
	while btree of same index works
In-reply-to: <1163761151.27956.592.camel@silverbirch.site> 
References: <200611051347.kA5DlVCk032222@wwwmaster.postgresql.org>
	<7530.1163202951@sss.pgh.pa.us>
	<1163233075.3634.944.camel@silverbirch.site>
	<1884.1163717296@sss.pgh.pa.us>
	<1163761151.27956.592.camel@silverbirch.site>
Comments: In-reply-to "Simon Riggs" <simon@2ndquadrant.com>
	message dated "Fri, 17 Nov 2006 10:59:11 +0000"
Date: Fri, 17 Nov 2006 10:08:47 -0500
Message-ID: <13525.1163776127@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

"Simon Riggs" <simon@2ndquadrant.com> writes:
> Do we think there is hope of improving hash indexes?

Sure.  They lack WAL support, which is just a small matter of
programming.  And no one has ever spent any time on performance
optimization for them, but it certainly seems like there ought to be
scope for improvement.  I don't we should toss them unless it's been
proven that their theoretical performance advantages can't be realized
for some reason.  (This is unlike the situation for rtree, because with
rtree there was no reason to expect that rtree could dominate gist along
any axis.)

> If there is hope, is there a specific place to look?

I recall some speculation that using bucket size == page size might
be a bad choice, because it leads to mostly-empty buckets for typical
key sizes and fill factors.  Using a configurable bucket size could
help a lot.

			regards, tom lane