DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta;
	h=received:message-id:date:from:user-agent:mime-version:to:cc:subject:references:in-reply-to:content-type:content-transfer-encoding:sender;
	b=o0Y8mokIrZOK74Ax+Wa/1btbyk4Ksda6ZPz+iuyMQJ6XqW9f/yBAnsqBuNFRhM2TrmRf8VgKp44faG1gccD4zprRosv4Mg5xTbTS18Y3Zwhc6HOKCcU81TKhNlvrAHn+nSIEKE/U2YXlh8nDiQEqmZBEoaWxsHM66vj0/R5KZlY=
Message-ID: <46406FA5.3080907@enterprisedb.com>
Date: Tue, 08 May 2007 13:40:05 +0100
From: Heikki Linnakangas <heikki@enterprisedb.com>
User-Agent: Icedove 1.5.0.10 (X11/20070329)
MIME-Version: 1.0
To: Guillaume Cottenceau <gc@mnc.ch>
CC: pgsql-performance@postgresql.org
Subject: Re: estimating the need for VACUUM FULL and REINDEX
References: <87odkvmsav.fsf@meuh.mnc.lan>
In-Reply-To: <87odkvmsav.fsf@meuh.mnc.lan>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Guillaume Cottenceau wrote:
> According to documentation[1], VACUUM FULL's only benefit is
> returning unused disk space to the operating system; am I correct
> in assuming there's also the benefit of optimizing the
> performance of scans, because rows are physically compacted on
> the disk?

That's right.

> With that in mind, I've tried to estimate how much benefit would
> be brought by running VACUUM FULL, with the output of VACUUM
> VERBOSE. However, it seems that for example the "removable rows"
> reported by each VACUUM VERBOSE run is actually reused by VACUUM,
> so is not what I'm looking for.

Take a look at contrib/pgstattuple. If a table has high percentage of 
free space, VACUUM FULL will compact that out.

> Then according to documentation[2], REINDEX has some benefit when
> all but a few index keys on a page have been deleted, because the
> page remains allocated (thus, I assume it improves index scan
> performance, am I correct?). However, again I'm unable to
> estimate the expected benefit. With a slightly modified version
> of a query found in documentation[3] to see the pages used by a
> relation[4], I'm able to see that the index data from a given
> table...

See pgstatindex, in the same contrib-module. The number you're looking 
for is avg_leaf_density. REINDEX will bring that to 90% (with default 
fill factor), so if it's much lower than that REINDEX will help.

-- 
   Heikki Linnakangas
   EnterpriseDB   http://www.enterprisedb.com