autovacuum doesn't seem to vacuum pg_attribute

Hi PostgreSQL developers!

While cleaning up the postgresql bug reports in Debian, I stumbled
over the following gem. Do you know about this problem, and is it
still an issue for 8.0 and 8.1?

Thanks in advance,

Martin

--- http://bugs.debian.org/275207 ----

I have a postgresql server which runs a query that generates a temporary
table once per minute. This creation and deletion of a table seems to
cause the pg_attribute table to fill up with deleted entries, but the
n_tup_del and n_tup_upd values in pg_stat_all_tables do not seem to
reflect this:

emokona=# select * from pg_stat_all_tables where relname = 'pg_attribute';
relid | schemaname | relname | seq_scan | seq_tup_read | idx_scan | idx_tup_fetch | n_tup_ins | n_tup_upd | n_tup_del
-------+------------+--------------+----------+--------------+----------+---------------+-----------+-----------+-----------
1249 | pg_catalog | pg_attribute | 12208 | 619174 | 1928209 | 4182563 | 755387 | 85 | 220
(1 row)

Time: 46.274 ms
mokona=# select count(*) from pg_attribute;
count
-------
1554
(1 row)

Time: 54.020 ms

Because n_tup_upd and n_tup_del are always small, pg_autovacuum never
vacuums this table--pg_autovacuum by design only operates on tables with
high numbers in these fields. The index files grow to many megabytes
until I eventually run 'vacuum pg_attribute' manually:

mokona=# vacuum verbose pg_attribute;
INFO: vacuuming "pg_catalog.pg_attribute"
INFO: index "pg_attribute_relid_attnam_index" now contains 1554 row versions in 8980 pages
DETAIL: 13363 index row versions were removed.
8914 index pages have been deleted, 8712 are currently reusable.
CPU 2.09s/0.28u sec elapsed 48.56 sec.
INFO: index "pg_attribute_relid_attnum_index" now contains 1577 row versions in 2074 pages
DETAIL: 13363 index row versions were removed.
2035 index pages have been deleted, 1985 are currently reusable.
CPU 0.54s/0.19u sec elapsed 11.47 sec.
INFO: "pg_attribute": removed 13363 row versions in 220 pages
DETAIL: CPU 0.14s/0.08u sec elapsed 1.18 sec.
INFO: "pg_attribute": found 13363 removable, 1554 nonremovable row versions in 245 pages
DETAIL: 0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
0 pages are entirely empty.
CPU 2.78s/0.56u sec elapsed 61.23 sec.
VACUUM
Time: 61915.693 ms

As you can see, this takes a long time, and frees a lot of space, so it
is quite useful to vacuum this table...but pg_autovacuum won't, because it
never sees the need.

I'm not sure if this is a pg_autovacuum problem (pg_autovacuum might need
to consider counting index row versions as well as table row versions?) or
a postgresql statistics collection problem (is pg_attribute an exceptional
case for n_tup_* statistics collection?), so I'll just submit a bug report
for now and let the turf wars begin. ;-)

--
Martin Pitt http://www.piware.de
Ubuntu Developer http://www.ubuntu.com
Debian Developer http://www.debian.org