Here is an interesting new datapoint.
Modern Ubuntu distro - PostgreSQL 8.1. SATA drive. No Raid. Cannot
reproduce slow vacuum performance - vacuums take less than a second
for the whole database.
Reinstall OS - Fedora Core 6 - PostgreSQL 8.1. Push data through
PostgreSQL for a couple hours (same as above) and now vacuum reports
this:
INFO: vacuuming "public.cpe"
INFO: index "pk_cpe" now contains 50048 row versions in 2328 pages
DETAIL: 415925 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.05s/0.33u sec elapsed 0.94 sec.
INFO: index "ix_cpe_ispid" now contains 50090 row versions in 1338 pages
DETAIL: 415925 index row versions were removed.
953 index pages have been deleted, 0 are currently reusable.
CPU 0.27s/0.22u sec elapsed 8.93 sec.
INFO: index "ix_cpe_enable" now contains 50676 row versions in 1637 pages
DETAIL: 415925 index row versions were removed.
1161 index pages have been deleted, 0 are currently reusable.
CPU 0.45s/0.31u sec elapsed 14.01 sec.
INFO: "cpe": removed 415925 row versions in 10844 pages
DETAIL: CPU 1.48s/0.25u sec elapsed 35.86 sec.
INFO: "cpe": found 415925 removable, 50003 nonremovable row versions
in 10849 pages
DETAIL: 6 dead row versions cannot be removed yet.
There were 0 unused item pointers.
0 pages are entirely empty.
CPU 2.40s/1.18u sec elapsed 61.13 sec.
It tooks 61 seconds to vacuum, and the number of index row versions
removed was huge.
We than issued a reindex command for the entire database - and now the
vaccum times are back down under a second.
What on earth could be going on between PostgreSQL 8.1 and Fedora 6
that is bloating and/or corrupting the indexes like this?
Thanks,
Dan