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

Estimation for filtered rows less precise with JSON histogram comparing to DOUBLE_PREC (#6)

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Critical
    • Resolution: Fixed
    • Affects Version/s: N/A
    • Fix Version/s: 10.8.0
    • Component/s: Optimizer
    • Labels:
      None

      Description

      --source include/have_sequence.inc
       
      create table t1 (a char(1)) engine=MyISAM;
      insert into t1 select chr(seq%26+97) from seq_1_to_50;
      insert into t1 select ':' from t1;
       
      select count(*) from t1 where a <> 'a';
       
      set histogram_type=JSON_HB;
      analyze table t1 persistent for all;
       
      explain format=json select COUNT(*) FROM t1 WHERE a <> 'a';
       
      set histogram_type=DOUBLE_PREC_HB;
      analyze table t1 persistent for all;
       
      explain format=json select COUNT(*) FROM t1 WHERE a <> 'a';
       
      # Cleanup
      drop table t1;
      

      The result of the query is 99 rows:

      select count(*) from t1 where a <> 'a';
      count(*)
      99
      

      So, the estimation with DOUBLE_PREC_HB is quite close:

      preview-10.8-MDEV-26519-json-histograms 98cb4351

        "query_block": {
          "select_id": 1,
          "nested_loop": [
            {
              "table": {
                "table_name": "t1",
                "access_type": "ALL",
                "rows": 100,
                "filtered": 100,
                "attached_condition": "t1.a <> 'a'"
              }
            }
          ]
        }
      

      The estimation with JSON_HB is off:

        "query_block": {
          "select_id": 1,
          "nested_loop": [
            {
              "table": {
                "table_name": "t1",
                "access_type": "ALL",
                "rows": 100,
                "filtered": 52.70370102,
                "attached_condition": "t1.a <> 'a'"
              }
            }
          ]
        }
      

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              psergei Sergei Petrunia
              Reporter:
              elenst Elena Stepanova
              Votes:
              0 Vote for this issue
              Watchers:
              2 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.