Re: Yet Another (Simple) Case of Index not used

On Wed, 9 Apr 2003, Martijn van Oosterhout wrote:

> On Tue, Apr 08, 2003 at 12:57:16PM -0700, Denis wrote:
> > The query I am trying to do (fast) is:
> >
> > select count(*) from addresses;
> >
> > This takes more than a second to complete, because, as the 'explain' command
> > shows me,
> > the index created on 'addresses' is not used, and a seq scan is being used.
> > One would assume that the creation of an index would allow the counting of
> > the number of entries in a table to be instantanous?
>
> Incorrect assumption. select count(*) can produce different results in
> different backends depending on the current state of the active
> transactions.

Some thoughts:

Select count(*) is often applied to views, and may take some time
depending on the underlying query.

However, for a single table, I would have thought that if there are no
write locks or open transactions for the table, the index would return a
faster result than a scan? Is there room for some optimisation here?

Does count(<primary_key>) work faster, poss using the unique index on the
key (for non-composite keys)?

Cheers
Brent Wood