On Sat, Jun 21, 2025 at 10:59 AM Merlin Moncure <mmonc...@gmail.com> wrote: > On Sat, Jun 21, 2025 at 8:38 AM Shayon Mukherjee <shay...@gmail.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
