Re: Proposal to Enable/Disable Index using ALTER INDEX (with patch)

+ This is the
+ default state for newly created indexes.

This is not needed in the ALTER INDEX docs, IMO.ss

+ <para>
+ Disable the specified index. A disabled index is not used for
queries, but it
+ is still updated when the underlying table data changes and will still be
+ used to enforce constraints (such as UNIQUE, or PRIMARY KEY constraints).
+ This can be useful for testing query performance with and
without specific
+ indexes. If performance degrades after disabling an index, it
can be easily
+ re-enabled using <literal>ENABLE</literal>. Before disabling,
it's recommended
+ to check
<structname>pg_stat_user_indexes</structname>.<structfield>idx_scan</structfield>
+ to identify potentially unused indexes.
+ </para>

This got me thinking if dropping the index is the only
use case we really care about. For example, you may want
to prevent an index that is enforcing a constraint from
being used by the planner, but you probably don't want to
drop it. In fact, I also think that you may want the index
from being used in one part of your application but could
potentially benefit other parts of your application. In that
case, I can see a GUC that allows you to force the use of a
an index that has been CREATED or ALTERED as DISABLED.
UNlike the GUC suggested earlier in the thread, this GUC
can simply be a boolean to allow the force usage of a
DISABLED index. FWIW, Oracle has a similar parameter called
OPTIMIZER_USE_INVISIBLE_INDEXES.

+ underlying table data changes. This can be useful when you
want to create
+ an index without immediately impacting query performance,
allowing you to

c/performance/planning ??

I have also been thinking about DISABLE as the keyword,
and I really don't like it. DISABLE indicates, at least ot me,
that the index is not available for either reads or writes.

Looking at other engines, Sqlserver uses DISABLE to drop
the index data, but keeps the index metadata around.

Oracle uses INVISIBLE and MariabDB uses IGNORABLE to
provide similar functionality to that being discussed here. I
find those keywords to be more appropriate for this purpose.

What about if we use HIDDEN instead of DISABLE as the keyword?

Regards,

Sami