Determining last auto vacuum / analyze

Hi all,

According to http://heatware.net/databases/how-when-postgres-tables-auto-vacuum-analyze/ (August 2010), running:

select relname,last_vacuum, last_autovacuum, last_analyze, last_autoanalyze from pg_stat_user_tables;

should tell me which tables have been auto vacuumed and auto analyzed. My schema has >300 tables, wheighs in at ~650 GiB on-disk and compresses to 70 GiB as a plain SQL dump. Running the above query + "WHERE last_autoanalyze is not null or last_autovacuum is not null" only returns 38 rows?! I see the autovacuum daemon work when polling pg_stat_activity. Something doesn't jive here...

I have not touched the autovacuum settings at all, so everything is at the default values (see details at bottom).

The application is on-line analytics with long reporting queries. 3/hour I import new data. Once a day, I rollup the raw values into summary tables and run reports on the summary values. One of the steps in each of the import and summary scripts is to ANALYZE the tables (not VACUUM, plain ANALYZE).

I suspect autovacuum / autoanalyze doesn't kick in frequently enough. Would you say this is true? What would be recommended settings given the above? First thing I'll do is set log_autovacuum_min_duration to 0 to see what's really going on.

Thanks!
François

> SELECT version(); -- Stock Ubuntu 12.04 PostgreSQL installed from apt
PostgreSQL 9.1.8 on x86_64-unknown-linux-gnu, compiled by gcc (Debian 4.7.2-5) 4.7.2, 64-bit

$ cat /etc/postgresql/9.1/main/postgresql.conf
data_directory = '/var/lib/postgresql/9.1/main'

listen_addresses = '*'
port = 5432
max_connections = 120

shared_buffers = 8GB
work_mem = 512MB

fsync = on
synchronous_commit = off
checkpoint_segments = 96
checkpoint_timeout = 10min
checkpoint_completion_target = 0.9
effective_cache_size = 12GB
maintenance_work_mem = 2GB

hot_standby = on
hot_standby_feedback = on

> SHOW all;
autovacuum | on | Starts the autovacuum subprocess.
autovacuum_analyze_scale_factor | 0.1 | Number of tuple inserts, updates or deletes prior to analyze as a fraction of reltuples.
autovacuum_analyze_threshold | 50 | Minimum number of tuple inserts, updates or deletes prior to analyze.
autovacuum_freeze_max_age | 200000000 | Age at which to autovacuum a table to prevent transaction ID wraparound.
autovacuum_max_workers | 3 | Sets the maximum number of simultaneously running autovacuum worker processes.
autovacuum_naptime | 1min | Time to sleep between autovacuum runs.
autovacuum_vacuum_cost_delay | 20ms | Vacuum cost delay in milliseconds, for autovacuum.
autovacuum_vacuum_cost_limit | -1 | Vacuum cost amount available before napping, for autovacuum.
autovacuum_vacuum_scale_factor | 0.2 | Number of tuple updates or deletes prior to vacuum as a fraction of reltuples.
autovacuum_vacuum_threshold | 50 | Minimum number of tuple updates or deletes prior to vacuum.