Re: autoanalyze criteria

On Fri, Feb 22, 2013 at 5:57 AM, Stefan Andreatta
<s.andreatta@synedra.com>wrote:

> Hi,
>
> If I understand http://www.postgresql.org/**docs/9.1/static/routine-**
> vacuuming.html<http://www.postgresql.org/docs/9.1/static/routine-vacuuming.html>correctly, the autovacuum threshold
incould be estimated like this in 
> PostgreSQL 9.1:
>
> SELECT pg_stat_user_tables.relname,
>      pg_stat_user_tables.n_dead_**tup,
>      CAST(current_setting('**autovacuum_vacuum_threshold') AS bigint)
>          + (CAST(current_setting('**autovacuum_vacuum_scale_**factor') AS
> numeric)
>             * pg_class.reltuples) AS av_threshold
>  FROM pg_stat_user_tables
>      JOIN pg_class on pg_stat_user_tables.relid = pg_class.oid
>  ORDER BY 1;
>
> If pg_stat_user_tables.n_dead_tup exceeds av_threshold autovacuum should
> kick in. Obviously, that does rely on up-to-date statistics. Is that how it
> is actually done?
>

Pretty much, yes.  With the caveat that table storage settings can override
the global settings.


>
> 2nd question: because pg_stat_user_tables.n_dead_tup is itself estimated
> by ANALYZE it cannot be used as a criterion for the next autoanalyze run, I
> think.


n_dead_tup is updated by the stats collector, not by ANALYZE.

Cheers,

Jeff