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.