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 created issue -
            elenst Elena Stepanova made changes -
            Field Original Value New Value
            elenst Elena Stepanova made changes -
            Priority Major [ 3 ] Critical [ 2 ]
            elenst Elena Stepanova made changes -
            Summary Estimation for filtered rows less precise with JSON histogram comparing to DOUBLE_PREC Estimation for filtered rows less precise with JSON histogram comparing to DOUBLE_PREC #2
            elenst Elena Stepanova made changes -
            Summary Estimation for filtered rows less precise with JSON histogram comparing to DOUBLE_PREC #2 Estimation for filtered rows less precise with JSON histogram comparing to DOUBLE_PREC (#2)
            psergei Sergei Petrunia made changes -
            Fix Version/s N/A [ 14700 ]
            Fix Version/s 10.7 [ 24805 ]
            Resolution Duplicate [ 3 ]
            Status Open [ 1 ] Closed [ 6 ]
            psergei Sergei Petrunia made changes -
            Description {code:sql}
            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;
            {code}

            {code:sql|title=preview-10.7-MDEV-26519-json-histograms 508f5f3f11e}
            select * from t where a = 23 or a = 20;
            a
            23
            {code}
            {code:sql|title=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"
                }
              }
            {code}
            {code:sql|title=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"
                }
              }
            }
            {code}

            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.             		li{code:sql}
            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;
            {code}

            {code:sql|title=preview-10.7-MDEV-26519-json-histograms 508f5f3f11e}
            select * from t where a = 23 or a = 20;
            a
            23
            {code}
            {code:sql|title=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"
                }
              }
            {code}
            {code:sql|title=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"
                }
              }
            }
            {code}

            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.
            serg Sergei Golubchik made changes -
            Workflow MariaDB v3 [ 126515 ] MariaDB v4 [ 159800 ]

            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.