At some point after we upgraded to Postgres 7.2 from 7.1, we noticed that
VACUUM ANALYZE wasn't updating pg_class.reltuples. It only happened for
systems where we'd upgraded; a fresh installation or a new database was
not affected. So we started using
# vacuum TABLENAME; analyze TABLENAME;
where appropriate. Suddenly (like within the last week or so?), ANALYZE
isn't working properly (it is recording pg_class.reltuples far lower than
the actual row count).
This is just way too weird:
$ psql
# select count(*) from stat_fetch;
count
--------
143243
(1 row)
(Big table, two indexes.)
# analyze stat_fetch;
ANALYZE
(That should update all the stats, right?)
# select relname, reltuples from pg_class
# where relname = 'stat_fetch';
relname | reltuples
------------+-----------
stat_fetch | 3419
(1 row)
(Wha? Huh?)
# vacuum stat_fetch;
VACUUM
(That should NOT impact the stats, right?)
# select relname, reltuples from pg_class
# where relname = 'stat_fetch';
relname | reltuples
------------+-----------
stat_fetch | 179955
(1 row)
(Hmm, a bit bigger this time?!? And why so different
from the count(*)??)
# analyze stat_fetch;
ANALYZE
(Just to make sure it's not a one-time thing)
# select relname, reltuples from pg_class
# where relname = 'stat_fetch';
relname | reltuples
------------+-----------
stat_fetch | 3422
(1 row)
(I give up, I need a strong beverage of some sort ...)
--
Jeff Boes vox 616.226.9550 ext 24
Database Engineer fax 616.349.9076
Nexcerpt, Inc. http://www.nexcerpt.com
...Nexcerpt... Extend your Expertise