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

Selectivity of equality condition in ref access not discounted if range access on same index involved a non-equality condition

    Details

    • Type: Bug
    • Status: In Progress (View Workflow)
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: 10.1, 10.2, 10.3, 10.4
    • Fix Version/s: 10.1, 10.2, 10.3, 10.4
    • Component/s: Optimizer
    • Labels:
      None

      Description

      CREATE TABLE t1 (
      pk1 INT,
      pk2 INT, 
      f1 VARCHAR(3),
      f2 VARCHAR(1021),
      PRIMARY KEY (pk1,pk2),
      KEY(f2)
      ) ENGINE=InnoDB;
       
      INSERT INTO t1 VALUES (1,2,'2','abc'),(2,3,'3','def');
      INSERT INTO t1 VALUES (2,0,'2','abc');
      INSERT INTO t1 VALUES (2,5,'2','abc');
      INSERT INTO t1 VALUES (2,4,'2','abc');
       
      Also use
      set optimizer_use_condition_selecitivity=4;
      

      MariaDB [test]> analyze select * from t1 force index(f2)  where pk1 <= 5 and pk2 <=5 and f2 = 'abc' and f1 <= '3';
      +------+-------------+-------+-------+---------------+------+---------+------+------+--------+----------+------------+------------------------------------+
      | id   | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | r_rows | filtered | r_filtered | Extra                              |
      +------+-------------+-------+-------+---------------+------+---------+------+------+--------+----------+------------+------------------------------------+
      |    1 | SIMPLE      | t1    | range | f2            | f2   | 1032    | NULL | 4    | 4.00   |    80.00 |     100.00 | Using index condition; Using where |
      +------+-------------+-------+-------+---------------+------+---------+------+------+--------+----------+------------+------------------------------------+
      1 row in set (0.004 sec)
      

      So filtered here shows 80%. The reason is we set filtered during best_access_path and there we actually picked ref access instead of range and later in plan refinement stage we switched the plan to use range access instead of ref access.

      The optimizer trace shows that we picked ref access first

                  "considered_execution_plans": [
                    {
                      "plan_prefix": [],
                      "table": "t1",
                      "best_access_path": {
                        "considered_access_paths": [
                          {
                            "access_type": "ref",
                            "index": "f2",
                            "used_range_estimates": true,
                            "rows": 4,
                            "cost": 3,
                            "chosen": true
                          },
                          {
                            "access_type": "range",
                            "resulting_rows": 4,
                            "cost": 6.3004,
                            "chosen": false
                          }
                        ]
                      },
                      "rows_for_plan": 4,
                      "cost_for_plan": 3.8,
                      "selectivity": 0.8,
                      "join_cardinality": 3.2
                    }
      

      ref access on f2 is cheaper than range access on f2.
      The selectivity is 0.8 (80%) which we see in the analyze output.
      The issue with such cases is that for ref access estimates we sometimes use range access estimates(because they are more accurate).
      One can see in the optimizer trace used_range_estimates which means that we actually used the range estimates. So if we used the range estimates we took care of the selecitivity of the entire condition.
      So the selectivity here should have been 1. There is some bug while discounting selectivity for conditions that were already taken into account.
      We only discount the selectivity when the range access and ref access on the index has the same keyparts and they are of the type keypart_i = const

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                varun Varun Gupta
                Reporter:
                varun Varun Gupta
              • Votes:
                0 Vote for this issue
                Watchers:
                1 Start watching this issue

                Dates

                • Created:
                  Updated: