Re: CPU-intensive autovacuuming

Phil Endecott wrote:

> Matthew T. O'Connor wrote:
>
>> Indeed you have. I have head a few similar reports but perhaps none
>> as bad as yours. One person put a small sleep value so that it
>> doesn't spin so tight. You could also just up the sleep delay so
>> that it doesn't do this work quite so often. No other quick
>> suggestions.
>
>
> I do wonder why autovacuum is keeping its table list in memory rather
> than in the database.

For better or worse, this was a conscious design decision that the
contrib version of autovacuum be non-invasive to your database.

> But given that it is keeping it in memory, I think the real fix is to
> sort that list (or keep it ordered when building or updating it). It
> is trivial to also get the query results ordered, and they can then be
> compared in O(n) time.

I'm quite sure there is a better way, please submit a patch if you can.
This was never a real concern for most people since the number of tables
is typically small enough not to be a problem. The integrated version
of autovacuum that didn't make the cut before 8.0 avoids this problem
since the autovacuum data is stored in the database.

> I notice various other places where there seem to be nested loops,
> e.g. in the update_table_list function. I'm not sure if they can be
> fixed by similar means.

I would think so, they all basically do the same type of loop.