Details
Major Description
Here is the dataset
create table t0 |
(
|
key1 int not null, |
INDEX i1(key1) |
);
|
|
|
insert into t0 values (1),(2),(3),(4),(5),(6),(7),(8); |
|
|
let $1=7;
|
set @d=8; |
while ($1)
|
{
|
eval insert into t0 select key1+@d from t0; |
eval set @d=@d*2; |
dec $1; |
}
|
|
|
alter table t0 add key2 int not null, add index i2(key2); |
analyze format=json select * from (select * from t0 where key1 = 3 or key2 =3) as Z ;
|
ANALYZE
|
{
|
"query_block": {
|
"select_id": 1,
|
"r_loops": 1,
|
"r_total_time_ms": 0.5064,
|
"table": {
|
"table_name": "<derived2>",
|
"access_type": "ALL",
|
"r_loops": 1,
|
"rows": 1024,
|
"r_rows": 1,
|
"r_total_time_ms": 0.0103,
|
"filtered": 100,
|
"r_filtered": 100,
|
"materialized": {
|
"query_block": {
|
"select_id": 2,
|
"r_loops": 1,
|
"r_total_time_ms": 0.4476,
|
"table": {
|
"table_name": "t0",
|
"access_type": "index_merge",
|
"possible_keys": ["i1", "i2"],
|
"key_length": "4,4",
|
"index_merge": {
|
"union": {
|
"range": {
|
"key": "i1",
|
"used_key_parts": ["key1"]
|
},
|
"range": {
|
"key": "i2",
|
"used_key_parts": ["key2"]
|
}
|
}
|
},
|
"r_loops": 1,
|
"rows": 2,
|
"r_rows": 1,
|
"r_total_time_ms": 0.0096,
|
"filtered": 100,
|
"r_filtered": 100,
|
"attached_condition": "t0.key1 = 3 or t0.key2 = 3"
|
}
|
}
|
}
|
}
|
}
|
}
|
|
alter table t0 add key3 int not null, add index i3(key3);
alter table t0 add key8 int not null, add index i8(key8);
update t0 set key2=key1,key3=key1,key8=1024-key1;
analyze table t0;
set optimizer_switch='derived_merge=off,derived_with_keys=off';
|
|
set optimizer_use_condition_selectivity=2;
analyze format=json select * from (select * from t0 where key1 = 3 or key2 =3) as Z ;
|
ANALYZE
|
{
|
"query_block": {
|
"select_id": 1,
|
"r_loops": 1,
|
"r_total_time_ms": 0.5064,
|
"table": {
|
"table_name": "<derived2>",
|
"access_type": "ALL",
|
"r_loops": 1,
|
"rows": 1024,
|
"r_rows": 1,
|
"r_total_time_ms": 0.0103,
|
"filtered": 100,
|
"r_filtered": 100,
|
"materialized": {
|
"query_block": {
|
"select_id": 2,
|
"r_loops": 1,
|
"r_total_time_ms": 0.4476,
|
"table": {
|
"table_name": "t0",
|
"access_type": "index_merge",
|
"possible_keys": ["i1", "i2"],
|
"key_length": "4,4",
|
"index_merge": {
|
"union": {
|
"range": {
|
"key": "i1",
|
"used_key_parts": ["key1"]
|
},
|
"range": {
|
"key": "i2",
|
"used_key_parts": ["key2"]
|
}
|
}
|
},
|
"r_loops": 1,
|
"rows": 2,
|
"r_rows": 1,
|
"r_total_time_ms": 0.0096,
|
"filtered": 100,
|
"r_filtered": 100,
|
"attached_condition": "t0.key1 = 3 or t0.key2 = 3"
|
}
|
}
|
}
|
}
|
}
|
}
|
set optimizer_use_condition_selectivity=1;
analyze format=json select * from (select * from t0 where key1 = 3 or key2 =3) as Z ;
|
ANALYZE
|
{
|
"query_block": {
|
"select_id": 1,
|
"r_loops": 1,
|
"r_total_time_ms": 0.3456,
|
"table": {
|
"table_name": "<derived2>",
|
"access_type": "ALL",
|
"r_loops": 1,
|
"rows": 2,
|
"r_rows": 1,
|
"r_total_time_ms": 0.0092,
|
"filtered": 100,
|
"r_filtered": 100,
|
"materialized": {
|
"query_block": {
|
"select_id": 2,
|
"r_loops": 1,
|
"r_total_time_ms": 0.3075,
|
"table": {
|
"table_name": "t0",
|
"access_type": "index_merge",
|
"possible_keys": ["i1", "i2"],
|
"key_length": "4,4",
|
"index_merge": {
|
"union": {
|
"range": {
|
"key": "i1",
|
"used_key_parts": ["key1"]
|
},
|
"range": {
|
"key": "i2",
|
"used_key_parts": ["key2"]
|
}
|
}
|
},
|
"r_loops": 1,
|
"rows": 2,
|
"r_rows": 1,
|
"r_total_time_ms": 0.0089,
|
"filtered": 100,
|
"r_filtered": 100,
|
"attached_condition": "t0.key1 = 3 or t0.key2 = 3"
|
}
|
}
|
}
|
}
|
}
|
}
|
The issue we see is when we have optimizer_use_condition_selectivity set to 2 the derived table has rows =1024 but index merge predicts that there would be 2 rows but looks that during calculation of selectivity we don't take into consideration the rows predicited by index merge
Attachments
Issue Links
- is part of
-
MDEV-15253 Default optimizer setting changes for MariaDB 10.4
-
- Closed
-
Activity
The issue has been that the calculate of selectivity for the condition does not include the selectivity of index merge[union or sort union].
For index_merge/intersection
it is made of multiple range accesses and each of them already has contributed to the estimate of table->cond_selectivity.
table->cond_selectivity= table->cond_selectivity *( ( quick_cond_selectivity(index1) *quick_cond_selectivity(index2)* ... ))For index_merge/union
the selectivity of index_merge union is not counted in the selectivity estimate