On Tue, Mar 5, 2019 at 8:01 PM Kyotaro HORIGUCHI
<horiguchi.kyot...@lab.ntt.co.jp> wrote:
>
> Hello, I have some other comments.
>
Thank you for the comment!
On Tue, Mar 5, 2019 at 8:01 PM Kyotaro HORIGUCHI
<horiguchi.kyot...@lab.ntt.co.jp> wrote:
>
>
> + nleft; /* item pointers we left */
>
> The name seems to be something other, and the comment doesn't
> makes sense at least.. for me.. Looking below,
>
> + "%.0f tuples are left as dead.\n",
> + nleft),
> + nleft);
>
> How about "nleft_dead; /* iterm pointers left as dead */"?
Fixed.
>
>
>
> In this block:
>
> - if (nindexes == 0 &&
> + if ((nindexes == 0 || skip_index_vacuum) &&
> vacrelstats->num_dead_tuples > 0)
> {
>
> Is it right that vacuumed_pages is incremented and FSM is updated
> while the page is not actually vacuumed?
Good catch. I think the FSM stuff is right because we actually did HOT
pruning but the increment of vacuumed_page seems wrong to me. I think
we should not increment it and not report "removed XX row version in
YY pages" message.
>
>
> tups_vacuumed += heap_page_prune(onerel, buf, OldestXmin, false,
> - &vacrelstats->latestRemovedXid);
> + &vacrelstats->latestRemovedXid,
> + &tups_pruned);
>
> tups_pruned looks as "HOT pruned tuples". It is named "unused" in
> the function's parameters. (But I think it is useless. Please see
> the details below.)
>
>
> I tested it with a simple data set.
>
> (autovacuum = off)
> drop table if exists t;
> create table t with (fillfactor=50) as select a, a % 3 as b from
> generate_series(1, 9) a;
> create index on t(a);
> update t set a = -a where b = 0;
> update t set b = b + 1 where b = 1;
>
> We now have 9 tuples, 15 versions and 3 out of 6 "old" tuples are
> to be "left dead" by DISABLE_INDEX_CLEANUP vacuum. It means,
> three tuples ends with "left dead", three tuples are removed and
> 12 tuples will survive the vacuum below.
>
> vacuum (verbose, freeze ,disable_index_cleanup, disable_page_skipping) t;
>
> > INFO: "t": removed 0 row versions in 1 pages
> > INFO: "t": found 0 removable, 9 nonremovable row versions in 1 out of 1
> > pages
> > DETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 925
> > There were 0 unused item pointers.
> > Skipped 0 pages due to buffer pins, 0 frozen pages.
> > 0 pages are entirely empty.
> > 3 tuples are left as dead.
>
> Three tuple versions have vanished. Actually they were removed
> but not shown in the message.
>
> heap_prune_chain() doesn't count a live root entry of a chain as
> "unused (line pointer)" since it is marked as "redierected". As
> the result the vanished tuples are counted in tups_vacuumed, not
> in tups_pruned. Maybe the name tups_vacuumed is confusing. After
> removing tups_pruned code it works correctly.
>
> > INFO: "t": removed 6 row versions in 1 pages
> > INFO: "t": found 6 removable, 9 nonremovable row versions in 1 out of 1
> > pages
> > DETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 932
> > There were 0 unused item pointers.
> > Skipped 0 pages due to buffer pins, 0 frozen pages.
> > 0 pages are entirely empty.
> > 3 tuples are left as dead.
>
> I see two choices of the second line above.
>
> 1> "t": found 6 removable, 9 nonremovable row versions in 1 out of 1 pages
>
> "removable" includes "left dead" tuples.
>
> 2> "t": found 3 removable, 12 nonremovable row versions in 1 out of 1 pages
>
> "removable" excludes "left dead" tuples.
>
> If you prefer the latter, removable and nonremoveable need to be
> corrected using nleft.
I think that the first vacuum should report the former message because
it's true that the table has 6 removable tuples. We remove 6 tuples
but leave 3 item pointers. So in the second vacuum, it should be
"found 0 removable, 9 nonremovable row versions ..." and "3 tuples are
left as dead". But to report more precisely it'd be better to report
"0 tuples and 3 item identifiers are left as dead" here.
Attached updated patch incorporated all of comments. Also I've added
new reloption vacuum_index_cleanup as per discussion on the "reloption
to prevent VACUUM from truncating empty pages at the end of relation"
thread. Autovacuums also can skip index cleanup when the reloption is
set to false. Since the setting this to false might lead some problems
I've made autovacuums report the number of dead tuples and dead
itemids we left.
Regards,
Masahiko Sawada
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center
From 70358ea1ae7d7c8f78d9101176f4cc1652c3c978 Mon Sep 17 00:00:00 2001
From: Masahiko Sawada <sawada.mshk@gmail.com>
Date: Fri, 1 Feb 2019 16:02:50 +0100
Subject: [PATCH v8 2/2] Add --diable-index-cleanup option to vacuumdb.
---
doc/src/sgml/ref/vacuumdb.sgml | 15 +++++++++++++++
src/bin/scripts/t/100_vacuumdb.pl | 9 ++++++++-
src/bin/scripts/vacuumdb.c | 29 +++++++++++++++++++++++++++++
3 files changed, 52 insertions(+), 1 deletion(-)
diff --git a/doc/src/sgml/ref/vacuumdb.sgml b/doc/src/sgml/ref/vacuumdb.sgml
index 41c7f3d..02d6e46 100644
--- a/doc/src/sgml/ref/vacuumdb.sgml
+++ b/doc/src/sgml/ref/vacuumdb.sgml
@@ -118,6 +118,21 @@ PostgreSQL documentation
</varlistentry>
<varlistentry>
+ <term><option>--disable-index-cleanup</option></term>
+ <listitem>
+ <para>
+ Disable index vacuuming and index cleanup.
+ </para>
+ <note>
+ <para>
+ This option is only available for servers running
+ <productname>PostgreSQL</productname> 12 and later.
+ </para>
+ </note>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
<term><option>-e</option></term>
<term><option>--echo</option></term>
<listitem>
diff --git a/src/bin/scripts/t/100_vacuumdb.pl b/src/bin/scripts/t/100_vacuumdb.pl
index 7f3a9b1..d227225 100644
--- a/src/bin/scripts/t/100_vacuumdb.pl
+++ b/src/bin/scripts/t/100_vacuumdb.pl
@@ -3,7 +3,7 @@ use warnings;
use PostgresNode;
use TestLib;
-use Test::More tests => 44;
+use Test::More tests => 47;
program_help_ok('vacuumdb');
program_version_ok('vacuumdb');
@@ -38,6 +38,10 @@ $node->issues_sql_like(
qr/statement: VACUUM \(DISABLE_PAGE_SKIPPING\).*;/,
'vacuumdb --disable-page-skipping');
$node->issues_sql_like(
+ [ 'vacuumdb', '--disable-index-cleanup', 'postgres' ],
+ qr/statement: VACUUM \(DISABLE_INDEX_CLEANUP\).*;/,
+ 'vacuumdb --disable-index-cleanup');
+$node->issues_sql_like(
[ 'vacuumdb', '--skip-locked', 'postgres' ],
qr/statement: VACUUM \(SKIP_LOCKED\).*;/,
'vacuumdb --skip-locked');
@@ -48,6 +52,9 @@ $node->issues_sql_like(
$node->command_fails(
[ 'vacuumdb', '--analyze-only', '--disable-page-skipping', 'postgres' ],
'--analyze-only and --disable-page-skipping specified together');
+$node->command_fails(
+ [ 'vacuumdb', '--analyze-only', '--disable-index-cleanup', 'postgres' ],
+ '--analyze-only and --disable-index-cleanup specified together');
$node->command_ok([qw(vacuumdb -Z --table=pg_am dbname=template1)],
'vacuumdb with connection string');
diff --git a/src/bin/scripts/vacuumdb.c b/src/bin/scripts/vacuumdb.c
index 5ac41ea..4705217 100644
--- a/src/bin/scripts/vacuumdb.c
+++ b/src/bin/scripts/vacuumdb.c
@@ -42,6 +42,7 @@ typedef struct vacuumingOptions
bool full;
bool freeze;
bool disable_page_skipping;
+ bool disable_index_cleanup;
bool skip_locked;
int min_xid_age;
int min_mxid_age;
@@ -117,6 +118,7 @@ main(int argc, char *argv[])
{"skip-locked", no_argument, NULL, 5},
{"min-xid-age", required_argument, NULL, 6},
{"min-mxid-age", required_argument, NULL, 7},
+ {"disable-index-cleanup", no_argument, NULL, 8},
{NULL, 0, NULL, 0}
};
@@ -244,6 +246,11 @@ main(int argc, char *argv[])
exit(1);
}
break;
+ case 8:
+ {
+ vacopts.disable_index_cleanup = true;
+ break;
+ }
default:
fprintf(stderr, _("Try \"%s --help\" for more information.\n"), progname);
exit(1);
@@ -288,6 +295,12 @@ main(int argc, char *argv[])
progname, "disable-page-skipping");
exit(1);
}
+ if (vacopts.disable_index_cleanup)
+ {
+ fprintf(stderr, _("%s: cannot use the \"%s\" option when performing only analyze\n"),
+ progname, "disable-index-cleanup");
+ exit(1);
+ }
/* allow 'and_analyze' with 'analyze_only' */
}
@@ -418,6 +431,14 @@ vacuum_one_database(const char *dbname, vacuumingOptions *vacopts,
exit(1);
}
+ if (vacopts->disable_index_cleanup && PQserverVersion(conn) < 120000)
+ {
+ PQfinish(conn);
+ fprintf(stderr, _("%s: cannot use the \"%s\" option on server versions older than PostgreSQL 12\n"),
+ progname, "disable-index-cleanup");
+ exit(1);
+ }
+
if (vacopts->skip_locked && PQserverVersion(conn) < 120000)
{
PQfinish(conn);
@@ -868,6 +889,13 @@ prepare_vacuum_command(PQExpBuffer sql, int serverVersion,
appendPQExpBuffer(sql, "%sDISABLE_PAGE_SKIPPING", sep);
sep = comma;
}
+ if (vacopts->disable_index_cleanup)
+ {
+ /* DISABLE_INDEX_CLEANUP is supported since 12 */
+ Assert(serverVersion >= 120000);
+ appendPQExpBuffer(sql, "%sDISABLE_INDEX_CLEANUP", sep);
+ sep = comma;
+ }
if (vacopts->skip_locked)
{
/* SKIP_LOCKED is supported since v12 */
@@ -1221,6 +1249,7 @@ help(const char *progname)
printf(_(" -a, --all vacuum all databases\n"));
printf(_(" -d, --dbname=DBNAME database to vacuum\n"));
printf(_(" --disable-page-skipping disable all page-skipping behavior\n"));
+ printf(_(" --disable-index-cleanup disable index vacuuming and index cleanup\n"));
printf(_(" -e, --echo show the commands being sent to the server\n"));
printf(_(" -f, --full do full vacuuming\n"));
printf(_(" -F, --freeze freeze row transaction information\n"));
--
2.10.5
From ecfaa508634bd7b17e43d9806bc0653617e2f296 Mon Sep 17 00:00:00 2001
From: Masahiko Sawada <sawada.mshk@gmail.com>
Date: Mon, 21 Jan 2019 19:07:44 +0900
Subject: [PATCH v8 1/2] Add DISABLE_INDEX_CLEANUP option to VACUUM command
With this option, VACUUM does HOT-pruning for live tuples but doesn't
remove dead tuples completely and disables index vacuum.
vacrelstats->dead_tuples could have tuples that became dead after
checked at a HOT-pruning time, which are not marked as dead. Per
discussion on pgsql-hackers We normally records and remove them but
with this option we don't process and leave for the next vacuum for
simplifing the code. That's okay because it's very rare condition and
those tuples will be processed by the next vacuum.
---
doc/src/sgml/ref/create_table.sgml | 16 ++++++
doc/src/sgml/ref/vacuum.sgml | 29 ++++++++++-
src/backend/access/common/reloptions.c | 13 ++++-
src/backend/access/heap/vacuumlazy.c | 93 +++++++++++++++++++++++++++-------
src/backend/commands/vacuum.c | 15 +++++-
src/backend/parser/gram.y | 2 +
src/bin/psql/tab-complete.c | 4 +-
src/include/nodes/parsenodes.h | 3 +-
src/include/utils/rel.h | 1 +
src/test/regress/expected/vacuum.out | 3 ++
src/test/regress/sql/vacuum.sql | 3 ++
11 files changed, 159 insertions(+), 23 deletions(-)
diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml
index 22dbc07..cd9cea9 100644
--- a/doc/src/sgml/ref/create_table.sgml
+++ b/doc/src/sgml/ref/create_table.sgml
@@ -1345,6 +1345,22 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
</varlistentry>
<varlistentry>
+ <term><literal>vacuum_index_cleanup</literal> (<type>boolean</type>)</term>
+ <listitem>
+ <para>
+ Per table setting to use <literal>DISABLE_INDEX_CLEANUP</literal> option
+ of <command>VACUUM</command> command. The default value is true. If false,
+ autovacuum daemon and <command>VACUUM</command> never perform index vacuuming
+ and index cleanup, that is, always set <literal>DISABLE_INDEX_CLEANUP</literal>
+ option. Note that out of disk space due to index bloat. Setting this
+ parameter to false makes sense to avoid scanning large indexes when the
+ table has a few dead tuples. See <xref linkend="sql-vacuum"/> for more
+ details on <literal>DISABLE_INDEX_CLEANUP</literal> option.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
<term><literal>autovacuum_vacuum_threshold</literal>, <literal>toast.autovacuum_vacuum_threshold</literal> (<type>integer</type>)</term>
<listitem>
<para>
diff --git a/doc/src/sgml/ref/vacuum.sgml b/doc/src/sgml/ref/vacuum.sgml
index fd911f5..ac4a104 100644
--- a/doc/src/sgml/ref/vacuum.sgml
+++ b/doc/src/sgml/ref/vacuum.sgml
@@ -31,6 +31,7 @@ VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ ANALYZE ] [ <replaceable class="paramet
VERBOSE
ANALYZE
DISABLE_PAGE_SKIPPING
+ DISABLE_INDEX_CLEANUP
SKIP_LOCKED
<phrase>and <replaceable class="parameter">table_and_columns</replaceable> is:</phrase>
@@ -161,7 +162,26 @@ VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ ANALYZE ] [ <replaceable class="paramet
</listitem>
</varlistentry>
- <varlistentry>
+ <varlistentry>
+ <term><literal>DISABLE_INDEX_CLEANUP</literal></term>
+ <listitem>
+ <para>
+ <command>VACUUM</command> removes dead tuples and prunes HOT-updated
+ tuples chain for live tuples on table. If the table has any dead tuple
+ it removes them from both the table and its indexes and marks the
+ corresponding item identifers as available for re-use. With this option
+ <command>VACUUM</command> still removes dead tuples from the table, but
+ it does not process any indexes, and the item identifers are marked as
+ dead instead of available. This is suitable for avoiding transaction
+ ID wraparound (see <xref linkend="vacuum-for-wraparound"/>) but not
+ sufficient for avoiding index bloat. This option is ignored if the
+ table does not have index. This cannot be used in conjunction with
+ <literal>FULL</literal> option.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
<term><literal>SKIP_LOCKED</literal></term>
<listitem>
<para>
@@ -261,6 +281,13 @@ VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ ANALYZE ] [ <replaceable class="paramet
</para>
<para>
+ Setting a values for <literal>vacuum_index_cleanup</literal> via
+ <xref linkend="sql-altertable"/> also enables and disables index cleanup.
+ The <literal>DISABLE_INDEX_CLEANUP</literal> options to
+ <command>VACUUM</command> takes precedence over this option.
+ </para>
+
+ <para>
<command>VACUUM</command> causes a substantial increase in I/O traffic,
which might cause poor performance for other active sessions. Therefore,
it is sometimes advisable to use the cost-based vacuum delay feature.
diff --git a/src/backend/access/common/reloptions.c b/src/backend/access/common/reloptions.c
index cdf1f4a..2cb423d 100644
--- a/src/backend/access/common/reloptions.c
+++ b/src/backend/access/common/reloptions.c
@@ -138,6 +138,15 @@ static relopt_bool boolRelOpts[] =
},
false
},
+ {
+ {
+ "vacuum_index_cleanup",
+ "Enables index vacuuming and index cleanup",
+ RELOPT_KIND_HEAP,
+ ShareUpdateExclusiveLock
+ },
+ true
+ },
/* list terminator */
{{NULL}}
};
@@ -1388,7 +1397,9 @@ default_reloptions(Datum reloptions, bool validate, relopt_kind kind)
{"parallel_workers", RELOPT_TYPE_INT,
offsetof(StdRdOptions, parallel_workers)},
{"vacuum_cleanup_index_scale_factor", RELOPT_TYPE_REAL,
- offsetof(StdRdOptions, vacuum_cleanup_index_scale_factor)}
+ offsetof(StdRdOptions, vacuum_cleanup_index_scale_factor)},
+ {"vacuum_index_cleanup", RELOPT_TYPE_BOOL,
+ offsetof(StdRdOptions, vacuum_index_cleanup)}
};
options = parseRelOptions(reloptions, validate, kind, &numoptions);
diff --git a/src/backend/access/heap/vacuumlazy.c b/src/backend/access/heap/vacuumlazy.c
index 9416c31..cad0f98 100644
--- a/src/backend/access/heap/vacuumlazy.c
+++ b/src/backend/access/heap/vacuumlazy.c
@@ -112,7 +112,12 @@
typedef struct LVRelStats
{
- /* hasindex = true means two-pass strategy; false means one-pass */
+ /*
+ * hasindex = true means two-pass strategy; false means one-pass.
+ *
+ * If DISABLE_INDEX_CLEANUP is used, hasindex may still be true,
+ * but we'll always use the one-pass strategy.
+ */
bool hasindex;
/* Overall statistics about rel */
BlockNumber old_rel_pages; /* previous value of pg_class.relpages */
@@ -125,6 +130,8 @@ typedef struct LVRelStats
double new_rel_tuples; /* new estimated total # of tuples */
double new_live_tuples; /* new estimated total # of live tuples */
double new_dead_tuples; /* new estimated total # of dead tuples */
+ double nleft_dead_tuples; /* # of dead tuples we left */
+ double nleft_dead_itemids; /* # of dead item pointers we left */
BlockNumber pages_removed;
double tuples_deleted;
BlockNumber nonempty_pages; /* actually, last nonempty page + 1 */
@@ -167,7 +174,8 @@ static bool should_attempt_truncation(LVRelStats *vacrelstats);
static void lazy_truncate_heap(Relation onerel, LVRelStats *vacrelstats);
static BlockNumber count_nondeletable_pages(Relation onerel,
LVRelStats *vacrelstats);
-static void lazy_space_alloc(LVRelStats *vacrelstats, BlockNumber relblocks);
+static void lazy_space_alloc(LVRelStats *vacrelstats, BlockNumber relblocks,
+ bool skip_index_vacuum);
static void lazy_record_dead_tuple(LVRelStats *vacrelstats,
ItemPointer itemptr);
static bool lazy_tid_reaped(ItemPointer itemptr, void *state);
@@ -403,6 +411,9 @@ heap_vacuum_rel(Relation onerel, int options, VacuumParams *params,
vacrelstats->new_rel_tuples,
vacrelstats->new_dead_tuples,
OldestXmin);
+ appendStringInfo(&buf, "%.0f tuples and %.0f item identifiers are left as dead.\n",
+ vacrelstats->nleft_dead_tuples,
+ vacrelstats->nleft_dead_itemids);
appendStringInfo(&buf,
_("buffer usage: %d hits, %d misses, %d dirtied\n"),
VacuumPageHit,
@@ -485,13 +496,17 @@ lazy_scan_heap(Relation onerel, int options, LVRelStats *vacrelstats,
live_tuples, /* live tuples (reltuples estimate) */
tups_vacuumed, /* tuples cleaned up by vacuum */
nkeep, /* dead-but-not-removable tuples */
- nunused; /* unused item pointers */
+ nunused, /* unused item pointers */
+ nleft_dead_tuples, /* tuples we left as dead */
+ nleft_dead_itemids; /* item pointers we left as dead,
+ * includes nleft_dead_tuples. */
IndexBulkDeleteResult **indstats;
int i;
PGRUsage ru0;
Buffer vmbuffer = InvalidBuffer;
BlockNumber next_unskippable_block;
bool skipping_blocks;
+ bool skip_index_vacuum = (options & VACOPT_DISABLE_INDEX_CLEANUP) != 0;
xl_heap_freeze_tuple *frozen;
StringInfoData buf;
const int initprog_index[] = {
@@ -517,7 +532,8 @@ lazy_scan_heap(Relation onerel, int options, LVRelStats *vacrelstats,
empty_pages = vacuumed_pages = 0;
next_fsm_block_to_vacuum = (BlockNumber) 0;
- num_tuples = live_tuples = tups_vacuumed = nkeep = nunused = 0;
+ num_tuples = live_tuples = tups_vacuumed = nkeep = nunused =
+ nleft_dead_itemids = nleft_dead_tuples = 0;
indstats = (IndexBulkDeleteResult **)
palloc0(nindexes * sizeof(IndexBulkDeleteResult *));
@@ -529,7 +545,7 @@ lazy_scan_heap(Relation onerel, int options, LVRelStats *vacrelstats,
vacrelstats->nonempty_pages = 0;
vacrelstats->latestRemovedXid = InvalidTransactionId;
- lazy_space_alloc(vacrelstats, nblocks);
+ lazy_space_alloc(vacrelstats, nblocks, skip_index_vacuum);
frozen = palloc(sizeof(xl_heap_freeze_tuple) * MaxHeapTuplesPerPage);
/* Report that we're scanning the heap, advertising total # of blocks */
@@ -722,6 +738,9 @@ lazy_scan_heap(Relation onerel, int options, LVRelStats *vacrelstats,
};
int64 hvp_val[2];
+ /* Index vacuum must be enabled in two-pass vacuum */
+ Assert(!skip_index_vacuum);
+
/*
* Before beginning index vacuuming, we release any pin we may
* hold on the visibility map page. This isn't necessary for
@@ -1054,7 +1073,16 @@ lazy_scan_heap(Relation onerel, int options, LVRelStats *vacrelstats,
HeapTupleIsHeapOnly(&tuple))
nkeep += 1;
else
+ {
tupgone = true; /* we can delete the tuple */
+
+ /*
+ * Since the dead tuples will be not be vacuumed
+ * and ignored when index vacuum is disabled we
+ * count them for reporting.
+ */
+ nleft_dead_tuples++;
+ }
all_visible = false;
break;
case HEAPTUPLE_LIVE:
@@ -1206,15 +1234,33 @@ lazy_scan_heap(Relation onerel, int options, LVRelStats *vacrelstats,
}
/*
- * If there are no indexes then we can vacuum the page right now
- * instead of doing a second scan.
+ * If there are no indexes or index vacuum is disabled we can
+ * vacuum the page right now instead of doing a second scan.
*/
- if (nindexes == 0 &&
+ if ((nindexes == 0 || skip_index_vacuum) &&
vacrelstats->num_dead_tuples > 0)
{
- /* Remove tuples from heap */
- lazy_vacuum_page(onerel, blkno, buf, 0, vacrelstats, &vmbuffer);
- has_dead_tuples = false;
+ if (nindexes == 0)
+ {
+ /* Remove tuples from heap if the table has no index */
+ lazy_vacuum_page(onerel, blkno, buf, 0, vacrelstats, &vmbuffer);
+ vacuumed_pages++;
+ has_dead_tuples = false;
+ }
+ else
+ {
+ /*
+ * Here, we have indexes but index vacuum is disabled. Instead of
+ * vacuuming the dead tuples on the heap, we just forget them.
+ *
+ * Note that vacrelstats->dead_tuples could have tuples which
+ * became dead after HOT-pruning but are not marked dead yet.
+ * We do not process them because it's a very rare condition, and
+ * the next vacuum will process them anyway.
+ */
+ Assert(skip_index_vacuum);
+ nleft_dead_itemids += vacrelstats->num_dead_tuples;
+ }
/*
* Forget the now-vacuumed tuples, and press on, but be careful
@@ -1222,7 +1268,6 @@ lazy_scan_heap(Relation onerel, int options, LVRelStats *vacrelstats,
* valid.
*/
vacrelstats->num_dead_tuples = 0;
- vacuumed_pages++;
/*
* Periodically do incremental FSM vacuuming to make newly-freed
@@ -1348,7 +1393,9 @@ lazy_scan_heap(Relation onerel, int options, LVRelStats *vacrelstats,
/* save stats for use later */
vacrelstats->tuples_deleted = tups_vacuumed;
- vacrelstats->new_dead_tuples = nkeep;
+ vacrelstats->new_dead_tuples = nkeep + nleft_dead_tuples;
+ vacrelstats->nleft_dead_tuples = nleft_dead_tuples;
+ vacrelstats->nleft_dead_itemids = nleft_dead_itemids;
/* now we can compute the new value for pg_class.reltuples */
vacrelstats->new_live_tuples = vac_estimate_reltuples(onerel,
@@ -1379,6 +1426,9 @@ lazy_scan_heap(Relation onerel, int options, LVRelStats *vacrelstats,
};
int64 hvp_val[2];
+ /* Index vacuum must be enabled in two-pass vacuum */
+ Assert(!skip_index_vacuum);
+
/* Log cleanup info before we touch indexes */
vacuum_log_cleanup_info(onerel, vacrelstats);
@@ -1417,10 +1467,14 @@ lazy_scan_heap(Relation onerel, int options, LVRelStats *vacrelstats,
PROGRESS_VACUUM_PHASE_INDEX_CLEANUP);
/* Do post-vacuum cleanup and statistics update for each index */
- for (i = 0; i < nindexes; i++)
- lazy_cleanup_index(Irel[i], indstats[i], vacrelstats);
+ if (!skip_index_vacuum)
+ for (i = 0; i < nindexes; i++)
+ lazy_cleanup_index(Irel[i], indstats[i], vacrelstats);
- /* If no indexes, make log report that lazy_vacuum_heap would've made */
+ /*
+ * If no index or index vacuum is disabled, make log report that
+ * lazy_vacuum_heap would've make.
+ */
if (vacuumed_pages)
ereport(elevel,
(errmsg("\"%s\": removed %.0f row versions in %u pages",
@@ -1449,6 +1503,8 @@ lazy_scan_heap(Relation onerel, int options, LVRelStats *vacrelstats,
"%u pages are entirely empty.\n",
empty_pages),
empty_pages);
+ appendStringInfo(&buf, "%.0f tuples and %.0f item identifiers are left as dead.\n",
+ nleft_dead_tuples, nleft_dead_itemids);
appendStringInfo(&buf, _("%s."), pg_rusage_show(&ru0));
ereport(elevel,
@@ -2085,14 +2141,15 @@ count_nondeletable_pages(Relation onerel, LVRelStats *vacrelstats)
* See the comments at the head of this file for rationale.
*/
static void
-lazy_space_alloc(LVRelStats *vacrelstats, BlockNumber relblocks)
+lazy_space_alloc(LVRelStats *vacrelstats, BlockNumber relblocks,
+ bool skip_index_vacuum)
{
long maxtuples;
int vac_work_mem = IsAutoVacuumWorkerProcess() &&
autovacuum_work_mem != -1 ?
autovacuum_work_mem : maintenance_work_mem;
- if (vacrelstats->hasindex)
+ if (vacrelstats->hasindex && !skip_index_vacuum)
{
maxtuples = (vac_work_mem * 1024L) / sizeof(ItemPointerData);
maxtuples = Min(maxtuples, INT_MAX);
diff --git a/src/backend/commands/vacuum.c b/src/backend/commands/vacuum.c
index e91df21..a0f2622 100644
--- a/src/backend/commands/vacuum.c
+++ b/src/backend/commands/vacuum.c
@@ -203,7 +203,8 @@ vacuum(int options, List *relations, VacuumParams *params,
stmttype)));
/*
- * Sanity check DISABLE_PAGE_SKIPPING option.
+ * Sanity check DISABLE_PAGE_SKIPPING option and DISABLE_INDEX_CLEANUP
+ * option.
*/
if ((options & VACOPT_FULL) != 0 &&
(options & VACOPT_DISABLE_PAGE_SKIPPING) != 0)
@@ -211,6 +212,11 @@ vacuum(int options, List *relations, VacuumParams *params,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("VACUUM option DISABLE_PAGE_SKIPPING cannot be used with FULL")));
+ if ((options & VACOPT_FULL) != 0 &&
+ (options & VACOPT_DISABLE_INDEX_CLEANUP) != 0)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("VACUUM option DISABLE_INDEX_CLEANUP cannot be used with FULL")));
/*
* Send info about dead objects to the statistics collector, unless we are
* in autovacuum --- autovacuum.c does this for itself.
@@ -1672,6 +1678,13 @@ vacuum_rel(Oid relid, RangeVar *relation, int options, VacuumParams *params)
LockRelationIdForSession(&onerelid, lmode);
/*
+ * Disables index cleanup based on reloptions.
+ */
+ if (onerel->rd_options &&
+ !((StdRdOptions *) onerel->rd_options)->vacuum_index_cleanup)
+ options |= VACOPT_DISABLE_INDEX_CLEANUP;
+
+ /*
* Remember the relation's TOAST relation for later, if the caller asked
* us to process it. In VACUUM FULL, though, the toast table is
* automatically rebuilt by cluster_rel so we shouldn't recurse to it.
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 0279013..0b13ad7 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -10471,6 +10471,8 @@ vacuum_option_elem:
{
if (strcmp($1, "disable_page_skipping") == 0)
$$ = VACOPT_DISABLE_PAGE_SKIPPING;
+ else if (strcmp($1, "disable_index_cleanup") == 0)
+ $$ = VACOPT_DISABLE_INDEX_CLEANUP;
else if (strcmp($1, "skip_locked") == 0)
$$ = VACOPT_SKIP_LOCKED;
else
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index 7b7a88f..b4e416d 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -1039,6 +1039,7 @@ static const char *const table_storage_parameters[] = {
"toast.log_autovacuum_min_duration",
"toast_tuple_target",
"user_catalog_table",
+ "vacuum_index_cleanup",
NULL
};
@@ -3423,7 +3424,8 @@ psql_completion(const char *text, int start, int end)
*/
if (ends_with(prev_wd, '(') || ends_with(prev_wd, ','))
COMPLETE_WITH("FULL", "FREEZE", "ANALYZE", "VERBOSE",
- "DISABLE_PAGE_SKIPPING");
+ "DISABLE_PAGE_SKIPPING",
+ "DISABLE_INDEX_CLEANUP");
}
else if (HeadMatches("VACUUM") && TailMatches("("))
/* "VACUUM (" should be caught above, so assume we want columns */
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index a7e859d..f6d52ac 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -3163,7 +3163,8 @@ typedef enum VacuumOption
VACOPT_FULL = 1 << 4, /* FULL (non-concurrent) vacuum */
VACOPT_SKIP_LOCKED = 1 << 5, /* skip if cannot get lock */
VACOPT_SKIPTOAST = 1 << 6, /* don't process the TOAST table, if any */
- VACOPT_DISABLE_PAGE_SKIPPING = 1 << 7 /* don't skip any pages */
+ VACOPT_DISABLE_PAGE_SKIPPING = 1 << 7, /* don't skip any pages */
+ VACOPT_DISABLE_INDEX_CLEANUP = 1 << 8 /* skip index vacuum and cleanup */
} VacuumOption;
/*
diff --git a/src/include/utils/rel.h b/src/include/utils/rel.h
index 1d05465..14e325e 100644
--- a/src/include/utils/rel.h
+++ b/src/include/utils/rel.h
@@ -253,6 +253,7 @@ typedef struct StdRdOptions
AutoVacOpts autovacuum; /* autovacuum-related options */
bool user_catalog_table; /* use as an additional catalog relation */
int parallel_workers; /* max number of parallel workers */
+ bool vacuum_index_cleanup; /* enables index vacuuming and cleanup */
} StdRdOptions;
#define HEAP_MIN_FILLFACTOR 10
diff --git a/src/test/regress/expected/vacuum.out b/src/test/regress/expected/vacuum.out
index fa9d663..cffce59 100644
--- a/src/test/regress/expected/vacuum.out
+++ b/src/test/regress/expected/vacuum.out
@@ -80,6 +80,9 @@ CONTEXT: SQL function "do_analyze" statement 1
SQL function "wrap_do_analyze" statement 1
VACUUM FULL vactst;
VACUUM (DISABLE_PAGE_SKIPPING) vaccluster;
+VACUUM (DISABLE_INDEX_CLEANUP) vaccluster;
+VACUUM (DISABLE_INDEX_CLEANUP) vactst; -- DISABLE_INDEX_CLEANUP is ignored
+VACUUM (DISABLE_INDEX_CLEANUP, FREEZE) vaccluster;
-- partitioned table
CREATE TABLE vacparted (a int, b char) PARTITION BY LIST (a);
CREATE TABLE vacparted1 PARTITION OF vacparted FOR VALUES IN (1);
diff --git a/src/test/regress/sql/vacuum.sql b/src/test/regress/sql/vacuum.sql
index 9defa0d..9c4bdb7 100644
--- a/src/test/regress/sql/vacuum.sql
+++ b/src/test/regress/sql/vacuum.sql
@@ -61,6 +61,9 @@ VACUUM FULL vaccluster;
VACUUM FULL vactst;
VACUUM (DISABLE_PAGE_SKIPPING) vaccluster;
+VACUUM (DISABLE_INDEX_CLEANUP) vaccluster;
+VACUUM (DISABLE_INDEX_CLEANUP) vactst; -- DISABLE_INDEX_CLEANUP is ignored
+VACUUM (DISABLE_INDEX_CLEANUP, FREEZE) vaccluster;
-- partitioned table
CREATE TABLE vacparted (a int, b char) PARTITION BY LIST (a);
--
2.10.5
