Re: Proposal to Enable/Disable Index using ALTER INDEX

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.)