Preserve statistics targets with ALTER TABLE ALTER COLUMN TYPE
Hello!
While looking at 6713a6e04cb I noticed a similar issue with statistics
targets: ALTER TABLE ALTER COLUMN TYPE forgets the statistics targets
when it recreates the extended statistics that depend on the altered
column, or on an index that depends on the altered column.
The attached patches fix this limitation.
Attachments:
t253417_10002-Preserve-statistics-targets-of-indexes-rebuilt-by-AL.patchapplication/octet-stream; name=0002-Preserve-statistics-targets-of-indexes-rebuilt-by-AL.patchDownload+193-1
0001-Preserve-statistics-target-of-extended-statistics-re.patchapplication/octet-stream; name=0001-Preserve-statistics-target-of-extended-statistics-re.patchDownload+51-21
On Fri, Aug 14, 2026 at 6:38 PM Zsolt Parragi <zsolt.parragi@percona.com> wrote:
While looking at 6713a6e04cb I noticed a similar issue with statistics
targets: ALTER TABLE ALTER COLUMN TYPE forgets the statistics targets
when it recreates the extended statistics that depend on the altered
column, or on an index that depends on the altered column.The attached patches fix this limitation.
Hi Zsolt,
I haven't dug into 0001, but I modified and included 0002 in another
series posted here [1]/messages/by-id/CAAKRu_ajK0QhmOA3N2-QynT7hXx-VVV99RTXrae3sc51we2TSA@mail.gmail.com.
- Melanie
[1]: /messages/by-id/CAAKRu_ajK0QhmOA3N2-QynT7hXx-VVV99RTXrae3sc51we2TSA@mail.gmail.com
Hi,
I reviewed and tested the patch.I was able to reproduce the reported
behavior on an unpatched PostgreSQL build. After creating an index and
generating index usage statistics (idx_scan > 0), executing ALTER
TABLE ... ALTER COLUMN ... TYPE caused the index statistics to be
reset.
Example:
Before ALTER TABLE:
test_b_idx | 1
After ALTER TABLE:
test_b_idx | 0
I then applied the v2 patch, rebuilt PostgreSQL, and repeated the same
test. With the patch applied, the index statistics were preserved
across ALTER TABLE TYPE.
Example:
Before ALTER TABLE:
test_b_idx | 1
After ALTER TABLE:
test_b_idx | 1
I also verified that the index continued to be used through an Index
Scan before and after the ALTER operation.Based on this testing, the
patch appears to address the reported issue and preserves the
historical index usage statistics during ALTER TABLE TYPE.
Regards,
Solai
I attached a rebased v2 since v1 no longer applies on master.
I kept both 0001 and 0002 for now since the other thread including
0001 is also still under discussion.
Attachments:
t253417_4v2-0002-Preserve-statistics-targets-of-indexes-rebuilt-by.patchapplication/octet-stream; name=v2-0002-Preserve-statistics-targets-of-indexes-rebuilt-by.patchDownload+193-1
v2-0001-Preserve-statistics-target-of-extended-statistics.patchapplication/octet-stream; name=v2-0001-Preserve-statistics-target-of-extended-statistics.patchDownload+51-21
On Thu, Oct 1, 2026 at 5:32 PM Zsolt Parragi <zsolt.parragi@percona.com> wrote:
I attached a rebased v2 since v1 no longer applies on master.
I kept both 0001 and 0002 for now since the other thread including
0001 is also still under discussion.
Dear Zsolt,
I tested the rebased v2 patches on the latest master.
Both patches applied cleanly, and I tested the statistics-target
preservation during ALTER TABLE ALTER COLUMN TYPE.
0001 – Preserve statistics target of extended statistics-Verified that
a column statistics target set to 500 remained 500 after changing the
column type from int to bigint.
0002 – Preserve statistics targets of indexes rebuilt by ALTER
TABLE-Verified that an expression index statistics target set to 400
remained 400 after changing the underlying column type from text to
varchar(100).
Both test cases passed, and the configured statistics targets were
preserved after the type changes.
Regards,
solai