Upto PostgreSQL 9.0,We need to gather auto-vacuum stats like number of times auto-vacuum,manual vacuum kicked off on each tables resides on Databases from Database server log files. These Information really helpful to tune auto-vacuum settings in cluster level.
Now,PostgreSQL 9.1 has been added new columns I.e vacuum_count,autovacuum_count,autoanalyze_count,analyze_count in pg_stat_*_tables view and these columns will shows the number of times vacuumed and analyzed in each table available in database.
vacuum_count -- Number of times it has been vacuumed manually
Autovacuum_count -- Number of times it has been vacuumed by the autovacuum daemon
Analyze_count -- Number of times it has been analyzed manually
autoanalyze_count -- Number of time it has been analyzed by the autovacuum daemon.
postgres=# select * from pg_stat_all_tables;
relid | 2617
schemaname | pg_catalog
relname | pg_operator
seq_scan | 0
seq_tup_read | 0
idx_scan | 87
idx_tup_fetch | 228
n_tup_ins | 2
n_tup_upd | 2
n_tup_del | 0
n_tup_hot_upd | 2
n_live_tup | 708
n_dead_tup | 0
last_vacuum | 2012-03-07 17:46:00.899226+05:30
last_autovacuum |
last_analyze | 2012-03-07 17:46:00.903273+05:30
last_autoanalyze |
vacuum_count | 5
autovacuum_count | 0
analyze_count | 5
autoanalyze_count | 0
