sql/opt: explore splitting disjunctions of same column in some cases

[michae2]

The SplitDisjunction optimizer rules do not currently split disjunctions of predicates on the same column. The reason is that these predicates can usually become multiple spans in the same constrained scan, so there's no need to explore each disjunction separately. For example:

CREATE TABLE abc (
  a INT NOT NULL,
  b INT NOT NULL,
  c INT NOT NULL,
  INDEX (a, b),
  INDEX (a, c)
);
INSERT INTO abc SELECT 5, i, i FROM generate_series(-10, 9999) AS s (i);
ANALYZE abc;
-- we split this disjunction because the columns are different
EXPLAIN SELECT a FROM abc WHERE a = 5 AND (b < 0 OR c >= 9990);
-- we do not split this disjunction because the columns are the same
-- instead we can use a single constrained scan with multiple spans
EXPLAIN SELECT a FROM abc WHERE a = 5 AND (b < 0 OR b >= 9990);

There is one case, however, where it would be worth exploring splitting disjunctions of the same column: if there are partial indexes with different predicates on that column:

ALTER INDEX abc_a_b_idx NOT VISIBLE;
ALTER INDEX abc_a_c_idx NOT VISIBLE;
CREATE INDEX ON abc (a) WHERE b < 0;
CREATE INDEX ON abc (a) WHERE b >= 9990;
-- we can't use a single constrained scan in this case
-- it would be worth exploring splitting the disjunction
EXPLAIN SELECT a FROM abc WHERE a = 5 AND (b < 0 OR b >= 9990);

Jira issue: CRDB-56303