Re: Proposal to Enable/Disable Index using ALTER INDEX

On Mon, Sep 23, 2024 at 11:14 AM Peter Eisentraut <peter(at)eisentraut(dot)org> wrote:
> 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.
>
> 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.
>
> (I think we have had proposals like this before, but I can't find the
> discussion I'm thinking of right now.)

I feel like a given user could want either one of these things. If
you've discovered that a certain index is causing your production
application to pick the wrong index, disabling it and thereby
affecting all backends is what you want. If you're trying to
experiment with different query plans without changing anything for
other backends, being able to set some session-local state is better.
I don't understand the argument that one of these is categorically
better than the other.

--
Robert Haas
EDB: http://www.enterprisedb.com