[MDEV-16695] Estimate for rows of derived tables is very high when we are using index_merge union - Jira

Varun Gupta (Inactive) created issue - 2018-07-05 11:37

Varun Gupta (Inactive) made changes - 2018-07-05 11:37

Field	Original Value	New Value
Link		This issue duplicates ~~MDEV-15253~~ [ ~~MDEV-15253~~ ]

Varun Gupta (Inactive) made changes - 2018-07-05 11:44

Description

Here is the dataset

{code:sql}
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);
{noformat}
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"
          }
        }
      }
    }
  }
}

{noformat}

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';
{code}

set @@optimizer_use_condition_selectivity=2;

{noformat}
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"
          }
        }
      }
    }
  }
}
{noformat}

set optimizer_use_condition_selectivity=1;

{noformat}
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"
          }
        }
      }
    }
  }
}
{noformat}

drop table t1,t0;

Varun Gupta (Inactive) made changes - 2018-07-05 11:46

Description

Here is the dataset

{code:sql}
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);
{noformat}
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"
          }
        }
      }
    }
  }
}

{noformat}

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';
{code}

set @@optimizer_use_condition_selectivity=2;

{noformat}
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"
          }
        }
      }
    }
  }
}
{noformat}

set optimizer_use_condition_selectivity=1;

{noformat}
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"
          }
        }
      }
    }
  }
}
{noformat}

drop table t1,t0;

Here is the dataset

{code:sql}
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);
{noformat}
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"
          }
        }
      }
    }
  }
}

{noformat}

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';
{code}

set optimizer_use_condition_selectivity=2;

{noformat}
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"
          }
        }
      }
    }
  }
}
{noformat}

set optimizer_use_condition_selectivity=1;

{noformat}
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"
          }
        }
      }
    }
  }
}
{noformat}

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

Varun Gupta (Inactive) made changes - 2018-07-05 11:46

Link

This issue is part of ~~MDEV-15253~~ [ ~~MDEV-15253~~ ]

Varun Gupta (Inactive) made changes - 2018-07-05 11:46

Link

This issue duplicates ~~MDEV-15253~~ [ ~~MDEV-15253~~ ]

Varun Gupta (Inactive) made changes - 2018-07-05 13:29

Summary

Estimate for rows of derived tables is very high when we are using index merge

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

Sergei Petrunia made changes - 2018-07-05 15:28

Description

Here is the dataset

{code:sql}
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);
{noformat}
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"
          }
        }
      }
    }
  }
}

{noformat}

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';
{code}

set optimizer_use_condition_selectivity=2;

{noformat}
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"
          }
        }
      }
    }
  }
}
{noformat}

set optimizer_use_condition_selectivity=1;

{noformat}
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"
          }
        }
      }
    }
  }
}
{noformat}

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

Here is the dataset

{code:sql}
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);
{code}

{noformat}
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"
          }
        }
      }
    }
  }
}

{noformat}

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';
{code}

set optimizer_use_condition_selectivity=2;

{noformat}
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"
          }
        }
      }
    }
  }
}
{noformat}

set optimizer_use_condition_selectivity=1;

{noformat}
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"
          }
        }
      }
    }
  }
}
{noformat}

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

Varun Gupta (Inactive) made changes - 2018-07-06 09:14

Status

Open [ 1 ]

In Progress [ 3 ]

Varun Gupta (Inactive) made changes - 2018-07-09 07:48

Assignee	Varun Gupta [ varun ]	Sergei Petrunia [ psergey ]
Status	In Progress [ 3 ]	In Review [ 10002 ]

Sergei Petrunia made changes - 2018-08-26 11:58

Assignee	Sergei Petrunia [ psergey ]	Varun Gupta [ varun ]
Status	In Review [ 10002 ]	Stalled [ 10000 ]

Sergei Petrunia made changes - 2018-08-26 12:22

Assignee

Varun Gupta [ varun ]

Sergei Petrunia [ psergey ]

Sergei Petrunia made changes - 2018-10-25 10:11

Status

Stalled [ 10000 ]

In Progress [ 3 ]

Sergei Petrunia made changes - 2018-10-25 10:11

Assignee

Sergei Petrunia [ psergey ]

Varun Gupta [ varun ]

Varun Gupta (Inactive) made changes - 2018-11-01 08:12

Fix Version/s		10.0.37 [ 22917 ]
Fix Version/s		10.3.11 [ 23141 ]
Fix Version/s		10.1.37 [ 23204 ]
Fix Version/s		10.2.19 [ 23207 ]
Fix Version/s	10.2 [ 14601 ]
Fix Version/s	10.0 [ 16000 ]
Fix Version/s	10.1 [ 16100 ]
Fix Version/s	10.3 [ 22126 ]
Fix Version/s	10.4 [ 22408 ]
Resolution		Fixed [ 1 ]
Status	In Progress [ 3 ]	Closed [ 6 ]

Marko Mäkelä made changes - 2018-11-05 09:11

Fix Version/s		10.0.38 [ 23211 ]
Fix Version/s	10.0.37 [ 22917 ]

Marko Mäkelä made changes - 2018-11-05 09:12

Fix Version/s		10.1.38 [ 23209 ]
Fix Version/s	10.1.37 [ 23204 ]

Sergei Golubchik made changes - 2021-12-06 21:47

Workflow

MariaDB v3 [ 88256 ]

MariaDB v4 [ 154628 ]

MariaDB Server

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

Details

Description

Attachments

Issue Links

Activity

People

Dates

Git Integration