Gavin Hamill <gdh@laterooms.com> writes:
> 2006-10-27 08:37:12 UTC INFO: vacuuming "public.Allocation"
> 2006-10-27 08:37:21 UTC INFO: "Allocation": found 56449 removable, 4989360 nonremovable row versions in 47158 pages
> 2006-10-27 08:37:21 UTC DETAIL: 0 dead row versions cannot be removed yet.
> Nonremovable row versions range from 64 to 72 bytes long.
> There were 1 unused item pointers.
> Total free space (including removable row versions) is 5960056 bytes.
> 13 pages are or will become empty, including 0 at the end of the table.
> 5258 pages containing 4282736 free bytes are potential move destinations.
> CPU 0.16s/0.07u sec elapsed 9.55 sec.
> 2006-10-27 08:44:25 UTC INFO: index "allocation_pkey" now contains 4989360 row versions in 102198 pages
> 2006-10-27 08:44:25 UTC DETAIL: 56449 index row versions were removed.
> 1371 index pages have been deleted, 1371 are currently reusable.
> CPU 1.02s/0.38u sec elapsed 423.22 sec.
So the time is all in index vacuuming, eh? I think what's happening is
that the physical order of the index is degrading over time, and so the
vacuum scan takes longer due to more seeking. Can you afford to do a
REINDEX? If this theory is correct that should drive the time back
down.
8.2 has rewritten btree index vacuuming code that scans the index in
physical not logical order, so this problem should largely go away in
8.2, but in existing releases I can't see much you can do about it
except REINDEX when things get slow.
regards, tom lane
