Re: doc fixes: vacuum_cleanup_index_scale_factor

Hi!

Thank you for your attention on this subject. It's definitely right,
that documentation needs to be revised in these places.

On Wed, May 2, 2018 at 6:43 PM, Justin Pryzby <pryzby(at)telsasoft(dot)com> wrote:

> index eabe2a9..e305de9 100644
> --- a/doc/src/sgml/config.sgml
> +++ b/doc/src/sgml/config.sgml
> @@ -1893,14 +1893,16 @@ include_dir 'conf.d'
> </term>
> <listitem>
> <para>
> - When no tuples were deleted from the heap, B-tree indexes might
> still
> - be scanned during <command>VACUUM</command> cleanup stage by two
> - reasons. The first reason is that B-tree index contains deleted
> pages
> - which can be recycled during cleanup. The second reason is that
> B-tree
> - index statistics is stalled. The criterion of stalled index
> statistics
> - is number of inserted tuples since previous statistics collection
> - is greater than <varname>vacuum_cleanup_index_
> scale_factor</varname>
> - fraction of total number of heap tuples.
> + When no tuples were deleted from the heap, B-tree indexes are
> still
> + scanned during <command>VACUUM</command> cleanup stage unless two
> + conditions are met: the index contains no deleted pages which can
> be
> + recycled during cleanup; and, the index statistics are not stale.
> + Index statistics are considered stale unless
> + <varname>vacuum_cleanup_index_scale_factor</varname>
> + is set to a non-negative value, and the number of inserted tuples
> since
> + the previous statistics collection is less than that fraction of
> the
> + total number of heap tuples. The default is -1, which means index
> + scans during <command>VACUUM</command> cleanup are not skipped.
> </para>
> </listitem>
> </varlistentry>
>

The default value of vacuum_cleanup_index_scale_factor GUC is 0.1,
that means that 10% of tuples need to be inserted in order to trigger
vacuum cleanup. See guc.c

{
{"vacuum_cleanup_index_scale_factor", PGC_SIGHUP, AUTOVACUUM,
gettext_noop("Number of tuple inserts prior to index cleanup as a fraction
of reltuples."),
NULL
},
&vacuum_cleanup_index_scale_factor,
0.1, 0.0, 100.0,
NULL, NULL, NULL
},

Default value of vacuum_cleanup_index_scale_factor reloption is -1,
it means that by default value of vacuum_cleanup_index_scale_factor GUC
is used. See following piece of code in _bt_vacuum_needs_cleanup().

cleanup_scale_factor = (relopts &&
relopts->vacuum_cleanup_index_scale_factor >= 0)
? relopts->vacuum_cleanup_index_scale_factor
: vacuum_cleanup_index_scale_factor;

In order to have vacuum cleanup scan every time, one should set
vacuum_cleanup_index_scale_factor GUC to 0. Assuming this,
we need to replace "cleanup_scale_factor < 0" to
"cleanup_scale_factor <= 0" in the following condition:

if (cleanup_scale_factor < 0 ||
metad->btm_last_cleanup_num_heap_tuples < 0 ||
info->num_heap_tuples > (1.0 + cleanup_scale_factor) *
metad->btm_last_cleanup_num_heap_tuples)
result = true;

Another issue is that we by default store -1 in
metad->btm_last_cleanup_num_heap_tuples in order to evade overhead
of meta-page rewrite. metad->btm_last_cleanup_num_heap_tuples is
set at first btcleanup() call when no tuples were deleted. Second and
subsequent btcleanup() calls may skip index scan. This aspect needs
to be properly documented.

I'm going to propose a patch for this subject in a couple of days.
That patch would incorporate some of your changes as well as contain
some changes from me.

------
Alexander Korotkov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company