On Saturday, February 23, 2013, Stefan Andreatta wrote: > > > Thanks Jeff, that helped a lot (as did a careful rereading of > http://www.postgresql.org/docs/9.1/static/monitoring-stats.html and > http://www.postgresql.org/docs/9.1/static/catalog-pg-class.html ;-) > > However, to estimate whether autoanalyze should be triggered, I am still > missing something: the analyze threshold is compared to the "total number > of tuples inserted, updated, or deleted since the last ANALYZE." > (according to > http://www.postgresql.org/docs/9.1/static/routine-vacuuming.html). > > pg_stat_user_tables.n_live tup - pg_class.reltuples should give something > like the sum of rows inserted minus rows deleted since the last ANALYZE. > But according to the documentation we would need the sum of those values. > And we are still missing a number for rows updated since the last analyze. > pg_stat_usert_tables. n_dead_tup, on the other hand, is only set back by > successful VACUUM. autoanalyzing a table with more than 10% dead rows would > therefore keep autoanalyze in a loop until the ratio rises beyond 20% > (default configuration) and autovacuum kicks in. So that wouldn't make a > lot of sense. > > Hi Stefan,
Sorry, I got tunnel vision about the how the threshold was computed, and forgot about the thing it was compared to. There is a "secret" data point in the stats collector called changes_since_analyze. This is not exposed in the pg_stat_user_tables. But I think it should be as I often have wanted to see it. Cheers, Jeff
