On Fri, 6 Jun 2025 at 14:32, Robert Treat <r...@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_activity to > see if the plans have flipped in some way that leads to an overloaded server > (extra latency, poor caching effects, extra buffers usage, etc). And the > replicated bit? Sadly someone launched some big DML operation so you're > waiting 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
