Optimize limit over range partitioned table

[you06]

Enhancement

Run this case:

CREATE TABLE t(id int PRIMARY KEY, val int)
PARTITION BY RANGE (id)
(PARTITION p1 VALUES LESS THAN (100),
 PARTITION p2 VALUES LESS THAN (200),
 PARTITION p3 VALUES LESS THAN (300),
 PARTITION p4 VALUES LESS THAN (400),
 PARTITION p5 VALUES LESS THAN (500),
 PARTITION p6 VALUES LESS THAN (600),
 PARTITION p7 VALUES LESS THAN (700),
 PARTITION p8 VALUES LESS THAN (800),
 PARTITION p9 VALUES LESS THAN (900),
 PARTITION p10 VALUES LESS THAN (1000));

INSERT INTO t VALUES(50, 50), (150, 150), (250, 250);
ANALYZE TABLE t;

EXPLAIN ANALYZE SELECT * FROM t ORDER BY id ASC LIMIT 1;

+----------------------------+---------+---------+-----------+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------+-----------+------+
| id                         | estRows | actRows | task      | access object | execution info                                                                                                                                                                                                                                              | operator info                | memory    | disk |
+----------------------------+---------+---------+-----------+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------+-----------+------+
| TopN_7                     | 1.00    | 1       | root      |               | time:5.73ms, loops:2                                                                                                                                                                                                                                        | test.t.id, offset:0, count:1 | 280 Bytes | N/A  |
| └─TableReader_14           | 1.00    | 3       | root      | partition:all | time:5.7ms, loops:3, cop_task: {num: 10, max: 757.6µs, min: 384.5µs, avg: 542.6µs, p95: 757.6µs, max_proc_keys: 1, p95_proc_keys: 1, rpc_num: 10, rpc_time: 5.16ms, copr_cache_hit_ratio: 0.00, build_task_duration: 91µs, max_distsql_concurrency: 1}      | data:Limit_13                | 305 Bytes | N/A  |
|   └─Limit_13               | 1.00    | 3       | cop[tikv] |               | tikv_task:{proc max:0s, min:0s, avg: 0s, p80:0s, p95:0s, iters:10, tasks:10}, scan_detail: {total_process_keys: 3, total_process_keys_size: 113, total_keys: 13, get_snapshot_time: 264.3µs, rocksdb: {key_skipped_count: 3, block: {}}}                    | offset:0, count:1            | N/A       | N/A  |
|     └─TableFullScan_12     | 1.00    | 3       | cop[tikv] | table:t       | tikv_task:{proc max:0s, min:0s, avg: 0s, p80:0s, p95:0s, iters:10, tasks:10}                                                                                                                                                                                | keep order:true              | N/A       | N/A  |
+----------------------------+---------+---------+-----------+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------+-----------+------+
4 rows in set (0.007 sec)

Notice the table table scan stats cop_task: {num: 10, max: 757.6µs, min: 384.5µs, avg: 542.6µs, ..., max_distsql_concurrency: 1} , there are 10 tasks over 10 partitions, but the distsql concurrency is set to 1 here.

There are 2 possible optimization:

• It's ok to use limit over range partitioned table because range partitioned is ordered.
• DO NOT reduce concurrency for partitioned table for such case.

[tiancaiamao]

B.T.W, this is the plan when @@tidb_partition_prune_mode = 'dynamic', which is the default setting now.
How about @@tidb_partition_prune_mode = 'static'? At least a change should not break that setting.
@you06

[tiancaiamao]

See also #42024, that one try to make the data reader on partition table support ordering property, which is more general and more complex too.

[Defined2014]

After #42024 and #41615, I think we could close this issue.