Re: [PATCH] Proposal to Enable/Disable Index using ALTER INDEX

On Sat, Jun 21, 2025 at 10:59 AM Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:
> On Sat, Jun 21, 2025 at 8:38 AM Shayon Mukherjee <shayonj(at)gmail(dot)com> wrote:
>> The primary use case I have in mind is for helping engineers (ones not so seasoned like DBAs) decide whether to drop *existing* indexes. For new indexes, I expect most users would create them in visible mode (the default). Or so has been my experience so far.
>

+1

>
> What I would be using this for is when the server is choosing the wrong index, often in multi column index scenarios. The server can be obtuse in those situations. So I see this as a query optimization aid rather than a 'should I drop this?' Given that there are several ways to do that already. I can see scenarios where I'd want the index backed constraint to never be used for some/all queries.
>
> ALTER driving this seems ok. It seems more of a planner directive to me but having potential permanent configuration (vs mostly temporary needs) tips the scale IMO.
>

If your use case falls along the lines of modifying planner decisions,
a DDL based interface is really the wrong interface for that; it
forces system wide impact and provides no ability to work in a per
query/connection/role/etc type manner, and is the most susceptible to
having rollback issues. These types of issues have always been
resolved through GUCs, which again, fits the use case here as well. I
guess I'll caveat that with the note that your use case is already
addressable using pg_hint_plan, which operates using sql comments, but
I think we're trying to not mention query hints in this thread :-)

Robert Treat
https://xzilla.net