Using Limit to replace Topn when estrows is small.
- Dominant language
- Go
- Stars
- 40.5k
- Forks
- 6.2k
- PR merge metrics
- PR metrics pending
Description
## Bug Report
We encounter bad performance issue for the sql plan choiced the Topn operator.Statistics may not timely, estrows usually not accurate.When actrows is small the sql execution times don't make a big difference between limit and topn,but when actrows is larger,keep order:true is more suitable than keep order:false.
So i think we should use Limit to replace TopN when estrows is small.
### 1. Minimal reproduce step (Required)
### 2. What did you expect to see? (Required)
```
id task estRows operator info actRows execution info memory disk
Projection_8 root 30 test.test.source, test.test.source_id, test.test.first_name, test.test.last_name 0 time:13.3ms, loops:1, Concurrency:OFF 27.0 KB N/A
└─Projection_28 root 30 test.test.user_id, test.test.source, test.test.source_id, test.test.token, test.test.first_name, test.test.last_name 0 time:13.3ms, loops:1, Concurrency:OFF 35.7 KB N/A
└─IndexLookUp_27 root 30 limit embedded(offset:0, count:30) 0 time:13.3ms, loops:1 244 Bytes N/A
├─Limit_26(Build) cop[tikv] 30 offset:0, count:30 0 time:12ms, loops:1, cop_task: {num: 1, max: 6.52ms, proc_keys: 0, tot_proc: 38.2µs, tot_wait: 35.8µs, rpc_num: 1, rpc_time: 6.5ms, copr_cache_hit_ratio: 0.00, build_task_duration: 31.5µs, max_distsql_concurrency: 1}, tikv_task:{time:0s, loops:1}, scan_detail: {total_keys: 1, get_snapshot_time: 7.85µs, rocksdb: {block: {cache_hit_count: 6}}} N/A N/A
│ └─IndexRangeScan_24 cop[tikv] 30.00 table:test, index:PRIMARY(user_id, source, token, source_id), range:[1234567891742 2,1234567891742 2], keep order:true 0 tikv_task:{time:0s, loops:1} N/A N/A
└─TableRowIDScan_25(Probe) cop[tikv] 30 table:test, keep order:false 0 N/A N/A
```
### 3. What did you see instead (Required)
```
id task estRows operator info actRows execution info memory disk
Projection_8 root 1.13 test.test.source, test.test.source_id, test.test.first_name, test.test.last_name 60 time:419.1ms, loops:2, Concurrency:OFF 22.3 KB N/A
└─TopN_10 root 1.13 test.test.token, test.test.source_id, offset:0, count:60 60 time:419.1ms, loops:2 23.7 KB N/A
└─IndexLookUp_23 root 1.13 120 time:419ms, loops:3, index_task: {total_time: 415ms, fetch_handle: 415ms, build: 1.98µs, wait: 13.6µs}, table_task: {total_time: 7.98ms, num: 2, concurrency: 5}, next: {wait_index: 411ms, wait_table_lookup_build: 174.8µs, wait_table_lookup_resp: 7.82ms} 48.2 KB N/A
├─TopN_22(Build) cop[tikv] 1.13 test.test.token, test.test.source_id, offset:0, count:60 120 time:415ms, loops:4, cop_task: {num: 2, max: 414.9ms, min: 289.8ms, avg: 352.4ms, p95: 414.9ms, max_proc_keys: 475500, p95_proc_keys: 475500, tot_proc: 699.1ms, tot_wait: 46.7µs, rpc_num: 2, rpc_time: 704.7ms, copr_cache_hit_ratio: 0.00, build_task_duration: 7.89µs, max_distsql_concurrency: 2}, tikv_task:{proc max:416ms, min:288ms, avg: 352ms, p80:416ms, p95:416ms, iters:782, tasks:2}, scan_detail: {total_process_keys: 799148, total_process_keys_size: 79856116, total_keys: 799150, get_snapshot_time: 20.9µs, rocksdb: {key_skipped_count: 799148, block: {cache_hit_count: 1965}}} N/A N/A
│ └─IndexRangeScan_18 cop[tikv] 1.13 table:test, index:PRIMARY(user_id, source, token, source_id), range:[1234567891742 1,1234567891742 1], keep order:false 799148 tikv_task:{proc max:284ms, min:232ms, avg: 258ms, p80:284ms, p95:284ms, iters:782, tasks:2} N/A N/A
└─TableRowIDScan_19(Probe) cop[tikv] 1.13 table:test, keep order:false 120 time:7.77ms, loops:4, cop_task: {num: 7, max: 1.67ms, min: 0s, avg: 1.07ms, p95: 1.67ms, max_proc_keys: 29, p95_proc_keys: 29, tot_proc: 1.79ms, tot_wait: 384.6µs, rpc_num: 6, rpc_time: 7.43ms, copr_cache_hit_ratio: 0.00, build_task_duration: 52.8µs, max_distsql_concurrency: 1, max_extra_concurrency: 1, store_batch_num: 1}, tikv_task:{proc max:0s, min:0s, avg: 0s, p80:0s, p95:0s, iters:7, tasks:7}, scan_detail: {total_process_keys: 120, total_process_keys_size: 11524, total_keys: 134, get_snapshot_time: 87.4µs, rocksdb: {key_skipped_count: 35, block: {cache_hit_count: 580}}} N/A N/A
```
### 4. What is your TiDB version? (Required)
v7.1.0
Contributor guide
Assessment
This issue has not been assessed yet.