Subject: Re: Optional skipping of unchanged relations during ANALYZE?

From: VASUKI M <[email-masked]>
Date: Wed, 21 Jan 2026 11:59:24 +0530

Hi all,

Thanks a lot for the thoughtful feedback.

The points about statistics targets and random sampling make sense. In
particular, I agree that even without data changes, re-running ANALYZE
can still be beneficial (for example after increasing
default_statistics_target or per-column targets, or simply to obtain a
different sample).

Given that, my intention is to keep this strictly as an opt-in
behavior, so that existing semantics are unchanged unless the user
explicitly requests it. In the current prototype, tables that have
never been analyzed before are not skipped, and SMART only considers
relations that already have statistics.

Regarding statistics targets, since PostgreSQL does not currently track
which target was used to collect existing statistics, SMART ANALYZE
would not attempt to account for target changes. I plan to document
this limitation clearly so users understand the trade-off when opting
into this mode [for now later will look into it].

I’ll take this feedback into account while cleaning up the patch and
documentation, and will follow up with a v1 proposal once ready.

Thanks again for the guidance.

Regards,
Vasuki M
C-DAC,Chennai

---

From: Ilia Evdokimov <[email-masked]>
Date: Wed, 21 Jan 2026 10:47:33 +0300

Another concern with skipping ANALYZE on unchanged tables is extended 
statistics.

If CREATE/ALTER STATISTICS is executed, it would still be desirable for 
ANALYZE to collect the newly statistics and extended ones, even if the 
table data itself has not changed.

What do you think about this?

-- 
Best regards,
Ilia Evdokimov,
Tantor Labs LLC,
https://tantorlabs.com/

---

From: VASUKI M <[email-masked]>
Date: Wed, 21 Jan 2026 15:07:01 +0530

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-masked]>
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

---

From: Christoph Berg <[email-masked]>
Date: Wed, 21 Jan 2026 10:44:45 +0100

Re: VASUKI M
>     VACUUM(SMART);

IMHO it was a historical mistake to combine VACUUM and ANALYZE into a
single command. We should not add any more options on that
combination. If people want to pass options to ANALYZE, they should
call ANALYZE and not VACUUM.

Christoph

---

From: VASUKI M <[email-masked]>
Date: Wed, 21 Jan 2026 15:17:44 +0530

Sorry,forgot to mention that it will produce error as 'SMART requires
ANALYZE'

It's just a checking command :)

-Vasu

---

From: Christoph Berg <[email-masked]>
Date: Wed, 21 Jan 2026 10:50:59 +0100

Re: VASUKI M
> Sorry,forgot to mention that it will produce error as 'SMART requires

SMART is also a terribly non-descriptive name. How about CHANGED_ONLY?

Christoph