Re: Proposal to Enable/Disable Index using ALTER INDEX

> On Oct 7, 2024, at 4:52 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>
> 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.

Makes sense to me and it’s something I am somewhat split on as well. I suppose with a GUC you can still do some thing like

ALTER USER foobar SET disabled_indexes to ‘idx_test_table_id’

[thinking…] This way all new sessions will start to not consider the index when query planning. Of course it does not help existing sessions, so one may need to kill those backends, which could be heavy handed.

Both these options clearly serve slightly different purposes with good pros and I am currently thinking if GUC is that good middle ground solution.

Curious if someone has a stronger opinion on which one of these might make more sense perhaps :-D.

[thinking…] Unless - we try to do support both a GUC and the ALTER INDEX ENABLE/DISABLE grammar + isdisabled attribute on pg_index?

I can see that both implementations (GUC and the new attribute on pg_index via ALTER) have the primary logic managed by `get_relation_info` in `plancat.c`. Here, we set `isdisabled` (new attribute) on `IndexOptInfo` and compare it against `disabled_indexes` in the GUC (from the previous GUC patch). Similarly, for `pg_index`, which is already open in `get_relation_info`, we can read from `pg_index.isdisabled` and accordingly update `IndexOptInfo.isdisabled`.

[0] https://www.postgresql.org/message-id/6CE345C1-6FFD-4E4C-8775-45DA659C57CF@gmail.com

Thanks
Shayon