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

Hi,

On Thu, Oct 16, 2025 at 02:06:01PM +0900, Michael Paquier wrote:
> On Fri, Oct 10, 2025 at 07:37:59AM -0500, Sami Imseih wrote:
> >> 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).
> > 
> > I agree.
> 
> Hmm.  Why should it be always OK to preserve the stats of an index
> when one of its attributes is changed so as a relation is rewritten?

I agree that in this case the stats (namely idx_scan, idx_tup_read and
idx_tup_fetch) would represent a mixture of two different index structures.

> Hence, the planner may decide
> to treat a given index differently (doesn't it?  Tuple width or
> whole-row references come into mind).

I do think so, yes.

> Keeping the past stats may
> actually lead to confusing conclusions when overlapping them with some
> of the new number generated under the new type?  Could there be more
> benefits in always resetting them as we do now?

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. 

Also, users already have pg_stat_reset_single_table_counters() if 
they want to reset the index stats. This patch gives users the choice to preserve 
stats or reset them. Currently, they have no choice: the stats are 
always lost.

Also, when the rewrite also occurs on the table (type changes) a stat like
seq_scan is preserved (because the table Oid does not change, only the 
relfilenode does). Why would it be ok to preserve seq_scan and not idx_scan?

Regards,

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