Preserve index stats during ALTER TABLE ... TYPE ...

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