Re: autoanalyze criteria

On 02/23/2013 05:10 PM, Jeff Janes wrote:
> 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

Sounds like a very good idea to me - any way I could help to make such a
thing happen?

Stefan