pingcap / pingcap/tidb

Full table scan is incorrectly selected when the indexscan rowcount is large

Open
#43,199 1 comment 0 reactions 0 assignees View on GitHub
affects-8.1 report/customer type/enhancement
Dominant language
Go
Stars
40.5k
Forks
6.2k
PR merge metrics
PR metrics pending

Description

## Enhancement
Full table scan is incorrectly selected when the indexscan rowcount is large(doubleRead(tasks(xxx)*tidb_request_factor(6e+06)).
We need to calibrate using `correlation`

### 1. Minimal reproduce step (Required)

### 2. What did you expect to see? (Required)
```
mysql> explain analyze format='cost_trace' SELECT /*+ USE_INDEX(test, date_idx) */ SUM(col1) FROM `test` WHERE date='2023-03-01';
+----------------------------------+------------+----------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+-----------+---------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------+-----------+------+
| id | estRows | estCost | costFormula | actRows | task | access object | execution info | operator info | memory | disk |
+----------------------------------+------------+----------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+-----------+---------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------+-----------+------+
| HashAgg_13 | 1.00 | 19424309608.89 | (cpu(10*3*tidb_cpu_factor(49.9))) + ((((net(9.942217571318075e+06*rowsize(16.25)*tidb_kv_net_factor(3.96))) + (scan(9.942217571318075e+06*logrowsize(32)*tikv_scan_factor(40.7))))/15.00) + (((((net(9.942217571318075e+06*rowsize(8.125)*tidb_kv_net_factor(3.96))) + ((cpu(10*3*tikv_cpu_factor(49.9))) + (scan(9.942217571318075e+06*logrowsize(755)*tikv_scan_factor(40.7))) + (((agg(9.942217571318075e+06*aggs(1)*tikv_cpu_factor(49.9))) + (group(9.942217571318075e+06*cols(0)*tikv_cpu_factor(49.9))) + ((hashkey(1*0*tikv_cpu_factor(49.9))) + (hashmem(1*40*tikv_mem_factor(0.2))) + (hashbuild(1*tikv_cpu_factor(49.9)))) + ((hashkey(9.942217571318075e+06*0*tikv_cpu_factor(49.9))) + (hashprobe(9.942217571318075e+06*tikv_cpu_factor(49.9)))))/5.00)))/15.00) + ((double-read-cpu(9.942217571318075e+06*tidb_cpu_factor(49.9))) + (doubleRead(tasks(15907.54811410892)*tidb_request_factor(6e+06)))))/5.00)) + (((agg(1*aggs(1)*tidb_cpu_factor(49.9))) + (group(1*cols(0)*tidb_cpu_factor(49.9))) + ((hashkey(1*0*tidb_cpu_factor(49.9))) + (hashmem(1*40*tidb_mem_factor(0.2))) + (hashbuild(1*tidb_cpu_factor(49.9)))) + ((hashkey(1*0*tidb_cpu_factor(49.9))) + (hashprobe(1*tidb_cpu_factor(49.9)))))/5.00) | 1 | root | | time:1.25ms, loops:2, partial_worker:{wall_time:1.186324ms, concurrency:5, task_num:0, tot_wait:5.666668ms, tot_exec:0s, tot_time:5.675052ms, max:1.143714ms, p95:1.143714ms}, final_worker:{wall_time:1.215082ms, concurrency:5, task_num:0, tot_wait:5.899789ms, tot_exec:11.055µs, tot_time:5.916761ms, max:1.195314ms, p95:1.195314ms} | funcs:sum(Column#34)->Column#32 | 20.2 KB | N/A |
| └─IndexLookUp_14 | 1.00 | 19424308080.35 | (((net(9.942217571318075e+06*rowsize(16.25)*tidb_kv_net_factor(3.96))) + (scan(9.942217571318075e+06*logrowsize(32)*tikv_scan_factor(40.7))))/15.00) + (((((net(9.942217571318075e+06*rowsize(8.125)*tidb_kv_net_factor(3.96))) + ((cpu(10*3*tikv_cpu_factor(49.9))) + (scan(9.942217571318075e+06*logrowsize(755)*tikv_scan_factor(40.7))) + (((agg(9.942217571318075e+06*aggs(1)*tikv_cpu_factor(49.9))) + (group(9.942217571318075e+06*cols(0)*tikv_cpu_factor(49.9))) + ((hashkey(1*0*tikv_cpu_factor(49.9))) + (hashmem(1*40*tikv_mem_factor(0.2))) + (hashbuild(1*tikv_cpu_factor(49.9)))) + ((hashkey(9.942217571318075e+06*0*tikv_cpu_factor(49.9))) + (hashprobe(9.942217571318075e+06*tikv_cpu_factor(49.9)))))/5.00)))/15.00) + ((double-read-cpu(9.942217571318075e+06*tidb_cpu_factor(49.9))) + (doubleRead(tasks(15907.54811410892)*tidb_request_factor(6e+06)))))/5.00) | 0 | root | | time:1.12ms, loops:1 | | 270 Bytes | N/A |
| ├─IndexRangeScan_11(Build) | 9942217.57 | 2023241275.76 | scan(9.942217571318075e+06*logrowsize(32)*tikv_scan_factor(40.7)) | 0 | cop[tikv] | table:test, index:date_idx(date) | time:887.7µs, loops:1, cop_task: {num: 1, max: 782.8µs, proc_keys: 0, rpc_num: 1, rpc_time: 744.6µs, copr_cache_hit_ratio: 0.00, build_task_duration: 29.3µs, max_distsql_concurrency: 1}, tikv_task:{time:0s, loops:1}, scan_detail: {total_keys: 1, get_snapshot_time: 42.3µs, rocksdb: {block: {}}} | range:[2023-03-01,2023-03-01], keep order:false | N/A | N/A |
| └─HashAgg_7(Probe) | 1.00 | 4067020171.35 | (cpu(10*3*tikv_cpu_factor(49.9))) + (scan(9.942217571318075e+06*logrowsize(755)*tikv_scan_factor(40.7))) + (((agg(9.942217571318075e+06*aggs(1)*tikv_cpu_factor(49.9))) + (group(9.942217571318075e+06*cols(0)*tikv_cpu_factor(49.9))) + ((hashkey(1*0*tikv_cpu_factor(49.9))) + (hashmem(1*40*tikv_mem_factor(0.2))) + (hashbuild(1*tikv_cpu_factor(49.9)))) + ((hashkey(9.942217571318075e+06*0*tikv_cpu_factor(49.9))) + (hashprobe(9.942217571318075e+06*tikv_cpu_factor(49.9)))))/5.00) | 0 | cop[tikv] | | | funcs:sum(demo.test.col1)->Column#34 | N/A | N/A |
| └─TableRowIDScan_12 | 9942217.57 | 3868572000.04 | scan(9.942217571318075e+06*logrowsize(755)*tikv_scan_factor(40.7)) | 0 | cop[tikv] | table:test | | keep order:false | N/A | N/A |
+----------------------------------+------------+----------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+-----------+---------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------+-----------+------+
5 rows in set (0.01 sec)

```
### 3. What did you see instead (Required)
```
mysql> explain analyze format='cost_trace' SELECT SUM(col1) FROM `test` WHERE date='2023-03-01';
+------------------------------+--------------+----------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+-----------+-------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------+-----------+------+
| id | estRows | estCost | costFormula | actRows | task | access object | execution info | operator info | memory | disk |
+------------------------------+--------------+----------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+-----------+-------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------+-----------+------+
| HashAgg_13 | 1.00 | 4403286880.71 | (cpu(10*3*tidb_cpu_factor(49.9))) + ((((cpu(10*3*tikv_cpu_factor(49.9))) + ((cpu(1.5e+08*filters(1)*tikv_cpu_factor(49.9))) + (scan(1.5e+08*logrowsize(755)*tikv_scan_factor(40.7)))) + (((agg(9.942217571318075e+06*aggs(1)*tikv_cpu_factor(49.9))) + (group(9.942217571318075e+06*cols(0)*tikv_cpu_factor(49.9))) + ((hashkey(1*0*tikv_cpu_factor(49.9))) + (hashmem(1*40*tikv_mem_factor(0.2))) + (hashbuild(1*tikv_cpu_factor(49.9)))) + ((hashkey(9.942217571318075e+06*0*tikv_cpu_factor(49.9))) + (hashprobe(9.942217571318075e+06*tikv_cpu_factor(49.9)))))/5.00)) + (net(1*rowsize(40)*tidb_kv_net_factor(3.96))))/15.00) + (((agg(1*aggs(1)*tidb_cpu_factor(49.9))) + (group(1*cols(0)*tidb_cpu_factor(49.9))) + ((hashkey(1*0*tidb_cpu_factor(49.9))) + (hashmem(1*40*tidb_mem_factor(0.2))) + (hashbuild(1*tidb_cpu_factor(49.9)))) + ((hashkey(1*0*tidb_cpu_factor(49.9))) + (hashprobe(1*tidb_cpu_factor(49.9)))))/5.00) | 1 | root | | time:4.54ms, loops:2, partial_worker:{wall_time:4.451996ms, concurrency:5, task_num:0, tot_wait:21.973675ms, tot_exec:0s, tot_time:21.982932ms, max:4.422931ms, p95:4.422931ms}, final_worker:{wall_time:4.520625ms, concurrency:5, task_num:0, tot_wait:22.279891ms, tot_exec:21.621µs, tot_time:22.308845ms, max:4.484706ms, p95:4.484706ms} | funcs:sum(Column#33)->Column#32 | 20.2 KB | N/A |
| └─TableReader_14 | 1.00 | 4403285352.17 | (((cpu(10*3*tikv_cpu_factor(49.9))) + ((cpu(1.5e+08*filters(1)*tikv_cpu_factor(49.9))) + (scan(1.5e+08*logrowsize(755)*tikv_scan_factor(40.7)))) + (((agg(9.942217571318075e+06*aggs(1)*tikv_cpu_factor(49.9))) + (group(9.942217571318075e+06*cols(0)*tikv_cpu_factor(49.9))) + ((hashkey(1*0*tikv_cpu_factor(49.9))) + (hashmem(1*40*tikv_mem_factor(0.2))) + (hashbuild(1*tikv_cpu_factor(49.9)))) + ((hashkey(9.942217571318075e+06*0*tikv_cpu_factor(49.9))) + (hashprobe(9.942217571318075e+06*tikv_cpu_factor(49.9)))))/5.00)) + (net(1*rowsize(40)*tidb_kv_net_factor(3.96))))/15.00 | 0 | root | | time:4.39ms, loops:1, cop_task: {num: 1, max: 1.28ms, proc_keys: 0, rpc_num: 2, rpc_time: 1.92ms, copr_cache_hit_ratio: 0.00, build_task_duration: 11.4µs, max_distsql_concurrency: 1}, backoff{regionMiss: 2ms} | data:HashAgg_6 | 269 Bytes | N/A |
| └─HashAgg_6 | 1.00 | 66049280124.17 | (cpu(10*3*tikv_cpu_factor(49.9))) + ((cpu(1.5e+08*filters(1)*tikv_cpu_factor(49.9))) + (scan(1.5e+08*logrowsize(755)*tikv_scan_factor(40.7)))) + (((agg(9.942217571318075e+06*aggs(1)*tikv_cpu_factor(49.9))) + (group(9.942217571318075e+06*cols(0)*tikv_cpu_factor(49.9))) + ((hashkey(1*0*tikv_cpu_factor(49.9))) + (hashmem(1*40*tikv_mem_factor(0.2))) + (hashbuild(1*tikv_cpu_factor(49.9)))) + ((hashkey(9.942217571318075e+06*0*tikv_cpu_factor(49.9))) + (hashprobe(9.942217571318075e+06*tikv_cpu_factor(49.9)))))/5.00) | 0 | cop[tikv] | | tikv_task:{time:0s, loops:1}, scan_detail: {total_keys: 1, get_snapshot_time: 102.9µs, rocksdb: {block: {}}} | funcs:sum(demo.test.col1)->Column#33 | N/A | N/A |
| └─Selection_12 | 9942217.57 | 65850831952.87 | (cpu(1.5e+08*filters(1)*tikv_cpu_factor(49.9))) + (scan(1.5e+08*logrowsize(755)*tikv_scan_factor(40.7))) | 0 | cop[tikv] | | tikv_task:{time:0s, loops:1} | eq(demo.test.date, 2023-03-01 00:00:00.000000) | N/A | N/A |
| └─TableFullScan_11 | 150000000.00 | 58365831952.87 | scan(1.5e+08*logrowsize(755)*tikv_scan_factor(40.7)) | 0 | cop[tikv] | table:test | tikv_task:{time:0s, loops:1} | keep order:false | N/A | N/A |
+------------------------------+--------------+----------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+-----------+-------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------+-----------+------+
5 rows in set (0.02 sec)

```
### 4. What is your TiDB version? (Required)
v6.6

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.