Re: Proposal to Enable/Disable Index using ALTER INDEX

On Tue, 24 Sept 2024 at 03:14, Peter Eisentraut <peter(at)eisentraut(dot)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.

How would you ensure no cached plans are still using the index after
changing the GUC?

> 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].

David

[1] https://www.postgresql.org/message-id/ed8c9ed7-bb5d-aaec-065b-ad4893645deb%402ndQuadrant.com
[2] https://www.postgresql.org/message-id/29800.1529359024%40sss.pgh.pa.us