Redundant expressions containing user parameters on irrelevant columns interfere with estimation on other columns in PREPARE/EXECUTE now
- Dominant language
- Go
- Stars
- 40.5k
- Forks
- 6.2k
- PR merge metrics
- PR metrics pending
Description
## Reproduce
```sql
create table t(a int, b int, c int, d int, z int);
insert into t value (2,1,1,1,10), (1,2,1,1,10), (1,1,2,1,10), (1,1,1,2,10);
insert into t value (1,1,1,1,10), (1,1,1,1,10), (1,1,1,1,10), (1,1,1,1,10), (1,1,1,1,10), (1,1,1,1,10);
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
select sleep(1);
analyze table t all columns;
-- Not use `PREPARE/EXECUTE`:
explain select * from t where a = 2 and b = 2 and c = 2 and d = 2 and z = 10 and z in (10,3,4);
-- Use `PREPARE/EXECUTE`:
prepare stmt from "select * from t where a = 2 and b = 2 and c = 2 and d = 2 and z = ? and z in (10,3,4)";
set @a:=10;
select connection_id();
execute stmt using @a;
explain for connection XXX
```
Not use `PREPARE/EXECUTE`:
```
> explain select * from t where a = 2 and b = 2 and c = 2 and d = 2 and z = 10 and z in (10,3,4);
+-------------------------+----------+-----------+---------------+--------------------------------------------------------------------------------------+
| id | estRows | task | access object | operator info |
+-------------------------+----------+-----------+---------------+--------------------------------------------------------------------------------------+
| TableReader_7 | 2.05 | root | | data:Selection_6 |
| └─Selection_6 | 2.05 | cop[tikv] | | eq(test.t.a, 2), eq(test.t.b, 2), eq(test.t.c, 2), eq(test.t.d, 2), eq(test.t.z, 10) |
| └─TableFullScan_5 | 20480.00 | cop[tikv] | table:t | keep order:false |
+-------------------------+----------+-----------+---------------+--------------------------------------------------------------------------------------+
```
Use `PREPARE/EXECUTE`:
```
> explain for connection 3877634054;
+-------------------------+----------+---------+-----------+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------+--------+------+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
+-------------------------+----------+---------+-----------+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------+--------+------+
| TableReader_7 | 1638.40 | 0 | root | | time:15ms, open:30.4µs, close:4.82µs, loops:1, cop_task: {num: 1, max: 14.9ms, proc_keys: 20480, tot_proc: 14.4ms, tot_wait: 35.5µs, copr_cache_hit_ratio: 0.00, build_task_duration: 6.46µs, max_distsql_concurrency: 1}, rpc_info:{Cop:{num_rpc:1, total_time:14.9ms}} | data:Selection_6 | N/A | N/A |
| └─Selection_6 | 1638.40 | 0 | cop[tikv] | | tikv_task:{time:13ms, loops:25}, scan_detail: {total_process_keys: 20480, total_process_keys_size: 1085440, total_keys: 20481, get_snapshot_time: 18.9µs, rocksdb: {delete_skipped_count: 19840, key_skipped_count: 40320, block: {}}}, time_detail: {total_process_time: 14.4ms, total_suspend_time: 30.9µs, total_wait_time: 35.5µs, total_kv_read_wall_time: 13ms, tikv_wall_time: 14.6ms} | 1, eq(test.t.a, 2), eq(test.t.b, 2), eq(test.t.c, 2), eq(test.t.d, 2), eq(test.t.z, 10) | N/A | N/A |
| └─TableFullScan_5 | 20480.00 | 20480 | cop[tikv] | table:t | tikv_task:{time:13ms, loops:25} | keep order:false | N/A | N/A |
+-------------------------+----------+---------+-----------+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------+--------+------+
```
Contributor guide
Assessment
This issue has not been assessed yet.