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

On Fri, 6 Jun 2025 at 08:14, Robert Treat <rob@xzilla.net> wrote:
> I know this thread is rather old and there doesn't seem to be full
> agreement on the ALTER vs GUC implementation idea, and even though I
> agree with the sentiment that the GUC system is little more than the
> "half-baked take on planner hints", the upside of GUC first
> implementations is that they tend to provide better usability than
> most grammer related implementations. Consider that any implementation
> which requires the use of ALTER statements (which this one does)
> undercuts its own usefulness because it adds significant operational
> risk in any attempt to use it just by the nature of ALTER leading to
> system-wide (including multi-server) changes, and while it feels like
> we often dismiss operational risk, those are exactly the folks who
> need this feature the most.

Thanks for weighing in.

In my mind, this feature is for "I'm almost 100% certain this index
isn't needed, I want to make sure I'm 100% right in a way that I can
quickly fix the ensuing chaos if I'm wrong".  It sounds like in your
mind it's "I want to run some experiments to see if this index is
needed or not". I think both have merit, but I think the former gets
you closer to 100% certainty, as it'll be replicated to physical
replica servers.

I'd personally be looking at something like pg_stat_all_indexes
instead of playing around with session-level GUC setting to figure out
if an index was being used or not and I'd be looking to the ALTER
TABLE once I'd seen nothing changing in pg_stat_all_indexes for some
time period. I mean, what am I really going to do in session-level
GUC? -- Run all possible queries that the application runs and check
they're still fast? If I could do that, then I could equally just not
use the GUC and look at EXPLAIN on all those queries to see if the
index is picked anywhere.

Maybe we need to hear from a few more people who have recently faced
the dilemma of removing a seemingly unused index on a critical
application.

For me, I have been in this situation before. The database wasn't
massive. I could likely have put the index back in 10 mins or so.
However, it'd still have been nice to have something else to try
before trying DROP INDEX. It's quite easy to imagine your finger
hovering over the [Enter] key for a while before typing that statement
when the index is large.

> P.S. I really do want to thank Shayon for sticking with this;

+1

David