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

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

Details

    • Bug
    • Status: Closed (View Workflow)
    • Critical
    • Resolution: Duplicate
    • N/A
    • N/A
    • Optimizer
    • None

    Description

      li

      create or replace table t (a int);
      		 
      insert into t values
      		 
        (17),(6),(14),(3),(24),(6),(23),(15),(24),(21),
      		 
        (25),(17),(6),(2),(21),(16),(19),(9),(10),(3);
      		 
       
      		 
      select * from t where a = 23 or a = 20;
      		 
       
      		 
      set histogram_type= JSON_HB;
      		 
      analyze table t persistent for all;
      		 
      explain format=json select * from t where a = 23 or a = 20;
      		 
       
      		 
      set histogram_type= DOUBLE_PREC_HB;
      		 
      analyze table t persistent for all;
      		 
      explain format=json select * from t where a = 23 or a = 20;
      		 
       
      		 
      # Cleanup
      		 
      drop table t;

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

      		 
      select * from t where a = 23 or a = 20;
      		 
      a
      		 
      23

      JSON histogram

      		 
        "query_block": {
      		 
          "select_id": 1,
      		 
          "table": {
      		 
            "table_name": "t",
      		 
            "access_type": "ALL",
      		 
            "rows": 20,
      		 
            "filtered": 12.14299965,
      		 
            "attached_condition": "t.a = 23 or t.a = 20"
      		 
          }
      		 
        }

      DOUBLE_PREC histogram

      		 
      {
      		 
        "query_block": {
      		 
          "select_id": 1,
      		 
          "table": {
      		 
            "table_name": "t",
      		 
            "access_type": "ALL",
      		 
            "rows": 20,
      		 
            "filtered": 5.329219818,
      		 
            "attached_condition": "t.a = 23 or t.a = 20"
      		 
          }
      		 
        }
      		 
      }

      So, the actual result set contains 1 row out of 20, DOUBLE_PREC gives filtered=5.3 which is almost correct, JSON gives filtered=12.1 which is further off.

      Reproducible with MyISAM and InnoDB alike.

      Attachments

        Issue Links

          is caused by

          Task - A task that needs to be done. MDEV-26519 JSON Histograms: improve histogram collection

          • Blocker - Blocks development and/or testing work, production could not run.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            elenst Elena Stepanova added a comment -

            Raised to critical on procedural reasons. Feel free to demote and remove from "must-do" scope (or close if it's not worth fixing).

            elenst Elena Stepanova added a comment - Raised to critical on procedural reasons. Feel free to demote and remove from "must-do" scope (or close if it's not worth fixing).
            psergei Sergei Petrunia added a comment -

            This is the same issue as MDEV-26849.

            psergei Sergei Petrunia added a comment - This is the same issue as MDEV-26849 .
            psergei Sergei Petrunia added a comment -

            With fix for MDEV-26849:

            cond real mysql mariadb mariadb_old postgresql
            col = 23 or col = 20 1 1 2 1.0658439636 2
            col=23 1 1 1 0.9375 1
            col=20 0 1 1 0.1283439278 1
            psergei Sergei Petrunia added a comment - With fix for MDEV-26849 : cond real mysql mariadb mariadb_old postgresql col = 23 or col = 20 1 1 2 1.0658439636 2 col=23 1 1 1 0.9375 1 col=20 0 1 1 0.1283439278 1
            elenst Elena Stepanova added a comment -

            Please push it then. We can't really release (and even meaningfully test) the functionality which works only for existing values.

            elenst Elena Stepanova added a comment - Please push it then. We can't really release (and even meaningfully test) the functionality which works only for existing values.

            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.