Re: [PATCH] Re: Proposal to Enable/Disable Index using ALTER INDEX

On Fri, 6 Jun 2025 at 14:32, Robert Treat <rob@xzilla.net> wrote:
> In production, you aren't watching to see what happen with pg_stat_all_indexes, because you will first be watching
pg_stat_activityto see if the plans have flipped in some way that leads to an overloaded server (extra latency, poor
cachingeffects, extra buffers usage, etc). And the replicated bit? Sadly someone launched some big DML operation so
you'rewaiting for that to finish so the "quick rollback" can actually get to those other servers. 

I think you've misunderstood when you'd be looking at
pg_stat_all_indexes. The time when you'd want to look at
pg_stat_all_indexes is *before* you DROP INDEX and before you ALTER
TABLE INVISIBLE the index. What you'd likely want to look for there
are indexes that have the last_idx_scan set to something far in the
past or set to NULL.

I'm curious to know if you've ever had to drop an index out of
production before? What did you think about when you'd just typed the
DROP INDEX command and were contemplating your future? How long did
you pause before pressing [Enter]?

Can you list your proposed series of steps you'd recommend to a DBA
wishing to remove an index, assuming this feature exists in core as
you'd like it to?

David