v2-0001-During-CREATE-INDEX-don-t-update-stats-if-autovac.patch

From a8945b9ce4e358f4a79d3065c07f3b42a94fd387 Mon Sep 17 00:00:00 2001
From: Jeff Davis <jeff@j-davis.com>
Date: Thu, 27 Feb 2025 17:06:00 -0800
Subject: [PATCH v2] During CREATE INDEX, don't update stats if autovacuum is
 off.

We previously fixed this for binary upgrade in 71b66171d0, but a
similar problem existed when using pg_dump --no-data without
pg_upgrade involved.

Fix by not implicitly updating stats during create index when
autovacuum is disabled.

Reported-by: Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>
Discussion: https://postgr.es/m/CAExHW5vf9D+8-a5_BEX3y=2y_xY9hiCxV1=C+FnxDvfprWvkng@mail.gmail.com
---
 src/backend/catalog/index.c                | 31 +++++++++++++++--
 src/test/regress/expected/stats_import.out | 39 ++++++++++++++++++----
 src/test/regress/sql/stats_import.sql      | 22 ++++++++++--
 3 files changed, 82 insertions(+), 10 deletions(-)

diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.c
index f37b990c81d..318a44e1e1d 100644
--- a/src/backend/catalog/index.c
+++ b/src/backend/catalog/index.c
@@ -63,6 +63,7 @@
 #include "optimizer/optimizer.h"
 #include "parser/parser.h"
 #include "pgstat.h"
+#include "postmaster/autovacuum.h"
 #include "rewrite/rewriteManip.h"
 #include "storage/bufmgr.h"
 #include "storage/lmgr.h"
