Rewrite exprs like `a = 10 or a = 20 or a = 30 ...` to `a in (10,20,30)` for better expr evaluation performance in the execution engine
- Dominant language
- Go
- Stars
- 40.5k
- Forks
- 6.2k
- PR merge metrics
- PR metrics pending
Description
## Enhancement
Currently, if conditions are not converted to index ranges, they will be evaluated in the `Selection` operator unchanged. However, for expressions like `a = 10 or a = 20 or a = 30 ...`, it's better to convert it to `a in (10,20,30)`, especially when there are lots of values.
It's because:
1. The structure of the first expression is more complicated. Each `eq` expression is an independent expression, and they are connected by `or` expressions, which can connect two expressions at once. The `in` expression has a more compact structure.
2. We have implemented more optimizations for the `in` expression, like https://github.com/tikv/tikv/pull/6000. According to members of the execution engine team, the performance of `in` is better than `eq` connected by `or` in tidb, tikv, and tiflash.
In one of the cases we met, there are ~1200 varchar values. The total row count is ~200k and the row count after the filter is ~50k. The query execution time with `or` + `eq` is ~5s while the equivalent query with `in` is ~1s.
#### Example:
```sql
create table t(a int, b int, c int);
explain select * from t where a = 1 or a = 2 or a = 3 or a = 4;
explain select * from t where a in (1,2,3,4);
```
Current execution plan:
```
> explain select * from t where a = 1 or a = 2 or a = 3 or a = 4;
+-------------------------+----------+-----------+---------------+--------------------------------------------------------------------------------+
| id | estRows | task | access object | operator info |
+-------------------------+----------+-----------+---------------+--------------------------------------------------------------------------------+
| TableReader_7 | 40.00 | root | | data:Selection_6 |
| └─Selection_6 | 40.00 | cop[tikv] | | or(or(eq(test.t.a, 1), eq(test.t.a, 2)), or(eq(test.t.a, 3), eq(test.t.a, 4))) |
| └─TableFullScan_5 | 10000.00 | cop[tikv] | table:t | keep order:false, stats:pseudo |
+-------------------------+----------+-----------+---------------+--------------------------------------------------------------------------------+
```
Better execution plan:
```
> explain select * from t where a in (1,2,3,4);
+-------------------------+----------+-----------+---------------+--------------------------------+
| id | estRows | task | access object | operator info |
+-------------------------+----------+-----------+---------------+--------------------------------+
| TableReader_7 | 40.00 | root | | data:Selection_6 |
| └─Selection_6 | 40.00 | cop[tikv] | | in(test.t.a, 1, 2, 3, 4) |
| └─TableFullScan_5 | 10000.00 | cop[tikv] | table:t | keep order:false, stats:pseudo |
+-------------------------+----------+-----------+---------------+--------------------------------+
```
Contributor guide
Assessment
This issue has not been assessed yet.