On Mon, Jul 21, 2025 at 5:24 PM Sami Imseih <samims...@gmail.com> wrote: > > > > > it will still be extremely risky in > > > > heavy production workloads. In short, we're both walking a bull > > > > through the china shop, but it would seem mine is much more > > > > temperamental than yours. > > > > > > Robert, Could you describe the GUC you would like to see? > > > > > > Also, I'd like to ask. what would be the argument against offering both > > > options, > > > ALTER and a GUC to override the catalog, as currently proposed in the > > > patch? > > > > > > This conversation has been mainly GUC is better than ALTER, or vice versa. > > > > > > It is clear, at least to me, there are merits in both approaches, so > > > what would be > > > the argument against making both options available ( maybe with a GUC that > > > could be more useful than a simple boolean )? > > > > > > > Just to reiterate, I am not against having both a GUC and ALTER > > option, if implemented correctly. > > Thanks. This got lost, at least to me, in the thread above. > > > Like David, I don't have good > > feelings about the ALTER / use_invisible_index GUC overwriting > > behavior that is explicitly written in the catalog, > > OK, although I don't necessarily think this is something to be frowned > upon. I mean, if we end up with an ALTER/GUC combo, I can't see > how we can avoid such behavior. >
I laid out two potential options in my previous email; if we implement this like a storage option, then you are setting the guc at the index level, which would resolve any opposing behavior. Even if you didn't do that, if the catalog only indicates that index should be ignored, rather than trying to control yes/no of index ignoring, then there is no conflict. If the flag says ignore, you ignore. If the GUC says ignore, you ignore. If you don't find either, you don't ignore (default). > > and I see no > > reason to settle for a technically awkward solution when I think it > > also delivers a poor user interface that will be hard to reason about > > and/or debug in production. > > 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 :-) 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. 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 think precisely because it is awkward to work with. I think we should try to make the most common use case easier. > > 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, and arguably there are use cases against wanting to do that (imagine a multi-tenant scenario where you want a specific role to ignore an index regardless of which database it connects to, as all databases have the same schema). > Also, if you drop an index, you now must go cleanup the list, > and especially if at some point you recreate the index with the same name. > There is also that you have to push this GUC to all your standbys manually. > > This never sounded good to me as a core feature, or do I think it's a really > friendly interface, and I think you can get in more trouble trying to deal > with > such a GUC that requires such management. > It isn't clear to me which option you are speaking about here. If you believe you need a GUC (which you have said up-thread), then there is going to be some GUC management for any such implementation. If you set a role with a boolean "ignore_disabled_indexes" because you are dropping an index, you'll certainly want to clean that up once the index is dropped. 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. Robert Treat https://xzilla.net
