Re: Proposal to Enable/Disable Index using ALTER INDEX

Hello,

Also added this as a post in Commit Fest [0]

[0] https://commitfest.postgresql.org/50/5274/

Thank you
Shayon

> On Sep 26, 2024, at 1:39 PM, Shayon Mukherjee <shayonj(at)gmail(dot)com> wrote:
>
> Hello,
>
> I am back with a PATCH :). Thanks to everyone in the threads for all the helpful discussions.
>
> This proposal is for a PATCH to introduce a GUC variable to disable specific indexes during query planning.
>
> This is an alternative approach to the previous PATCH I had proposed and is improved upon after some of the recent discussions in the thread. The PATCH contains the relevant changes, regression tests, and documentation.
>
> I went with the GUC approach to introduce a way for a user to disable indexes during query planning over dedicated SQL Grammar and introducing the `isenabled` attribute in `pg_index` for the following reasons:
>
> - Inspired by the discussions brought in earlier about this setting being something that unprivileged users can benefit from versus an ALTER statement.
> - A GUC variable felt more closely aligned with the query tuning purpose, which this feature would serve, over index maintenance, the state of which is more closely reflected in `pg_index`.
>
> Implementation details:
>
> The patch introduces a new GUC parameter `disabled_indexes` that allows users to specify a comma-separated list of indexes to be ignored during query planning. Key aspects:
>
> - Adds a new `isdisabled` attribute to the `IndexOptInfo` structure.
> - Modifies `get_relation_info` in `plancat.c` to skip disabled indexes entirely, thus reducing the number of places we need to check if an index is disabled or not.
> - Implements GUC hooks for parameter validation and assignment.
> - Resets the plan cache when the `disabled_indexes` list is modified through `ResetPlanCache()`
>
> I chose to modify the logic within `get_relation_info` as compared to, say, reducing the cost to make the planner not consider an index during planning, mostly to keep the number of changes being introduced to a minimum and also the logic itself being self-contained and easier to under perhaps (?).
>
> As mentioned before, this does not impact the building of the index. That still happens.
>
> I have added regression tests for:
>
> - Basic single-column and multi-column indexes
> - Partial indexes
> - Expression indexes
> - Join indexes
> - GIN and GiST indexes
> - Covering indexes
> - Range indexes
> - Unique indexes and constraints
>
> I'd love to hear any feedback on the proposed PATCH and also the overall approach.
> <v1-0001-Ability-to-enable-disable-indexes-through-GUC.patch>
>> On Sep 24, 2024, at 9:19 AM, Shayon Mukherjee <shayonj(at)gmail(dot)com> wrote:
>>
>> --
>> Kind Regards,
>> Shayon Mukherjee
>> <v1-0001-Proof-of-Concept-Ability-to-enable-disable-indexe.patch>
>