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
Description: Binary data
> On Sep 24, 2024, at 9:19 AM, Shayon Mukherjee <shay...@gmail.com> wrote: > > -- > Kind Regards, > Shayon Mukherjee > <v1-0001-Proof-of-Concept-Ability-to-enable-disable-indexe.patch>
