Received: from localhost (unknown [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id 927FE2E41BF for ; Thu, 20 Dec 2007 18:07:06 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-maia, port 10024) with ESMTP id 26543-02 for ; Thu, 20 Dec 2007 18:06:55 -0400 (AST) Received: from wa-out-1112.google.com (wa-out-1112.google.com [209.85.146.176]) by postgresql.org (Postfix) with ESMTP id 1B3132E4200 for ; Thu, 20 Dec 2007 18:06:57 -0400 (AST) Received: by wa-out-1112.google.com with SMTP id m38so74397waf.5 for ; Thu, 20 Dec 2007 14:06:55 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:to:subject:mime-version:content-type; bh=rk7eK0f5A+fNTzUAy/67eyT+U5Z1JsaBgXjdr2WON0k=; b=D2BR6Z/t6CV7rOMXSCy3rBzmzYiw831u0V9qvfP3P/eDkONI/TjYbOl4XfNdThsfImADB75vTM/2JcgQ1YAkNJYMjVZSVS3uv2AYuZ0FfyLDw6moBxGpI9+26k/Zk2sICxXJG6ZmFsy8iLXirYKWOxq9lhwdyl+fWaBnep7KsVE= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:to:subject:mime-version:content-type; b=N4Ewyb3eWu6tqM1Kwzt5bKDIl2knNvHufkMA6gP25Ftq2opFgvbBscyyDAhTrr5EFd+b+EHyk02RVs4T+aoEpsGD5o5a4ka0oMb9O+3jlELxWd4ILb7sPuROjd1mg7NEEWsevFznYPw/7UBkddzj75slL4TKMTdqN4l8z1d4FrM= Received: by 10.114.80.4 with SMTP id d4mr539806wab.44.1198188415103; Thu, 20 Dec 2007 14:06:55 -0800 (PST) Received: by 10.115.78.10 with HTTP; Thu, 20 Dec 2007 14:06:55 -0800 (PST) Message-ID: <4a5f56930712201406r3a3b4810v96f30ef4c2fb6310@mail.gmail.com> Date: Thu, 20 Dec 2007 17:06:55 -0500 From: "S Golly" To: pgsql-performance@postgresql.org Subject: performance index scan vs bitmap-seq scan. MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_11635_33151352.1198188415101" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200712/329 X-Sequence-Number: 28145 ------=_Part_11635_33151352.1198188415101 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline The server is running 8.2.5 FreeBSD 6.1 with 3 GB of RAM. I have a table with over 100M rows. I have a unique index (primary key) on column name called aid. The select count(aid) .. does a Bitmap heap scan when the right side condition is above 100,000,000 (if i take one zero off it does a pure index scan). My question : why is the optimizer choosing an Bitmap Heap Scan when count can be done with index. When i set the bitmap scan to off, it selects an seq scan, after which i turn the seq scan off , then it does index scan only but it takes 8 minutes longer than doing a index scan+bitmap scan. Any insight is appreciated. thank you ! g explain select count(aid) from topcat.aid where aid >= 10000000 and aid <= 100000000; QUERY PLAN -------------------------------------------------------------------------------------------- Aggregate (cost=2786143.42..2786143.43 rows=1 width=4) -> Bitmap Heap Scan on aid (cost=142949.13..2742507.95 rows=17454188 width=4) Recheck Cond: ((aid >= 10000000) AND (aid <= 100000000)) -> Bitmap Index Scan on idx_aid_aid (cost=0.00..142949.13rows=17454188 width=0) Index Cond: ((aid >= 10000000) AND (aid <= 100000000)) (5 rows) explain select count(aid) from topcat.aid where aid >= 100000 and aid <= 100000000; QUERY PLAN -------------------------------------------------------------------------------------------- Aggregate (cost=2786143.42..2786143.43 rows=1 width=4) -> Bitmap Heap Scan on aid (cost=142949.13..2742507.95 rows=17454188 width=4) Recheck Cond: ((aid >= 100000) AND (aid <= 100000000)) -> Bitmap Index Scan on idx_aid_aid (cost=0.00..142949.13rows=17454188 width=0) Index Cond: ((aid >= 100000) AND (aid <= 100000000)) (5 rows) explain select count(aid) from topcat.aid where aid >= 1000000 and aid <= 100000000; QUERY PLAN -------------------------------------------------------------------------------------------- Aggregate (cost=2786143.42..2786143.43 rows=1 width=4) -> Bitmap Heap Scan on aid (cost=142949.13..2742507.95 rows=17454188 width=4) Recheck Cond: ((aid >= 1000000) AND (aid <= 100000000)) -> Bitmap Index Scan on idx_aid_aid (cost=0.00..142949.13rows=17454188 width=0) Index Cond: ((aid >= 1000000) AND (aid <= 100000000)) (5 rows) explain select count(aid) from topcat.aid where aid >= 1000000 and aid <= 10000000; QUERY PLAN ----------------------------------------------------------------------------- Aggregate (cost=5.58..5.59 rows=1 width=4) -> Index Scan using idx_aid_aid on aid (cost=0.00..5.58 rows=1 width=4) Index Cond: ((aid >= 1000000) AND (aid <= 10000000)) ------=_Part_11635_33151352.1198188415101 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline The server is running 8.2.5 FreeBSD 6.1 with 3 GB of RAM.
I have a table with over 100M rows. I have a unique index (primary key) on column name called aid.
The select count(aid) .. does a Bitmap heap scan when the right side condition is above 100,000,000 (if i take one zero off it does a pure index scan).
My question : why is the optimizer choosing an Bitmap Heap Scan when count can be done with index.

When i set the bitmap scan to off, it selects an seq scan, after which i turn the seq scan off , then it does index scan only but it takes 8 minutes longer than doing a index scan+bitmap scan.

Any insight is appreciated.
thank you !
g





explain select count(aid) from topcat.aid where aid >= 10000000 and aid <= 100000000;
                                         QUERY PLAN
--------------------------------------------------------------------------------------------
 Aggregate  (cost=2786143.42..2786143.43 rows=1 width=4)
   ->  Bitmap Heap Scan on aid  (cost=142949.13..2742507.95 rows=17454188 width=4)
         Recheck Cond: ((aid >= 10000000) AND (aid <= 100000000))
         ->  Bitmap Index Scan on idx_aid_aid  (cost=0.00..142949.13 rows=17454188 width=0)
               Index Cond: ((aid >= 10000000) AND (aid <= 100000000))
(5 rows)

 explain select count(aid) from topcat.aid where aid >= 100000 and aid <= 100000000;
                                         QUERY PLAN
--------------------------------------------------------------------------------------------
 Aggregate  (cost=2786143.42..2786143.43 rows=1 width=4)
   ->  Bitmap Heap Scan on aid  (cost=142949.13..2742507.95 rows=17454188 width=4)
         Recheck Cond: ((aid >= 100000) AND (aid <= 100000000))
         ->  Bitmap Index Scan on idx_aid_aid  (cost=0.00..142949.13 rows=17454188 width=0)
               Index Cond: ((aid >= 100000) AND (aid <= 100000000))
(5 rows)

 explain select count(aid) from topcat.aid where aid >= 1000000 and aid <= 100000000;
                                         QUERY PLAN
--------------------------------------------------------------------------------------------
 Aggregate  (cost= 2786143.42..2786143.43 rows=1 width=4)
   ->  Bitmap Heap Scan on aid  (cost=142949.13..2742507.95 rows=17454188 width=4)
         Recheck Cond: ((aid >= 1000000) AND (aid <= 100000000))
         ->  Bitmap Index Scan on idx_aid_aid  (cost= 0.00..142949.13 rows=17454188 width=0)
               Index Cond: ((aid >= 1000000) AND (aid <= 100000000))
(5 rows)

explain select count(aid) from topcat.aid where aid >= 1000000 and aid <= 10000000;
                                 QUERY PLAN
-----------------------------------------------------------------------------
 Aggregate  (cost=5.58..5.59 rows=1 width=4)
   ->  Index Scan using idx_aid_aid on aid  (cost= 0.00..5.58 rows=1 width=4)
         Index Cond: ((aid >= 1000000) AND (aid <= 10000000))


------=_Part_11635_33151352.1198188415101--