Re: Proposal to Enable/Disable Index using ALTER INDEX

On Mon, Sep 23, 2024 at 8:31 PM David Rowley <dgrowleyml(at)gmail(dot)com> wrote:

> 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.
>
>
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`)

void
assign_disabled_indexes(const char *newval, void *extra)
{
if (disabled_indexes != newval)
ResetPlanCache();
}

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

Shayon