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

On Sat, Jan 11, 2025 at 5:50 PM Sami Imseih <samimseih(at)gmail(dot)com> wrote:

> 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?
>
>
That's a good call out, thank you. I was mostly interested in the other use
cases and I like how this bool GUC unlocks the use case you mentioned.
Plus, also the ability to conditionally use indexes in session that are
globally marked as invisible.

> > 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.
>
> 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.
>

I like VISIBLE/INVISIBLE. Proposing a new patch with the following changes

- We now have moved away from DISABLE/ENABLE grammar to VISIBLE/INVISIBLE.
No change in functionality, it's all the same as before, just new grammar.
Backed by regression specs like before too.
- The column in pg_index is now called indisvisible.
- Introduced a new GUC - use_invisible_index. When set to on, it will not
respect the visibility state in pg_index (related to the above
conversation).
- When GUC is assigned/updated we accordingly reset plan cache. Backed this
specs with.

The patch is rebased against master and passes in CI. Happy to iterate on
any feedback received.

Thanks
Shayon