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. 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? > >>
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, 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. [1] https://github.com/HypoPG/hypopg Regards, Sami
