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