Re: Large DB

Manfred Koizar <mkoi-pg(at)aon(dot)at> writes:
> Hackers, what could update reltuples, but not relpages?

Nothing --- they are always updated together. One possibility is that
the 4M pages and 290M rows numbers really do go together (for about 112
bytes/row) and that the table has since grown, or perhaps merely bloated
due to lack of vacuuming of updated rows.

A different line of thought is that they were updated together, but the
relpages estimate was accurate while reltuples was not. ANALYZE knows
the actual table size in pages (because it asks the OS) but reltuples is
extrapolated from an average of the number of live tuples on the pages
ANALYZE looks at. It is possible for ANALYZE to be fooled badly if, for
instance, there are lots and lots of dead rows near the start of the
table. (Lack of regular vacuuming would certainly improve the odds of
this happening...)

Note that VACUUM is not subject to this error because it has to grovel
over every page anyway. So either "VACUUM" or "VACUUM ANALYZE" will
give you a known-good reltuples, it's only standalone "ANALYZE" that
has a risk of estimation error.

regards, tom lane