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