Hi, On 2018-06-18 17:57:04 -0400, Tom Lane wrote: > Andrew Dunstan <andrew.duns...@2ndquadrant.com> writes: > > This is a MySQL feature, where an index is not considered by the > > planner. Implementing it should be fairly straightforward, adding a new > > boolean to pg_index, and options to CREATE INDEX and ALTER INDEX. I > > guess VISIBLE would become a new unreserved keyword. > > > The most obvious use case is to see what the planner does when the index > > is not visible, for example which other index(es) it might use. There > > are probably other cases where we might want an index to enforce a > > constraint but not to be used in query planning. > > Traditionally the way to do the former is > > begin; > drop index unwanted; > explain ....; > rollback; > > Admittedly, this isn't great in a production environment, but neither > would be disabling the index in the way you suggest.
Yea, I don't think a global action - which'll at least take a something like a share-update-exclusive lock - is a suitable approach for this kinda thing. > I think the actually desirable way to handle this sort of thing is through > an "index advisor" sort of plugin, which can hide a given index from the > planner without any globally visible side-effects. Although I'm a bit doubtful that just shoving this into an extension is really sufficient. This is an extremely common task. Greetings, Andres Freund