Re: vacuum full taking much longer than dump + restore?

On Mon, Mar 20, 2006 at 10:50:42AM +0100, Dragan Matic wrote:
> We have a few larger tables (~3,5 million rows largest, ~ 1 million rows
> smallest) on our production database which had at least one column
> defined as char(nn) (nn being larger or equal to 60).

<snip>

> 15-20 minutes of vacuum analyze. I'm guessing that this
> dump->restore->analyze has done effectively the same thing what vacuum
> full was supposed to do. How is it possible that vacuum full was so
> slow, are there some configuration parameters that might be
> misconfigured? I am using fedora core 4 with pre-built 8.1.3 rpms.
> Server has 2 gb of ram.

How many indexes did you have on that table? Once vacuum has decided to
clear out an old tuple, it needs to remove it from the index. Depending
the number and size of indexes, this can be quite expensive. so
dropping the indexes first, vacuuming and recreating may be faster.

Incidently, some changes have been made to vacuum recently to make this
a bit better, but what it have is kind of the worst case scenario. It
is known that sometimes clustering a table is faster than vacuuming it.

I think "maintainence_work_mem" has a significant impact on vacuum,
especially how many passes it needs to make. Upping that should help.

Hope this helps,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.