Re: Second attempt, roll your own autovacuum

Glen Parker wrote:
> Alvaro Herrera wrote:
> >Glen Parker wrote:
> >
> >>The trouble now is, I don't see how to reset the statistics. My
> >>assumption was that vacuum did it, but that appears to be false. How
> >>does autovacuum do it? Can I do it with SQL?
> >
> >Huh, reset what statistics? Autovacuum does not reset anything. What
> >statistics are you using? The number of dead tuples _should_ show as
> >zero on the stat system after a vacuum, certainly.
>
> pg_stat_user_tables.[n_tup_ins|n_tup_upd|n_tup_del]. Hmm maybe I'm
> doing this all wrong then. Is there a way to find the estimated dead
> tuples from SQL, the same number autovacuum looks at?

Hmm, I thought the number of dead tuples was being exposed in
pg_stat_user_tables but evidently not. I think this is an oversight
which we could "fix" in 8.3. (For a current release I guess you could
install your own function, it shouldn't be too difficult to code it).

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support