pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedperformance index scan vs bitmap-seq scan.
2+ messages / 2 participants
[nested] [flat]
* performance index scan vs bitmap-seq scan.
@ 2007-12-20 22:06 S Golly <gollykai@gmail.com>
0 siblings, 1 reply; 2+ messages in thread
From: S Golly @ 2007-12-20 22:06 UTC (permalink / raw)
To: pgsql-performance
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))
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: performance index scan vs bitmap-seq scan.
@ 2007-12-20 22:15 Scott Marlowe <scott.marlowe@gmail.com>
parent: S Golly <gollykai@gmail.com>
0 siblings, 0 replies; 2+ messages in thread
From: Scott Marlowe @ 2007-12-20 22:15 UTC (permalink / raw)
To: S Golly <gollykai@gmail.com>; +Cc: pgsql-performance
On Dec 20, 2007 4:06 PM, S Golly <gollykai@gmail.com> wrote:
> 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.
Because count can't be done with the index alone.
In pgsql the visibility info is in the table itself, so even if it can
hit the index, it has to go back and hit the table to be sure if the
tuple is visible.
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2007-12-20 22:15 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-12-20 22:06 performance index scan vs bitmap-seq scan. S Golly <gollykai@gmail.com>
2007-12-20 22:15 ` Scott Marlowe <scott.marlowe@gmail.com>
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