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.