Re: autoanalyze criteria

On Fri, Feb 22, 2013 at 5:57 AM, Stefan Andreatta
<s(dot)andreatta(at)synedra(dot)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 in could 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