I found an old thread here [0]. Also, a question: If we go with the GUC approach, how do we expect `pg_get_indexdef` to behave?
I suppose it would behave no differently than it otherwise would, because there's no new SQL grammar to support and, given its GUC status, it seems reasonable that `pg_get_indexdef` doesn’t reflect whether an index is enabled or not. If so, then I wonder if using a dedicated `ALTER` command and keeping the state in `pg_index` would be better for consistency's sake? [0]https://postgrespro.com/list/id/20151212.112536.1628974191058745674.t-is...@sraoss.co.jp Thank you Shayon > On Sep 23, 2024, at 4:51 PM, Shayon Mukherjee <shay...@gmail.com> wrote: > > 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.) >> >
