Received: from localhost (uranus.hub.org [200.46.204.60]) by postgresql.org (Postfix) with ESMTP id D2FB49F9806; Fri, 17 Nov 2006 06:59:41 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.60]) (amavisd-new, port 10024) with ESMTP id 83833-01; Fri, 17 Nov 2006 06:59:39 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- X-Greylist: from auto-whitelisted by SQLgrey- Received: from mail01.enterprisedb.com (mail01.enterprisedb.com [63.246.7.168]) by postgresql.org (Postfix) with ESMTP id 2B0D59F9B05; Fri, 17 Nov 2006 06:59:38 -0400 (AST) thread-index: AccKN3rntldWb4WSQP+e6/qf5KSVIg== Received: from [192.168.0.20] ([62.232.55.118]) by mail01.enterprisedb.com with Microsoft SMTPSVC(6.0.3790.1830); Fri, 17 Nov 2006 05:59:40 -0500 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.2757 Content-class: urn:content-classes:message Importance: normal Subject: Re: [PERFORM] BUG #2737: hash indexing large tablefails, while btree of same index works From: "Simon Riggs" To: "Tom Lane" Cc: "Balazs Nagy" , , In-Reply-To: <1884.1163717296@sss.pgh.pa.us> References: <200611051347.kA5DlVCk032222@wwwmaster.postgresql.org> <7530.1163202951@sss.pgh.pa.us> <1163233075.3634.944.camel@silverbirch.site> <1884.1163717296@sss.pgh.pa.us> Content-Type: text/plain; charset="iso-8859-1" Date: Fri, 17 Nov 2006 10:59:11 +0000 Message-ID: <1163761151.27956.592.camel@silverbirch.site> MIME-Version: 1.0 X-Mailer: Evolution 2.6.0 Content-Transfer-Encoding: 7bit X-OriginalArrivalTime: 17 Nov 2006 10:59:40.0281 (UTC) FILETIME=[7AC8D690:01C70A37] X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200611/118 X-Sequence-Number: 16253 On Thu, 2006-11-16 at 17:48 -0500, Tom Lane wrote: > AFAICS, any hash index exceeding a single segment is at serious risk. > The fact that we've not heard gripes before suggests that no one is > using gigabyte-sized hash indexes. Seems so. > But it seems mighty late in the beta cycle to be making subtle changes > in the smgr API. What I'm inclined to do for now is to hack > _hash_expandtable() to write a page of zeroes at the end of each file > segment when an increment in hashm_ovflpoint causes the logical EOF to > cross segment boundary(s). This is pretty ugly and nonmodular, but it > will fix the bug without risking breakage of any non-hash code. > I'll revisit the cleaner solution once 8.3 devel begins. Comments? Do we think there is hope of improving hash indexes? If not, I'm inclined to remove them rather than to spend time bolstering them. We can remap the keyword as was done with RTREE. It's somewhat embarrassing having an index without clear benefit that can't cope across crashes. We wouldn't accept that in other parts of the software... If there is hope, is there a specific place to look? It would be good to brain dump some starting places for an investigation. Does anybody have a perf test that shows hash indexes beating btrees by any significant margin? (Not saying there isn't one...) I can see the argument that fixed hash indexes would be faster, but there are obviously major downsides to that approach. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com