Re: PROPOSAL: Support global and local disabling of indexes

Hi,

On Thu, Mar 17, 2022 at 11:16:24PM -0700, Paul Martinez wrote:
>
> Adding and removing indexes is a regular part of database maintenance,
> but in a large database, removing an index can be a very risky operation.
> Removing the wrong index could have disastrous consequences for
> performance, and it could take tens of minutes, or even hours, to rebuild
> the entire index.
>
> I propose adding an ALTER INDEX command that can enable or disable an
> index on a global level:
>
> ALTER INDEX index_name ENABLE;
> ALTER INDEX index_name DISABLE;
>
> A disabled index is still updated, and still enforces constraints, but it
> will not be used for queries.
>
> Whether or not the index is disabled could also be specified at index
> creation:
>
> CREATE INDEX index_name ON table_name (col1, col2) ENABLED; -- default
> CREATE INDEX index_name ON table_name (col1, col2) DISABLED;
>
> This would be useful if a user anticipates index creation to take a long
> time and they want to be able to carefully monitor the database once the
> index starts getting used.
>
> It would also be useful to be able to enable and disable indexes locally
> in the context of a single session to easily and safely verify that a
> query can still be executed efficiently without an index:
>
> ALTER INDEX index_name DISABLE SESSION;

For the record, all of that is already doable using plantuner extension:
https://github.com/postgrespro/plantuner.