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 <dgrowle...@gmail.com> wrote: > On Tue, 10 Sept 2024 at 09:39, Shayon Mukherjee <shay...@gmail.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
