User-Agent: Microsoft-Entourage/11.1.0.040913
Date: Mon, 25 Jul 2005 17:51:48 -0700
Subject: Re: "Vacuum Full Analyze" taking so long
From: "Luke Lonergan" <llonergan@greenplum.com>
To: "John A Meinel" <john@arbash-meinel.com>,
	"Tomeh, Husam" <htomeh@firstam.com>
Cc: pgsql-performance@postgresql.org
Message-ID: <BF0AD734.9F38%llonergan@greenplum.com>
In-Reply-To: <42E5763C.7030804@arbash-meinel.com>
MIME-Version: 1.0
Content-Type: text/plain;
 charset=us-ascii
Content-Transfer-Encoding: 7bit


Husam,

On 7/25/05 4:31 PM, "John A Meinel" <john@arbash-meinel.com> wrote:

> Tomeh, Husam wrote:
>> 
>> Nothing was running except the job. The server did not look stressed out
>> looking at top and vmstat. We have seen slower query performance when
>> performing load tests, so I run the re-index on all application indexes
>> and then issue a full vacuum. I ran the same thing on a staging server
>> and it took less than 12 hours. Is there a possibility the DB pages are
>> corrupted. Is there a command to verify that. (In Oracle, there's a
>> dbverify command that checks for corruption on the data files level).
>> 
>> The other question I have. What would be the proper approach to rebuild
>> indexes. I re-indexes and then run vacuum/analyze. Should I not use the
>> re-index approach, and instead, drop the indexes, vacuum the tables, and
>> then create the indexes, then run analyze on tables and indexes??
> 
> I *think* if you are planning on dropping the indexes anyway, just drop
> them, VACUUM ANALYZE, and then recreate them, I don't think you have to
> re-analyze after you have recreated them.

I agree - and don't run "VACUUM FULL", it is quite different from "VACUUM".
Also - you should only need to vacuum if you've deleted a lot of data.  It's
job is to reclaim space lost to rows marked deleted.  So, in fact, you may
not even need to run VACUUM.

"VACUUM FULL" is like a disk defragmentation operation within the DBMS, and
is only necessary if there is a slowdown in performance from lots and lots
of deletes and/or updates and new data isn't finding sequential pages for
storage, which is rare.  Given the need for locking, it's generally better
to dump and restore in that case, but again it's a very rare occasion.

I don't know of a command to check for page corruption, but I would think
that if you can run VACUUM (not full) you should be OK.

- Luke