Re: [PATCH] Re: Proposal to Enable/Disable Index using ALTER INDEX

> Thanks for weighing in.
+1

> In my mind, this feature is for "I'm almost 100% certain this index
> isn't needed, I want to make sure I'm 100% right in a way that I can
> quickly fix the ensuing chaos if I'm wrong".

This is the primary use-case. A user performs an ALTER INDEX...
INVISIBLE, and they monitor the workload and pg_stat_all_indexes
( on primary and hot standbys ) until they feel confident enough
to fully commit to dropping the index. This is the case that many
users out there want. The bonus is the locking acquired to flip
the VISIBLE/INVISIBLE flag is a ShareUpdateExclusiveLock
on the index, so this operation can only be blocked by VACUUM
or other ALTERs, etc,

> I'm also skeptical of the idea that
> users need a way to add invisible indexes they can then test to see if
> they are useful because 1) this is basically how indexes already work,
> meaning if you add an index and it isn't useful, it doesn't get used,

The GUC will be useful for experimentation or for the safer rollout of
new indexes. For example, an index can be created as INVISIBLE initially,
and with use_invisible_index, one can observe how the index may impact
various queries before fully committing to enabling it. Also, if we allow an
index to be INVISIBLE initially, we need to provide the user with this
GUC; otherwise, I can’t see why a user would want to make an
index INVISIBLE initially.

> and 2) we have an extension (hypopg) which arguably provides this
> functionality without causing a bunch of i/o, and there isn't nearly
> the clamor to add this functionality in to core as there is for having
> a way to "soft drop" indexes.

I have not worked much with HypoPG, but from what I understand,
it works only at the EXPLAIN level. It is purely an experimentation tool.
However, the proposed GUC can also be used in more places,
including, pg_hint_plan ( at least with the SET hint without any changes
to pg_hint_plan).

> > P.S. I really do want to thank Shayon for sticking with this;

> +1
+1

--
Sami Imseih
Amazon Web Services (AWS)