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
