opt: wrong index alteration recommendation when multiple invisible indexes exist

[rytaft]

Describe the problem

When there are multiple invisible indexes in a table, ALTER INDEX ... VISIBLE index recommendations may suggest making the wrong index visible.

To Reproduce

On master or 23.1, run cockroach demo, and then run the following:

CREATE TABLE t_notvisible (
  k INT PRIMARY KEY,
  v INT,
  i INT,
  j INT,
  INDEX idx_i_visible(i) VISIBLE,
  INDEX idx_v_invisible(v) NOT VISIBLE,
  INDEX idx_j_invisible(j) NOT VISIBLE
);

EXPLAIN SELECT j FROM t_notvisible WHERE j > 1;

This produces:

                                      info
---------------------------------------------------------------------------------
  distribution: local
  vectorized: true

  • filter
  │ filter: j > 1
  │
  └── • scan
        missing stats
        table: t_notvisible@t_notvisible_pkey
        spans: FULL SCAN

  index recommendations: 1
  1. type: index alteration
     SQL command: ALTER INDEX movr.public.t_notvisible@idx_v_invisible VISIBLE;
(14 rows)

Notice that the index recommendation is to make idx_v_invisible, which is not useful for the given query.

Expected behavior

We should recommend to make idx_j_invisible visible instead.

Jira issue: CRDB-30491