Hi David, Answered below
> On Oct 9, 2024, at 9:19 AM, David Rowley <dgrowle...@gmail.com> wrote: > > On Wed, 9 Oct 2024 at 20:07, Shayon Mukherjee <shay...@gmail.com> wrote: >> [thinking…] Unless - we try to do support both a GUC and the ALTER INDEX >> ENABLE/DISABLE grammar + isdisabled attribute on pg_index? > > I just wanted to explain my point of view on this. This is my opinion > and is by no means authoritative. > > I was interested in this patch when you proposed it as an ALTER INDEX > option. I know other committers seem interested, but I personally > don't have any interest in the GUC option. I think the reason I > dislike it is that it's yet another not even half-baked take on > planner hints (the other one being enable* GUCs). I often thought that > if we ever did planner hints that it would be great to have multiple > ways to specify the hints. Ordinarily, I'd expect some special comment > type as the primary method to specify hints, but equally, it would be > nice to be able to specify them in other ways. e.g. a GUC to have them > apply to more than just 1 query. Useful for things such as "don't use > index X". Thank you so much this context, as someone new to psql-hackers, having this insight is super useful. Also getting a sense of how folks feel about controlling different behaviors like planner hints through GUC and SQL grammar. For instance: I wasn’t quite able to figure out the how to properly distinguish + reason between the enable* GUCs and ALTER index for this case, and patches are per my limited understand of the historical context as well. > > Now, I'm not suggesting you go off and code up planner hints. That's a > huge project. I'm just concerned that we've already got a fair bit of > cruft that will be left remaining if we ever get core planner hints > and a disabled_indexes GUC will just add to that. I don't feel like > the ALTER INDEX method would be leftover cruft from us gaining core > planner hints. Others might feel differently on that one. I feel the > ALTER INDEX option is less controversial. > > I'll also stand by what I said earlier on this thread. If PeterG gets > index skip scans done for PG18, then it's likely there's going to be > lots of users considering if they still need a certain index or not > after upgrading to PG18. Likewise, I personally feel that the ability to disable indexes quickly and reverse the disabling (also quickly) is super useful, especially from an operational POV (point of view). So, I am very keen on getting this landed and happy to iterate on as many patches as it takes. :D At this point, I am indifferent to each of the approaches (GUC or SQL grammar) based on the pros/cons I shared earlier in the thread & discussions in the thread. However, I would like us to make progress on getting _something_ out since the topic of disabling indexes has come up many times on pgsql-hackers in the past years and there is no easy way to toggle this behavior yet. “yet another not even half-baked take on planner hints" is a good way to put things about enable* GUCs, so I am very much on board with proposing an updated PATCH to support disabling of indexes through ALTER. The original PATCH was here for context [1]. I am also curious about supporting this ([1]) through the ALTER grammar and not having the planner consider indexes by updating `get_relation_info` in `plancat.c`. Basically, through `pg_index.isdisabled`, which is already open in `get_relation_info`, we can read from `pg_index.isdisabled` and accordingly update `IndexOptInfo.isdisabled`. So, I'm happy to explore that as well and share my findings. [1] https://www.postgresql.org/message-id/CANqtF-oBaBtRfw9O7GAoHN3nNEZQYsW3oaGfD%2BwJfG8R29nZYw%40mail.gmail.com Thanks Shayon
