Re: Proposal to Enable/Disable Index using ALTER INDEX

> On Oct 9, 2024, at 1:41 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>
> On Wed, Oct 9, 2024 at 4:19 AM David Rowley <dgrowleyml(at)gmail(dot)com <mailto: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".
>
> +1. A GUC can be done as a contrib module using existing hooks, and I
> think that's already been done outside of core, perhaps multiple
> times. That certainly doesn't mean we CAN'T add it as an in-core
> feature, but I do think "yet another not even half-baked take on
> planner hints" is a fair description. What I would personally like to
> see is for us to ship one or possibly more than one contrib module
> that let people do hint-like things in useful ways, and this could be
> a part of that. But I think we need better infrastructure for
> controlling the planner behavior first, hence the "allowing extensions
> to control planner behavior" thread.
>

Thank you for sharing this Robert. I like the idea behind "allowing extensions to control planner behavior” overall and I think it does help towards a powerful extension ecosystem too. I wonder if there is a reality where we can achieve both the outcomes here

- Support disabling of indexes [1] through ALTER command
- While also building on "allowing extensions to control planner behavior” for the reasons above?

[1] https://www.postgresql.org/message-id/ABD42A12-4DCF-4EE4-B903-4C657903CECE%40gmail.com

Thanks
Shayon