Re: Proposal to Enable/Disable Index using ALTER INDEX

On Wed, 16 Oct 2024 at 03:40, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>
> On Sat, Oct 12, 2024 at 5:56 AM Shayon Mukherjee <shayonj(at)gmail(dot)com> wrote:
> > Thank you for sharing this Robert. I like the idea behind "allowing extensions to control planner behavior” overall and I think it does help towards a powerful extension ecosystem too. I wonder if there is a reality where we can achieve both the outcomes here
> >
> > - Support disabling of indexes [1] through ALTER command
> > - While also building on "allowing extensions to control planner behavior” for the reasons above?
> >
> > [1] https://www.postgresql.org/message-id/ABD42A12-4DCF-4EE4-B903-4C657903CECE%40gmail.com
>
> Yes, I think we can do both things.

I think so too. I imagine there'd be cases where even hints global to
all queries running on the server wouldn't result in the index being
completely disabled. For example, a physical replica might not be
privy to the hints defined on the primary and it might just be the
queries running on the physical replica that are getting the most use
out of the given index. Having the change made in pg_index would mean
physical replicas have the index disabled too. For the primary use
case I have in mind (test disabling indexes you're considering
dropping), having the disabledness replicate would be very useful.

David