Received: from localhost (maia-2.hub.org [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id 1F3BA9FBA95 for ; Wed, 11 Jul 2007 15:52:09 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-maia, port 10024) with ESMTP id 52192-09 for ; Wed, 11 Jul 2007 15:51:54 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from nz-out-0506.google.com (nz-out-0506.google.com [64.233.162.225]) by postgresql.org (Postfix) with ESMTP id 6A7009FBA30 for ; Wed, 11 Jul 2007 15:52:04 -0300 (ADT) Received: by nz-out-0506.google.com with SMTP id l8so1281869nzf for ; Wed, 11 Jul 2007 11:52:01 -0700 (PDT) DKIM-Signature: a=rsa-sha1; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=kyiFS8b8rKK2sZ3EtIvwZaF0VzfjV5OXestzHZOX5U5iAurZfie5GE9FEeRt+R38xGnqTg36BXgx9WTtv9qNPWWeqJjS/Do5rTEGhQAdGgZUfxMTgH2ES9J/CJmYmccC/OQ/JA61b0SJcNw3LemjaX2PWdR/ei1Im1t3PaHAVxE= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=DOLMCnJn7aXxtYsGmNDExRCohzEgcIxsM4/tL8/7erpVHO2ycVjZ5nrJDwc+cqzbfzzKHuvLOUbm3eI3jY5SZp0yfjYpkoY05IjxVnug50TD/axL2NITtkNCd+BR2Ol6+ojS70jb7Z9CwhFZoHjZpMdjw67b6XaZ4DEebazc4mY= Received: by 10.141.15.19 with SMTP id s19mr1716333rvi.1184179921074; Wed, 11 Jul 2007 11:52:01 -0700 (PDT) Received: by 10.140.252.21 with HTTP; Wed, 11 Jul 2007 11:52:01 -0700 (PDT) Message-ID: Date: Wed, 11 Jul 2007 14:52:01 -0400 From: "Alex Deucher" To: "Andreas Kretschmer" Subject: Re: bitmap-index-scan slower than normal index scan Cc: pgsql-performance@postgresql.org In-Reply-To: <20070711183638.GA30872@KanotixBox> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline References: <20070711183638.GA30872@KanotixBox> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.056 tagged_above=0 required=5 tests=AWL=0.474, BAYES_05=-1.11, DNS_FROM_AHBL_RHSBL=0.692 X-Spam-Level: X-Archive-Number: 200707/113 X-Sequence-Number: 25797 On 7/11/07, Andreas Kretschmer wrote: > Hi, > > Okay, i know, not really a recent version: > PostgreSQL 8.1.4 on i386-pc-linux-gnu, compiled by GCC cc (GCC) 3.3.5 (Debian 1:3.3.5-13) > > I have a fresh ANALYZED table with some indexes. > > scholl=*# set enable_bitmapscan=1; > SET > scholl=*# explain analyse select sum(flaeche) from bde_meldungen where maschine=1200 and ab = 347735; > QUERY PLAN > -------------------------------------------------------------------------------------------------------------------------------------------------- > Aggregate (cost=1371.95..1371.96 rows=1 width=8) (actual time=163.788..163.790 rows=1 loops=1) > -> Bitmap Heap Scan on bde_meldungen (cost=1217.69..1371.85 rows=39 width=8) (actual time=163.702..163.758 rows=2 loops=1) > Recheck Cond: ((ab = 347735) AND (maschine = 1200)) > -> BitmapAnd (cost=1217.69..1217.69 rows=39 width=0) (actual time=163.681..163.681 rows=0 loops=1) > -> Bitmap Index Scan on idx_ab (cost=0.00..5.95 rows=558 width=0) (actual time=0.078..0.078 rows=109 loops=1) > Index Cond: (ab = 347735) > -> Bitmap Index Scan on idx_maschine (cost=0.00..1211.49 rows=148997 width=0) (actual time=163.459..163.459 rows=164760 loops=1) > Index Cond: (maschine = 1200) > Total runtime: 163.901 ms > (9 rows) > > > Okay, 163.901 ms with Bitmap Index Scan. > > And now i disable this and runs the same select: > > scholl=*# set enable_bitmapscan=0; > SET > scholl=*# explain analyse select sum(flaeche) from bde_meldungen where maschine=1200 and ab = 347735; > QUERY PLAN > -------------------------------------------------------------------------------------------------------------------------------- > Aggregate (cost=2142.77..2142.78 rows=1 width=8) (actual time=0.229..0.231 rows=1 loops=1) > -> Index Scan using idx_ab on bde_meldungen (cost=0.00..2142.67 rows=39 width=8) (actual time=0.046..0.209 rows=2 loops=1) > Index Cond: (ab = 347735) > Filter: (maschine = 1200) > Total runtime: 0.326 ms > (5 rows) > > Okay, i got a really different plan, but i expected _NOT_ a > performance-boost like this. I expected the opposite. > > > It's not a really problem, i just played with this. But i'm confused > about this... > your results are getting cached. try two queries in a row with the same plan. Alex