Re: Proposal to Enable/Disable Index using ALTER INDEX

Hello,

Thank you for the detailed information and feedback David. Comments inline.

P.S Re-sending it to the mailing list, because I accidentally didn't select
reply-all on the last reply.

On Mon, Sep 9, 2024 at 6:16 PM David Rowley <dgrowleyml(at)gmail(dot)com> wrote:

> On Tue, 10 Sept 2024 at 09:39, Shayon Mukherjee <shayonj(at)gmail(dot)com> wrote:
> > 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 personally think having some way to alter an index to stop it from
> being used in query plans would be very useful for the reasons you
> mentioned. I don't have any arguments against the syntax you've
> proposed. We'd certainly have to clearly document that constraints
> are still enforced. Perhaps there is some other syntax which would
> self-document slightly better. I just can't think of it right now.
>

Thank you and likewise. I was thinking of piggy backing off of VALID / NOT
VALID, but that might have similar issues (if not more confusion) to the
current proposed syntax. Will be sure to update the documentation.

>
> > Implementation:
> > To keep this simple, I suggest toggling the indisvalid flag in pg_index
> during the enable/disable operation.
>
> That's not a good idea as it would allow ALTER INDEX ... ENABLE; to be
> used to make valid a failed concurrently created index. I think this
> would need a new flag and everywhere in the planner would need to be
> adjusted to ignore indexes when that flag is false.
>

That is a great call and I wasn't thinking of the semantics with the
existing usage of concurrently created indexes.

>
> > Additional Considerations:
> > - Keeping the index up-to-date while it’s disabled seems preferable, as
> it avoids the need to rebuild the index if it’s re-enabled later. The
> alternative would be dropping and rebuilding the index upon re-enabling,
> which I believe would introduce additional overhead in terms of application
> logic & complexity.
>
> I think the primary use case here is to assist in dropping useless
> indexes in a way that can very quickly be undone if the index is more
> useful than thought. If you didn't keep the index up-to-date then that
> would make the feature useless for that purpose.
>

+1

>
> If we get the skip scan feature for PG18, then there's likely going to
> be lots of people with indexes that they might want to consider
> removing after upgrading. Maybe this is a good time to consider this
> feature as it possibly won't ever be more useful than it will be after
> we get skip scans.
>
> David
>

Thank you for the feedback again, I will look into the changes required and
accordingly propose a PATCH.

--
Kind Regards,
Shayon Mukherjee