Re: Proposal to Enable/Disable Index using ALTER INDEX

Hi David,

Answered below

> On Oct 9, 2024, at 9:19 AM, David Rowley <dgrowleyml(at)gmail(dot)com> wrote:
>
> On Wed, 9 Oct 2024 at 20:07, Shayon Mukherjee <shayonj(at)gmail(dot)com> wrote:
>> [thinking…] Unless - we try to do support both a GUC and the ALTER INDEX ENABLE/DISABLE grammar + isdisabled attribute on pg_index?
>
> I just wanted to explain my point of view on this. This is my opinion
> and is by no means authoritative.
>
> I was interested in this patch when you proposed it as an ALTER INDEX
> option. I know other committers seem interested, but I personally
> don't have any interest in the GUC option. I think the reason I
> dislike it is that it's yet another not even half-baked take on
> planner hints (the other one being enable* GUCs). I often thought that
> if we ever did planner hints that it would be great to have multiple
> ways to specify the hints. Ordinarily, I'd expect some special comment
> type as the primary method to specify hints, but equally, it would be
> nice to be able to specify them in other ways. e.g. a GUC to have them
> apply to more than just 1 query. Useful for things such as "don't use
> index X".

Thank you so much this context, as someone new to psql-hackers, having this insight is super useful. Also getting a sense of how folks feel about controlling different behaviors like planner hints through GUC and SQL grammar.

For instance: I wasn’t quite able to figure out the how to properly distinguish + reason between the enable* GUCs and ALTER index for this case, and patches are per my limited understand of the historical context as well.

>
> Now, I'm not suggesting you go off and code up planner hints. That's a
> huge project. I'm just concerned that we've already got a fair bit of
> cruft that will be left remaining if we ever get core planner hints
> and a disabled_indexes GUC will just add to that. I don't feel like
> the ALTER INDEX method would be leftover cruft from us gaining core
> planner hints. Others might feel differently on that one. I feel the
> ALTER INDEX option is less controversial.
>
> I'll also stand by what I said earlier on this thread. If PeterG gets
> index skip scans done for PG18, then it's likely there's going to be
> lots of users considering if they still need a certain index or not
> after upgrading to PG18.

Likewise, I personally feel that the ability to disable indexes quickly and reverse the disabling (also quickly) is super useful, especially from an operational POV (point of view). So, I am very keen on getting this landed and happy to iterate on as many patches as it takes. :D

At this point, I am indifferent to each of the approaches (GUC or SQL grammar) based on the pros/cons I shared earlier in the thread & discussions in the thread. However, I would like us to make progress on getting _something_ out since the topic of disabling indexes has come up many times on pgsql-hackers in the past years and there is no easy way to toggle this behavior yet.

“yet another not even half-baked take on planner hints" is a good way to put things about enable* GUCs, so I am very much on board with proposing an updated PATCH to support disabling of indexes through ALTER. The original PATCH was here for context [1].

I am also curious about supporting this ([1]) through the ALTER grammar and not having the planner consider indexes by updating `get_relation_info` in `plancat.c`. Basically, through `pg_index.isdisabled`, which is already open in `get_relation_info`, we can read from `pg_index.isdisabled` and accordingly update `IndexOptInfo.isdisabled`. So, I'm happy to explore that as well and share my findings.

[1] https://www.postgresql.org/message-id/CANqtF-oBaBtRfw9O7GAoHN3nNEZQYsW3oaGfD%2BwJfG8R29nZYw%40mail.gmail.com

Thanks
Shayon