Enhance access path selection

[alivxxx]

Feature Request

Is your feature request related to a problem? Please describe:

Suppose there is a table:
create table t(a int, b int, c int, index idx1(b, a), index idx2(a));;
For queries like select * from t where a = x and b = xx, the optimizer may choose idx2, but it is a very risky path compares to idx1.

Describe the feature you'd like:

If there are multi paths available, and the access conditions of the path a could strictly covers b, then we should never choose b as access condition.

[eurekaka]

Seems it already works now?

mysql> create table t(a int, b int, c int, index idx1(b, a), index idx2(a));
Query OK, 0 rows affected (0.01 sec)

mysql> explain select * from t where a = 1 and b = 1;
+-------------------+-------+------+----------------------------------------------------------------------+
| id                | count | task | operator info                                                        |
+-------------------+-------+------+----------------------------------------------------------------------+
| IndexLookUp_10    | 0.10  | root |                                                                      |
| ├─IndexScan_8     | 0.10  | cop  | table:t, index:b, a, range:[1 1,1 1], keep order:false, stats:pseudo |
| └─TableScan_9     | 0.10  | cop  | table:t, keep order:false, stats:pseudo                              |
+-------------------+-------+------+----------------------------------------------------------------------+
3 rows in set (0.00 sec)

You mean the stats may result in a wrong choice?

[alivxxx]

@eurekaka Yes, the stats may result in a wrong choice.

[shenli]

I guess there are many heuristic rules in other database's operators. Could we borrow something?

[alivxxx]

Yes, there are some other interesting rules like skyline pruning, we could learn from them.