From: Heikki Linnakangas <heikki@enterprisedb.com>
To: Guillaume Cottenceau <gc@mnc.ch>
Cc: pgsql-performance@postgresql.org
Subject: Re: estimating the need for VACUUM FULL and REINDEX
Date: Tue, 08 May 2007 13:40:05 +0100
Message-ID: <46406FA5.3080907@enterprisedb.com> (raw)
In-Reply-To: <87odkvmsav.fsf@meuh.mnc.lan>
References: <87odkvmsav.fsf@meuh.mnc.lan>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: heikki@enterprisedb.com, gc@mnc.ch
Subject: Re: estimating the need for VACUUM FULL and REINDEX
In-Reply-To: <46406FA5.3080907@enterprisedb.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox