Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit8e12f4a

Browse files
committed
Various improvements of skipping index scan during vacuum technics
- Change vacuum_cleanup_index_scale_factor GUC to PGC_USERSET. vacuum_cleanup_index_scale_factor GUC was defined as PGC_SIGHUP. But this GUC affects not only autovacuum. So it might be useful to change it from user session in order to influence manually runned VACUUM.- Add missing tab-complete support for vacuum_cleanup_index_scale_factor reloption.- Fix condition for B-tree index cleanup. Zero value of vacuum_cleanup_index_scale_factor means that user wants B-tree index cleanup to be never skipped.- Documentation and comment improvementsAuthors: Justin Pryzby, Alexander Korotkov, Liudmila MantrovaReviewed by: all authors and Robert HaasDiscussion:https://www.postgresql.org/message-id/flat/20180502023025.GD7631%40telsasoft.com
1 parentddc1f32 commit8e12f4a

File tree

5 files changed

+43
-21
lines changed

5 files changed

+43
-21
lines changed

‎doc/src/sgml/config.sgml

Lines changed: 27 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -1893,15 +1893,34 @@ include_dir 'conf.d'
18931893
</term>
18941894
<listitem>
18951895
<para>
1896-
When no tuples were deleted from the heap, B-tree indexes might still
1897-
be scanned during <command>VACUUM</command> cleanup stage by two
1898-
reasons. The first reason is that B-tree index contains deleted pages
1899-
which can be recycled during cleanup. The second reason is that B-tree
1900-
index statistics is stalled. The criterion of stalled index statistics
1901-
is number of inserted tuples since previous statistics collection
1902-
is greater than <varname>vacuum_cleanup_index_scale_factor</varname>
1903-
fraction of total number of heap tuples.
1896+
Specifies the fraction of the total number of heap tuples counted in
1897+
the previous statistics collection that can be inserted without
1898+
incurring an index scan at the <command>VACUUM</command> cleanup stage.
1899+
This setting currently applies to B-tree indexes only.
19041900
</para>
1901+
1902+
<para>
1903+
If no tuples were deleted from the heap, B-tree indexes are still
1904+
scanned at the <command>VACUUM</command> cleanup stage when at least one
1905+
of the following conditions is met: the index statistics are stale, or
1906+
the index contains deleted pages that can be recycled during cleanup.
1907+
Index statistics are considered to be stale if the number of newly
1908+
inserted tuples exceeds the <varname>vacuum_cleanup_index_scale_factor</varname>
1909+
fraction of the total number of heap tuples detected by the previous
1910+
statistics collection. The total number of heap tuples is stored in
1911+
the index meta-page. Note that the meta-page does not include this data
1912+
until <command>VACUUM</command> finds no dead tuples, so B-tree index
1913+
scan at the cleanup stage can only be skipped if the second and
1914+
subsequent <command>VACUUM</command> cycles detect no dead tuples.
1915+
</para>
1916+
1917+
<para>
1918+
The value can range from <literal>0</literal> to <literal>100</literal>.
1919+
When <varname>vacuum_cleanup_index_scale_factor</varname> is set to
1920+
<literal>0</literal>, index scans are never skipped during
1921+
<command>VACUUM</command> cleanup. The default value is <literal>0.1</literal>.
1922+
</para>
1923+
19051924
</listitem>
19061925
</varlistentry>
19071926
</variablelist>

