From 6ca33a885bf892a7fa34020a2620c83ccec3cdd7 Mon Sep 17 00:00:00 2001 From: Alexander Korotkov Date: Tue, 26 Jun 2018 15:00:51 +0300 Subject: [PATCH] Increase upper limit for vacuum_cleanup_index_scale_factor Upper limits for vacuum_cleanup_index_scale_factor GUC and reloption were initially set to 100.0 in 857f9c36. However, after further discussion, it appears that some users like to disable B-tree cleanup index scan completely (assuming there are no deleted pages). vacuum_cleanup_index_scale_factor is used barely to protect against stalled index statistics. And after detailed consideration it appears that risk of stalled index statistics is low. And it would be nice to allow advanced users setting higher values of vacuum_cleanup_index_scale_factor. So, set upper limit for these GUC and reloption to DBL_MAX. Author: Alexander Korotkov Reviewed-by: Masahiko Sawada Discussion: https://postgr.es/m/CAC8Q8tJCb%3DgxhzcV7T6ctx7PY-Ux1oA-AsTJc6cAVNsQiYcCzA%40mail.gmail.com --- doc/src/sgml/config.sgml | 2 +- src/backend/access/common/reloptions.c | 2 +- src/backend/access/nbtree/nbtree.c | 8 +++++--- src/backend/utils/misc/guc.c | 2 +- src/test/regress/expected/btree_index.out | 2 +- 5 files changed, 9 insertions(+), 7 deletions(-) diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml index 7bfbc87109..574837889b 100644 --- a/doc/src/sgml/config.sgml +++ b/doc/src/sgml/config.sgml @@ -6989,7 +6989,7 @@ COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv; - The value can range from 0 to 100. + The value should be non-negative. When vacuum_cleanup_index_scale_factor is set to 0, index scans are never skipped during VACUUM cleanup. The default value is 0.1. diff --git a/src/backend/access/common/reloptions.c b/src/backend/access/common/reloptions.c index e0c9c3431c..5671bb6830 100644 --- a/src/backend/access/common/reloptions.c +++ b/src/backend/access/common/reloptions.c @@ -416,7 +416,7 @@ static relopt_real realRelOpts[] = RELOPT_KIND_BTREE, ShareUpdateExclusiveLock }, - -1, 0.0, 100.0 + -1, 0.0, DBL_MAX }, /* list terminator */ {{NULL}} diff --git a/src/backend/access/nbtree/nbtree.c b/src/backend/access/nbtree/nbtree.c index 27a3032e42..cdd0403e1d 100644 --- a/src/backend/access/nbtree/nbtree.c +++ b/src/backend/access/nbtree/nbtree.c @@ -816,6 +816,7 @@ _bt_vacuum_needs_cleanup(IndexVacuumInfo *info) { StdRdOptions *relopts; float8 cleanup_scale_factor; + float8 prev_num_heap_tuples; /* * If table receives enough insertions and no cleanup was performed, @@ -829,11 +830,12 @@ _bt_vacuum_needs_cleanup(IndexVacuumInfo *info) relopts->vacuum_cleanup_index_scale_factor >= 0) ? relopts->vacuum_cleanup_index_scale_factor : vacuum_cleanup_index_scale_factor; + prev_num_heap_tuples = metad->btm_last_cleanup_num_heap_tuples; 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) + prev_num_heap_tuples < 0 || + (info->num_heap_tuples - prev_num_heap_tuples) / + prev_num_heap_tuples >= cleanup_scale_factor) result = true; } diff --git a/src/backend/utils/misc/guc.c b/src/backend/utils/misc/guc.c index 859ef931e7..daa2894c5a 100644 --- a/src/backend/utils/misc/guc.c +++ b/src/backend/utils/misc/guc.c @@ -3253,7 +3253,7 @@ static struct config_real ConfigureNamesReal[] = NULL }, &vacuum_cleanup_index_scale_factor, - 0.1, 0.0, 100.0, + 0.1, 0.0, DBL_MAX, NULL, NULL, NULL }, diff --git a/src/test/regress/expected/btree_index.out b/src/test/regress/expected/btree_index.out index 4778ac14a4..1ad33190a2 100644 --- a/src/test/regress/expected/btree_index.out +++ b/src/test/regress/expected/btree_index.out @@ -165,7 +165,7 @@ select reloptions from pg_class WHERE oid = 'btree_idx1'::regclass; -- Fail while setting improper values create index btree_idx_err on btree_test(a) with (vacuum_cleanup_index_scale_factor = -10.0); ERROR: value -10.0 out of bounds for option "vacuum_cleanup_index_scale_factor" -DETAIL: Valid values are between "0.000000" and "100.000000". +DETAIL: Valid values are between "0.000000" and "179769313486231570814527423731704356798070567525844996598917476803157260780028538760589558632766878171540458953514382464234321326889464182768467546703537516986049910576551282076245490090389328944075868508455133942304583236903222948165808559332123348274797826204144723168738177180919299881250404026184124858368.000000". create index btree_idx_err on btree_test(a) with (vacuum_cleanup_index_scale_factor = 100.0); create index btree_idx_err on btree_test(a) with (vacuum_cleanup_index_scale_factor = 'string'); ERROR: invalid value for floating point option "vacuum_cleanup_index_scale_factor": string -- 2.40.0