On Tue, 24 Sept 2024 at 03:14, Peter Eisentraut <peter@eisentraut.org> wrote: > > On 09.09.24 23:38, Shayon Mukherjee wrote: > > ALTER INDEX index_name ENABLE; > > ALTER INDEX index_name DISABLE; > > I think a better approach would be to make the list of disabled indexes > a GUC setting, which would then internally have an effect similar to > enable_indexscan, meaning it would make the listed indexes unattractive > to the planner.
I understand the last discussion went down that route too. For me, it seems strange that adding some global variable is seen as cleaner than storing the property in the same location as all the other index properties.
That was my first instinct as well. Although, being able to control this setting on a per session level and as an unprivileged user is somewhat attractive.
How would you ensure no cached plans are still using the index after changing the GUC?
Could we call ResetPlanCache() to invalidate all plan caches from the assign_ hook on GUC when it's set (and doesn't match the old value). Something like this (assuming the GUC is called `disabled_indexes`)
A bit heavy-handed, but perhaps it's OK, since it's not meant to be used frequently also ?
> This seems better than the proposed DDL command, because you'd be able > to use this per-session, instead of forcing a global state, and even > unprivileged users could use it.
That's true.
> (I think we have had proposals like this before, but I can't find the > discussion I'm thinking of right now.)
I think it's the one that was already linked by Nathan. [1]? The GUC seems to have been first suggested on the same thread in [2].
