Is vacuuming any table supposed to zero the statistics for all
shared tables? Doesn't that have implications for autovacuum? The
example below is in 8.2.4 but I'm seeing similar behavior in 8.1.9
and 8.3devel. Additionally, in 8.3devel doing anything that queries
or modifies a shared table seems to zero the statistics for all
shared tables.
test=> select relname, seq_scan, idx_scan, n_tup_ins, n_tup_upd, n_tup_del
test-> from pg_stat_all_tables
test-> where relid in (select oid from pg_class where relisshared)
test-> order by relname;
relname | seq_scan | idx_scan | n_tup_ins | n_tup_upd | n_tup_del
------------------+----------+----------+-----------+-----------+-----------
pg_auth_members | 25 | 3 | 1 | 0 | 1
pg_authid | 7 | 40 | 0 | 0 | 0
pg_database | 2 | 7 | 0 | 0 | 0
pg_pltemplate | 2 | 0 | 0 | 0 | 0
pg_shdepend | 0 | 4 | 2 | 0 | 2
pg_shdescription | 2 | 0 | 0 | 0 | 0
pg_tablespace | 2 | 0 | 0 | 0 | 0
pg_toast_1260 | 1 | 0 | 0 | 0 | 0
pg_toast_1262 | 1 | 0 | 0 | 0 | 0
pg_toast_2396 | 1 | 0 | 0 | 0 | 0
(10 rows)
test=> vacuum foo;
VACUUM
test=> select relname, seq_scan, idx_scan, n_tup_ins, n_tup_upd, n_tup_del
test-> from pg_stat_all_tables
test-> where relid in (select oid from pg_class where relisshared)
test-> order by relname;
relname | seq_scan | idx_scan | n_tup_ins | n_tup_upd | n_tup_del
------------------+----------+----------+-----------+-----------+-----------
pg_auth_members | 0 | 0 | 0 | 0 | 0
pg_authid | 0 | 0 | 0 | 0 | 0
pg_database | 1 | 0 | 0 | 0 | 0
pg_pltemplate | 0 | 0 | 0 | 0 | 0
pg_shdepend | 0 | 0 | 0 | 0 | 0
pg_shdescription | 0 | 0 | 0 | 0 | 0
pg_tablespace | 0 | 0 | 0 | 0 | 0
pg_toast_1260 | 0 | 0 | 0 | 0 | 0
pg_toast_1262 | 0 | 0 | 0 | 0 | 0
pg_toast_2396 | 0 | 0 | 0 | 0 | 0
(10 rows)
--
Michael Fuhr
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
