Thread: SELECT COUNT(*) does a scan?

When I do an EXPLAIN SELECT COUNT(*) FROM tablename, I noted that it
does a table scan.  I thought PG had some sort of table stat that kept
track of the current number of rows in a table, but that doesn't appear
to always be the case.

It seems that right after a VACUUM ANALYZE, that command is very fast
(on a table with 100,000+ rows), but it can also get quite slow, as if a
table scan is taking place.

Does this make sense?  Is there an algorithm that says to use the stats
from analyze only until sufficient updates/inserts/deletes have taken
place to make them "out of date"?

David

David Wall wrote:
> When I do an EXPLAIN SELECT COUNT(*) FROM tablename, I noted that it
> does a table scan.  I thought PG had some sort of table stat that kept
> track of the current number of rows in a table, but that doesn't appear
> to always be the case.

It's not the case, and this is a FAQ -- search archives.postgresql.org
for more details (the short version is that maintaining a row count
doesn't work well with MVCC).

> It seems that right after a VACUUM ANALYZE, that command is very fast (on a table with 100,000+ rows), but it can
alsoget quite slow, as if a table scan is taking place. 
> Does this make sense?  Is there an algorithm that says to use the stats from analyze only until sufficient
updates/inserts/deleteshave taken place to make them "out of date"? 

Most likely a VACUUM ANALYZE is just pulling the whole table into cache,
so there is less disk I/O needed to do the scan.

-O

See Oliver's post:

Additionally you can get count to use an index, but you need a where
clause.

Dave
On 8-Sep-05, at 11:22 AM, David Wall wrote:

> When I do an EXPLAIN SELECT COUNT(*) FROM tablename, I noted that
> it does a table scan.  I thought PG had some sort of table stat
> that kept track of the current number of rows in a table, but that
> doesn't appear to always be the case.
>
> It seems that right after a VACUUM ANALYZE, that command is very
> fast (on a table with 100,000+ rows), but it can also get quite
> slow, as if a table scan is taking place.
> Does this make sense?  Is there an algorithm that says to use the
> stats from analyze only until sufficient updates/inserts/deletes
> have taken place to make them "out of date"?
>
> David
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>
>