planner: decrease the default value (64MB) of `tidb_opt_range_max_size`
- Dominant language
- Go
- Stars
- 40.5k
- Forks
- 6.2k
- PR merge metrics
- PR metrics pending
Description
## Enhancement
Large access ranges caused by large `in-list` are both harmful to the optimization and execution.
See the example:
```
create table t (
id int,
item_id varchar(100),
a int,
b int,
c int,
primary key(id),
key item(item_id),
key a(a, item_id),
key b(b, item_id),
key c(c, item_id)
);
EXPLAIN SELECT 1 FROM t WHERE
item_id NOT IN ('0','1','2','3', ... '30000')
AND id > 0;
```
The second explain-stmt spent more than `40s` to finish:
Here is the CPU profile:
The optimizer needs to spend massive resources to deal with this long `in-list`.
And these massive access ranges are also harmful to execution, since they'll cause too many seek operations on the storage layer, please see this issue: https://github.com/pingcap/tidb/issues/62499
`tidb_opt_range_max_size` could be a workaround for this, for example, after setting it to `5MB`, both optimization time and execution time of this SQL become better:
In this test, `10MB` can support around 30000 ranges, then the default `64MB` seems support around 200000 ranges, which seems too large. For most users, the default value `64MB` seems too large, we should decrease it.
Contributor guide
Assessment
This issue has not been assessed yet.