Hi Hackers,
As discussed in the recent thread, I’m sharing an initial v1 patch
introducing an opt-in SMART mode for ANALYZE.
When ANALYZE (SMART) is specified, relations that have not been modified
since their last analyze are skipped, based on existing pg_stat counters
(n_mod_since_analyze = 0). Relations without existing statistics are
still analyzed normally. The default ANALYZE behavior remains unchanged.
The primary goal of this patch is to reduce unnecessary work when
running ANALYZE over many mostly-static tables, while keeping the
behavior strictly opt-in.
Scope of this v1 patch:
- Uses existing pg_stat statistics only
- Does not skip relations that were never analyzed before
- Includes regression tests demonstrating that only modified tables are
re-analyzed
- Partitioned tables, inheritance, foreign tables,extended statistics and
other edge cases are intentionally not handled yet; I plan to look into
those in follow-up work based on feedback
Example usage / how to observe behavior:
SET client_min_messages = debug1;
ANALYZE (SMART);
ANALYZE (SMART, VERBOSE);
ANALYZE (SMART) table1;
ANALYZE (SMART) table1, table2;
VACUUM(SMART);
Thanks for your time and review.
Best regards,
Vasuki M
C-DAC,Chennai
From 6fa990921c3a4e956bbbbaf61563ef639c21b240 Mon Sep 17 00:00:00 2001
From: Vasuki M <[email protected]>
Date: Wed, 21 Jan 2026 14:39:43 +0530
Subject: [PATCH v1] ANALYZE: add optional SMART mode to skip unchanged
relations
Introduce an opt-in SMART option for ANALYZE that skips relations which
have not been modified since their last analyze, based on pg_stat
counters (n_mod_since_analyze = 0).
When SMART is specified, relations with no recorded modifications since
the previous ANALYZE are skipped, while relations without existing
statistics are still analyzed normally. The default ANALYZE behavior is
unchanged.
This can reduce unnecessary work when analyzing databases with many
mostly-static tables.
Regression tests are included.
---
src/backend/commands/analyze.c | 25 ++++++++++
src/backend/commands/vacuum.c | 15 +++++-
src/include/commands/vacuum.h | 2 +-
src/test/regress/expected/analyze_smart.out | 51 +++++++++++++++++++++
src/test/regress/parallel_schedule | 1 +
src/test/regress/sql/analyze_smart.sql | 38 +++++++++++++++
6 files changed, 130 insertions(+), 2 deletions(-)
create mode 100644 src/test/regress/expected/analyze_smart.out
create mode 100644 src/test/regress/sql/analyze_smart.sql
diff --git a/src/backend/commands/analyze.c b/src/backend/commands/analyze.c
index a4834241..a4d445d9 100644
--- a/src/backend/commands/analyze.c
+++ b/src/backend/commands/analyze.c
@@ -45,6 +45,7 @@
#include "storage/bufmgr.h"
#include "storage/procarray.h"
#include "utils/attoptcache.h"
+#include "utils/relcache.h"
#include "utils/datum.h"
#include "utils/guc.h"
#include "utils/lsyscache.h"
@@ -140,6 +141,26 @@ analyze_rel(Oid relid, RangeVar *relation,
onerel = vacuum_open_relation(relid, relation, params.options & ~(VACOPT_VACUUM),
params.log_analyze_min_duration >= 0,
ShareUpdateExclusiveLock);
+ /* SMART ANALYZE: skip unchanged relations */
+ if ((params.options & VACOPT_SMART_ANALYZE) &&
+ onerel->rd_rel->relkind == RELKIND_RELATION)
+ {
+ PgStat_StatTabEntry *tabstat;
+
+ tabstat = pgstat_fetch_stat_tabentry(RelationGetRelid(onerel));
+
+ if (tabstat && tabstat->mod_since_analyze == 0)
+ {
+
+ elog(DEBUG1,
+ "SMART ANALYZE: skipping relation \"%s\" (OID %u), no modifications since last analyze",
+ RelationGetRelationName(onerel),
+ RelationGetRelid(onerel));
+
+ table_close(onerel, ShareUpdateExclusiveLock);
+ return;
+ }
+ }
/* leave if relation could not be opened or locked */
if (!onerel)
@@ -314,6 +335,10 @@ do_analyze_rel(Relation onerel, const VacuumParams params,
PgStat_Counter startreadtime = 0;
PgStat_Counter startwritetime = 0;
+ elog(DEBUG1, "ANALYZE processing relation \"%s\" (OID %u)",
+ RelationGetRelationName(onerel),
+ RelationGetRelid(onerel));
+
verbose = (params.options & VACOPT_VERBOSE) != 0;
instrument = (verbose || (AmAutoVacuumWorkerProcess() &&
params.log_analyze_min_duration >= 0));
diff --git a/src/backend/commands/vacuum.c b/src/backend/commands/vacuum.c
index aa4fbec1..8fd7016f 100644
--- a/src/backend/commands/vacuum.c
+++ b/src/backend/commands/vacuum.c
@@ -165,6 +165,7 @@ ExecVacuum(ParseState *pstate, VacuumStmt *vacstmt, bool isTopLevel)
BufferAccessStrategy bstrategy = NULL;
bool verbose = false;
bool skip_locked = false;
+ bool smart = false;
bool analyze = false;
bool freeze = false;
bool full = false;
@@ -229,6 +230,9 @@ ExecVacuum(ParseState *pstate, VacuumStmt *vacstmt, bool isTopLevel)
ring_size = result;
}
+ else if (strcmp(opt->defname, "smart") == 0)
+ smart = defGetBoolean(opt);
+
else if (!vacstmt->is_vacuumcmd)
ereport(ERROR,
(errcode(ERRCODE_SYNTAX_ERROR),
@@ -306,6 +310,7 @@ ExecVacuum(ParseState *pstate, VacuumStmt *vacstmt, bool isTopLevel)
(verbose ? VACOPT_VERBOSE : 0) |
(skip_locked ? VACOPT_SKIP_LOCKED : 0) |
(analyze ? VACOPT_ANALYZE : 0) |
+ (smart ? VACOPT_SMART_ANALYZE : 0) |
(freeze ? VACOPT_FREEZE : 0) |
(full ? VACOPT_FULL : 0) |
(disable_page_skipping ? VACOPT_DISABLE_PAGE_SKIPPING : 0) |
@@ -315,7 +320,7 @@ ExecVacuum(ParseState *pstate, VacuumStmt *vacstmt, bool isTopLevel)
(only_database_stats ? VACOPT_ONLY_DATABASE_STATS : 0);
/* sanity checks on options */
- Assert(params.options & (VACOPT_VACUUM | VACOPT_ANALYZE));
+ Assert(params.options & (VACOPT_VACUUM | VACOPT_ANALYZE | VACOPT_SMART_ANALYZE));
Assert((params.options & VACOPT_VACUUM) ||
!(params.options & (VACOPT_FULL | VACOPT_FREEZE)));
@@ -351,6 +356,14 @@ ExecVacuum(ParseState *pstate, VacuumStmt *vacstmt, bool isTopLevel)
}
}
+ /*
+ * SMART is only meaningful with ANALYZE.
+ */
+ if ((params.options & VACOPT_SMART_ANALYZE) &&
+ !(params.options & VACOPT_ANALYZE))
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("SMART option requires ANALYZE")));
/*
* Sanity check DISABLE_PAGE_SKIPPING option.
diff --git a/src/include/commands/vacuum.h b/src/include/commands/vacuum.h
index e885a4b9..08533ec7 100644
--- a/src/include/commands/vacuum.h
+++ b/src/include/commands/vacuum.h
@@ -188,7 +188,7 @@ typedef struct VacAttrStats
#define VACOPT_DISABLE_PAGE_SKIPPING 0x100 /* don't skip any pages */
#define VACOPT_SKIP_DATABASE_STATS 0x200 /* skip vac_update_datfrozenxid() */
#define VACOPT_ONLY_DATABASE_STATS 0x400 /* only vac_update_datfrozenxid() */
-
+#define VACOPT_SMART_ANALYZE 0x00010000 /* skip unchanged relations during ANALYZE */
/*
* Values used by index_cleanup and truncate params.
*
diff --git a/src/test/regress/expected/analyze_smart.out b/src/test/regress/expected/analyze_smart.out
new file mode 100644
index 00000000..2ccec3b2
--- /dev/null
+++ b/src/test/regress/expected/analyze_smart.out
@@ -0,0 +1,51 @@
+--
+-- SMART ANALYZE regression test
+--
+CREATE TABLE sa1 (id int);
+CREATE TABLE sa2 (id int);
+-- Initial analyze so stats exist
+ANALYZE;
+-- Modify only sa1
+INSERT INTO sa1 VALUES (1);
+-- Make sure stats snapshot is fresh
+SELECT pg_stat_clear_snapshot();
+ pg_stat_clear_snapshot
+------------------------
+
+(1 row)
+
+-- Check modifications
+SELECT relname, n_mod_since_analyze
+FROM pg_stat_user_tables
+WHERE relname IN ('sa1', 'sa2')
+ORDER BY relname;
+ relname | n_mod_since_analyze
+---------+---------------------
+ sa1 | 0
+ sa2 | 0
+(2 rows)
+
+-- Run SMART ANALYZE on both tables
+ANALYZE (SMART) sa1, sa2;
+-- Refresh stats again
+SELECT pg_stat_clear_snapshot();
+ pg_stat_clear_snapshot
+------------------------
+
+(1 row)
+
+-- Verify only sa1 was analyzed
+SELECT
+ relname,
+ n_mod_since_analyze = 0 AS reset_after_smart_analyze
+FROM pg_stat_user_tables
+WHERE relname IN ('sa1', 'sa2')
+ORDER BY relname;
+ relname | reset_after_smart_analyze
+---------+---------------------------
+ sa1 | t
+ sa2 | t
+(2 rows)
+
+DROP TABLE sa1;
+DROP TABLE sa2;
diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule
index 905f9bca..c77194dd 100644
--- a/src/test/regress/parallel_schedule
+++ b/src/test/regress/parallel_schedule
@@ -90,6 +90,7 @@ test: rules psql psql_crosstab psql_pipeline amutils stats_ext collate.linux.utf
test: select_parallel
test: write_parallel
test: vacuum_parallel
+test: analyze_smart
# Run this alone, because concurrent DROP TABLE would make non-superuser
# "ANALYZE;" fail with "relation with OID $n does not exist".
diff --git a/src/test/regress/sql/analyze_smart.sql b/src/test/regress/sql/analyze_smart.sql
new file mode 100644
index 00000000..993c035b
--- /dev/null
+++ b/src/test/regress/sql/analyze_smart.sql
@@ -0,0 +1,38 @@
+--
+-- SMART ANALYZE regression test
+--
+
+CREATE TABLE sa1 (id int);
+CREATE TABLE sa2 (id int);
+
+-- Initial analyze so stats exist
+ANALYZE;
+
+-- Modify only sa1
+INSERT INTO sa1 VALUES (1);
+
+-- Make sure stats snapshot is fresh
+SELECT pg_stat_clear_snapshot();
+
+-- Check modifications
+SELECT relname, n_mod_since_analyze
+FROM pg_stat_user_tables
+WHERE relname IN ('sa1', 'sa2')
+ORDER BY relname;
+
+-- Run SMART ANALYZE on both tables
+ANALYZE (SMART) sa1, sa2;
+
+-- Refresh stats again
+SELECT pg_stat_clear_snapshot();
+
+-- Verify only sa1 was analyzed
+SELECT
+ relname,
+ n_mod_since_analyze = 0 AS reset_after_smart_analyze
+FROM pg_stat_user_tables
+WHERE relname IN ('sa1', 'sa2')
+ORDER BY relname;
+
+DROP TABLE sa1;
+DROP TABLE sa2;
--
2.43.0
