Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.1(EOL), 10.2(EOL), 10.3(EOL), 10.4(EOL)
Description
MDEV-15253 inbtriduced a change in MariaDB 10.4. Now by default optimizer_use_condition_selectivity=4 instead of 1. As a result we may get bad plans after upgrade to 10.4. For example, in 10.4 we get:
1 SIMPLE ticketgrou0_ range PRIMARY,index_ticket_group_on_business_id_and_date... index_ticket_group_on_business_id_and_is_archived_... 10 1770 1770.00 0.00 100.00 Using where; Using index
|
1 SIMPLE tables0_ ref index_ticket_group_table_on_table_id,index_ticket_... index_ticket_group_table_on_tg_id 4 tock_staging.ticketgrou0_.id 1 1.24 100.00 100.00 Using where; Using index
|
1 SIMPLE tableentit1_ ALL PRIMARY 66037 66280.00 100.00 0.00 Using where; Using join buffer (flat, BNL join)
|
Same query on MariaDB 10.3:
1 PRIMARY ticketgrou0_ range PRIMARY,index_ticket_group_on_business_id_and_date... index_ticket_group_on_business_id_and_is_archived_... 10 1770 1770.00 100.00 100.00 Using where; Using index
|
1 PRIMARY tables0_ ref index_ticket_group_table_on_tg_id,index_ticket_gro... index_ticket_group_table_on_tg_id 4 tock_prod.ticketgrou0_.id 1 1.24 100.00 100.00 Using index condition
|
1 PRIMARY tableentit1_ eq_ref PRIMARY PRIMARY 4 tock_prod.tables0_.table_id 1 1.00 100.00 100.00 Using where
|
Note that instead of eq_ref access by primary key we get full table scan and BNL join, that in practice end up as a very serious performance regression.
Setting optimizer_use_condition_selectivity=1 allows to get the same better plan as in 10.3.
Attachments
Issue Links
- relates to
-
-
- Closed
-
Activity
Filtered for tickettgroup0_ shows 0.00, this for the case when optimizer_use_condition_selectivity > 1 is the selectivity of conditions when joining a table, this for our case should be 100%, this looks incorrect, this would also set incorrect values for record_count(records in the prefix), so looks like the bug is in table_cond_selectivity().
I confirm this issue, have similar query and with optimizer_use_condition_selectivity=4 is full scan performed. Even FORCE INDEX is ignored.
Reproduced by this mtr test case:
--source include/have_sequence.inc
|
|
|
create table t1(a int, b int, c int, d int, key(a,b)); |
insert into t1 select 50,seq-1,seq-1,seq from seq_1_to_100; |
insert into t1 select seq-1,seq-1,seq-1,seq from seq_1_to_1000 limit 900; |
|
|
create table t2(a int, b int, c int, primary key(a)); |
insert into t2 select seq-1,seq-1,seq-1 from seq_1_to_100; |
|
|
create table t3(a int, b int, c int, primary key(a)); |
insert into t3 select seq-1,seq-1,seq-1 from seq_1_to_100 limit 30; |
|
|
let $query= select * from t1,t2,t3 |
where t1.c = t2.a AND t1.d = t3.a and t1.a = 50 and t1.b <= 100; |
|
|
set optimizer_use_condition_selectivity=1; |
eval explain extended $query;
|
set optimizer_use_condition_selectivity=2; |
eval explain extended $query;
|
|
|
drop table t1,t2,t3; |
MariaDB [test]> set optimizer_use_condition_selectivity=2;
|
Query OK, 0 rows affected (0.001 sec)
|
|
|
MariaDB [test]> explain extended select * from t1,t2,t3 where t1.c = t2.a AND t1.d = t3.a and t1.a = 50 and t1.b <= 100;
|
+------+-------------+-------+--------+---------------+---------+---------+-----------+------+----------+-------------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+------+-------------+-------+--------+---------------+---------+---------+-----------+------+----------+-------------------------------------------------+
|
| 1 | SIMPLE | t1 | range | a | a | 10 | NULL | 101 | 10.10 | Using index condition; Using where |
|
| 1 | SIMPLE | t2 | eq_ref | PRIMARY | PRIMARY | 4 | test.t1.c | 1 | 100.00 | |
|
| 1 | SIMPLE | t3 | ALL | PRIMARY | NULL | NULL | NULL | 30 | 100.00 | Using where; Using join buffer (flat, BNL join) |
|
+------+-------------+-------+--------+---------------+---------+---------+-----------+------+----------+-------------------------------------------------+
|
3 rows in set, 1 warning (0.006 sec)
|
|
|
MariaDB [test]> set optimizer_use_condition_selectivity=1;
|
Query OK, 0 rows affected (0.001 sec)
|
|
|
MariaDB [test]> explain extended select * from t1,t2,t3 where t1.c = t2.a AND t1.d = t3.a and t1.a = 50 and t1.b <= 100;
|
+------+-------------+-------+--------+---------------+---------+---------+-----------+------+----------+------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+------+-------------+-------+--------+---------------+---------+---------+-----------+------+----------+------------------------------------+
|
| 1 | SIMPLE | t1 | range | a | a | 10 | NULL | 101 | 100.00 | Using index condition; Using where |
|
| 1 | SIMPLE | t2 | eq_ref | PRIMARY | PRIMARY | 4 | test.t1.c | 1 | 100.00 | |
|
| 1 | SIMPLE | t3 | eq_ref | PRIMARY | PRIMARY | 4 | test.t1.d | 1 | 100.00 | |
|
+------+-------------+-------+--------+---------------+---------+---------+-----------+------+----------+------------------------------------+
|
3 rows in set, 1 warning (0.005 sec)
|
With optimizer_use_condition_selectivity >=1 we pick table scan over table t3 rather than eq_ref access
Making EXPLAINs a bit more readable.
MariaDB 10.4:
table type key rows r_rows filtered r_filtered1 SIMPLE ticketgrou0_ range index_ticket_group_on_business_id... 1770 1770.00 0.00 100.00 Using where; Using index1 SIMPLE tables0_ ref index_ticket_group_table_on_tg_id 1 1.24 100.00 100.00 Using where; Using index1 SIMPLE tableentit1_ ALL NULL 66037 66280.00 100.00 0.00 Using where; Using join buffer (flat, BNL join)Same query on MariaDB 10.3:
table type key rows r_rows filtered r_filtered1 PRIMARY ticketgrou0_ range index_ticket_group_on_business_id... 1770 1770.00 100.00 100.00 Using where; Using index1 PRIMARY tables0_ ref index_ticket_group_table_on_tg_id 1 1.24 100.00 100.00 Using index condition1 PRIMARY tableentit1_ eq_ref PRIMARY 1 1.00 100.00 100.00 Using whereNote that "filtered" is 100% here (so why does optimizer_use_condition_selectivity play a role)?
The incoming fanout into the 3rd table is 1.7K rows... and then: