sql: update index rec to consider not visible indexes #85477
Description
Describe the problem
After adding not visible indexes feature, we should update index recommendation
accordingly. The index recommendation engine now treats all indexes as
they are visible.
For example, the query below has no recommendation because it treats idx as a
visible index. This is wrong because the index is actually not visible to the
optimizer which causes a full table scan.
demo@127.0.0.1:26257/movr> CREATE TABLE t (p INT NOT NULL, c INT, INDEX idx (c) NOT VISIBLE);
CREATE TABLE
Time: 8ms total (execution 8ms / network 0ms)
demo@127.0.0.1:26257/movr> EXPLAIN SELECT c FROM t WHERE c > 0;
info
--------------------------
distribution: local
vectorized: true
• filter
│ filter: c > 0
│
└── • scan
missing stats
table: t@t_pkey
spans: FULL SCAN
(10 rows)
Time: 7ms total (execution 6ms / network 0ms)
Marking idx visible would give a more efficient query plan.
demo@127.0.0.1:26257/movr> ALTER INDEX idx VISIBLE;
ALTER INDEX
Time: 32ms total (execution 31ms / network 0ms)
demo@127.0.0.1:26257/movr> EXPLAIN SELECT c FROM t WHERE c > 0;
info
-----------------------
distribution: local
vectorized: true
• scan
missing stats
table: t@idx
spans: [/1 - ]
(7 rows)
Time: 1ms total (execution 1ms / network 0ms)
Epic: CRDB-11155
Jira issue: CRDB-18264