Re: Proposal to Enable/Disable Index using ALTER INDEX

On 24.09.24 20:21, Tom Lane wrote:
> 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.

Yes, I think you'd want that, because one of the purposes of this
feature would be to test whether it's okay to drop an index. So you
don't want the planner to take any account of the index for its decisions.