Received: from localhost (wm.hub.org [200.46.204.128]) by postgresql.org (Postfix) with ESMTP id BBA479F9F0F; Fri, 17 Nov 2006 10:27:15 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.128]) (amavisd-new, port 10024) with ESMTP id 35825-03; Fri, 17 Nov 2006 14:27:07 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey- X-Greylist: from auto-whitelisted by SQLgrey- Received: from svr4.postgresql.org (svr4.postgresql.org [66.98.251.159]) by postgresql.org (Postfix) with ESMTP id 190EB9F9CF9; Fri, 17 Nov 2006 10:27:06 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.3 X-Greylist: domain auto-whitelisted by SQLgrey-1.7.3 Received: from is.rice.edu (is.rice.edu [128.42.42.24]) by svr4.postgresql.org (Postfix) with ESMTP id 4C7235AF031; Fri, 17 Nov 2006 14:27:05 +0000 (GMT) Received: from scan2.mail.rice.edu (scan2.mail.rice.edu [128.42.59.161]) by is.rice.edu (Postfix) with ESMTP id 57B254182B; Fri, 17 Nov 2006 08:26:58 -0600 (CST) Received: from is.rice.edu ([128.42.42.24]) by scan2.mail.rice.edu (scan2.mail.rice.edu [128.42.59.161]) (amavisd-new, port 10024) with ESMTP id 22007-07; Fri, 17 Nov 2006 08:26:57 -0600 (CST) Received: by is.rice.edu (Postfix, from userid 18612) id 8A0C441819; Fri, 17 Nov 2006 08:26:57 -0600 (CST) Date: Fri, 17 Nov 2006 08:26:57 -0600 From: Kenneth Marshall To: Simon Riggs Cc: Tom Lane , Balazs Nagy , pgsql-bugs@postgresql.org, pgsql-performance@postgresql.org Subject: Re: [PERFORM] BUG #2737: hash indexing large tablefails, while btree of same index works Message-ID: <20061117142657.GQ12572@it.is.rice.edu> 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> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <1163761151.27956.592.camel@silverbirch.site> User-Agent: Mutt/1.4.2i X-Virus-Scanned: by amavis-2.2.1 at scan2.mail.rice.edu X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200611/137 X-Sequence-Number: 16272 On Fri, Nov 17, 2006 at 10:59:11AM +0000, Simon Riggs wrote: > 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... > While I understand that there are currently serious problems in terms of recovery with the hash index as it stands currently, it is theoretically possible to get a result back with a single I/O probe from a hash and that is not the case with btree indexes. At the top end of the performance curve, every little bit that can be done to minimize actual I/Os is needed. I certainly hold out some hope that they can improved. I would like to see them still included. Once they are gone, it will be much harder to ever add them back. Ken Marshall