public inbox for [email protected]  

help / color / mirror / Atom feed

From: Shayon Mukherjee <[email protected]>
To: Robert Haas <[email protected]>
Cc: Peter Eisentraut <[email protected]>
Cc: [email protected]
Subject: Re: Proposal to Enable/Disable Index using ALTER INDEX
Date: Wed, 9 Oct 2024 08:06:50 +0100
Message-ID: <[email protected]> (raw)
In-Reply-To: <CA+TgmoZw+o3UpKVf-=-o-x4fC2A54buK6ogLwk-TZ4C6h2W_ww@mail.gmail.com>
References: <CANqtF-oXKe0M=0QOih6H+sZRjE2BWAbkW_1+9nMEAMLxUJg5jA@mail.gmail.com>
	<[email protected]>
	<CA+TgmoZw+o3UpKVf-=-o-x4fC2A54buK6ogLwk-TZ4C6h2W_ww@mail.gmail.com>



> On Oct 7, 2024, at 4:52 PM, Robert Haas <[email protected]> wrote:
> 
> On Mon, Sep 23, 2024 at 11:14 AM Peter Eisentraut <[email protected]> wrote:
>> I think a better approach would be to make the list of disabled indexes
>> a GUC setting, which would then internally have an effect similar to
>> enable_indexscan, meaning it would make the listed indexes unattractive
>> to the planner.
>> 
>> This seems better than the proposed DDL command, because you'd be able
>> to use this per-session, instead of forcing a global state, and even
>> unprivileged users could use it.
>> 
>> (I think we have had proposals like this before, but I can't find the
>> discussion I'm thinking of right now.)
> 
> I feel like a given user could want either one of these things. If
> you've discovered that a certain index is causing your production
> application to pick the wrong index, disabling it and thereby
> affecting all backends is what you want. If you're trying to
> experiment with different query plans without changing anything for
> other backends, being able to set some session-local state is better.
> I don't understand the argument that one of these is categorically
> better than the other.

Makes sense to me and it’s something I am somewhat split on as well. I suppose with a GUC you can still do some thing like

ALTER USER foobar SET disabled_indexes to ‘idx_test_table_id’ 

[thinking…] This way all new sessions will start to not consider the index when query planning. Of course it does not help existing sessions, so one may need to kill those backends, which could be heavy handed.

Both these options clearly serve slightly different purposes with good pros and I am currently thinking if GUC is that good middle ground solution.

Curious if someone has a stronger opinion on which one of these might make more sense perhaps :-D.

[thinking…] Unless - we try to do support both a GUC and the ALTER INDEX ENABLE/DISABLE grammar + isdisabled attribute on pg_index?

I can see that both implementations (GUC and the new attribute on pg_index via ALTER) have the primary logic managed by `get_relation_info` in `plancat.c`. Here, we set `isdisabled` (new attribute) on `IndexOptInfo` and compare it against `disabled_indexes` in the GUC (from the previous GUC patch). Similarly, for `pg_index`, which is already open in `get_relation_info`, we can read from `pg_index.isdisabled` and accordingly update `IndexOptInfo.isdisabled`.

[0] https://www.postgresql.org/message-id/[email protected]

Thanks
Shayon 

view thread (23+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected], [email protected]
  Subject: Re: Proposal to Enable/Disable Index using ALTER INDEX
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox