Re: Much Ado About COUNT(*)

On Wed, Jan 12, 2005 at 14:09:07 -0700,
"Jonah H. Harris" <jharris(at)tvi(dot)edu> wrote:

Please keep stuff posted to the list so that other people can contribute
and learn from the discussion unless there is a particular reason to
limited who is involved in the discussion.

> Bruno,
>
> Thanks for the information. I was told that PostgreSQL couldn't use
> index scans for count(*) because of the visibility issue. Has something
> changed or was I told incorrectly?

It isn't that it can't, it is that for cases where you are counting more
than a few percent of a table, it will be faster to use a sequential
scan. Part of the reason is that for any hits you get in the index, you
have to check in the table to make sure the current transaction can see
the current tuple. Even if you could just get away with using just an
index scan you are only going to see a constant factor speed up with
probably not too big of a constant.

Perhaps you think that the count is somehow saved in the index so that
you don't have to scan through the whole index to get the number of
rows in a table? That isn't the case, but is what creating a materialized
view would effectively do for you.