Re: Postgresql backend to perform vacuum automatically

Bruce Momjian wrote:
>
> > Are there tables that track information that would be useful for guiding
> > vacuum? Could I write a program which queries some statistical tables and and
> > knows which tables need to be vacuumed?
> >
> > If the info is around, I could whip up something pretty easily, I think.
>
> Sure, Jan's new statistics tables in 7.2 had just that info.
>
> test=> \d pg_stat_user_tables
> View "pg_stat_user_tables"
> Column | Type | Modifiers
> ---------------+---------+-----------
> relid | oid |
> relname | name |
> seq_scan | bigint |
> seq_tup_read | bigint |
> idx_scan | numeric |
> idx_tup_fetch | numeric |
> n_tup_ins | bigint |
> n_tup_upd | bigint |
> n_tup_del | bigint |

I have a system running 7.2b2, does it update these fields? Is it an option?
All I am getting is zeros.

relid | relname | seq_scan | seq_tup_read | idx_scan |
idx_tup_fetch | n_tup_ins | n_tup_upd | n_tup_del
-----------+----------------------+----------+--------------+----------+---------------+-----------+-----------+-----------
16559 | snf_cache | 0 | 0 | 0
| 0 | 0 | 0 | 0
8689760 | fav_stat | 0 | 0 |
| | 0 | 0 | 0
19174244 | mbr_art_aff | 0 | 0 | 0
| 0 | 0 | 0 | 0
20788376 | artist_affinity | 0 | 0 | 0
| 0 | 0 | 0 | 0
83345144 | saffweak | 0 | 0 |
| | 0 | 0 | 0
94811871 | pga_queries | 0 | 0 |
| | 0 | 0 | 0
94812980 | pga_forms | 0 | 0 |
| | 0 | 0 | 0
94813425 | pga_scripts | 0 | 0 |
| | 0 | 0 | 0
94813869 | pga_reports | 0 | 0 |
| | 0 | 0 | 0
94814245 | pga_schema | 0 | 0 |
| | 0 | 0 | 0
116675008 | int_song_affinity | 0 | 0 |
| | 0 | 0 | 0
166147508 | favorites | 0 | 0 | 0
| 0 | 0 | 0 | 0
173869647 | song_affinity_orig_t | 0 | 0 |
| | 0 | 0 | 0
176339567 | song_affinity | 0 | 0 | 0
| 0 | 0 | 0 | 0
178658941 | song_affinity_array | 0 | 0 | 0
| 0 | 0 | 0 | 0
186403716 | mbr_art_aff_t | 0 | 0 |
| | 0 | 0 | 0