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
