Re: poor VACUUM performance on large tables

Jan Peterson <jan.l.peterson@gmail.com> writes:
> We have been experiencing poor performance of VACUUM in our production
> database.

Which PG version, exactly?

> Everything works great until our rolling delete kicks in.  Of course,
> we are doing periodic VACUUMS on all tables, with frequent VACUUMs on
> the more active tables.  The problem arises when we start deleting the
> bulk data and have to VACUUM pg_largeobject and our other larger
> tables.  We have seen VACUUM run for several hours (even tens of
> hours).

Plain VACUUM (not FULL) certainly ought not take that long.  (If you're
using VACUUM FULL, the answer is going to be "don't do that".)  What
maintenance_work_mem (or vacuum_mem in older releases) are you running
it under?  Can you get VACUUM VERBOSE output from some of these cases
so we can see which phase(s) are eating the time?  It'd also be
interesting to watch the output of vmstat or local equivalent --- it
might just be that your I/O capability is nearly saturated and VACUUM is
pushing the system over the knee of the response curve.  If so, the
vacuum delay options of 8.0 would be worth experimenting with.

> Statistics are also skewed during this
> process and we have observed the planner choosing sequential scans on
> tables where it is obvious that an index scan would be more efficient.

That's really pretty hard to believe; VACUUM doesn't affect the
statistics until the very end.  Can you give some specifics of how
the "statistics are skewed"?

            regards, tom lane