Preserve statistics targets with ALTER TABLE ALTER COLUMN TYPE

Started by Zsolt Parragi3 months ago5 messageshackers
Jump to latest
#1Zsolt Parragi
zsolt.parragi@percona.com

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_1
0002-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
#2Melanie Plageman
melanieplageman@gmail.com
In reply to: Zsolt Parragi (#1)
Re: Preserve statistics targets with ALTER TABLE ALTER COLUMN TYPE

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

#3solai v
solai.cdac@gmail.com
In reply to: Melanie Plageman (#2)
Re: Preserve statistics targets with ALTER TABLE ALTER COLUMN TYPE

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

#4Zsolt Parragi
zsolt.parragi@percona.com
In reply to: solai v (#3)
Re: Preserve statistics targets with ALTER TABLE ALTER COLUMN TYPE

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_4
v2-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
#5solai v
solai.cdac@gmail.com
In reply to: Zsolt Parragi (#4)
Re: Preserve statistics targets with ALTER TABLE ALTER COLUMN TYPE

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