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

Hi,

On Thu, Oct 16, 2025 at 03:39:59PM -0500, Sami Imseih wrote:
> >
> > The issue is that these stats are also exposed at the table level
> > (idx_scan, last_idx_scan, idx_tup_fetch in pg_stat_all_tables).
> > That's valuable information for understanding table access patterns
> > that is currently lost.
> >
> > It would make more sense to reset the index stats if table level
> > stats were tracked independently from the underlying index stats.
>
> This sounds like a good enhancement. This will also take care of the
> index stats being preserved on a table in the case an index is dropped.
>
> But that means we will need some new fields to aggregate index access
> in PgStat_StatTabEntry,

Yeah, we'd need to add say:

total_idx_numscans
idx_lastscan
total_tuples_idx_fetched

to get rid of the pg_stat_get_*() calls on the indexes in pg_stat_all_tables().

That way we don't need to worry about copying the statistics during the alter
command.

> which may not be so good in
> terms of memory and performance.

Performance:

We could populate those fields at the "table" level when we flush the
index stats (similar to what we do currently for some tables stats that populate
some database stats at flush time). That would avoid double incrementing.

Memory:

Adding those 3 extra fields to PgStat_StatTabEntry does not worry me that
much given the number of fields already in PgStat_StatTabEntry.

The thing that is not ideal is that as PgStat_StatTabEntry is currently used
for both tables and indexes stats then we'll add fields that would be only used
for the table case. But that's already the case for some other fields and this
will be "solved" once we'll resume working on "Split index and table statistics
into different types of stats" ([1]) means after relfilenode stats ([2]) are
implemented (I'm currently working on it).

I prefer this approach as compared to the current proposal (copying the stats
during the alter command). Thoughts?

[1]: https://www.postgresql.org/message-id/flat/f572abe7-a1bb-e13b-48c7-2ca150546822%40gmail.com
[2]: https://www.postgresql.org/message-id/flat/ZlGYokUIlERemvpB(at)ip-10-97-1-34(dot)eu-west-3(dot)compute(dot)internal

Regards,

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