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