pingcap / pingcap/tidb

Using Limit to replace Topn when estrows is small.

Open
#46,925 4 comments 0 reactions 2 assignees Claimed by @qw4990 View on GitHub
affects-7.1 affects-8.1 epic/cardinality-estimation epic/cost-model found/gs sig/planner type/enhancement
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

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.