‎src/backend/access/nbtree/nbtpage.c

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -189,7 +189,7 @@ _bt_update_meta_cleanup_info(Relation rel, TransactionId oldestBtpoXact,
189189
if (metad->btm_version<BTREE_VERSION)
190190
_bt_upgrademetapage(metapg);
191191

192-
/* update cleanup-relatedinfromation */
192+
/* update cleanup-relatedinformation */
193193
metad->btm_oldest_btpo_xact=oldestBtpoXact;
194194
metad->btm_last_cleanup_num_heap_tuples=numHeapTuples;
195195
MarkBufferDirty(metabuf);

‎src/backend/access/nbtree/nbtree.c

Lines changed: 10 additions & 9 deletions
Original file line numberDiff line numberDiff line change
@@ -818,18 +818,19 @@ _bt_vacuum_needs_cleanup(IndexVacuumInfo *info)
818818
float8cleanup_scale_factor;
819819

820820
/*
821-
* If table receives large enough amount of insertions and no cleanup
822-
* was performed, then index might appear to have stalled statistics.
823-
* In order to evade that, we perform cleanup when table receives
824-
* vacuum_cleanup_index_scale_factor fractions of insertions.
821+
* If table receives enough insertions and no cleanup was performed,
822+
* then index would appear have stale statistics. If scale factor
823+
* is set, we avoid that by performing cleanup if the number of
824+
* inserted tuples exceeds vacuum_cleanup_index_scale_factor fraction
825+
* of original tuples count.
825826
*/
826827
relopts= (StdRdOptions*)info->index->rd_options;
827828
cleanup_scale_factor= (relopts&&
828829
relopts->vacuum_cleanup_index_scale_factor >=0)
829830
?relopts->vacuum_cleanup_index_scale_factor
830831
:vacuum_cleanup_index_scale_factor;
831832

832-
if (cleanup_scale_factor<0||
833+
if (cleanup_scale_factor <=0||
833834
metad->btm_last_cleanup_num_heap_tuples<0||
834835
info->num_heap_tuples> (1.0+cleanup_scale_factor)*
835836
metad->btm_last_cleanup_num_heap_tuples)
@@ -870,8 +871,8 @@ btbulkdelete(IndexVacuumInfo *info, IndexBulkDeleteResult *stats,
870871
&oldestBtpoXact);
871872

872873
/*
873-
* Update cleanup-related information in metapage.These information
874-
* is used only for cleanup but keepingupthem to date can avoid
874+
* Update cleanup-related information in metapage.This information
875+
* is used only for cleanup but keeping them up to date can avoid
875876
* unnecessary cleanup even after bulkdelete.
876877
*/
877878
_bt_update_meta_cleanup_info(info->index,oldestBtpoXact,
@@ -899,8 +900,8 @@ btvacuumcleanup(IndexVacuumInfo *info, IndexBulkDeleteResult *stats)
899900
* If btbulkdelete was called, we need not do anything, just return the
900901
* stats from the latest btbulkdelete call. If it wasn't called, we might
901902
* still need to do a pass over the index, to recycle any newly-recyclable
902-
* pagesand to obtain index statistics. _bt_vacuum_needs_cleanup checks
903-
*is therearenewly-recyclable or stalled index statistics.
903+
* pagesor to obtain index statistics. _bt_vacuum_needs_cleanup
904+
*determines if eitherareneeded.
904905
*
905906
* Since we aren't going to actually delete any leaf items, there's no
906907
* need to go through all the vacuum-cycle-ID pushups.

‎src/backend/utils/misc/guc.c

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -3238,7 +3238,7 @@ static struct config_real ConfigureNamesReal[] =
32383238
},
32393239

32403240
{
3241-
{"vacuum_cleanup_index_scale_factor",PGC_SIGHUP,AUTOVACUUM,
3241+
{"vacuum_cleanup_index_scale_factor",PGC_USERSET,AUTOVACUUM,
32423242
gettext_noop("Number of tuple inserts prior to index cleanup as a fraction of reltuples."),
32433243
NULL
32443244
},

‎src/bin/psql/tab-complete.c

Lines changed: 4 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1855,13 +1855,15 @@ psql_completion(const char *text, int start, int end)
18551855
COMPLETE_WITH_CONST("(");
18561856
/* ALTER INDEX <foo> SET|RESET ( */
18571857
elseif (Matches5("ALTER","INDEX",MatchAny,"RESET","("))
1858-
COMPLETE_WITH_LIST7("fillfactor","recheck_on_update",
1858+
COMPLETE_WITH_LIST8("fillfactor","recheck_on_update",
1859+
"vacuum_cleanup_index_scale_factor",/* BTREE */
18591860
"fastupdate","gin_pending_list_limit",/* GIN */
18601861
"buffering",/* GiST */
18611862
"pages_per_range","autosummarize"/* BRIN */
18621863
);
18631864
elseif (Matches5("ALTER","INDEX",MatchAny,"SET","("))
1864-
COMPLETE_WITH_LIST7("fillfactor =","recheck_on_update =",
1865+
COMPLETE_WITH_LIST8("fillfactor =","recheck_on_update =",
1866+
"vacuum_cleanup_index_scale_factor =",/* BTREE */
18651867
"fastupdate =","gin_pending_list_limit =",/* GIN */
18661868
"buffering =",/* GiST */
18671869
"pages_per_range =","autosummarize ="/* BRIN */

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp