Re: Proposal to Enable/Disable Index using ALTER INDEX (with patch)

Thanks for the updates patch!

>> This got me thinking if dropping the index is the only
>> use case we really care about. For example, you may want
>> to prevent an index that is enforcing a constraint from
>> being used by the planner, but you probably don't want to
>> drop it. In fact, I also think that you may want the index
>> from being used in one part of your application but could
>> potentially benefit other parts of your application. In that
>> case, I can see a GUC that allows you to force the use of a
>> an index that has been CREATED or ALTERED as DISABLED.
>> UNlike the GUC suggested earlier in the thread, this GUC
>> can simply be a boolean to allow the force usage of a
>> DISABLED index. FWIW, Oracle has a similar parameter called
>> OPTIMIZER_USE_INVISIBLE_INDEXES.
>
>
> I totally see where you are coming from. Some rough thoughts/notes:
>
> - The patch/proposed feature today doesn't disable constraints, like uniqueness. It only impacts query planning.
Maybeit should ? 
> - I was imagining this feature as being short-lived in production - that is, you disable a potential index to collect
dataon query performance and then make a decision on whether you need the index permanently. However, yes, one can
alwayskeep an index disabled for longer, and conditionally use it in another part of an application in which case a GUC
tobypass the disabled/invisible index would come in handy as you mentioned. 
> - I don't have a strong opinion either way, but I do wonder - considering that this GUC is an additive feature - if
it'ssomething worth implementing once we have more feedback from the usage (in v18 pre release, alpha, ec) of marking
anindex as disabled/invisible first? Or perhaps as a follow-up patch? 
>
> If we do go with a GUC - is FORCE_INVISIBLE_INDEX a good name?
>
>>

Here is a use-case where the GUC may be useful. I can see a user
wanting to try out the index before committing to using it across the
board. They can create the index as invisible and force using
it in a specific part of the application. If they are happy with the results,
they can make it visible. This is similar to but not exactly what HypoPG [1]
does. HypoPG does not actually create the index and can only be used
with EXPLAIN ( not EXPLAIN ANALYZE ) in a specific session. I see the
ability to test on a real index may be more useful. Maybe others have
other thoughts on this?

> I agree. DISABLE doesn't sit right. I noticed INVISIBLE in MariaDB. I like HIDDEN/VISIBLE or ACTIVE/INACTIVE as well,
sinceit impacts query planning. 

Let's see if other have an opinion on this, but VISIBLE/INVISIBLE
seem the best way to indicate that the indexes are visible or invisible
from the optimizer. ACTIVE/INACTIVE sound a lot like ENABLE/DISABLE.

[1] https://github.com/HypoPG/hypopg

Regards,

Sami