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

On Fri, Jan 3, 2025 at 4:09 PM Sami Imseih <samimseih(at)gmail(dot)com> wrote:

> + This is the
> + default state for newly created indexes.
>
> This is not needed in the ALTER INDEX docs, IMO.ss
>
> Updated and attached the 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. Maybe it should ?
- I was imagining this feature as being short-lived in production - that
is, you disable a potential index to collect data on query performance and
then make a decision on whether you need the index permanently. However,
yes, one can always keep an index disabled for longer, and conditionally
use it in another part of an application in which case a GUC to bypass 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's something worth implementing
once we have more feedback from the usage (in v18 pre release, alpha, ec)
of marking an index 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?

>
> + underlying table data changes. This can be useful when you
> want to create
> + an index without immediately impacting query performance,
> allowing you to
>
> c/performance/planning ??
>

Updated and attached the patch.

> I have also been thinking about DISABLE as the keyword,
> and I really don't like it. DISABLE indicates, at least ot me,
> that the index is not available for either reads or writes.
>
> Looking at other engines, Sqlserver uses DISABLE to drop
> the index data, but keeps the index metadata around.
>
> Oracle uses INVISIBLE and MariabDB uses IGNORABLE to
> provide similar functionality to that being discussed here. I
> find those keywords to be more appropriate for this purpose.
>
> What about if we use HIDDEN instead of DISABLE as the keyword?
>
>
I agree. DISABLE doesn't sit right. I noticed INVISIBLE in MariaDB. I like
HIDDEN/VISIBLE or ACTIVE/INACTIVE as well, since it impacts query planning.

Happy to propose a patch with HIDDEN/VISIBLE if that sounds like a good
idea.

Thank you
Shayon