planner: large estimation error for queries with large IN list in some cases
- Dominant language
- Go
- Stars
- 40.5k
- Forks
- 6.2k
- PR merge metrics
- PR metrics pending
Description
## Enhancement
Use the SQLs below to prepare data:
```
create table t (a int, b int, key ab(a, b));
set @@cte_max_recursion_depth=1000;
insert into t select * from (
with recursive cte as (
select 1 as a, 1 as b
union all
select a + 1 as a, b + 1 as b from cte where a < 1000
)
select * from cte
) tt;
analyze table t;
```
Run the below SQL:
```
explain analyze select 1 from t use index(ab) where a = 1 and b in (0,1,2,3,4, ...,499)
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
| Projection_4 | 500.00 | 1 | root | | time:865.1µs, open:176.6µs, close:18.7µs, loops:2, RU:0.48, Concurrency:OFF | 1->Column#4 | 760 Bytes | N/A |
| └─IndexReader_7 | 500.00 | 1 | root | | time:846.5µs, open:171.1µs, close:17.2µs, loops:2, cop_task: {num: 1, max: 573.4µs, proc_keys: 0, copr_cache_hit_ratio: 0.00, build_task_duration: 24.1µs, max_distsql_concurrency: 1}, fetch_resp_duration: 621.4µs, rpc_info:{Cop:{num_rpc:1, total_time:539.1µs}} | index:IndexRangeScan_6 | 235 Bytes | N/A |
| └─IndexRangeScan_6 | 500.00 | 1 | cop[tikv] | table:t, index:ab(a, b) | tikv_task:{time:463.6µs, loops:0} | range:[1 0,1 0], [1 1,1 1], [1 2,1 2], [1 3,1 3], [1 4,1 4], ..., [1 499,1 499], keep order:false | N/A | N/A |
```
The estimation error above is huge `est(500) vs act(1)`.
The reason is that when calculating the estimation of `a=1 and b in (0, 1, ... 499)`, we'll expand it into 500 ranges like `[1 0, 1 0]`, `[1 1, 1 1]`, ... `[1 499, 1 499]`, and we calculate estimation for each range individually and accumulate their results together as the final result.
For each range, its estimation is pretty accurate `est(1) vs act(1 or 0)`, but after accumulating, the estimation error is magnified, so the final estimation error is 500, `est(500) vs act(1)`.
Contributor guide
Assessment
This issue has not been assessed yet.