opt: include != and IS DISTINCT FROM and IS NOT NULL columns in index recommendations

[michae2]

Index recommendations currently do not consider hypothetical indexes on columns used with the following operators:

• !=
• IS DISTINCT FROM
• IS NOT NULL

I think columns used with these operators could go into the R (range) set of columns during hypothetical index creation, as we still generate constrained index scans for them when an index exists.

Jira issue: CRDB-27339

[michae2]

Here are some examples:

CREATE TABLE a (a INT);
INSERT INTO a SELECT IF(i % 10 = 0, NULL, i) FROM generate_series(0, 99) s(i);
ANALYZE a;

-- these all recommend CREATE INDEX ON a (a)
EXPLAIN SELECT * FROM a WHERE a = 3;
EXPLAIN SELECT * FROM a WHERE a > 3;
EXPLAIN SELECT * FROM a WHERE a IS NOT DISTINCT FROM 3;
EXPLAIN SELECT * FROM a WHERE a IS NULL;
EXPLAIN SELECT * FROM a WHERE a IS NOT DISTINCT FROM NULL;

-- these do not, even though they do use the index when created
EXPLAIN SELECT * FROM a WHERE a IS DISTINCT FROM 3;
EXPLAIN SELECT * FROM a WHERE a != 3;
EXPLAIN SELECT * FROM a WHERE a IS NOT NULL;
EXPLAIN SELECT * FROM a WHERE a IS DISTINCT FROM NULL;