optimizer support limit pushdown when there are multiple values in the in predicates

[dbsid]

Feature Request

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

no limit pushdown when there are multiple values in the in predicates

Describe the feature you'd like:

• for state in ('ACTIVE','APPROVED') there is only topn pushdown
• for state in ('APPROVED') there is expected limit pushdown

CREATE TABLE `t` (
 ( `key` varchar(32) NOT NULL,
  `state` varchar(256) DEFAULT NULL,
  PRIMARY KEY (`key`) /*T![clustered_index] CLUSTERED */
);

ALTER TABLE t ADD KEY `tidb_test_001` (`state`, `key`);

mysql> explain analyze format=verbose select `key` from `t` FORCE INDEX(tidb_test_001) where `state` in ('ACTIVE','APPROVED') order by `key` limit 750,10;
+-------------------------------+------------+--------------+---------+-----------+---------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------+----------+------+
| id                            | estRows    | estCost      | actRows | task      | access object                                                       | execution info                                                                                                                                                                                                                                                                                                                                               | operator info                                                        | memory   | disk |
+-------------------------------+------------+--------------+---------+-----------+---------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------+----------+------+
| Projection_7                  | 10.00      | 24805894.19  | 10      | root      |                                                                     | time:793.4ms, loops:2, Concurrency:OFF                                                                                                                                                                                                                                                                                                                       | testdb.t.key                            | 1.49 KB  | N/A  |
| └─TopN_8                      | 10.00      | 24805870.19  | 10      | root      |                                                                     | time:793.4ms, loops:2                                                                                                                                                                                                                                                                                                                                        | testdb.t.key, offset:750, count:10      | 75.5 KB  | N/A  |
|   └─IndexReader_17            | 760.00     | 24784050.16  | 19648   | root      |                                                                     | time:790.9ms, loops:21, cop_task: {num: 32, max: 783.4ms, min: 1.47ms, avg: 92.9ms, p95: 605.9ms, max_proc_keys: 530635, p95_proc_keys: 527153, tot_proc: 2.92s, rpc_num: 32, rpc_time: 2.97s, copr_cache: disabled, distsql_concurrency: 15}                                                                                                                | index:TopN_16                                                        | 107.6 KB | N/A  |
|     └─TopN_16                 | 760.00     | 371696480.93 | 19648   | cop[tikv] |                                                                     | tikv_task:{proc max:780ms, min:0s, avg: 91.6ms, p80:200ms, p95:604ms, iters:2189, tasks:32}, scan_detail: {total_process_keys: 2152660, total_process_keys_size: 346809407, total_keys: 2152693, get_snapshot_time: 7.27ms, rocksdb: {key_skipped_count: 2152660, block: {cache_hit_count: 1601, read_count: 2881, read_byte: 17.2 MB, read_time: 208.4ms}}} | testdb.t.key, offset:0, count:760       | N/A      | N/A  |
|       └─IndexRangeScan_15     | 2151587.00 | 309925349.42 | 2152660 | cop[tikv] | table:t, index:tidb_test_001(state, key) | tikv_task:{proc max:732ms, min:0s, avg: 84.6ms, p80:184ms, p95:536ms, iters:2189, tasks:32}                                                                                                                                                                                                                                                                  | range:["ACTIVE","ACTIVE"], ["APPROVED","APPROVED"], keep order:false | N/A      | N/A  |
+-------------------------------+------------+--------------+---------+-----------+---------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------+----------+------+
5 rows in set (1.03 sec)

mysql> explain analyze format=verbose select `key` from `t` FORCE INDEX(tidb_test_001) where `state` in ('APPROVED') order by `key` limit 750,10;
+-------------------------------+---------+-----------+---------+-----------+---------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------+---------+------+
| id                            | estRows | estCost   | actRows | task      | access object                                                       | execution info                                                                                                                                                                                                                                                       | operator info                                  | memory  | disk |
+-------------------------------+---------+-----------+---------+-----------+---------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------+---------+------+
| Projection_7                  | 10.00   | 11605.71  | 10      | root      |                                                                     | time:5.16ms, loops:2, Concurrency:OFF                                                                                                                                                                                                                                | testdb.t.key      | 1.49 KB | N/A  |
| └─Limit_12                    | 10.00   | 11581.71  | 10      | root      |                                                                     | time:5.16ms, loops:2                                                                                                                                                                                                                                                 | offset:750, count:10                           | N/A     | N/A  |
|   └─IndexReader_22            | 760.00  | 11581.71  | 760     | root      |                                                                     | time:5.15ms, loops:1, cop_task: {num: 3, max: 2.15ms, min: 1.29ms, avg: 1.69ms, p95: 2.15ms, max_proc_keys: 760, p95_proc_keys: 760, rpc_num: 3, rpc_time: 5.02ms, copr_cache: disabled, distsql_concurrency: 1}                                                     | index:Limit_21                                 | 68.8 KB | N/A  |
|     └─Limit_21                | 760.00  | 109474.20 | 1464    | cop[tikv] |                                                                     | tikv_task:{proc max:0s, min:0s, avg: 0s, p80:0s, p95:0s, iters:12, tasks:3}, scan_detail: {total_process_keys: 1464, total_process_keys_size: 248880, total_keys: 1467, get_snapshot_time: 1.01ms, rocksdb: {key_skipped_count: 1464, block: {cache_hit_count: 23}}} | offset:0, count:760                            | N/A     | N/A  |
|       └─IndexRangeScan_20     | 760.00  | 109474.20 | 1464    | cop[tikv] | table:t, index:tidb_test_001(state, key) | tikv_task:{proc max:0s, min:0s, avg: 0s, p80:0s, p95:0s, iters:12, tasks:3}                                                                                                                                                                                          | range:["APPROVED","APPROVED"], keep order:true | N/A     | N/A  |
+-------------------------------+---------+-----------+---------+-----------+---------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------+---------+------+
5 rows in set (0.23 sec)

Describe alternatives you've considered:

None

Teachability, Documentation, Adoption, Migration Strategy:

[seiya-annie]

/report customer

[seiya-annie]

/report customer