Re: VACUUM/ANALYZE double-count the backend's own pending table stats

Hi Bertrand,

Thanks for the pointer. Yes, the VACUUM example in that thread has the
same cause. I agree with keeping stats updates batched, but I think
we can avoid this particular double count without changing that policy.

The patch only adjusts the pending counters of the backend running
VACUUM or ANALYZE. When it reports new live/dead estimates, it discards
its own pending deltas from earlier transactions, rather than adding
them on top of those estimates at the next flush. This does not require
more frequent flushes or access to another backend's pending stats.
The cumulative insert/update/delete counters are preserved.

pgstat_report_analyze() already subtracts the current transaction's
changes to avoid double counting at commit, and also subtracts pending
delta_dead_tuples. This is the same local accounting problem for changes
committed earlier by that backend. For mod_since_analyze, the patch
also discards the pending changed_tuples when ANALYZE resets the shared
counter. Otherwise an INSERT of N rows followed by ANALYZE can leave
n_mod_since_analyze at N after the pending stats are flushed, despite
there having been no subsequent changes.

Other backends' unflushed deltas can still make the estimates inaccurate.
I agree that solving that would be a different and much larger change.

> So maybe only the TRUNCATE case deserves a fix?

I'd still consider the local VACUUM/ANALYZE correction worthwhile. It
avoids reintroducing changes already covered by that backend's own
maintenance operation, including changes that can trigger another
autoanalyze or autovacuum, while retaining the existing flush policy.
I can separate the TRUNCATE fix so it can be reviewed independently.

--
Ilya Staroverov