Re: Proposal to Enable/Disable Index using ALTER INDEX

Peter Eisentraut <peter(at)eisentraut(dot)org> writes:
> On 23.09.24 22:51, Shayon Mukherjee wrote:
>> I am happy to draft a patch for this as well. I think I have a working
>> idea so far of where the necessary checks might go. However if you don’t
>> mind, can you elaborate further on how the effect would be similar to
>> enable_indexscan?

> Planner settings like enable_indexscan used to just add a large number
> (disable_cost) to the estimated plan node costs. It's a bit more
> sophisticated in PG17. But in any case, I imagine "disabling an index"
> could use the same mechanism. Or maybe not, maybe the setting would
> just completely ignore the index.

Yeah, I'd be inclined to implement this by having create_index_paths
just not make any paths for rejected indexes. Or you could back up
another step and keep plancat.c from building IndexOptInfos for them.
The latter might have additional effects, such as preventing the plan
from relying on a uniqueness condition enforced by the index. Not
clear to me if that's desirable or not.

[ thinks... ] One good reason for implementing it in plancat.c is
that you'd have the index relation open and be able to see its name
for purposes of matching to the filter. Anywhere else, getting the
name would involve additional overhead.

regards, tom lane