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

On Fri, Jan 24, 2025 at 4:03 PM Benoit Lobréau <benoit(dot)lobreau(at)gmail(dot)com>
wrote:

> I did notice something in the command prototype:
>
> +ALTER INDEX [ IF EXISTS ] <replaceable
> class="parameter">name</replaceable> VISIBLE
> +ALTER INDEX [ IF EXISTS ] <replaceable
> class="parameter">name</replaceable> INVISIBLE
>
> it would probably be better as:
>
> +ALTER INDEX [ IF EXISTS ] <replaceable
> class="parameter">name</replaceable> {VISIBLE|INVISIBLE}
>
>
Thank you for your review, really appreciate it! I have updated with your
feedback in v10 patch [1]

> The completion for the INVISIBLE / VISIBLE keyword is missing in psql.
>
>
Also updated in v10 patch [1]

> Additionally, I tried using the ALTER command on an index that supports
> a foreign key. As expected, delete and update operations on the referenced
> table became significantly slower. I was wondering if this behavior should
> be documented here.
>
> + Make the specified index invisible. The index will not be used
> for queries.
> + This can be useful for testing query performance with and
> without specific
> + indexes.
>
> Maybe something like :
>
> The index will not be used for user or system queries (e.g., an index
> supporting foreign keys).
>
> I noticed that you mentionned checking pg_stat_user_indexes before using
> the query but it might not be enough?
>

This part of the documentation has gone through some changes, and I have
sensed it's hard to convey the details without complicating or breaking
precedence. By saying "The index will not be used for queries", I (as a
PostgreSQL user) was assuming this would apply to both user and system
queries, and hence the distinction was implicit. However, I don't have a
strong opinion and am happy to make the changes. I am also curious if
anyone else has thoughts on this as well?

[1]
https://www.postgresql.org/message-id/CANqtF-rs5N4ZepiH9YV1VVi6YNtyFDzDbJabhcZKqh-hNgLmgw%40mail.gmail.com

Thank you
Shayon