Skyline pruning prunes maybe-good prefix index #27313
Description
Bug Report
Please answer these questions before submitting your issue. Thanks!
1. Minimal reproduce step (Required)
use test;
drop table if exists t;
create table t(a varchar(100), b int, c int, index idx1(a(2), b), index idx2(a));
explain format = 'verbose' select * from t where a = 'abcdefghijk' and b > 4;
2. What did you expect to see? (Required)
idx2 remains after skyline pruning.
3. What did you see instead (Required)
idx2 is pruned since skyline pruning thinks it is worse than idx1, which is not true considering the index column prefix length.
mysql> explain format = 'verbose' select * from t where a = 'abcdefghijk' and b > 4;
+-------------------------------+---------+---------+-----------+---------------------------+----------------------------------------------------------+
| id | estRows | estCost | task | access object | operator info |
+-------------------------------+---------+---------+-----------+---------------------------+----------------------------------------------------------+
| IndexLookUp_11 | 33.33 | 678.84 | root | | |
| ├─IndexRangeScan_8(Build) | 33.33 | 2370.00 | cop[tikv] | table:t, index:idx1(a, b) | range:("ab" 4,"ab" +inf], keep order:false, stats:pseudo |
| └─Selection_10(Probe) | 33.33 | 0.00 | cop[tikv] | | eq(test.t.a, "abcdefghijk") |
| └─TableRowIDScan_9 | 33.33 | 2370.00 | cop[tikv] | table:t | keep order:false, stats:pseudo |
+-------------------------------+---------+---------+-----------+---------------------------+----------------------------------------------------------+
4 rows in set, 1 warning (0.00 sec)
mysql> show warnings;
+-------+------+---------------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+---------------------------------------------------------------------------------------+
| Note | 1105 | [t,idx1] remain after pruning paths for t given Prop{SortItems: [], TaskTp: rootTask} |
+-------+------+---------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
4. What is your TiDB version? (Required)
mysql> SELECT tidb_version();
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tidb_version() |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Release Version: v5.2.0-alpha-653-g4a3c290c0
Edition: Community
Git Commit Hash: 4a3c290c0b6f80d9d3eea430a59bfc2f9cb9c78f
Git Branch: master
UTC Build Time: 2021-08-17 11:10:12
GoVersion: go1.16.4
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)