Re: [PATCH] Proposal to Enable/Disable Index using ALTER INDEX - Mailing list pgsql-hackers

> > 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