On Mon, Aug 28, 2017 at 8:43 AM, Don Seiler <don@seiler.us> wrote:
PostgreSQL 9.2.18. I have a table with 71M rows (via select count). In pg_stat_activity, n_live_tups is 170K, n_dead_tups is slightly more than that (203K). The autovacuum parameter is on, the scale factor is 0.2 (default) and threshold is 50 (default), autovacuum_max_workers is 3. There are no custom autovacuum thresholds set for this table. The last_autovacuum field is null.
Obviously I would expect autovacuum/autoanalyze to kick in for this table so the stats aren't getting so far out of whack from reality. When I query pg_stat_activity, I never see any autovacuum jobs running, so it isn't a matter of too much work from what I can tell.
autovacuum <> autoanalyze - even though the same underlying executable performs both tasks. If you are questioning why autoanalyze is not running you need to inspect the autoanalyze fields, not the autovacuum ones (both the indicators and the source data elements). Seeing an auto-analyze in pg_stat_activity would be pretty unlikely given how quickly they tend to run.
The question I have is how did this table get to be 71M records large? One bulk load or 1M records inserted a day for 2 months? How often do those records change once inserted?
I'm done for now but will leave with my own thought. The statistical counts can be reset, and I assume analyze must reset the counts that it uses as input once it has completed. But I can imagine a well-meaning but misguided activity monitoring script that wants to see (e.g. daily) volume choosing to issue a "pg_stat_reset()" daily just after capturing the count from the previous day. For sufficiently large tables its likely auto-analyze would never be run again because the magnitude of the counts never gets high enough.