[MDEV-27229] Estimation for filtered rows less precise with JSON histogram comparing to DOUBLE_PREC (#5) - Jira

--source include/have_sequence.inc

create table t1 (id int, a varchar(8)) engine=MyISAM;

insert into t1 select seq, 'bar' from seq_1_to_100;

insert into t1 select id, 'qux' from t1;

set histogram_type=JSON_HB;

analyze table t1 persistent for all;

explain format=json select COUNT(*) FROM t1 WHERE a > 'foo';

set histogram_type=DOUBLE_PREC_HB;

analyze table t1 persistent for all;

explain format=json select COUNT(*) FROM t1 WHERE a > 'foo';

# Cleanup

drop table t1;

The condition filters a half of the rows (obviously).

With JSON_HB the estimation is quite far off:

preview-10.8-MDEV-26519-json-histograms 98cb4351
"query_block": {
"select_id": 1,
"nested_loop": [
{
"table": {
"table_name": "t1",
"access_type": "ALL",
"rows": 200,
"filtered": 86.55463409,
"attached_condition": "t1.a > 'foo'"
}
}
]
}

With DOUBLE_PREC the estimation is in this case precise:

  "query_block": {

    "select_id": 1,

    "nested_loop": [

        "table": {

          "table_name": "t1",

          "access_type": "ALL",

          "rows": 200,

          "filtered": 50,

          "attached_condition": "t1.a > 'foo'"

is caused by

MDEV-26519 JSON Histograms: improve histogram collection

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

MariaDB Server