Received: from localhost (maia-2.hub.org [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id 34F6C9FB86E for ; Tue, 13 Feb 2007 12:33:05 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-new, port 10024) with ESMTP id 23491-09 for ; Tue, 13 Feb 2007 12:32:58 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4 Received: from wr-out-0506.google.com (wr-out-0506.google.com [64.233.184.231]) by postgresql.org (Postfix) with ESMTP id 2880D9FB8B3 for ; Tue, 13 Feb 2007 12:32:59 -0400 (AST) Received: by wr-out-0506.google.com with SMTP id i7so403472wra for ; Tue, 13 Feb 2007 08:32:58 -0800 (PST) DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:to:subject:mime-version:content-type:content-transfer-encoding:content-disposition; b=O1SseaYqyvCZl0Zv5LDe+I1AySJxP6NtL3g7G9qoWiWNgxgsuJMg/kUc+KOLgRCPDZg+bG1j2QulqYpbWcl/5wQoejXmZwmUb9GPelqw9oFYoviey1mtWcfOmWTd6V1Otvae1py6H4zIvbwiWzE2TIbYI4rQRHjhudigPbodHOE= Received: by 10.90.119.15 with SMTP id r15mr81543agc.1171384378521; Tue, 13 Feb 2007 08:32:58 -0800 (PST) Received: by 10.90.113.7 with HTTP; Tue, 13 Feb 2007 08:32:58 -0800 (PST) Message-ID: <1d4e0c10702130832i35484abfmb229f5d0b4d9223b@mail.gmail.com> Date: Tue, 13 Feb 2007 17:32:58 +0100 From: "Guillaume Smet" To: pgsql-performance@postgresql.org Subject: Question about Bitmap Heap Scan/BitmapAnd MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=BAYES_50, SPF_PASS X-Spam-Level: X-Archive-Number: 200702/126 X-Sequence-Number: 23180 Hi all, I'm currently working on optimizing a couple of queries. While studying the EXPLAIN ANALYZE output of a query, I found this Bitmap Heap Scan node: -> Bitmap Heap Scan on lieu l (cost=12.46..63.98 rows=53 width=94) (actual time=35.569..97.166 rows=78 loops=1) Recheck Cond: ('(4190964.86112204, 170209.656489245, 4801644.52951672),(4194464.86111106, 173709.656478266, 4805144.52950574)'::cube @ (ll_to_earth((wgslat)::double precision, (wgslon)::double precision))::cube) Filter: (parking AND (numlieu <> 0)) -> BitmapAnd (cost=12.46..12.46 rows=26 width=0) (actual time=32.902..32.902 rows=0 loops=1) -> Bitmap Index Scan on idx_lieu_earth (cost=0.00..3.38 rows=106 width=0) (actual time=30.221..30.221 rows=5864 loops=1) Index Cond: ('(4190964.86112204, 170209.656489245, 4801644.52951672),(4194464.86111106, 173709.656478266, 4805144.52950574)'::cube @ (ll_to_earth((wgslat)::double precision, (wgslon)::double precision))::cube) -> Bitmap Index Scan on idx_lieu_parking (cost=0.00..8.83 rows=26404 width=0) (actual time=0.839..0.839 rows=1095 loops=1) Index Cond: (parking = true) What surprises me is that "parking" is in the filter and not in the Recheck Cond whereas it's part of the second Bitmap Index Scan of the Bitmap And node. AFAIK, BitmapAnd builds a bitmap of the pages returned by the two Bitmap Index Scans so I supposed it should append both Index Cond in the Recheck Cond. Is there a reason why the second Index Cond in the filter? Does it make a difference in terms of performance (I suppose no but I'd like to have a confirmation)? Thanks. -- Guillaume