That's a good point. +1 for the idea of the GUC setting, especially since, as you mentioned, it allows unprivileged users to access it and being per-session..
I am happy to draft a patch for this as well. I think I have a working idea so far of where the necessary checks might go. However if you don’t mind, can you elaborate further on how the effect would be similar to enable_indexscan? I was thinking we could introduce a new GUC option called `disabled_indexes` and perform a check against in all places for each index being considered with its OID via get_relname_relid through a helper function in the various places we need to prompt the planner to not use the index (like in indxpath.c as an example). Curious to learn if you have a different approach in mind perhaps? Thank you, Shayon > On Sep 23, 2024, at 11:14 AM, Peter Eisentraut <pe...@eisentraut.org> wrote: > > On 09.09.24 23:38, Shayon Mukherjee wrote: >> *Problem*: >> Adding and removing indexes is a common operation in PostgreSQL. On larger >> databases, however, these operations can be resource-intensive. When >> evaluating the performance impact of one or more indexes, dropping them >> might not be ideal since as a user you may want a quicker way to test their >> effects without fully committing to removing & adding them back again. Which >> can be a time taking operation on larger tables. >> *Proposal*: >> I propose adding an ALTER INDEX command that allows for enabling or >> disabling an index globally. This could look something like: >> ALTER INDEX index_name ENABLE; >> ALTER INDEX index_name DISABLE; >> A disabled index would still receive updates and enforce constraints as >> usual but would not be used for queries. This allows users to assess whether >> an index impacts query performance before deciding to drop it entirely. > > I think a better approach would be to make the list of disabled indexes a GUC > setting, which would then internally have an effect similar to > enable_indexscan, meaning it would make the listed indexes unattractive to > the planner. > > This seems better than the proposed DDL command, because you'd be able to use > this per-session, instead of forcing a global state, and even unprivileged > users could use it. > > (I think we have had proposals like this before, but I can't find the > discussion I'm thinking of right now.) >
