> > This is already an established pattern has been used by other > > RDBMS's. Having worked with such interface in the past, a combo of > > ALTER and GUC, I never thought it was awkward and it's quite simple to > > understand/maintain. But that is subjective. > > > > It's amazing what people are willing to put up with if they are first > conditioned to believe it is the right way :-)
Well, it works and serves its purpose (or even multiple purposes). Also, whichever direction we go in will ultimately become the method our users adopt. That’s just how these things work. So, I respectfully disagree with your view :) > What stands out to me in the Oracle implementation is that they don't > sell it as a way to safely verify that indexes are unused before > dropping, but that it provides a way to safely create an index without > it being used. Ultimately, the ALTER command guarantees that the index is not being used, since it applies a global change. The GUC serves multiple purposes. For example,I can create an index as invisible and use it in a controlled way, which is helpful for experimenting with a new index. I can also make an index visible only to certain workloads, let's say the replicas only. Also, If part of my workload suffers because I made the index is invisible, I can selectively make the index visible again using this GUC whileI figure things out. In that case, it acts as a safety measure against the global change, without having to roll it back everywhere. I think it’s quite versatile in its application. > Both use cases are valid, but the former certainly > seems like the far more desired feature, and yet they seem to shy away > from showing the extra hoop jumping to make that work I'm not following your point about how it's awkward. > > > So I think the "right" interface looks something like a GUC that would > > > be something like "ignore_index_planning" which takes a csv list of > > > index names that the planner would ignore. > > > > A few years back, I explored this idea, and I did not really like the > > parsing > > overhead for every execution. You will need to supply a list of > > fully-qualified > > ( dbname.schemaname.indexname) names or carefully manage the GUC > > per database. > > I think I'd agree that you may need to be careful, but that's true of > most things. I'm less sure of the need to use fully qualified names; > pg_hint_plan does not have that restriction, pg_hint_plan works at the query level, and the hints are resolved based on aliases, if I recall correctly. This is quite different from a GUC, which can be applied at multiple levels, including the cluster level. > There might be more bookkeeping for the DBA with a > csv list, but only because it allows the DBA more flexibility in how > it is implemented. If you stick to managing one index at a time, the > bookkeeping is basically the same. Sure, that's a fair point, and I don't disagree with the flexibility that such a GUC provides. As I said, when I first started thinking about this problem, I found the flexibility of a list-based GUC to be desirable, but I couldn't rationalize the performance and maintenance trade-offs it incurs. I'm definitely open to having my mind changed again on this topic. But I don’t see this GUC as an opposing feature to the ALTER command, which I still believe we should have. In my view, the real question we are now debating is about how we should implement the GUC. -- Sami
