pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: S Golly <gollykai@gmail.com>
To: pgsql-performance@postgresql.org
Subject: performance index scan vs bitmap-seq scan.
Date: Thu, 20 Dec 2007 17:06:55 -0500
Message-ID: <4a5f56930712201406r3a3b4810v96f30ef4c2fb6310@mail.gmail.com> (raw)

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))

view thread (2+ messages)  latest in thread

Message-ID: <4a5f56930712201406r3a3b4810v96f30ef4c2fb6310@mail.gmail.com>
Permalink:  ../4a5f56930712201406r3a3b4810v96f30ef4c2fb6310@mail.gmail.com/
Also on:    postgresql.org/message-id/4a5f56930712201406r3a3b4810v96f30ef4c2fb6310@mail.gmail.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: gollykai@gmail.com
  Subject: Re: performance index scan vs bitmap-seq scan.
  In-Reply-To: <4a5f56930712201406r3a3b4810v96f30ef4c2fb6310@mail.gmail.com>
  References: <4a5f56930712201406r3a3b4810v96f30ef4c2fb6310@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox