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

On Wed Apr 2, 2025 at 6:58 PM PDT, Sami Imseih wrote:
>> > + indexes. If performance degrades after making an index invisible, it can be easily
>> > + be made visible using <literal>VISIBLE</literal>. Before making an index invisible, it's recommended
>> > + to check <structname>pg_stat_user_indexes</structname>.<structfield>idx_scan</structfield>
>> > + to identify potentially unused indexes.
>>
>> I feel ALTER INDEX command reference doc is not the right place for this kind of
>> operational advice. Is there a better place in documentation for this kind of
>> advice? Or maybe it needs to be reworded to fit the command reference style?
>
> I agree with you.
>
> What about we add this wording in the following section [0]? This
> section discusses techniques
> for experimenting with indexes. It says,
> ".... A good deal of experimentation is often necessary. The rest of
> this section gives some tips for that:...."
>
> A discussion about invisible indexes as one of the tools for
> experimentation can be added here.
> What do you think?
>
> [0] https://www.postgresql.org/docs/current/indexes-examine.html

That seems like a very good location for this advice. But the current
set of bullet points are all directed towards "... a general procedure
for determining which indexes to create". I propose that a new paragrph,
not a bullet point, be added towards the end of that section which
addresses the options of steps to take before dropping an index.
Something like the following:

Sometimes you may notice that an index is not being used anymore by the
application queries. In such cases, it is a good idea to investigate if
such an index can be dropped, because an index that is not being used
for query optimization still consumes resources and slows down INSERT,
UPDATE, and DELETE commands. To aid in such an investigation, look at
the pg_stat_user_indexes.idx_scan count for the index.

To determine the performance effects of dropping the index, without
actually dropping the said index, you may mark the index invisible to
the planner by using the ALTER INDEX ... INVISIIBLE command. If it turns
out that doing so causes a performance degradation, the index can be
quickly made visible to the planner for query optimization by using the
ALTER INDEX ... VISIBLE command.

Thoughts?

Best regards,
Gurjeet
http://Gurje.et