Re: estimating the need for VACUUM FULL and REINDEX

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