Re: [SQL] Vacuum takes more than 1 hr

Postgres <postgres(at)weblynk(dot)com> writes:
> I have a table with about 30 columns and 200K rows. When I perform a vacuum
> on it (no analyze) it takes usually a little more than 1 hour.

Hmm, doesn't seem all that large. One question is whether you are all
the way up-to-date on Postgres --- some performance problems in vacuum
were fixed recently. (The problems were excessive memory usage,
actually, but that could translate to long runtime if the process
started to swap. Does the backend that's doing the vacuum seem to grow
to a size much larger than it starts at?) I don't recall whether this
patch is in 6.5.2 or not, but it will be in 6.5.3, or you could pull
the current REL6_5 branch sources from the CVS server.

A performance problem that still remains is that vacuum seems
unreasonably slow at updating indexes. Some people have found that
dropping and recreating indexes around a vacuum nets out faster than
letting vacuum do it. You should also ask yourself whether each
index on the table is earning its keep --- each one costs time on
every insert or update, quite aside from vacuum. Only the indexes
that actually get used for your common queries are likely to be worth
their overhead.

regards, tom lane