diff --git a/src/include/commands/vacuum.h b/src/include/commands/vacuum.h
index cb27257bb6..9c3b9c5e1f 100644
--- a/src/include/commands/vacuum.h
+++ b/src/include/commands/vacuum.h
@@ -221,6 +221,10 @@ typedef struct VacuumParams
VacOptTernaryValue truncate; /* Truncate empty pages at the end,
* default value depends on reloptions */
+ double cleanup_index_scale_factor; /* Minimum fraction of relation pages
+ * with dead items required for index
+ * cleanup */
+
/*
* The number of parallel vacuum workers. 0 by default which means choose
* based on the number of indexes. -1 indicates parallel vacuum is
diff --git a/src/include/miscadmin.h b/src/include/miscadmin.h
index 95202d37af..9356b9c5b2 100644
--- a/src/include/miscadmin.h
+++ b/src/include/miscadmin.h
@@ -256,6 +256,8 @@ extern int VacuumCostPageDirty;
extern int VacuumCostLimit;
extern double VacuumCostDelay;
+extern double VacuumCleanupIndexScale;
+
extern int64 VacuumPageHit;
extern int64 VacuumPageMiss;
extern int64 VacuumPageDirty;
diff --git a/src/include/postmaster/autovacuum.h b/src/include/postmaster/autovacuum.h
index aacdd0f575..24cfbdad15 100644
--- a/src/include/postmaster/autovacuum.h
+++ b/src/include/postmaster/autovacuum.h
@@ -41,6 +41,7 @@ extern int autovacuum_freeze_max_age;
extern int autovacuum_multixact_freeze_max_age;
extern double autovacuum_vac_cost_delay;
extern int autovacuum_vac_cost_limit;
+extern double autovacuum_cleanup_index_scale;
/* autovacuum launcher PID, only valid when worker is shutting down */
extern int AutovacuumLauncherPid;
diff --git a/src/include/utils/guc_tables.h b/src/include/utils/guc_tables.h
index 6b40f1eeb8..30e69a9640 100644
--- a/src/include/utils/guc_tables.h
+++ b/src/include/utils/guc_tables.h
@@ -84,6 +84,7 @@ enum config_group
STATS_MONITORING,
STATS_COLLECTOR,
AUTOVACUUM,
+ VACUUM_INDEX,
CLIENT_CONN_STATEMENT,
CLIENT_CONN_LOCALE,
CLIENT_CONN_PRELOAD,
diff --git a/src/backend/access/heap/vacuumlazy.c b/src/backend/access/heap/vacuumlazy.c
index 17519a970f..8871fb8758 100644
--- a/src/backend/access/heap/vacuumlazy.c
+++ b/src/backend/access/heap/vacuumlazy.c
@@ -103,12 +103,6 @@
#define VACUUM_TRUNCATE_LOCK_WAIT_INTERVAL 50 /* ms */
#define VACUUM_TRUNCATE_LOCK_TIMEOUT 5000 /* ms */
-/*
- * Threshold that controls whether we bypass index vacuuming and heap
- * vacuuming as an optimization
- */
-#define BYPASS_THRESHOLD_PAGES 0.02 /* i.e. 2% of rel_pages */
-
/*
* When a table is small (i.e. smaller than this), save cycles by avoiding
* repeated failsafe checks
@@ -407,7 +401,8 @@ static void lazy_scan_prune(LVRelState *vacrel, Buffer buf,
BlockNumber blkno, Page page,
GlobalVisState *vistest,
LVPagePruneState *prunestate);
-static void lazy_vacuum(LVRelState *vacrel, bool onecall);
+static void lazy_vacuum(LVRelState *vacrel, VacuumParams *params,
+ bool onecall);
static bool lazy_vacuum_all_indexes(LVRelState *vacrel);
static void lazy_vacuum_heap_rel(LVRelState *vacrel);
static int lazy_vacuum_heap_page(LVRelState *vacrel, BlockNumber blkno,
@@ -1149,7 +1144,7 @@ lazy_scan_heap(LVRelState *vacrel, VacuumParams *params, bool aggressive)
}
/* Remove the collected garbage tuples from table and indexes */
- lazy_vacuum(vacrel, false);
+ lazy_vacuum(vacrel, params, false);
have_vacuumed_indexes = true;
/*
@@ -1567,7 +1562,7 @@ lazy_scan_heap(LVRelState *vacrel, VacuumParams *params, bool aggressive)
/* If any tuples need to be deleted, perform final vacuum cycle */
if (dead_tuples->num_tuples > 0)
- lazy_vacuum(vacrel, !have_vacuumed_indexes);
+ lazy_vacuum(vacrel, params, !have_vacuumed_indexes);
/*
* Vacuum the remainder of the Free Space Map. We must do this whether or
@@ -2052,7 +2047,7 @@ retry:
* wraparound.
*/
static void
-lazy_vacuum(LVRelState *vacrel, bool onecall)
+lazy_vacuum(LVRelState *vacrel, VacuumParams *params, bool onecall)
{
bool do_bypass_optimization;
@@ -2119,7 +2114,7 @@ lazy_vacuum(LVRelState *vacrel, bool onecall)
* The discrepancy should be negligible. If this optimization is ever
* expanded to cover more cases then this may need to be reconsidered.
*/
- threshold = (double) vacrel->rel_pages * BYPASS_THRESHOLD_PAGES;
+ threshold = (double) vacrel->rel_pages * params->cleanup_index_scale_factor;
do_bypass_optimization =
(vacrel->lpdead_item_pages < threshold &&
vacrel->lpdead_items < MAXDEADTUPLES(32L * 1024L * 1024L));
diff --git a/src/backend/commands/vacuum.c b/src/backend/commands/vacuum.c
index d549d0d86f..55c458da07 100644
--- a/src/backend/commands/vacuum.c
+++ b/src/backend/commands/vacuum.c
@@ -250,6 +250,9 @@ ExecVacuum(ParseState *pstate, VacuumStmt *vacstmt, bool isTopLevel)
/* user-invoked vacuum never uses this parameter */
params.log_min_duration = -1;
+ /* set scale factor for index cleanup based on GUC */
+ params.cleanup_index_scale_factor = VacuumCleanupIndexScale;
+
/* Now go through the common routine */
vacuum(vacstmt->rels, ¶ms, NULL, isTopLevel);
}
diff --git a/src/backend/postmaster/autovacuum.c b/src/backend/postmaster/autovacuum.c
index d516df0ac5..df9d52a2d2 100644
--- a/src/backend/postmaster/autovacuum.c
+++ b/src/backend/postmaster/autovacuum.c
@@ -124,6 +124,7 @@ int autovacuum_anl_thresh;
double autovacuum_anl_scale;
int autovacuum_freeze_max_age;
int autovacuum_multixact_freeze_max_age;
+double autovacuum_cleanup_index_scale = 0.02;
double autovacuum_vac_cost_delay;
int autovacuum_vac_cost_limit;
@@ -2922,6 +2923,7 @@ table_recheck_autovac(Oid relid, HTAB *table_toast_map,
int vac_cost_limit;
double vac_cost_delay;
int log_min_duration;
+ int cleanup_index_scale_factor;
/*
* Calculate the vacuum cost parameters and the freeze ages. If there
@@ -2968,6 +2970,9 @@ table_recheck_autovac(Oid relid, HTAB *table_toast_map,
? avopts->multixact_freeze_table_age
: default_multixact_freeze_table_age;
+ cleanup_index_scale_factor = autovacuum_cleanup_index_scale >= 0 ?
+ autovacuum_cleanup_index_scale : VacuumCostDelay;
+
tab = palloc(sizeof(autovac_table));
tab->at_relid = relid;
tab->at_sharedrel = classForm->relisshared;
@@ -2986,6 +2991,7 @@ table_recheck_autovac(Oid relid, HTAB *table_toast_map,
tab->at_params.multixact_freeze_table_age = multixact_freeze_table_age;
tab->at_params.is_wraparound = wraparound;
tab->at_params.log_min_duration = log_min_duration;
+ tab->at_params.cleanup_index_scale_factor = cleanup_index_scale_factor;
tab->at_vacuum_cost_limit = vac_cost_limit;
tab->at_vacuum_cost_delay = vac_cost_delay;
tab->at_relname = NULL;
diff --git a/src/backend/utils/init/globals.c b/src/backend/utils/init/globals.c
index 381d9e548d..953fb2ae08 100644
--- a/src/backend/utils/init/globals.c
+++ b/src/backend/utils/init/globals.c
@@ -143,6 +143,11 @@ int VacuumCostPageMiss = 2;
int VacuumCostPageDirty = 20;
int VacuumCostLimit = 200;
double VacuumCostDelay = 0;
+/*
+ * Fraction number that controls whether we bypass index vacuuming and heap
+ * vacuuming as an optimization.
+ */
+double VacuumCleanupIndexScale = 0.02; /* i.e. 2% of rel_pages */
int64 VacuumPageHit = 0;
int64 VacuumPageMiss = 0;
diff --git a/src/backend/utils/misc/guc.c b/src/backend/utils/misc/guc.c
index eb7f7181e4..3999bbeddb 100644
--- a/src/backend/utils/misc/guc.c
+++ b/src/backend/utils/misc/guc.c
@@ -759,6 +759,8 @@ const char *const config_group_names[] =
gettext_noop("Statistics / Query and Index Statistics Collector"),
/* AUTOVACUUM */
gettext_noop("Autovacuum"),
+ /* VACUUM_INDEX */
+ gettext_noop("Vacuum / Index"),
/* CLIENT_CONN_STATEMENT */
gettext_noop("Client Connection Defaults / Statement Behavior"),
/* CLIENT_CONN_LOCALE */
@@ -3739,6 +3741,26 @@ static struct config_real ConfigureNamesReal[] =
NULL, NULL, NULL
},
+ {
+ {"vacuum_cleanup_index_scale_factor", PGC_SIGHUP, VACUUM_INDEX,
+ gettext_noop("Fraction of relation pages, with at least one dead item, required to clean up indexes."),
+ NULL
+ },
+ &VacuumCleanupIndexScale,
+ 0.02, 0.0, 0.05,
+ NULL, NULL, NULL
+ },
+
+ {
+ {"autovacuum_cleanup_index_scale_factor", PGC_SIGHUP, AUTOVACUUM,
+ gettext_noop("Fraction of relation pages, with at least one dead item, required to clean up indexes, for autovacuum."),
+ NULL
+ },
+ &autovacuum_cleanup_index_scale,
+ -1, -1, 0.05,
+ NULL, NULL, NULL
+ },
+
{
{"autovacuum_vacuum_scale_factor", PGC_SIGHUP, AUTOVACUUM,
gettext_noop("Number of tuple updates or deletes prior to vacuum as a fraction of reltuples."),
diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample
index efde01ee56..d9b8d1ad42 100644
--- a/src/backend/utils/misc/postgresql.conf.sample
+++ b/src/backend/utils/misc/postgresql.conf.sample
@@ -173,6 +173,13 @@
#vacuum_cost_page_dirty = 20 # 0-10000 credits
#vacuum_cost_limit = 200 # 1-10000 credits
+# - Vacuum / Index
+
+#vacuum_cleanup_index_scale_factor = 0.02 # Minimum fraction of relation
+ # pages with at least one dead item
+ # needed to trigger index cleanup during
+ # manual VACUUM. Default is 2%.
+
# - Background Writer -
#bgwriter_delay = 200ms # 10-10000ms between rounds
@@ -642,6 +649,11 @@
#autovacuum_vacuum_cost_limit = -1 # default vacuum cost limit for
# autovacuum, -1 means use
# vacuum_cost_limit
+#autovacuum_cleanup_index_scale_factor = -1 # Minimum fraction of relation
+ # pages with at least one dead item
+ # needed to trigger index cleanup,
+ # -1 means use
+ # vacuum_cleanup_index_scale_factor
#------------------------------------------------------------------------------
diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index 45bd1f1b7e..ac77727564 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -2241,6 +2241,38 @@ include_dir 'conf.d'
+
+ Vacuum parameters for Indexes
+
+ During the execution of
+ and
+ commands, this controls the execution of vacuum operations
+ performed on indexes of the relation worked on.
+
+
+
+
+ vacuum_cleanup_index_scale_factor (floating point)
+
+ vacuum_cleanup_index_scale_factor
+ configuration parameter
+
+
+
+
+ Specifies the minimum percentage of relation pages required to have
+ at least one dead item to trigger index cleanup in
+ VACUUM operations. The default is
+ 0.02, meaning that at least 2% of the relation
+ pages need to have at least one dead item to trigger the cleanup
+ This parameter can only be set in the postgresql.conf
+ file or on the server command line.
+
+
+
+
+
+
Background Writer
@@ -8001,6 +8033,26 @@ COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv;
+
+ autovacuum_cleanup_index_scale_factor (floating point)
+
+ autovacuum_cleanup_index_scale_factor
+ configuration parameter
+
+
+
+
+ Specifies the minimum percentage of relation pages required to have
+ at least one dead item to trigger index cleanup in automatic
+ VACUUM operations. If -1 is
+ specified (which is the default), the regular
+ value will be used.
+ This parameter can only be set in the postgresql.conf
+ file or on the server command line.
+
+
+
+