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
beingper-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
foreach index being considered with its OID via get_relname_relid through a helper function in the various places we
needto 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 <peter@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
beresource-intensive. When evaluating the performance impact of one or more indexes, dropping them might not be ideal
sinceas 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
somethinglike:
>> 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.
Thisallows 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
havean 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
globalstate, 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.)
>
