Re: Vacuum time degrading

Wes <wespvp(at)syntegra(dot)com> writes:
> 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...

That doesn't follow from what you said. Did you check that the physical
sizes of the indexes were comparable before and after the reindex?

regards, tom lane