Re: autoanalyze criteria

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