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
signature.asc
Description: Digital signature
