Re: "analyze" putting wrong reltuples in pg_class

Ron Mayer <ron(at)intervideo(dot)com> writes:
> logs2=# select pgstattuple('e_ip_full');
> NOTICE: physical length: 293.84MB live tuples: 1697755 (169.26MB, 57.60%)
> dead tuples: 0 (0.00MB, 0.00%) free/reusable space: 110.84MB (37.72%)
> overhead: 4.67%
> pgstattuple
> -------------
> 0
> (1 row)

38% overhead space is awfully high. I am betting that your max_fsm_pages
configuration parameter needs to be kicked up --- it would seem that
your system is failing to reclaim free space effectively. (Check the
mail list archives for recent discussions of this point.)

What I think is happening is that the free space is not evenly
distributed but is concentrated near the start of the table. This
causes ANALYZE to make a faulty estimate of the average number of live
tuples per page, because its initial scan will see mostly free space
and not very many live tuples on the first few hundred pages. So it
extrapolates a too-small estimate for the total number of tuples.

It would probably be good at some point to make ANALYZE more robust,
but your immediate problem is too much wasted space. I'd recommend
bumping up max_fsm_pages to some reasonable fraction of your total
database size, and then doing a VACUUM FULL to get back the space leaked
so far.

regards, tom lane