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

Take selectivity of join conditions into account correctly with optimizer_use_condition_selectivity > 1

    XMLWordPrintable

    Details

      Description

      For multi equalities we try to add selectivity estimates using EITS but currently this does not work
      In the function table_multi_eq_cond_selectivity

          if (adjust_sel)
          {
            /* 
              If ref == 0 and there are no fields in the multiple equality
              item_equal that belong to the tables joined prior to s
              then the selectivity of multiple equality will be set to 1.0.
            */
            double eq_fld_sel= 1.0;
            fi.rewind();
            while ((fi++))
            {
              double curr_eq_fld_sel;
              Field *fld= fi.get_curr_field();
              if (!(fld->table->map & ~(table_bit | rem_tables)))
                continue;
              curr_eq_fld_sel= get_column_avg_frequency(fld) /
                               fld->table->stat_records();
              if (curr_eq_fld_sel < 1.0)
                set_if_bigger(eq_fld_sel, curr_eq_fld_sel);
            }
            sel*= eq_fld_sel;
          }
      

      So as we see here the comparision

              if (curr_eq_fld_sel < 1.0)
                set_if_bigger(eq_fld_sel, curr_eq_fld_sel);
      

      eq_fld_sel is set to 1, so eq_fld_sel would always remain 1 as we try to update the value in the cases curr_eq_fld_sel < 1.
      This needs to be corrected.
      What I understand the code it does here is it takes the highest selectivity estimate for fields inside the multiple equality which makes sense as
      we want to keep our estimates pessimistic

      After discussing with Igor he mentioned that the intention for the function was to take into account selectivity of equalities for condition on the same table like tbl.col1 = tbl.col2 .

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              igor Igor Babaev
              Reporter:
              varun Varun Gupta (Inactive)
              Votes:
              1 Vote for this issue
              Watchers:
              4 Start watching this issue

                Dates

                Created:
                Updated:

                  Git Integration