Hi hackers,
while working on relfilenode statistics [1], I observed that index stats
are not preserved during ALTER TABLE ... TYPE ....
Indeed, for example:
postgres=# CREATE TABLE test_tab(a int primary key, b int, c int);
CREATE INDEX test_b_idx ON test_tab(b);
-- Force an index scan on test_b_idx
SELECT * FROM test_tab WHERE b = 2;
CREATE TABLE
CREATE INDEX
a | b | c
---+---+---
(0 rows)
postgres=# select indexrelname, idx_scan from pg_stat_all_indexes where
indexrelname in ('test_b_idx', 'test_tab_pkey');
indexrelname | idx_scan
---------------+----------
test_tab_pkey | 0
test_b_idx | 1
(2 rows)
postgres=# select idx_scan from pg_stat_all_tables where relname = 'test_tab';
idx_scan
----------
1
(1 row)
postgres=# ALTER TABLE test_tab ALTER COLUMN b TYPE int;
ALTER TABLE
postgres=# select indexrelname, idx_scan from pg_stat_all_indexes where
indexrelname in ('test_b_idx', 'test_tab_pkey');
indexrelname | idx_scan
---------------+----------
test_tab_pkey | 0
test_b_idx | 0
(2 rows)
postgres=# select idx_scan from pg_stat_all_tables where relname = 'test_tab';
idx_scan
----------
0
(1 row)
During ALTER TABLE ... TYPE ... on an indexed column, a new index is created and
the old one is dropped.
As you can see, the index stats (linked to the column that has been altered) are
not preserved. I think that they should be preserved (like a REINDEX does).
Note that the issue is the same if a rewrite is involved (ALTER TABLE test_tab
ALTER COLUMN b TYPE bigint).
PFA, a patch to $SUBJECT.
A few remarks:
- We can not use pgstat_copy_relation_stats() because the old index is dropped
before the new one is created, so the patch adds a new PgStat_StatTabEntry
pointer in AlteredTableInfo.
- The stats are saved in ATPostAlterTypeParse() (before the old index is
dropped)
and restored in ATExecAddIndex() once the new index is created.
- Note that pending statistics (if any) are not preserved, only the
accumulated stats from previous transactions. I think this is
acceptable since the accumulated stats represent the historical usage patterns
we
want to maintain.
- The patch adds a few tests to cover multiple scenarios (with and without
rewrites, and indexes with and without associated constraints).
- I'm not familiar with this area of the code, the patch is an attempt to fix
the issue, maybe there is a more elegant way to solve it.
- The issue exists back to v13, but I'm not sure that's serious enough for
back-patching.
Looking forward to your feedback,
Regards,
[1]:
https://postgr.es/m/ZlGYokUIlERemvpB%40ip-10-97-1-34.eu-west-3.compute.internal
--
Bertrand Drouvot
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com
>From b238d9f8660c360e920f98eb6be586d3a2dd15b3 Mon Sep 17 00:00:00 2001
From: Bertrand Drouvot <[email protected]>
Date: Wed, 8 Oct 2025 16:47:43 +0000
Subject: [PATCH v1] Preserve index stats during ALTER TABLE ... TYPE ...
During ALTER TABLE ... TYPE ... on an indexed column, a new index is created and
the old one is dropped. Currently this causes the statistics such as idx_scan,
last_idx_scan, and related counters to be lost.
We can not use pgstat_copy_relation_stats() because the old index is dropped
before the new one is created, so this commit adds a new PgStat_StatTabEntry
pointer in AlteredTableInfo.
The stats are saved in ATPostAlterTypeParse() (before the old index is dropped)
and restored in ATExecAddIndex() once the new index is created.
Note that pending statistics (if any) are not preserved, only the
accumulated stats from previous transactions. This is acceptable since the
accumulated stats represent the historical usage patterns we want to maintain.
---
src/backend/commands/tablecmds.c | 60 +++++++++++++++++++++++++
src/test/regress/expected/stats.out | 70 ++++++++++++++++++++++++++++-
src/test/regress/sql/stats.sql | 31 ++++++++++++-
3 files changed, 157 insertions(+), 4 deletions(-)
30.6% src/backend/commands/
39.8% src/test/regress/expected/
29.5% src/test/regress/sql/
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index fc89352b661..5353bbc3ec1 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -102,6 +102,7 @@
#include "utils/lsyscache.h"
#include "utils/memutils.h"
#include "utils/partcache.h"
+#include "utils/pgstat_internal.h"
#include "utils/relcache.h"
#include "utils/ruleutils.h"
#include "utils/snapmgr.h"
@@ -208,6 +209,7 @@ typedef struct AlteredTableInfo
char *clusterOnIndex; /* index to use for CLUSTER */
List *changedStatisticsOids; /* OIDs of statistics to rebuild */
List *changedStatisticsDefs; /* string definitions of same */
+ PgStat_StatTabEntry *savedIndexStats; /* stats from old index, if any */
} AlteredTableInfo;
/* Struct describing one new constraint to check in Phase 3 scan */
@@ -9637,6 +9639,36 @@ ATExecAddIndex(AlteredTableInfo *tab, Relation rel,
index_close(irel, NoLock);
}
+ /* restore the statistics from the old index */
+ if (tab->savedIndexStats != NULL)
+ {
+ PgStatShared_Relation *shstats;
+ PgStat_EntryRef *entry_ref;
+ Relation irel = index_open(address.objectId, NoLock);
+
+ /* get or create the stats entry for the new index */
+ entry_ref = pgstat_get_entry_ref_locked(PGSTAT_KIND_RELATION,
+ irel->rd_rel->relisshared ?
+ InvalidOid : MyDatabaseId,
+ irel->rd_id,
+ false);
+
+ if (entry_ref != NULL)
+ {
+ shstats = (PgStatShared_Relation *) entry_ref->shared_stats;
+
+ /* copy the saved statistics */
+ shstats->stats = *(tab->savedIndexStats);
+
+ pgstat_unlock_entry(entry_ref);
+ }
+
+ pfree(tab->savedIndexStats);
+ tab->savedIndexStats = NULL;
+
+ index_close(irel, NoLock);
+ }
+
return address;
}
@@ -15664,9 +15696,22 @@ ATPostAlterTypeParse(Oid oldId, Oid oldRelId, Oid refRelId, char *cmd,
{
IndexStmt *stmt = (IndexStmt *) stm;
AlterTableCmd *newcmd;
+ PgStat_StatTabEntry *src_stats = pgstat_fetch_stat_tabentry(oldId);
+
+ /* keep the index's statistics */
+ if (src_stats)
+ {
+ tab->savedIndexStats = (PgStat_StatTabEntry *) palloc(sizeof(PgStat_StatTabEntry));
+ memcpy(tab->savedIndexStats, src_stats, sizeof(PgStat_StatTabEntry));
+ }
+ else
+ {
+ tab->savedIndexStats = NULL;
+ }
if (!rewrite)
TryReuseIndex(oldId, stmt);
+
stmt->reset_default_tblspc = true;
/* keep the index's comment */
stmt->idxcomment = GetComment(oldId, RelationRelationId, 0);
@@ -15690,12 +15735,27 @@ ATPostAlterTypeParse(Oid oldId, Oid oldRelId, Oid refRelId, char *cmd,
{
IndexStmt *indstmt;
Oid indoid;
+ PgStat_StatTabEntry *src_stats;
indstmt = castNode(IndexStmt, cmd->def);
indoid = get_constraint_index(oldId);
+ /* keep the index's statistics */
+ src_stats = pgstat_fetch_stat_tabentry(indoid);
+
+ if (src_stats)
+ {
+ tab->savedIndexStats = (PgStat_StatTabEntry *) palloc(sizeof(PgStat_StatTabEntry));
+ memcpy(tab->savedIndexStats, src_stats, sizeof(PgStat_StatTabEntry));
+ }
+ else
+ {
+ tab->savedIndexStats = NULL;
+ }
+
if (!rewrite)
TryReuseIndex(indoid, indstmt);
+
/* keep any comment on the index */
indstmt->idxcomment = GetComment(indoid,
RelationRelationId, 0);
diff --git a/src/test/regress/expected/stats.out b/src/test/regress/expected/stats.out
index 67e1860e984..7864a8f44b8 100644
--- a/src/test/regress/expected/stats.out
+++ b/src/test/regress/expected/stats.out
@@ -651,8 +651,9 @@ DROP TABLE prevstats;
-- granularity.
-----
BEGIN;
-CREATE TEMPORARY TABLE test_last_scan(idx_col int primary key, noidx_col int);
-INSERT INTO test_last_scan(idx_col, noidx_col) VALUES(1, 1);
+CREATE TEMPORARY TABLE test_last_scan(idx_col int primary key, idx_col2 int, noidx_col int);
+CREATE index test_last_scan_idx2 on test_last_scan(idx_col2);
+INSERT INTO test_last_scan(idx_col, idx_col2, noidx_col) VALUES(1, 1, 1);
SELECT pg_stat_force_next_flush();
pg_stat_force_next_flush
--------------------------
@@ -867,6 +868,71 @@ SELECT idx_scan, :'test_last_idx' < last_idx_scan AS idx_ok,
3 | t | f
(1 row)
+-- check that an index rebuild preserves the stats
+ALTER TABLE test_last_scan ALTER COLUMN idx_col TYPE int;
+SELECT idx_scan FROM pg_stat_all_tables WHERE relid = 'test_last_scan'::regclass;
+ idx_scan
+----------
+ 3
+(1 row)
+
+-- same test but with a rewrite
+ALTER TABLE test_last_scan ALTER COLUMN idx_col TYPE bigint;
+SELECT idx_scan FROM pg_stat_all_tables WHERE relid = 'test_last_scan'::regclass;
+ idx_scan
+----------
+ 3
+(1 row)
+
+-- do the same on an indexed column not part of a constraint
+-- cause one index scan
+BEGIN;
+SET LOCAL enable_seqscan TO off;
+SET LOCAL enable_indexscan TO on;
+SET LOCAL enable_bitmapscan TO off;
+EXPLAIN (COSTS off) SELECT count(*) FROM test_last_scan WHERE idx_col2 = 1;
+ QUERY PLAN
+--------------------------------------------------------------
+ Aggregate
+ -> Index Scan using test_last_scan_idx2 on test_last_scan
+ Index Cond: (idx_col2 = 1)
+(3 rows)
+
+SELECT count(*) FROM test_last_scan WHERE idx_col2 = 1;
+ count
+-------
+ 1
+(1 row)
+
+SELECT pg_stat_force_next_flush();
+ pg_stat_force_next_flush
+--------------------------
+
+(1 row)
+
+COMMIT;
+SELECT idx_scan FROM pg_stat_all_tables WHERE relid = 'test_last_scan'::regclass;
+ idx_scan
+----------
+ 4
+(1 row)
+
+-- check that an index rebuild preserves the stats
+ALTER TABLE test_last_scan ALTER COLUMN idx_col2 TYPE int;
+SELECT idx_scan FROM pg_stat_all_tables WHERE relid = 'test_last_scan'::regclass;
+ idx_scan
+----------
+ 4
+(1 row)
+
+-- same test but with a rewrite
+ALTER TABLE test_last_scan ALTER COLUMN idx_col2 TYPE bigint;
+SELECT idx_scan FROM pg_stat_all_tables WHERE relid = 'test_last_scan'::regclass;
+ idx_scan
+----------
+ 4
+(1 row)
+
-- check that the stats in pg_stat_all_indexes are reset
SELECT pg_stat_reset_single_table_counters('test_last_scan_pkey'::regclass);
pg_stat_reset_single_table_counters
diff --git a/src/test/regress/sql/stats.sql b/src/test/regress/sql/stats.sql
index 8768e0f27fd..eaf359aeba1 100644
--- a/src/test/regress/sql/stats.sql
+++ b/src/test/regress/sql/stats.sql
@@ -306,8 +306,9 @@ DROP TABLE prevstats;
-----
BEGIN;
-CREATE TEMPORARY TABLE test_last_scan(idx_col int primary key, noidx_col int);
-INSERT INTO test_last_scan(idx_col, noidx_col) VALUES(1, 1);
+CREATE TEMPORARY TABLE test_last_scan(idx_col int primary key, idx_col2 int, noidx_col int);
+CREATE index test_last_scan_idx2 on test_last_scan(idx_col2);
+INSERT INTO test_last_scan(idx_col, idx_col2, noidx_col) VALUES(1, 1, 1);
SELECT pg_stat_force_next_flush();
SELECT last_seq_scan, last_idx_scan FROM pg_stat_all_tables WHERE relid = 'test_last_scan'::regclass;
COMMIT;
@@ -390,6 +391,32 @@ SELECT idx_scan, :'test_last_idx' < last_idx_scan AS idx_ok,
stats_reset IS NOT NULL AS has_stats_reset
FROM pg_stat_all_indexes WHERE indexrelid = 'test_last_scan_pkey'::regclass;
+-- check that an index rebuild preserves the stats
+ALTER TABLE test_last_scan ALTER COLUMN idx_col TYPE int;
+SELECT idx_scan FROM pg_stat_all_tables WHERE relid = 'test_last_scan'::regclass;
+-- same test but with a rewrite
+ALTER TABLE test_last_scan ALTER COLUMN idx_col TYPE bigint;
+SELECT idx_scan FROM pg_stat_all_tables WHERE relid = 'test_last_scan'::regclass;
+
+-- do the same on an indexed column not part of a constraint
+-- cause one index scan
+BEGIN;
+SET LOCAL enable_seqscan TO off;
+SET LOCAL enable_indexscan TO on;
+SET LOCAL enable_bitmapscan TO off;
+EXPLAIN (COSTS off) SELECT count(*) FROM test_last_scan WHERE idx_col2 = 1;
+SELECT count(*) FROM test_last_scan WHERE idx_col2 = 1;
+SELECT pg_stat_force_next_flush();
+COMMIT;
+SELECT idx_scan FROM pg_stat_all_tables WHERE relid = 'test_last_scan'::regclass;
+
+-- check that an index rebuild preserves the stats
+ALTER TABLE test_last_scan ALTER COLUMN idx_col2 TYPE int;
+SELECT idx_scan FROM pg_stat_all_tables WHERE relid = 'test_last_scan'::regclass;
+-- same test but with a rewrite
+ALTER TABLE test_last_scan ALTER COLUMN idx_col2 TYPE bigint;
+SELECT idx_scan FROM pg_stat_all_tables WHERE relid = 'test_last_scan'::regclass;
+
-- check that the stats in pg_stat_all_indexes are reset
SELECT pg_stat_reset_single_table_counters('test_last_scan_pkey'::regclass);
--
2.34.1
