Re: Proposal to Enable/Disable Index using ALTER INDEX

Hello,

Regarding GUC implementation for index disabling, I was imagining something
like the attached PATCH. The patch compiles and can be applied for testing.
It's not meant to be production ready, but I am sharing it as a way to get
a sense of the nuts and bolts. It requires more proper test cases and docs,
etc. Example towards the end of the email.

That said, I am still quite torn between GUC setting or having a dedicated
ALTER grammar. My additional thoughts which is mostly a summary of what
David and Peter have already very nicely raised earlier are:

- GUC allows a non-privileged user to disable one or more indexes per
session.

- If we think of the task of disabling indexes temporarily (without
stopping any updates to the index), then it feels more in the territory of
query tuning than index maintenance. In which case, a GUC setting makes
more sense and sits well with others in the team like enable_indexscan,
enable_indexonlyscan and so on.

- At the same time, as David pointed out earlier, GUC is also a global
setting and perhaps storing the state of whether or not an index is being
used is perhaps better situated along with other index properties in
pg_index.

- One of my original motivations for the proposal was also that we can
disable an index for _all_ sessions quickly without it impacting index
build and turn it back on quickly as well. To do so with GUC, we would need
to do something like the following, if I am not mistaken, in which case
that is not something an unprivileged user may be able to perform, so just
calling it out.

ALTER USER example_user SET disabled_indexes = 'idx_foo_bar';

- For an ALTER statement, I think an ALTER INDEX makes more sense than
ALTER TABLE, especially since we have the existing ALTER INDEX grammar and
functionality. But let me know if I am missing something here.

- Resetting plan cache could still be an open question for GUC. I was
wondering if we can reset the plan cache local to the session for GUC (like
the one in the PATCH attached) and if that is enough? This concern doesn't
apply with managing property in pg_index.

- With a GUC attribute, the state of an index being enabled/disabled won't
be captured in pg_get_indexdef(), and that is likely OK, but maybe that
would need to be made explicit through docs.

Example 1

CREATE TABLE b AS SELECT generate_series(1,1000) AS b;
CREATE INDEX ON b((b%10));
ANALYZE b;
EXPLAIN SELECT DISTINCT b%10 FROM b;

SET disabled_indexes = 'b_expr_idx';

EXPLAIN SELECT DISTINCT b%10 FROM b; -- HashAggregate rows=10000

Example 2

CREATE TABLE disabled_index_test(id int PRIMARY KEY, data text);
INSERT INTO disabled_index_test SELECT g, 'data ' || g FROM
generate_series(1, 1000) g;
CREATE INDEX disabled_index_idx1 ON disabled_index_test(data);
EXPLAIN (COSTS OFF) SELECT * FROM disabled_index_test WHERE data = 'data
500';

SET disabled_indexes = 'b_expr_idx, disabled_index_idx1';

EXPLAIN SELECT * FROM disabled_index_test WHERE data = 'data 500'; -- no
index is used

Wrapping up...

I am sure there are things I am missing or unintentionally overlooking.
Since this would be a nice feature to have, I'd love some guidance on which
approach seems like a good next step to take. I am happy to work
accordingly on the patch.

Thank you
Shayon

On Tue, Sep 24, 2024 at 12:38 AM Maciek Sakrejda <m(dot)sakrejda(at)gmail(dot)com>
wrote:

> If one of the use cases is soft-dropping indexes, would a GUC approach
> still support that? ALTER TABLE?
>

--
Kind Regards,
Shayon Mukherjee