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

Estimate for rows of derived tables is very high when we are using index_merge union

    XMLWordPrintable

    Details

      Description

      Here is the dataset

      create table t0
      (
        key1 int not null,
        INDEX i1(key1)
      );
       
      insert into t0 values (1),(2),(3),(4),(5),(6),(7),(8);
       
      let $1=7;
      set @d=8;
      while ($1)
      {
        eval insert into t0 select key1+@d from t0;
        eval set @d=@d*2;
        dec $1;
      }
       
      alter table t0 add key2 int not null, add index i2(key2);
      

      analyze format=json select * from (select * from t0 where key1 = 3 or key2 =3) as Z ;
      ANALYZE
      {
        "query_block": {
          "select_id": 1,
          "r_loops": 1,
          "r_total_time_ms": 0.5064,
          "table": {
            "table_name": "<derived2>",
            "access_type": "ALL",
            "r_loops": 1,
            "rows": 1024,
            "r_rows": 1,
            "r_total_time_ms": 0.0103,
            "filtered": 100,
            "r_filtered": 100,
            "materialized": {
              "query_block": {
                "select_id": 2,
                "r_loops": 1,
                "r_total_time_ms": 0.4476,
                "table": {
                  "table_name": "t0",
                  "access_type": "index_merge",
                  "possible_keys": ["i1", "i2"],
                  "key_length": "4,4",
                  "index_merge": {
                    "union": {
                      "range": {
                        "key": "i1",
                        "used_key_parts": ["key1"]
                      },
                      "range": {
                        "key": "i2",
                        "used_key_parts": ["key2"]
                      }
                    }
                  },
                  "r_loops": 1,
                  "rows": 2,
                  "r_rows": 1,
                  "r_total_time_ms": 0.0096,
                  "filtered": 100,
                  "r_filtered": 100,
                  "attached_condition": "t0.key1 = 3 or t0.key2 = 3"
                }
              }
            }
          }
        }
      }
      
      

      alter table t0 add key3 int not null, add index i3(key3);
      alter table t0 add key8 int not null, add index i8(key8);
      update t0 set key2=key1,key3=key1,key8=1024-key1;
      analyze table t0;
      set optimizer_switch='derived_merge=off,derived_with_keys=off';

       

      set optimizer_use_condition_selectivity=2;

      analyze format=json select * from (select * from t0 where key1 = 3 or key2 =3) as Z ;
      ANALYZE
      {
        "query_block": {
          "select_id": 1,
          "r_loops": 1,
          "r_total_time_ms": 0.5064,
          "table": {
            "table_name": "<derived2>",
            "access_type": "ALL",
            "r_loops": 1,
            "rows": 1024,
            "r_rows": 1,
            "r_total_time_ms": 0.0103,
            "filtered": 100,
            "r_filtered": 100,
            "materialized": {
              "query_block": {
                "select_id": 2,
                "r_loops": 1,
                "r_total_time_ms": 0.4476,
                "table": {
                  "table_name": "t0",
                  "access_type": "index_merge",
                  "possible_keys": ["i1", "i2"],
                  "key_length": "4,4",
                  "index_merge": {
                    "union": {
                      "range": {
                        "key": "i1",
                        "used_key_parts": ["key1"]
                      },
                      "range": {
                        "key": "i2",
                        "used_key_parts": ["key2"]
                      }
                    }
                  },
                  "r_loops": 1,
                  "rows": 2,
                  "r_rows": 1,
                  "r_total_time_ms": 0.0096,
                  "filtered": 100,
                  "r_filtered": 100,
                  "attached_condition": "t0.key1 = 3 or t0.key2 = 3"
                }
              }
            }
          }
        }
      }
      

      set optimizer_use_condition_selectivity=1;

      analyze format=json select * from (select * from t0 where key1 = 3 or key2 =3) as Z ;
      ANALYZE
      {
        "query_block": {
          "select_id": 1,
          "r_loops": 1,
          "r_total_time_ms": 0.3456,
          "table": {
            "table_name": "<derived2>",
            "access_type": "ALL",
            "r_loops": 1,
            "rows": 2,
            "r_rows": 1,
            "r_total_time_ms": 0.0092,
            "filtered": 100,
            "r_filtered": 100,
            "materialized": {
              "query_block": {
                "select_id": 2,
                "r_loops": 1,
                "r_total_time_ms": 0.3075,
                "table": {
                  "table_name": "t0",
                  "access_type": "index_merge",
                  "possible_keys": ["i1", "i2"],
                  "key_length": "4,4",
                  "index_merge": {
                    "union": {
                      "range": {
                        "key": "i1",
                        "used_key_parts": ["key1"]
                      },
                      "range": {
                        "key": "i2",
                        "used_key_parts": ["key2"]
                      }
                    }
                  },
                  "r_loops": 1,
                  "rows": 2,
                  "r_rows": 1,
                  "r_total_time_ms": 0.0089,
                  "filtered": 100,
                  "r_filtered": 100,
                  "attached_condition": "t0.key1 = 3 or t0.key2 = 3"
                }
              }
            }
          }
        }
      }
      

      The issue we see is when we have optimizer_use_condition_selectivity set to 2 the derived table has rows =1024 but index merge predicts that there would be 2 rows but looks that during calculation of selectivity we don't take into consideration the rows predicited by index merge

        Attachments

          Issue Links

            Activity

              People

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

                Dates

                Created:
                Updated:
                Resolved: