On Tue, Jul 22, 2025 at 6:50 PM David Rowley <dgrowle...@gmail.com> wrote:
> On Tue, 22 Jul 2025 at 05:16, Sami Imseih <samims...@gmail.com> wrote:
> > Also, I'd like to ask. what would be the argument against offering both
> > options,
> > ALTER and a GUC to override the catalog, as currently proposed in the patch?
>
> For me, the reason I don't like ALTER TABLE + the use_invisible_index
> / force_invisible_index (the v18 patch seems to be confused about the
> name of that GUC) is because it puts into question what "invisible"
> means. It's going to be a pretty useless feature for use cases where a
> DBA wants to ensure a certain index is *never* used, but does not want
> to drop it. A DBA might want to disable a certain index to investigate
> certain forms of index corruption and it might not be good if people
> can just overwrite that to bypass the DBA's choice.
>
Thanks for elaborating on this, you said it better than me.
So I'll note that in my proposal, the hypothetical catalog update
("alter index set guc" or whatever) is a one way door; if the dba (or
whomever) sets that, then the index is ignored everywhere, since the
session level guc can only also suggest the index be ignored from
planning. That is enough to allow people to both slow roll out OR slow
roll in new indexes, as needed, which I think covers enough ground
without the complexity going too far (which your below example clearly
shows is possible).
> It might be a slightly more flexible feature if there were 3 possible
> states and one of those states could be clearly defined to mean that
> users can overwrite the disabledness of all indexes by setting a GUC.
> I'm still struggling to like that, however.
>
> Now wondering if it would be better to spend the effort looking at
> pg_hint_plan and seeing how hard it would be to get global hints added
> which are applied to all queries, and then add a way to disable use of
> a named index. (I don't have any experience with that extension other
> than looking at the documentation)
>
I'd be interested in your evaluation of this, but the GUC I've
outlined would accomplish most of the use cases here automagically.
Robert Treat
https://xzilla.net
