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