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)

Details

    • Bug
    • Status: Closed (View Workflow)
    • Critical
    • Resolution: Fixed
    • N/A
    • 10.8.0
    • Optimizer
    • 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

            Pushed a fix for this one.
            We may need a cleanup in the code that handles edge cases like this, though. It is not apparent that the current code handles all such cases.

            psergei Sergei Petrunia added a comment - Pushed a fix for this one. We may need a cleanup in the code that handles edge cases like this, though. It is not apparent that the current code handles all such cases.

            People

              psergei Sergei Petrunia
              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.