sql: improve tuple index spans

[maddyblue]

Things missing from the current tuple index selection code:

• Prefixes (

  cockroach/sql/index_selection.go

  Line 573 in 69c632d

  // TODO(mjibson): support prefixes: (a,b,c) > (1,2,3) -> (a,b) >= (1,2)

  )
• Multi-directional indexes (

  cockroach/sql/index_selection.go

  Line 586 in 69c632d

  // restrict them to a single direction. See #6346.

  ,

  cockroach/sql/index_selection_test.go

  Line 531 in c7d6b66

  // TODO(mjibson): fix this, see #6346

  )

[maddyblue]

The prefix thing appears to be fixed, although I'm not sure when or how:

> create table t (a int, b int, c int, index (a, b, c));
root@:26257> explain select * from t where (a, b) > (1, 2);
+-------+------+---------------------+
| Level | Type |     Description     |
+-------+------+---------------------+
|     0 | scan | t@t_a_b_c_idx /1/3- |
+-------+------+---------------------+
(1 row)
root@:26257> explain select * from t where (a, b) < (1, 2);
+-------+------+-----------------------+
| Level | Type |      Description      |
+-------+------+-----------------------+
|     0 | scan | t@t_a_b_c_idx /#-/1/2 |
+-------+------+-----------------------+
(1 row)
root@:26257>  

[RaduBerinde]

I think the prefix thing was the other way around - having an index on (a, b) and having a condition with more columns (a, b, c) > (1, 2, 3). That condition implies (a, b) >= (1, 2) so we could use that to make a span with that index.

[RaduBerinde]

Regarding the second bullet point: if we have (a,b,c) > (1,2,3) and an index on (a ASC, b DESC, c ASC), we can't do better than just restrict to a >= 1. To get a constraints on all columns, the expression would need to be e.g. (a, -b, c) > (1, -2, 3).