Re: obsessive-compulsive vacuum behavior

Ben Chobot <bench(at)silentmedia(dot)com> writes:
> ...and so on. It's been running for an hour or so now, when it appears it shouldn't take 10 minutes. This seems pretty weird to me.... has anybody else seen this behavior? I'm not even sure what details I could report which would help figure out what's going on.

You're apparently trying to vacuum a really large table with a whole lot
of dead rows using a tiny maintenance_work_mem setting (16MB, looks
like). Every time the list of dead tuples fills up, it has to perform a
pass over the table and indexes to get rid of those tuples. Then it
goes back to scanning for more dead tuples.

If it's still going, I'd suggest cancelling it and starting again with a
more realistic maintenance_work_mem, maybe something in the several
hundred MB range, depending on how much memory is free in your server.

After it's done you probably ought to look into your autovacuum
settings. You shouldn't have gotten into a state with so many dead
tuples in the first place, I would think, if autovac were functioning
properly.

regards, tom lane