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

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

    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
       
      # One third are zeros, the rest are unique values
      create or replace table t (a tinyint) as select if(seq%3,seq,0) as a from seq_1_to_100;
      select count(*) from t where a <= 0;
       
      set histogram_type = JSON_HB;
      analyze table t persistent for all;
      explain format=json select * from t where a <= 0;
       
      set histogram_type = DOUBLE_PREC_HB;
      analyze table t persistent for all;
      explain format=json select * from t where a <= 0;
       
      # Cleanup
      drop table t;
      

      preview-10.7-MDEV-26519-json-histograms 508f5f3f

      select count(*) from t where a <= 0;
      count(*)
      33
      

      JSON histogram

      {
        "query_block": {
          "select_id": 1,
          "table": {
            "table_name": "t",
            "access_type": "ALL",
            "rows": 100,
            "filtered": 1.470600009,
            "attached_condition": "t.a <= 0"
          }
        }
      }
      

      DOUBLE_PREC histogram

      {
        "query_block": {
          "select_id": 1,
          "table": {
            "table_name": "t",
            "access_type": "ALL",
            "rows": 100,
            "filtered": 32.8125,
            "attached_condition": "t.a <= 0"
          }
        }
      }
      

      So, the actual result set contains 33 rows out of 100, DOUBLE_PREC gives filtered=32.8 which is very close, JSON histogram gives filtered=1.47 which is far off.

      Reproducible with InnoDB and MyISAM alike.

        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.