pingcap / pingcap/tidb

planner: decrease the default value (64MB) of `tidb_opt_range_max_size`

Open
#66,203 0 comments 0 reactions 1 assignee Claimed by @henrybw View on GitHub
report/customer sig/planner type/enhancement type/performance
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:

Image

Here is the CPU profile:

Image

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:

Image

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

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.