From 6fa990921c3a4e956bbbbaf61563ef639c21b240 Mon Sep 17 00:00:00 2001 From: Vasuki M 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