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



    • 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.3, 10.4
    • Component/s: Optimizer
    • Labels:


      CREATE TABLE t1 (
      pk1 INT,
      pk2 INT, 
      f1 VARCHAR(3),
      f2 VARCHAR(1021),
      PRIMARY KEY (pk1,pk2),
      ) 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


          Issue Links



              psergei Sergei Petrunia
              varun Varun Gupta (Inactive)
              0 Vote for this issue
              1 Start watching this issue



                  Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.