pingcap / pingcap/tidb

planner: consider seek operation cost on the optimizer cost model

Open
#63,487 11 comments 0 reactions 2 assignees Claimed by @terry1purcell View on GitHub
epic/cost-model sig/planner type/enhancement
Dominant language
Go
Stars
40.5k
Forks
6.2k
PR merge metrics
PR metrics pending

Description

## Enhancement
See the case below:
```
create table t (a int, b int, c int, key abc(a, b, c), key cba(c, b, a));

set @@cte_max_recursion_depth=1000000;
insert into t select * from (
with recursive cte as (
select 1 as a, 1 as b, 1 as c
union all
select a + 1 as a, b + 1 as b, c + 1 as c from cte where a < 100000
)
select * from cte
) tt;
analyze table t;

explain analyze format='verbose' select 1 from t use index(abc) where a=1 and b in (1, 3, ...99999);
| IndexReader_7 | 50321.13 | 1400228.93 | 1 | root | | time:140.8ms, open:9.64ms, close:3.46µs, loops:2, RU:79.86, cop_task: {num: 2, max: 131.4ms, min: 130.5ms, avg: 130.9ms, p95: 131.4ms, max_proc_keys: 1, p95_proc_keys: 1, tot_proc: 236.7ms, tot_wait: 118.3µs, copr_cache_hit_ratio: 0.00, build_task_duration: 916.5µs, max_distsql_concurrency: 2}, fetch_resp_duration: 130.8ms, rpc_info:{Cop:{num_rpc:2, total_time:261.4ms}} | index:IndexRangeScan_6 | 98 KB | N/A |
| └─IndexRangeScan_6 | 50321.13 | 11438393.79 | 1 | cop[tikv] | table:t, index:abc(a, b, c) | tikv_task:{proc max:119ms, min:118ms, avg: 118.5ms, p80:119ms, p95:119ms, iters:2, tasks:2}, scan_detail: {total_process_keys: 1, total_process_keys_size: 64, total_keys: 50001, get_snapshot_time: 89.5µs, rocksdb: {key_skipped_count: 1, block: {cache_hit_count: 150000}}}, time_detail: {total_process_time: 236.7ms, total_wait_time: 118.3µs, total_kv_read_wall_time: 237ms, tikv_grpc_process_time: 5.35ms, tikv_grpc_wait_time: 25.6µs, tikv_wall_time: 245.4ms} |
range:[1 1,1 1], [1 3,1 3], [1 5,1 5], [1 7,1 7], [1 9,1 9], [1 11,1 11], [1 13,1 13], [1 15,1 15], [1 17,1 17], [1 19,1 19], [1 21,1 21], [1 23,1 23], [1 25,1 25], [1 27,1 27], [1 29,1 29], [1 31,1 31], [1 33,1 33], [1 35,1 35], ...

explain analyze format='verbose' select 1 from t use index(cba) where a=1 and b in (1, 3, ...99999);
| IndexReader_8 | 50321.13 | 2818389.17 | 1 | root | | time:14ms, open:2.45ms, close:6.67µs, loops:2, RU:0.48, cop_task: {num: 1, max: 10.7ms, proc_keys: 0, tot_proc: 709ns, tot_wait: 33.7µs, copr_cache_hit_ratio: 1.00, build_task_duration: 8.88µs, max_distsql_concurrency: 1}, fetch_resp_duration: 11.5ms, rpc_info:{Cop:{num_rpc:1, total_time:10.7ms}} | index:Selection_7 | 300 Bytes | N/A |
| └─Selection_7 | 50321.13 | 32710797.38 | 1 | cop[tikv] | | tikv_task:{time:38ms, loops:102}, scan_detail: {get_snapshot_time: 20.7µs, rocksdb: {block: {}}}, time_detail: {total_process_time: 709ns, total_wait_time: 33.7µs, tikv_grpc_process_time: 7.65ms, tikv_grpc_wait_time: 16.9µs, tikv_wall_time: 9.33ms} |
eq(test.t.a, 1), in(test.t.b, 1, 3, 5, 7, 9, 11, 13, 15, 17, 19, 21, 23, 25, 27, 29, 31, 33, 35, 37, 39, 41, 43, 45, 47, 49, 51, 53, 55, 57, 59, 61, 63, 65, 67, 69, 71, 73, 75, 77, 79, 81, 83, 85, 87, 89, 91, 93, 95, 97, 99, 101, 103, 105, 107, 109, 111, 113, 115, 117, 119, 121, ...
| └─IndexFullScan_6 | 100000.00 | 22730797.38 | 100000 | cop[tikv] | table:t, index:cba(c, b, a) | tikv_task:{time:34ms, loops:102}
```

For the first plan, we can use `a=1 and b in (...)` to construct ranges on the index `abc` and directly seek data we want, so we can avoid scanning the whole data.
For the second plan, we can't take advantage of the index `cba` to construct ranges since there is no predicate of `c`.
Since the first plan scans less data, then the optimizer naturally chooses the first plan to run by default.
But actually the first plan is 10 times worse than the second one, the problem here is that our optimizer omits the cost of these seek operations.

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.