Hi, I would like to propose adding logic to recheck whether an ANALYZE is necessary at the end of VACUUM. I believe this could be a viable idea to mitigate a problem encountered by my customer.
The customer’s issue occurred as follows: 1. Every 5 minutes, a large amount of data (2 million records) was updated via DELETE/COPY operations. 2. Autovacuum executed VACUUM/ANALYZE, and the VACUUM process took 2 hours to complete. 3. Before the next autovacuum run (autovacuum_naptime=1min), no further DELETE/COPY operations occurred, leaving mod_since_analyze=0. 4. Autovacuum then ran VACUUM without ANALYZE, taking another 2 hours to complete. 5. At the end of this VACUUM, pg_class.reltuples was updated to a very small value (150), far lower than the actual row count (2 million). 6. The execution plan changed, resulting in a significant degradation in query performance (500ms → 1000s). The bitmap index scan node was estimated to return 30 rows but actually returned 50 million rows. 7. Query performance remained degraded until the next autovacuum run, which finally executed ANALYZE. However, since the next VACUUM wouldalso take 2 hours, performance degradation persisted during that period.
As you may already be thinking, there are ways to address or mitigate this issue depending on the application requirements and hardware configuration. For example, using TRUNCATE or adjusting parameters like cost_delay to speed up VACUUM can prevent table bloat and avoid significant query performance degradation. However, certain system requirements may make these solutions impractical. Even in such cases, if there is a way to mitigate the problem,
it would be worth adopting. Therefore, I decided to propose $SUBJECT.VACUUM updates pg_class.reltuples based on the state of tuples it observes during processing. However, it cannot observe tuples stored in pages that have already been vacuumed, which causes an issue. For example, in cases like this, there is a possibility that pg_class.reltuples may be updated to a value significantly smaller than the actual row count. To address such situations, I am considering introducing a mechanism to recheck whether an ANALYZE is necessary, particularly when a VACUUM process takes a long time. This would
ensure that ANALYZE is executed promptly in such cases. What do you think about this approach?This approach cannot completely prevent the issue. At the very least, between
the completion of VACUUM and the subsequent execution of ANALYZE,pg_class.reltuples will contain highly inaccurate values. However, it can
significantly reduce the time during which the problem occurs, thereby mitigating the impact of incorrect statistics.As a potential complete solution, we could consider skipping the update of statistics by VACUUM when an immediate ANALYZE is expected. However, I see this as a next step after adding the logic to recheck the necessity of ANALYZE.
Attached are two files:* reproduce_small_reltuples.tar.gz: Steps to reproduce the issue where reltuples
is updated to a small value at the end of VACUUM.* v1-0001-Add-logic-to-recheck-if-ANALYZE-is-needed-after-V.patch: A patch implementing the proposed logic to recheck the necessity of ANALYZE after VACUUM.
Concerns about the v1 patch: There may be other issues or better ideas, but here are my current concerns: * The number of accesses to pg_class increases, which could lead to performance issues, although I haven’t conducted performance tests.* When checking the necessity of ANALYZE, should the latest parameter values be referenced? The current implementation uses the parameter values at the
time of VACUUM execution, except for pg_class.reltuples. I welcome your feedback. I’m open to alternative ideas and not stick to this particular approach. Regards, -- Masahiro Ikeda NTT DATA CORPORATION
From 319f4c304f5e9ecb0933c63696fb36205442276b Mon Sep 17 00:00:00 2001
From: Masahiro Ikeda <ikeda...@oss.nttdata.com>
Date: Fri, 17 Jan 2025 17:46:19 +0900
Subject: [PATCH v1] Add logic to recheck if ANALYZE is needed after VACUUM
finishes by autovacuum
In cases where VACUUM runs for a long time and frequent data updates
occur, the reported pg_class.reltuples value by VACUUM may become
extremely small, potentially leading to query performance degradation.
This update introduces a mechanism to detect if statistical information
has been updated during a prolonged VACUUM process, allowing ANALYZE to
be executed immediately to mitigate the issue.
Note that query performance degradation may still occur for a short
period before ANALYZE is executed. To fully prevent this, further
changes, such as skipping the update of pg_class.reltuples by VACUUM,
would be necessary.
---
src/backend/postmaster/autovacuum.c | 213 ++++++++++++++++--
.../test_misc/t/008_recheck_autoanalyze.pl | 91 ++++++++
2 files changed, 289 insertions(+), 15 deletions(-)
create mode 100644 src/test/modules/test_misc/t/008_recheck_autoanalyze.pl
diff --git a/src/backend/postmaster/autovacuum.c b/src/backend/postmaster/autovacuum.c
index 0ab921a169b..486c354cea3 100644
--- a/src/backend/postmaster/autovacuum.c
+++ b/src/backend/postmaster/autovacuum.c
@@ -201,6 +201,8 @@ typedef struct autovac_table
VacuumParams at_params;
double at_storage_param_vac_cost_delay;
int at_storage_param_vac_cost_limit;
+ float4 anl_scale_factor;
+ int anl_base_thresh;
bool at_dobalance;
bool at_sharedrel;
char *at_relname;
@@ -334,12 +336,18 @@ static autovac_table *table_recheck_autovac(Oid relid, HTAB *table_toast_map,
static void recheck_relation_needs_vacanalyze(Oid relid, AutoVacOpts *avopts,
Form_pg_class classForm,
int effective_multixact_freeze_max_age,
- bool *dovacuum, bool *doanalyze, bool *wraparound);
+ bool *dovacuum, bool *doanalyze, bool *wraparound,
+ float4 *anl_scale_factor, int *anl_base_thresh);
+static void recheck_relation_needs_analyze(TupleDesc pg_class_desc, autovac_table *tab);
+
static void relation_needs_vacanalyze(Oid relid, AutoVacOpts *relopts,
Form_pg_class classForm,
PgStat_StatTabEntry *tabentry,
int effective_multixact_freeze_max_age,
- bool *dovacuum, bool *doanalyze, bool *wraparound);
+ bool *dovacuum, bool *doanalyze, bool *wraparound,
+ float4 *anl_scale_factor, int *anl_base_thresh);
+static void relation_needs_analyze(autovac_table *tab, Form_pg_class classForm,
+ PgStat_StatTabEntry *tabentry, bool *doanalyze);
static void autovacuum_do_vac_analyze(autovac_table *tab,
BufferAccessStrategy bstrategy);
@@ -1997,6 +2005,8 @@ do_autovacuum(void)
bool dovacuum;
bool doanalyze;
bool wraparound;
+ float4 anl_scale_factor;
+ int anl_base_thresh;
if (classForm->relkind != RELKIND_RELATION &&
classForm->relkind != RELKIND_MATVIEW)
@@ -2037,7 +2047,8 @@ do_autovacuum(void)
/* Check if it needs vacuum or analyze */
relation_needs_vacanalyze(relid, relopts, classForm, tabentry,
effective_multixact_freeze_max_age,
- &dovacuum, &doanalyze, &wraparound);
+ &dovacuum, &doanalyze, &wraparound,
+ &anl_scale_factor, &anl_base_thresh);
/* Relations that need work are added to table_oids */
if (dovacuum || doanalyze)
@@ -2090,6 +2101,8 @@ do_autovacuum(void)
bool dovacuum;
bool doanalyze;
bool wraparound;
+ float4 anl_scale_factor;
+ int anl_base_thresh;
/*
* We cannot safely process other backends' temp tables, so skip 'em.
@@ -2120,7 +2133,8 @@ do_autovacuum(void)
relation_needs_vacanalyze(relid, relopts, classForm, tabentry,
effective_multixact_freeze_max_age,
- &dovacuum, &doanalyze, &wraparound);
+ &dovacuum, &doanalyze, &wraparound,
+ &anl_scale_factor, &anl_base_thresh);
/* ignore analyze for toast tables */
if (dovacuum)
@@ -2470,6 +2484,80 @@ do_autovacuum(void)
did_vacuum = true;
+ /*
+ * If only VACUUM was executed, recheck whether the pgstat data
+ * indicates that ANALYZE is needed for this table.
+ *
+ * If VACUUM runs for a long time and frequent data updates occur,
+ * the reported pg_class.reltuples value may become extremely small,
+ * potentially leading to query performance degradation. Although
+ * the next ANALYZE will correct this, the next VACUUM might run
+ * for a long time again, and the degradation could persist for an
+ * extended period. This allows ANALYZE to be executed immediately.
+ */
+ if ((tab->at_params.options & VACOPT_VACUUM) &&
+ !(tab->at_params.options & VACOPT_ANALYZE))
+ {
+#ifdef USE_INJECTION_POINTS
+ char *rel_nspname = get_namespace_name(get_rel_namespace(relid));
+ if (strcmp(rel_nspname, "pg_catalog") != 0 &&
+ strcmp(rel_nspname, "information_schema") != 0 &&
+ strcmp(rel_nspname, "pg_toast") != 0)
+ INJECTION_POINT("before-recheck-autoanalyze");
+#endif
+ /* start transaction to fetch latest relation's relcache entry */
+ CommitTransactionCommand();
+ StartTransactionCommand();
+
+ recheck_relation_needs_analyze(pg_class_desc, tab);
+
+ if (tab->at_params.options & VACOPT_ANALYZE)
+ {
+ /*
+ * We will abort vacuuming the current table if something errors out,
+ * and continue with the next one in schedule; in particular, this
+ * happens if we are interrupted with SIGINT.
+ */
+ PG_TRY();
+ {
+ /* Use PortalContext for any per-table allocations */
+ MemoryContextSwitchTo(PortalContext);
+
+ /* have at it */
+ autovacuum_do_vac_analyze(tab, bstrategy);
+
+ /*
+ * Clear a possible query-cancel signal, to avoid a late reaction
+ * to an automatically-sent signal because of vacuuming the
+ * current table (we're done with it, so it would make no sense to
+ * cancel at this point.)
+ */
+ QueryCancelPending = false;
+ }
+ PG_CATCH();
+ {
+ /*
+ * Abort the transaction, start a new one, and proceed with the
+ * next table in our list.
+ */
+ HOLD_INTERRUPTS();
+ errcontext("automatic analyze of table \"%s.%s.%s\"",
+ tab->at_datname, tab->at_nspname, tab->at_relname);
+ EmitErrorReport();
+
+ /* this resets ProcGlobal->statusFlags[i] too */
+ AbortOutOfAnyTransaction();
+ FlushErrorState();
+ MemoryContextReset(PortalContext);
+
+ /* restart our transaction for the following operations */
+ StartTransactionCommand();
+ RESUME_INTERRUPTS();
+ }
+ PG_END_TRY();
+ }
+ }
+
/* ProcGlobal->statusFlags[i] are reset at the next end of xact */
/* be tidy */
@@ -2706,6 +2794,49 @@ extract_autovac_opts(HeapTuple tup, TupleDesc pg_class_desc)
return av;
}
+/*
+ * table_recheck_autoanalyze
+ *
+ * Recheck whether a table needs analyze after vacuum completes,
+ * and update tab->at_params.options.
+ */
+static void
+recheck_relation_needs_analyze(TupleDesc pg_class_desc, autovac_table *tab)
+{
+ HeapTuple classTup;
+ Form_pg_class classForm;
+ PgStat_StatTabEntry *tabentry;
+ bool doanalyze = false;
+
+ /* ANALYZE refuses to work with pg_statistic */
+ if (tab->at_relid == StatisticRelationId)
+ return;
+
+ /* fetch the relation's relcache entry */
+ classTup = SearchSysCacheCopy1(RELOID, ObjectIdGetDatum(tab->at_relid));
+ if (!HeapTupleIsValid(classTup))
+ return;
+ classForm = (Form_pg_class) GETSTRUCT(classTup);
+
+ /* ignore ANALYZE for toast tables */
+ if (classForm->relkind == RELKIND_TOASTVALUE)
+ {
+ heap_freetuple(classTup);
+ return;
+ }
+
+ /* fetch the pgstat table entry */
+ tabentry = pgstat_fetch_stat_tabentry_ext(classForm->relisshared,
+ tab->at_relid);
+
+ relation_needs_analyze(tab, classForm, tabentry, &doanalyze);
+
+ /* update options */
+ if (doanalyze)
+ tab->at_params.options = VACOPT_ANALYZE;
+
+ heap_freetuple(classTup);
+}
/*
* table_recheck_autovac
@@ -2727,6 +2858,8 @@ table_recheck_autovac(Oid relid, HTAB *table_toast_map,
autovac_table *tab = NULL;
bool wraparound;
AutoVacOpts *avopts;
+ float4 anl_scale_factor;
+ int anl_base_thresh;
/* fetch the relation's relcache entry */
classTup = SearchSysCacheCopy1(RELOID, ObjectIdGetDatum(relid));
@@ -2752,7 +2885,8 @@ table_recheck_autovac(Oid relid, HTAB *table_toast_map,
recheck_relation_needs_vacanalyze(relid, avopts, classForm,
effective_multixact_freeze_max_age,
- &dovacuum, &doanalyze, &wraparound);
+ &dovacuum, &doanalyze, &wraparound,
+ &anl_scale_factor, &anl_base_thresh);
/* OK, it needs something done */
if (doanalyze || dovacuum)
@@ -2830,6 +2964,8 @@ table_recheck_autovac(Oid relid, HTAB *table_toast_map,
avopts->vacuum_cost_limit : 0;
tab->at_storage_param_vac_cost_delay = avopts ?
avopts->vacuum_cost_delay : -1;
+ tab->anl_scale_factor = anl_scale_factor;
+ tab->anl_base_thresh = anl_base_thresh;
tab->at_relname = NULL;
tab->at_nspname = NULL;
tab->at_datname = NULL;
@@ -2862,7 +2998,9 @@ recheck_relation_needs_vacanalyze(Oid relid,
int effective_multixact_freeze_max_age,
bool *dovacuum,
bool *doanalyze,
- bool *wraparound)
+ bool *wraparound,
+ float4 *anl_scale_factor,
+ int *anl_base_thresh)
{
PgStat_StatTabEntry *tabentry;
@@ -2872,7 +3010,8 @@ recheck_relation_needs_vacanalyze(Oid relid,
relation_needs_vacanalyze(relid, avopts, classForm, tabentry,
effective_multixact_freeze_max_age,
- dovacuum, doanalyze, wraparound);
+ dovacuum, doanalyze, wraparound,
+ anl_scale_factor, anl_base_thresh);
/* ignore ANALYZE for toast tables */
if (classForm->relkind == RELKIND_TOASTVALUE)
@@ -2925,7 +3064,9 @@ relation_needs_vacanalyze(Oid relid,
/* output params below */
bool *dovacuum,
bool *doanalyze,
- bool *wraparound)
+ bool *wraparound,
+ float4 *anl_scale_factor,
+ int *anl_base_thresh)
{
bool force_vacuum;
bool av_enabled;
@@ -2933,11 +3074,9 @@ relation_needs_vacanalyze(Oid relid,
/* constants from reloptions or GUC variables */
int vac_base_thresh,
- vac_ins_base_thresh,
- anl_base_thresh;
+ vac_ins_base_thresh;
float4 vac_scale_factor,
- vac_ins_scale_factor,
- anl_scale_factor;
+ vac_ins_scale_factor;
/* thresholds calculated from above constants */
float4 vacthresh,
@@ -2983,11 +3122,11 @@ relation_needs_vacanalyze(Oid relid,
? relopts->vacuum_ins_threshold
: autovacuum_vac_ins_thresh;
- anl_scale_factor = (relopts && relopts->analyze_scale_factor >= 0)
+ *anl_scale_factor = (relopts && relopts->analyze_scale_factor >= 0)
? relopts->analyze_scale_factor
: autovacuum_anl_scale;
- anl_base_thresh = (relopts && relopts->analyze_threshold >= 0)
+ *anl_base_thresh = (relopts && relopts->analyze_threshold >= 0)
? relopts->analyze_threshold
: autovacuum_anl_thresh;
@@ -3048,7 +3187,7 @@ relation_needs_vacanalyze(Oid relid,
vacthresh = (float4) vac_base_thresh + vac_scale_factor * reltuples;
vacinsthresh = (float4) vac_ins_base_thresh + vac_ins_scale_factor * reltuples;
- anlthresh = (float4) anl_base_thresh + anl_scale_factor * reltuples;
+ anlthresh = (float4) *anl_base_thresh + *anl_scale_factor * reltuples;
/*
* Note that we don't need to take special consideration for stat
@@ -3085,6 +3224,50 @@ relation_needs_vacanalyze(Oid relid,
*doanalyze = false;
}
+/*
+ * relation_needs_analyze
+ *
+ * Check whether a relation needs to be analyzed. It reuses analyze equation
+ * parameters when called from relation_needs_vacanalyze().
+ */
+static void
+relation_needs_analyze(autovac_table *tab,
+ Form_pg_class classForm,
+ PgStat_StatTabEntry *tabentry,
+ /* output params below */
+ bool *doanalyze)
+{
+ /*
+ * If we found stats for the table, and autovacuum is currently enabled,
+ * make a threshold-based decision whether to analyze.
+ */
+ if (PointerIsValid(tabentry) && AutoVacuumingActive())
+ {
+ float4 reltuples;
+ float4 anlthresh;
+ float4 anltuples;
+
+ Assert(classForm != NULL);
+ Assert(OidIsValid(tab->at_relid));
+
+ reltuples = classForm->reltuples;
+ anltuples = tabentry->mod_since_analyze;
+
+ /* Vacuum could update the value to -1 */
+ if (reltuples < 0)
+ reltuples = 0;
+
+ /* Determine if this table needs analyze. */
+ anlthresh = (float4) tab->anl_base_thresh + tab->anl_scale_factor * reltuples;
+ *doanalyze = (anltuples > anlthresh);
+
+ elog(DEBUG3, "%s: anl: %.0f (threshold %.0f)",
+ NameStr(classForm->relname), anltuples, anlthresh);
+ }
+ else
+ *doanalyze = false;
+}
+
/*
* autovacuum_do_vac_analyze
* Vacuum and/or analyze the specified table
diff --git a/src/test/modules/test_misc/t/008_recheck_autoanalyze.pl b/src/test/modules/test_misc/t/008_recheck_autoanalyze.pl
new file mode 100644
index 00000000000..bd64ec7bc36
--- /dev/null
+++ b/src/test/modules/test_misc/t/008_recheck_autoanalyze.pl
@@ -0,0 +1,91 @@
+# Copyright (c) 2025, PostgreSQL Global Development Group
+
+# Tests rechecking whether an ANALYZE is required after an autovacuum
+# performs only a VACUUM on a table, even if the table didn't initially
+# require an ANALYZE when the VACUUM started.
+
+use strict;
+use warnings FATAL => 'all';
+use PostgreSQL::Test::Cluster;
+use Test::More;
+
+if ($ENV{enable_injection_points} ne 'yes')
+{
+ plan skip_all => 'Injection points not supported by this build';
+}
+
+# Initialize postgres
+my $psql_err = '';
+my $psql_out = '';
+my $node = PostgreSQL::Test::Cluster->new('node');
+$node->init;
+
+$node->append_conf(
+ 'postgresql.conf', qq[
+# This ensures a quick worker spawn.
+autovacuum_naptime = 1s
+log_autovacuum_min_duration = 0
+]);
+$node->start;
+
+# Check if the extension injection_points is available, as it may be
+# possible that this script is run with installcheck, where the module
+# would not be installed by default.
+if (!$node->check_extension('injection_points'))
+{
+ plan skip_all => 'Extension injection_points not installed';
+}
+
+$node->safe_psql('postgres', 'CREATE EXTENSION injection_points;');
+
+# From this point, autovacuum worker will wait after vacuuming one heap relation.
+$node->safe_psql('postgres',
+ "SELECT injection_points_attach('before-recheck-autoanalyze', 'wait');");
+
+# Grab location in logs of primary
+my $offset = -s $node->logfile;
+
+# Create a table and insert 70 rows of data, which exceeds the value of
+# autovacuum_vacuum_insert_threshold and doesn't exceeds the value of
+# autovacuum_analyze_threshold.
+$node->safe_psql(
+ 'postgres', qq[
+ CREATE TABLE public.test (id int) WITH
+ (autovacuum_vacuum_insert_threshold = 50, autovacuum_analyze_threshold = 100);
+ INSERT INTO public.test SELECT generate_series(1, 70);
+]);
+
+# Wait until an autovacuum worker starts.
+$node->wait_for_event('autovacuum worker', 'before-recheck-autoanalyze');
+
+# And grab one of them.
+my $av_pid = $node->safe_psql(
+ 'postgres',
+ "SELECT pid FROM pg_stat_activity "
+ . "WHERE backend_type = 'autovacuum worker' AND wait_event = 'before-recheck-autoanalyze';");
+
+# insert data to exceeds the value of autovacuum_analyze_threshold
+$node->safe_psql('postgres',
+ "INSERT INTO public.test SELECT generate_series(1, 70);");
+
+# Wakeup the injection point.
+$node->safe_psql('postgres',
+ "SELECT injection_points_wakeup('before-recheck-autoanalyze');");
+
+# Wait for the autovacuum worker to exit before scanning the logs.
+$node->poll_query_until('postgres',
+ "SELECT count(*) = 0 FROM pg_stat_activity "
+ . "WHERE pid = '$av_pid' AND backend_type = 'autovacuum worker';");
+
+# Check that ANALYZE is executed.
+ok($node->log_contains(
+ qr/\[$av_pid\] LOG: automatic analyze of table "postgres.public.test"/,
+ $offset),
+ "ANALYZE triggered by recheck after vacuum");
+
+# Release injection point.
+$node->safe_psql('postgres',
+ "SELECT injection_points_detach('before-recheck-autoanalyze');");
+
+$node->stop;
+done_testing();
--
2.34.1
reproduce_small_reltuples.tar.gz
Description: application/gzip
