Re: How can I tell if I'm autovacuuming?

On Sat, 22 May 2010, Joshua D. Drake wrote:

> On Sat, 2010-05-22 at 09:51 -0400, Doug Gorley wrote:
>> From what I see in the docs, these three settings in postgresql.conf
>> should be enough for PostgreSQL (8.2) to autovacuum with the default
>> settings:
>>
>> autovacuum = on
>> stats_start_collector = on
>> stats_row_level = on
>>
>> So, two questions:
>>
>> (a) Is this correct?  Should autovacuum be functional now?
>
> If you restarted.
>
>> (b) How can I verify that my databases are being vacuumed by autovacuum?
>
> pg_stat_user_tables should have a last_autovac column.


I'm wondering about the same question.  I did the above and restarted,
and now my pg_stat_user_tables looks like this:

postgres=# \d pg_stat_user_tables
View "pg_catalog.pg_stat_user_tables"
     Column     |  Type  | Modifiers
---------------+--------+-----------
  relid         | oid    |
  schemaname    | name   |
  relname       | name   |
  seq_scan      | bigint |
  seq_tup_read  | bigint |
  idx_scan      | bigint |
  idx_tup_fetch | bigint |
  n_tup_ins     | bigint |
  n_tup_upd     | bigint |
  n_tup_del     | bigint |
View definition:
  SELECT pg_stat_all_tables.relid, pg_stat_all_tables.schemaname, pg_stat_all_tables.relname,
pg_stat_all_tables.seq_scan,pg_stat_all_tables.seq_tup_read, pg_stat_all_tables.idx_scan,
pg_stat_all_tables.idx_tup_fetch,pg_stat_all_tables.n_tup_ins, pg_stat_all_tables.n_tup_upd,
pg_stat_all_tables.n_tup_del
    FROM pg_stat_all_tables
   WHERE pg_stat_all_tables.schemaname <> 'pg_catalog'::name AND pg_stat_all_tables.schemaname <> 'pg_toast'::name AND
pg_stat_all_tables.schemaname<> 'information_schema'::name; 


I don't see a last_autovac column, but I also don't see it in the
View definition.

So is this saying:
a) autovacuum is not running?
b) Something needs to update the view definition?
c) I need to wait [insert some period of time or number of transactions]
    before autovacuum runs and something updates the view definition?
d) none of the above?

-Mitch