Re: Vacuum time degrading

On 3/2/05 10:50 PM, "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> It wouldn't be easy --- there are some locking considerations that say
> btbulkdelete needs to scan the index in the same order that an ordinary
> scan would do. See the nbtree README for details.

Just a follow-up on this..

The vacuum time has been steadily increasing at a seemingly increasing rate,
although there are no deletes or updates to the database. The current DB
size is just over 500 million rows. Last week it was up to 6.84 hours to do
a vacuum. Over the weekend I reindexed all the major indexes. The two
largest indexes took about 10 hours to reindex both. After the reindexing,
the vacuum took only 1.44 hours. This is pretty much a linear scaling from
the original vacuum time I reported.

So, the increasing vacuum times would appear to be as Tom suggested - due to
the fact that vacuum processes indexes in index order, not physical disk
order. I guess we add a periodic reindex to our maintenance procedures...

Wes