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

              varun Varun Gupta (Inactive)
              varun Varun Gupta (Inactive)
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

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