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
-