Re: incoherent dead tuples between pg_stat_user_tables and pgstattuple?

Luca Ferrari <fluca1978(at)gmail(dot)com> writes:
> running 11.5 I've some misconception about pgstatuple: it seems it
> lags behind normal statistics.

n_live_tup/n_dead_tup should not be thought to be better than
approximations. Most operations adjust them only incrementally,
and messages to the stats collector can get dropped entirely
under heavy load, causing deltas to go missing. There are
also race conditions involved in some update scenarios.

> Even after running an ANALYZE, pgstattuple provides the same results.
> After a VACUUM FULL ANALYZE the world is as I would expect it to be:

VACUUM FULL is one of very few operations that reset those counters
to guaranteed-correct values (and I'm not sure the guarantee is
ironclad even in that case).

It's very hard to improve on this without giving up the desirable
ability to have concurrent table updates. If you really want an
accurate row count, COUNT(*) or pgstattuples will give you a more
reliable estimate ... at much higher cost, of course.

regards, tom lane