On 05/08/2018 02:05 PM, Justin Pryzby wrote:
3rd iteration ; thanks for bearing with me.
On Tue, May 08, 2018 at 12:35:00PM +0300, Alexander Korotkov wrote:
Hi, Justin!
Thank you for revising documentation patch.
On Mon, May 7, 2018 at 7:55 PM, Justin Pryzby <pry...@telsasoft.com> wrote:
+ In order to detect stale index statistics, the number of total heap
+ tuples during previous statistics collection is stored in the index
+ meta-page.
Consider removing: "during previous statistics collection"
Or: "during THE previous statistics collection"
+ Once the number of inserted tuples since previous
since THE previous
+ statistics collection is more than
+ <varname>vacuum_cleanup_index_scale_factor</varname> fraction of
Since the multiplier can be greater than 1, should we say "multiple" instead of
fraction?
+ during <command>VACUUM</command> cycle. Thus, skipping of the B-tree
+ index scan during cleanup stage is only possible when second and
+ subsequent <command>VACUUM</command> cycles detecting no dead tuples.
Change "detecting" to "detect". Or maybe just "find"
Justin
Hi Justin,
Thank you for helping with the docs. Attached is another doc patch that
should address most of the issues you've brought up.
I've also reshuffled the text a bit to make it more like an option
description. Hope you'll find it useful.
--
Liudmila Mantrova
Technical writer at Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company
diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index ffea744..c4afd14 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -1893,15 +1893,34 @@ 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.
+ Specifies the fraction of the total number of heap tuples counted in
+ the previous statistics collection that can be inserted without
+ incurring an index scan at the <command>VACUUM</command> cleanup stage.
+ This setting currently applies to B-tree indexes only.
</para>
+
+ <para>
+ If no tuples were deleted from the heap, B-tree indexes are still
+ scanned at the <command>VACUUM</command> cleanup stage when at least one
+ of the following conditions is met: the index statistics are stale, or
+ the index contains deleted pages that can be recycled during cleanup.
+ Index statistics are considered to be stale if the number of newly
+ inserted tuples exceeds the <varname>vacuum_cleanup_index_scale_factor</varname>
+ fraction of the total number of heap tuples detected by the previous
+ statistics collection. The total number of heap tuples is stored in
+ the index meta-page. Note that the meta-page does not include this data
+ until <command>VACUUM</command> finds no dead tuples, so B-tree index
+ scan at the cleanup stage can only be skipped if the second and
+ subsequent <command>VACUUM</command> cycles detect no dead tuples.
+ </para>
+
+ <para>
+ The value can range from <literal>0</literal> to <literal>100</literal>.
+ When <varname>vacuum_cleanup_index_scale_factor</varname> is set to
+ <literal>0</literal>, index scans are never skipped during
+ <command>VACUUM</command> cleanup. The default value is <literal>0.1</literal>.
+ </para>
+
</listitem>
</varlistentry>
</variablelist>
diff --git a/src/backend/access/nbtree/nbtpage.c b/src/backend/access/nbtree/nbtpage.c
index 3bcc56e..22b4a75 100644
--- a/src/backend/access/nbtree/nbtpage.c
+++ b/src/backend/access/nbtree/nbtpage.c
@@ -189,7 +189,7 @@ _bt_update_meta_cleanup_info(Relation rel, TransactionId oldestBtpoXact,
if (metad->btm_version < BTREE_VERSION)
_bt_upgrademetapage(metapg);
- /* update cleanup-related infromation */
+ /* update cleanup-related information */
metad->btm_oldest_btpo_xact = oldestBtpoXact;
metad->btm_last_cleanup_num_heap_tuples = numHeapTuples;
MarkBufferDirty(metabuf);
diff --git a/src/backend/access/nbtree/nbtree.c b/src/backend/access/nbtree/nbtree.c
index d894ba0..27a3032 100644
--- a/src/backend/access/nbtree/nbtree.c
+++ b/src/backend/access/nbtree/nbtree.c
@@ -818,10 +818,11 @@ _bt_vacuum_needs_cleanup(IndexVacuumInfo *info)
float8 cleanup_scale_factor;
/*
- * If table receives large enough amount of insertions and no cleanup
- * was performed, then index might appear to have stalled statistics.
- * In order to evade that, we perform cleanup when table receives
- * vacuum_cleanup_index_scale_factor fractions of insertions.
+ * If table receives enough insertions and no cleanup was performed,
+ * then index would appear have stale statistics. If scale factor
+ * is set, we avoid that by performing cleanup if the number of
+ * inserted tuples exceeds vacuum_cleanup_index_scale_factor fraction
+ * of original tuples count.
*/
relopts = (StdRdOptions *) info->index->rd_options;
cleanup_scale_factor = (relopts &&
@@ -870,8 +871,8 @@ btbulkdelete(IndexVacuumInfo *info, IndexBulkDeleteResult *stats,
&oldestBtpoXact);
/*
- * Update cleanup-related information in metapage. These information
- * is used only for cleanup but keeping up them to date can avoid
+ * Update cleanup-related information in metapage. This information
+ * is used only for cleanup but keeping them up to date can avoid
* unnecessary cleanup even after bulkdelete.
*/
_bt_update_meta_cleanup_info(info->index, oldestBtpoXact,
@@ -899,8 +900,8 @@ btvacuumcleanup(IndexVacuumInfo *info, IndexBulkDeleteResult *stats)
* If btbulkdelete was called, we need not do anything, just return the
* stats from the latest btbulkdelete call. If it wasn't called, we might
* still need to do a pass over the index, to recycle any newly-recyclable
- * pages and to obtain index statistics. _bt_vacuum_needs_cleanup checks
- * is there are newly-recyclable or stalled index statistics.
+ * pages or to obtain index statistics. _bt_vacuum_needs_cleanup
+ * determines if either are needed.
*
* Since we aren't going to actually delete any leaf items, there's no
* need to go through all the vacuum-cycle-ID pushups.
