v1-0001-Do-not-update-stats-on-empty-table-when-building-.patch

From 86c03cb525b49d24019c5c0ea8ec36bb82b3c58a 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 v1] Do not update stats on empty table when building index.

We previously fixed this for binary upgrade in 71b66171d0, but a
similar problem exists when using pg_dump --no-data without pg_upgrade
involved. Fix both problems by not updating the stats when the table
has no pages.

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                | 17 +++++++++++------
 src/test/regress/expected/stats_import.out | 22 +++++++++++++++++++++-
 src/test/regress/sql/stats_import.sql      | 12 ++++++++++++
 3 files changed, 44 insertions(+), 7 deletions(-)

diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.c
index f37b990c81d..1a3fdeab350 100644
--- a/src/backend/catalog/index.c
+++ b/src/backend/catalog/index.c
@@ -2833,11 +2833,7 @@ index_update_stats(Relation rel,
 	if (reltuples == 0 && rel->rd_rel->reltuples < 0)
 		reltuples = -1;
 
-	/*
-	 * Don't update statistics during binary upgrade, because the indexes are
-	 * created before the data is moved into place.
-	 */
-	update_stats = reltuples >= 0 && !IsBinaryUpgrade;
+	update_stats = reltuples >= 0;
 
 	/*
 	 * Finish I/O and visibility map buffer locks before
@@ -2850,7 +2846,16 @@ index_update_stats(Relation rel,
 	{
 		relpages = RelationGetNumberOfBlocks(rel);
 
-		if (rel->rd_rel->relkind != RELKIND_INDEX)
+		/*
+		 * Don't update statistics when the relation is completely empty. This
+		 * is important during binary upgrade, because at the time the schema
+		 * is loaded, the data has not yet been moved into place. It's also
+		 * useful when restoring a dump containing only schema and statistics.
+		 */
+		if (relpages == 0)
+			update_stats = false;
+
+		if (update_stats && rel->rd_rel->relkind != RELKIND_INDEX)
 			visibilitymap_count(rel, &relallvisible, NULL);
 	}
 
diff --git a/src/test/regress/expected/stats_import.out b/src/test/regress/expected/stats_import.out
index 1f150f7b08d..4c81fb60c91 100644
--- a/src/test/regress/expected/stats_import.out
+++ b/src/test/regress/expected/stats_import.out
@@ -12,14 +12,34 @@ 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)
+
+-- creating an index on an empty table shouldn't overwrite stats
 CREATE INDEX test_i ON stats_import.test(id);
+SELECT relpages, reltuples, relallvisible
+FROM pg_class
+WHERE oid = 'stats_import.test'::regclass;
+ relpages | reltuples | relallvisible 
+----------+-----------+---------------
+       18 |        21 |            24
+(1 row)
+
 -- starting stats
 SELECT relpages, reltuples, relallvisible
 FROM pg_class
 WHERE oid = 'stats_import.test'::regclass;
  relpages | reltuples | relallvisible 
 ----------+-----------+---------------
-        0 |        -1 |             0
+       18 |        21 |            24
 (1 row)
 
 BEGIN;
diff --git a/src/test/regress/sql/stats_import.sql b/src/test/regress/sql/stats_import.sql
index 8c183bceb8a..c8abb715130 100644
--- a/src/test/regress/sql/stats_import.sql
+++ b/src/test/regress/sql/stats_import.sql
@@ -15,8 +15,20 @@ 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);
+
+-- creating an index on an empty table shouldn't overwrite stats
 CREATE INDEX test_i ON stats_import.test(id);
 
+SELECT relpages, reltuples, relallvisible
+FROM pg_class
+WHERE oid = 'stats_import.test'::regclass;
+
 -- starting stats
 SELECT relpages, reltuples, relallvisible
 FROM pg_class
-- 
2.34.1