Re: Proposal to Enable/Disable Index using ALTER INDEX

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(dot)112536(dot)1628974191058745674(dot)t-ishii(at)sraoss(dot)co(dot)jp

Thank you
Shayon

> On Sep 23, 2024, at 4:51 PM, Shayon Mukherjee <shayonj(at)gmail(dot)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 <peter(at)eisentraut(dot)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.)
>>
>