On Sat, Jan 11, 2025 at 5:50 PM Sami Imseih <samims...@gmail.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
v9-0001-Introduce-the-ability-to-set-index-visibility-usi.patch
Description: Binary data
