Re: Preserve index stats during ALTER TABLE ... TYPE ... - Mailing list pgsql-hackers

Hi,

On Fri, Oct 10, 2025 at 03:52:58PM +0000, Bertrand Drouvot wrote:
> The idea is to:
> 
> - store a List of savedIndexStats. The savedIndexStats struct would get the
> PgStat_StatTabEntry + all the information needed to be able to use 
> CompareIndexInfo() when restoring the stats (so that we can restore each PgStat_StatTabEntry
> in the right index).
> 
> - Iterate on all the indexes and populate this new list in AlteredTableInfo in
> ATPostAlterTypeParse().
> 
> - Iterate on all the indexes and use the list above and CompareIndexInfo() to
> restore the stats in ATExecAddIndex().
> 
> Will polish and share next week.

PFA v2 that handles partitioned tables/indexes.

A few words about its design:

I started by just creating a list of of PgStat_StatTabEntry + all the information
needed to be able to use CompareIndexInfo() when restoring the stats.

But that lead to O(P^2) when restoring the stats (for each new partition index
(P), it was scanning through all saved ones (P)), and could be non negligible.

For example, with 20K partitions and no rewrite:

-   89.64%     0.00%  postgres  postgres           [.] ATController
   - ATController
      - 79.23% ATRewriteCatalogs
         - 64.43% ATExecCmd
            - 56.53% ATExecAddIndex
               + 46.34% DefineIndex 
               + 10.19% ATExecAddIndex_RestoreStats
            + 5.29% ATExecAlterColumnType
            + 2.60% CommandCounterIncrement
         + 11.91% ATPostAlterTypeCleanup
         + 2.77% relation_open
      + 8.79% ATPrepCmd
      + 1.62% ATRewriteTables

We can see ATExecAddIndex_RestoreStats was not negligible at that time. That was
less of an issue when rewrite was involved:

-   89.35%     0.00%  postgres  postgres           [.] ATController
   - ATController
      + 51.24% ATRewriteTables
      - 33.89% ATRewriteCatalogs
         - 26.98% ATExecCmd
            - 22.16% ATExecAddIndex
               + 17.44% DefineIndex
                 4.71% ATExecAddIndex_RestoreStats
            + 3.58% ATExecAlterColumnType
            + 1.24% CommandCounterIncrement
         + 5.53% ATPostAlterTypeCleanup
         + 1.32% relation_open
      + 4.22% ATPrepCmd

So I added a hash table keyed by partition table OID, with each entry containing
a list of saved index stats for that partition. This way, restoration is now O(P)
instead of O(P²).

With the attached, the perf profile (again 20K partitions and no rewrite) is:

-   89.06%     0.00%  postgres  postgres           [.] ATController
   - ATController
      - 77.65% ATRewriteCatalogs
         - 61.57% ATExecCmd
            - 52.63% ATExecAddIndex
               + 51.16% DefineIndex
               + 1.47% ATExecAddIndex_RestoreStats
            + 5.96% ATExecAlterColumnType
            + 2.98% CommandCounterIncrement
         + 13.26% ATPostAlterTypeCleanup
         + 2.73% relation_open
      + 9.59% ATPrepCmd
      + 1.82% ATRewriteTables

As we can see, the ATExecAddIndex_RestoreStats impact is now around 1.5%, which
I think is acceptable given the benefit of preserving historical statistics.

Additional remarks:

- I initially tried using only CompareIndexInfo() for matching, but this fails
when multiple indexes exist on the same column(s). So I added the index name as
the primary matching check with CompareIndexInfo() kept as a sanity check (I think
that it could be removed).

- The new resources are allocated in the PortalContext, it's a short lived one so
the patch does not free them explicitly.

- Much more tests have been added as compared to v1.

Regards,

-- 
Bertrand Drouvot
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com