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
