Re: VACUUM ANALYZE extremely slow

On Jun 17, 2007, at 2:15 PM, Sergei Shelukhin wrote:
>> This is my first (and, by the love of the God, last) project w/pgsql
>> and everything but the simplest selects is so slow I want to cry.
>> This is especially bad with vacuum analyze - it takes several hours
>> for a database of mere 15 Gb on a fast double-core server w/2Gb of RAM
>> and virtually no workload at the moment. Maintenance work mem is set
>> to 512 Mb.
I have a 23 GB PG 8.1 db running on a 3 year old dual processor Dell
running Red Hat Linux, 2GB RAM. My tests ran on our production database
while it was running (albeit not under a heavy load) with no obvious
slowdown for users using our application. PG was using 1.2% of the CPU,
and noted the server had been running for 322 days. I'm not sure if
there's a way to make vacuum use more processor time to speed it up or not.

"ANALYZE" took 1 minute 14 seconds.

"VACUUM" took significantly longer at 24 minutes 22 seconds.

I noted that we hadn't set maintenance_work_mem so it presumably is
using the default of 16384 (16MB).

I'm sure much depends on how many tables, how many deleted rows, etc.
are in your db, but "several hours" certainly is nothing like what we see.

David