Re: Proposal to Enable/Disable Index using ALTER INDEX

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.