Re: [PATCH] Re: Proposal to Enable/Disable Index using ALTER INDEX - Mailing list pgsql-hackers

On Sun, Sep 22, 2024 at 3:45 PM David Rowley <dgrowleyml@gmail.com> wrote:

> I think the documents should also mention that disabling an index is a
> useful way to verify an index is not being used before dropping it as
> the index can be enabled again at the first sign that performance has
> been effected. (It might also be good to mention that checking
> pg_stat_user_indexes.idx_scan should be the first port of call when
> checking for unused indexes)

While reviewing Shayon's v14 patch, I had removed text (quoted below) from the
ALTER INDEX docs that did not feel right in a command reference. I thought
of reading up on the history/discussion of the patch, and now I see why Shayon
chose to include an advice in ALTER INDEX docs.

> + 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?

Best regards,
Gurjeet
http://Gurje.et