Re: [PATCH] Proposal to Enable/Disable Index using ALTER INDEX

Hello Hackers,

First, thank you Shayon for proposing and maintaining the original patch
series. I found the previous discussion very helpful, especially the points
about whether this belongs in core, how it differs from session-level index
hints, and what semantics an index should retain while it is disabled or
invisible to the planner.

I would like to revive this topic with a narrower version of the patch.

In our own work, we still find value in having a global, catalog-level
invisible-index mechanism in core. The main use case is index removal
safety. In many production systems, before dropping an index, it is
difficult to know with confidence which SQL statements may still depend on
it. Making the index invisible lets users run the workload for some time
without letting the planner choose that index, while still keeping the
index physically present and maintained. If a regression is found, the
index can be made visible again immediately without rebuilding it. If no
regression is found, the user has more confidence that dropping the index
is safe.

This is different from a session-level hint mechanism. Extensions such as
pg_hint_plan, and potentially newer tools such as pg_plan_advice, can be
useful for per-session or per-query experimentation, including disabling a
specific index for a specific query. I think those extension-level
mechanisms are complementary. The patch here is intentionally focused only
on the global catalog-level behavior: once an index is marked invisible,
the optimizer simply does not consider it.

Compared with the earlier versions, I reset the patch around a smaller
design:

-

The GUC/session override has been removed.
There is no use_invisible_index or similar option in this version.
Invisible means invisible to the optimizer globally until changed by DDL.
-

The patch uses a separate pg_index.indisvisible catalog flag.
It does not reuse indisvalid or indislive. This keeps the existing
meanings of those flags unchanged.
-

Invisible indexes remain maintained.
The patch does not disable DML maintenance. Invisible indexes remain
valid/live/ready in the normal sense, and constraint-backed indexes
continue to enforce uniqueness, primary keys, and exclusion constraints.
ANALYZE also continues to refresh statistics for invisible expression
indexes; invisibility only prevents the planner from choosing the index as
an access path.
-

The patch keeps index visibility orthogonal to other index roles.
For example, an invisible index can still be used as REPLICA IDENTITY,
since logical decoding and replica identity lookup do not depend on the
planner's index path list.
-

The planner filters invisible indexes when building the relation index
list.
Conceptually, if an index is not valid or is not visible, it is skipped
before being added to the planner's RelOptInfo->indexlist.
-

Cached plans are invalidated when index visibility changes.
ALTER INDEX ... INVISIBLE and ALTER INDEX ... VISIBLE change the set of
indexes the planner may consider for the heap relation. The new patch
invalidates the heap relation's relcache entry with
CacheInvalidateRelcacheByRelid(heapOid), so cached/generic plans
depending on that relation are rebuilt. This avoids a broad
ResetPlanCache() and also avoids opening the heap relation just for
invalidation.
-

Partitioned indexes are supported.
Changing visibility on a leaf partition index affects only that leaf
index. Changing visibility on a partitioned parent index recurses through
the attached partitioned-index hierarchy and applies the same visibility to
attached child indexes. Multi-level partitioned index hierarchies are
covered by tests.
-

ALTER INDEX ... ATTACH PARTITION does not rewrite the child index's
existing visibility.
A pre-existing child index keeps its own indisvisible state when
attached. A later ALTER INDEX on the partitioned parent can be used to
align the hierarchy. I am not certain this is the only reasonable behavior,
so I would especially appreciate feedback on this point.
-

Index rebuild/copy paths preserve visibility.
REINDEX, REINDEX CONCURRENTLY, partitioned index cloning, and index
rebuilds from operations such as ALTER TABLE ... ALTER COLUMN TYPE
preserve the source index's visibility.
-

pg_dump/restore support is included.
The patch preserves invisible indexes through dump/restore, including
constraint-backed indexes, partitioned constraint-backed indexes, and
invisible indexes used as REPLICA IDENTITY. For constraint-backed
indexes, pg_dump emits a follow-up ALTER INDEX ... INVISIBLE where
needed.
-

psql, pg_get_indexdef(), and ddl_deparse support are included.
pg_get_indexdef() emits SQL-style INVISIBLE, while psql \d displays the
state as a descriptive , invisible annotation.

The intended SQL interface is:

CREATE INDEX idx ON tab(col) INVISIBLE;
CREATE INDEX idx ON tab(col) VISIBLE;
ALTER INDEX idx INVISIBLE;
ALTER INDEX idx VISIBLE;

A plain CREATE INDEX idx ON tab(col) still creates a visible index.

I have added regression and TAP coverage for the main behavior:

-

planner ignores invisible indexes;
-

visible/invisible state changes invalidate prepared/generic plans;
-

invisible unique and primary-key indexes still enforce constraints;
-

DML continues to maintain invisible indexes;
-

ANALYZE continues to maintain statistics for invisible expression
indexes;
-

invisible indexes can still be used as REPLICA IDENTITY;
-

REINDEX and rebuild paths preserve visibility;
-

partitioned parent and leaf index behavior;
-

multi-level partitioned index hierarchies;
-

ALTER INDEX ... ATTACH PARTITION visibility-retention behavior;
-

pg_dump output and restore-side catalog verification;
-

duplicate primary-key rejection after restore;
-

psql and ddl_deparse output.

The tests I have run locally are:

make check
targeted create_index regression test
pg_dump TAP tests
test_ddl_deparse

These passed in my local environment. I have not run a full make check-world
yet. My local macOS build was configured --without-icu because of an
unrelated local SDK/ICU setup issue.

I understand there was not enough interest to move the earlier version
forward at the time, but I hope hackers may consider this narrower version
again. In particular, I would appreciate feedback on:

1.

whether the no-GUC, catalog-only behavior is an acceptable core feature;
2.

whether ALTER INDEX ... ATTACH PARTITION should preserve child
visibility, as in this patch, or force synchronization with the parent;
3.

whether the cache invalidation approach is sufficient;
4.

whether pg_dump should preserve invisible state in the way proposed here;
5.

whether the psql display style is acceptable;
6.

whether keeping indisvisible orthogonal to expression-index statistics
and REPLICA IDENTITY is the right behavior.

Thanks again to Shayon and everyone who participated in the previous
discussion.

Regards,
Haibo

On Wed, Jun 17, 2026 at 7:21 PM Shayon Mukherjee <shayonj(at)gmail(dot)com> wrote:

> Hello Hackers,
>
> Looks like there may not be enough interest to port any functionality into
> core. So, just noting that I have withdrawn the patch from CommitFest[1].
> Thank you for the good discussions and leanings.
>
> [1] https://commitfest.postgresql.org/patch/5274/
>
> Shayon
>
> On Aug 11, 2025, at 6:22 AM, Michael Paquier <michael(at)paquier(dot)xyz> wrote:
>
> On Mon, Aug 04, 2025 at 01:19:46PM +0900, Michael Paquier wrote:
>
> Sami has proposed a patch for pg_hint_plan that goes in the direction
> of a DisableIndex hint, which is doable even if it requires some work
> inside the extension code like some refactoring:
> https://github.com/ossc-db/pg_hint_plan/issues/226
>
> So I cannot say much about this proposal for core, but for
> pg_hint_plan I'm looking at adding that for the v18 release of the
> module, which is planned for the end of August/beginning of September.
>
>
> FWIW, I have been able to merge the patch to add support for this
> DisableIndex hint in pg_hint_plan. So at least this will give people
> something to toy with even without this in-core feature. That will be
> included in next week's release, available in the version of the
> module compatible with v18.
> --
> Michael
>
>
>