autovacuum doesn't seem to vacuum pg_attribute - Mailing list pgsql-bugs

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=3D# select * from pg_stat_all_tables where relname =3D 'pg_attribut=
e';
 relid | schemaname |   relname    | seq_scan | seq_tup_read | idx_scan | i=
dx_tup_fetch | n_tup_ins | n_tup_upd | n_tup_del=20
-------+------------+--------------+----------+--------------+----------+--=
-------------+-----------+-----------+-----------
  1249 | pg_catalog | pg_attribute |    12208 |       619174 |  1928209 |  =
     4182563 |    755387 |        85 |       220
(1 row)

Time: 46.274 ms
mokona=3D# select count(*) from pg_attribute;=20
 count=20
-------
  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=3D# vacuum verbose pg_attribute;
INFO:  vacuuming "pg_catalog.pg_attribute"
INFO:  index "pg_attribute_relid_attnam_index" now contains 1554 row versio=
ns 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 versio=
ns 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 version=
s 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.  ;-)


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