Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-20424

New default value for optimizer_use_condition-selectivity in MariaDB 10.4 leads to bad plan

    Details

      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

          Activity

            People

            • Assignee:
              igor Igor Babaev
              Reporter:
              valerii Valerii Kravchuk
            • Votes:
              1 Vote for this issue
              Watchers:
              5 Start watching this issue

              Dates

              • Created:
                Updated: