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 <shay...@gmail.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 <shay...@gmail.com> wrote: >> >> -- >> Kind Regards, >> Shayon Mukherjee >> <v1-0001-Proof-of-Concept-Ability-to-enable-disable-indexe.patch> >
