X-Original-To: pgsql-general-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 8CF7D54CF8; Wed, 11 May 2005 10:59:21 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 13853-06; Wed, 11 May 2005 13:59:20 +0000 (GMT) Received: from stark.xeocode.com (stark.xeocode.com [216.58.44.227]) by svr1.postgresql.org (Postfix) with ESMTP id 0349D54CD6; Wed, 11 May 2005 10:59:19 -0300 (ADT) Received: from localhost ([127.0.0.1] helo=stark.xeocode.com) by stark.xeocode.com with smtp (Exim 3.36 #1 (Debian)) id 1DVrjt-0007jS-00; Wed, 11 May 2005 09:59:05 -0400 To: Neil Conway Cc: Greg Stark , Tom Lane , "Jim C. Nasby" , Christopher Petrilli , Ying Lu , pgsql-general@postgresql.org, pgsql-performance@postgresql.org Subject: Re: [PERFORM] "Hash index" vs. "b-tree index" (PostgreSQL References: <427F6853.90308@cs.concordia.ca> <427F757A.8000407@samurai.com> <59d991c405050908271b55e673@mail.gmail.com> <427F8321.5030702@samurai.com> <20050509163423.GH35026@decibel.org> <427F9211.2090008@samurai.com> <20050509165133.GJ35026@decibel.org> <427FFCD3.1010803@samurai.com> <4407.1115698257@sss.pgh.pa.us> <428037A2.4060304@samurai.com> <6686.1115700888@sss.pgh.pa.us> <878y2nsj7i.fsf@stark.xeocode.com> <428209BA.8070907@samurai.com> In-Reply-To: <428209BA.8070907@samurai.com> From: Greg Stark Organization: The Emacs Conspiracy; member since 1992 Date: 11 May 2005 09:59:05 -0400 Message-ID: <87d5rxrhhy.fsf@stark.xeocode.com> Lines: 17 User-Agent: Gnus/5.09 (Gnus v5.9.0) Emacs/21.3 MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.006 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200505/560 X-Sequence-Number: 77908 Neil Conway writes: > I'm posting mainly because I wasn't sure what to do to avoid false positives in > the case of hash collisions. In the hash AM code it is somewhat awkward to > fetch the pointed-to heap tuple and recheck the scankey.[1] I just did the > first thing that came to mind -- I marked all the hash AM opclasses as "lossy", > so the index qual is rechecked. This works, but suggestions for a better way to > do things would be welcome. I would have thought that would be the only way worth considering. Consider for example a query involving two or more hash indexes and the new bitmap indexscan plan. You don't want to fetch the tuples if you can eliminate them using one of the other indexes. -- greg