@@ -121,6 +122,7 @@ static void UpdateIndexRelation(Oid indexoid, Oid heapoid,
 								bool isready);
 static void index_update_stats(Relation rel,
 							   bool hasindex,
+							   bool update_stats,
 							   double reltuples);
 static void IndexCheckExclusion(Relation heapRelation,
 								Relation indexRelation,
@@ -1261,6 +1263,17 @@ index_create(Relation heapRelation,
 	}
 	else if ((flags & INDEX_CREATE_SKIP_BUILD) != 0)
 	{
+		bool update_stats = true;
+
+		/*
+		 * If autovacuum is disabled, don't implicitly update stats as a part
+		 * of index creation.
+		 */
+		if (!AutoVacuumingActive() ||
+			(heapRelation->rd_options != NULL &&
+			 !((StdRdOptions *) heapRelation->rd_options)->autovacuum.enabled))
+			update_stats = false;
+
 		/*
 		 * Caller is responsible for filling the index later on.  However,
 		 * we'd better make sure that the heap relation is correctly marked as
@@ -1268,6 +1281,7 @@ index_create(Relation heapRelation,
 		 */
 		index_update_stats(heapRelation,
 						   true,
+						   update_stats,
 						   -1.0);
 		/* Make the above update visible */
 		CommandCounterIncrement();
@@ -2807,9 +2821,9 @@ FormIndexDatum(IndexInfo *indexInfo,
 static void
 index_update_stats(Relation rel,
 				   bool hasindex,
+				   bool update_stats,
 				   double reltuples)
 {
-	bool		update_stats;
 	BlockNumber relpages = 0;	/* keep compiler quiet */
 	BlockNumber relallvisible = 0;
 	Oid			relid = RelationGetRelid(rel);
@@ -2837,7 +2851,8 @@ index_update_stats(Relation rel,
 	 * Don't update statistics during binary upgrade, because the indexes are
 	 * created before the data is moved into place.
 	 */
-	update_stats = reltuples >= 0 && !IsBinaryUpgrade;
+	if (reltuples < 0 || IsBinaryUpgrade)
+		update_stats = false;
 
 	/*
 	 * Finish I/O and visibility map buffer locks before
@@ -2981,6 +2996,7 @@ index_build(Relation heapRelation,
 	Oid			save_userid;
 	int			save_sec_context;
 	int			save_nestlevel;
+	bool		update_stats = true;
 
 	/*
 	 * sanity checks
@@ -3121,15 +3137,26 @@ index_build(Relation heapRelation,
 		table_close(pg_index, RowExclusiveLock);
 	}
 
+	/*
+	 * If autovacuum is disabled, don't implicitly update stats as a part
+	 * of index creation.
+	 */
+	if (!AutoVacuumingActive() ||
+		(heapRelation->rd_options != NULL &&
+		 !((StdRdOptions *) heapRelation->rd_options)->autovacuum.enabled))
+		update_stats = false;
+
 	/*
 	 * Update heap and index pg_class rows
 	 */
 	index_update_stats(heapRelation,
 					   true,
+					   update_stats,
 					   stats->heap_tuples);
 
 	index_update_stats(indexRelation,
 					   false,
+					   update_stats,
 					   stats->index_tuples);
 
 	/* Make the updated catalog row versions visible */
diff --git a/src/test/regress/expected/stats_import.out b/src/test/regress/expected/stats_import.out
index 1f150f7b08d..abd391181e4 100644
--- a/src/test/regress/expected/stats_import.out
+++ b/src/test/regress/expected/stats_import.out
@@ -12,15 +12,42 @@ CREATE TABLE stats_import.test(
     arange int4range,
     tags text[]
 ) WITH (autovacuum_enabled = false);
+SELECT
+    pg_catalog.pg_restore_relation_stats(
+        'relation', 'stats_import.test'::regclass,
+        'relpages', 18::integer,
+	'reltuples', 21::real,
+	'relallvisible', 24::integer);
+ pg_restore_relation_stats 
+---------------------------
+ t
+(1 row)
+
+-- CREATE INDEX on a table with autovac disabled should not overwrite
+-- stats
 CREATE INDEX test_i ON stats_import.test(id);
+SELECT
+    pg_catalog.pg_restore_relation_stats(
+        'relation', 'stats_import.test_i'::regclass,
+        'relpages', 28::integer,
+	'reltuples', 35::real,
+	'relallvisible', 42::integer);
+ pg_restore_relation_stats 
+---------------------------
+ t
+(1 row)
+
 -- starting stats
-SELECT relpages, reltuples, relallvisible
+SELECT relname, relpages, reltuples, relallvisible
 FROM pg_class
-WHERE oid = 'stats_import.test'::regclass;
- relpages | reltuples | relallvisible 
-----------+-----------+---------------
-        0 |        -1 |             0
-(1 row)
+WHERE oid = 'stats_import.test'::regclass
+   OR oid = 'stats_import.test_i'::regclass
+ORDER BY relname;
+ relname | relpages | reltuples | relallvisible 
+---------+----------+-----------+---------------
+ test    |       18 |        21 |            24
+ test_i  |       28 |        35 |            42
+(2 rows)
 
 BEGIN;
 -- regular indexes have special case locking rules
diff --git a/src/test/regress/sql/stats_import.sql b/src/test/regress/sql/stats_import.sql
index 8c183bceb8a..f8907504de1 100644
--- a/src/test/regress/sql/stats_import.sql
+++ b/src/test/regress/sql/stats_import.sql
@@ -15,12 +15,30 @@ CREATE TABLE stats_import.test(
     tags text[]
 ) WITH (autovacuum_enabled = false);
 
+SELECT
+    pg_catalog.pg_restore_relation_stats(
+        'relation', 'stats_import.test'::regclass,
+        'relpages', 18::integer,
+	'reltuples', 21::real,
+	'relallvisible', 24::integer);
+
+-- CREATE INDEX on a table with autovac disabled should not overwrite
+-- stats
 CREATE INDEX test_i ON stats_import.test(id);
 
+SELECT
+    pg_catalog.pg_restore_relation_stats(
+        'relation', 'stats_import.test_i'::regclass,
+        'relpages', 28::integer,
+	'reltuples', 35::real,
+	'relallvisible', 42::integer);
+
 -- starting stats
-SELECT relpages, reltuples, relallvisible
+SELECT relname, relpages, reltuples, relallvisible
 FROM pg_class
-WHERE oid = 'stats_import.test'::regclass;
+WHERE oid = 'stats_import.test'::regclass
+   OR oid = 'stats_import.test_i'::regclass
+ORDER BY relname;
 
 BEGIN;
 -- regular indexes have special case locking rules
-- 
2.34.1