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

Hi,

Thank you for the patch! I've had a need for this feature several times,
so I appreciate the work you’ve put into it.

I like the new name VISIBLE/INVISIBLE and the fact that it's a separate flag in
pg_index (it's easy to monitor).

I don’t feel qualified to provide an opinion on the code itself just yet.

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}

The completion for the INVISIBLE / VISIBLE keyword is missing in psql.


I also tested the ALTER command within a transaction, and it worked as I
expected: the changes are transactional (possibly because you didn’t use
systable_inplace_update_begin?).


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?