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

ANALYZE: r_filtered=100 may look confusing when r_rows=0

    XMLWordPrintable

Details

    • Bug
    • Status: Stalled (View Workflow)
    • Major
    • Resolution: Unresolved
    • 10.2, 10.3, 10.4, 10.5, 10.6, 10.7
    • 10.4, 10.5, 10.6
    • Optimizer
    • None

    Description

      --source include/have_sequence.inc
       
      CREATE TABLE t (a INT, b INT, KEY(a)) ENGINE=MyISAM;
      INSERT INTO t SELECT 5, 5 FROM seq_1_to_1000;
       
      ANALYZE TABLE t PERSISTENT FOR ALL;
       
      ANALYZE FORMAT=JSON
      SELECT * FROM t WHERE 100 = b AND a = 100 OR a IS NULL AND a > 100;
       
      # Cleanup
      DROP TABLE t;
      

      428b057e

      {
        "query_block": {
          "select_id": 1,
          "r_loops": 1,
          "r_total_time_ms": 0.021893114,
          "table": {
            "table_name": "t",
            "access_type": "ref_or_null",
            "possible_keys": ["a"],
            "key": "a",
            "key_length": "5",
            "used_key_parts": ["a"],
            "ref": ["const"],
            "r_loops": 1,
            "rows": 2000,
            "r_rows": 0,
            "r_table_time_ms": 0.014110678,
            "r_other_time_ms": 0.005044542,
            "filtered": 99.21875,
            "r_filtered": 100,
            "index_condition": "t.a = 100",
            "attached_condition": "t.b = 100"
          }
        }
      }
      

      So, neither the WHERE condition as a whole nor any part of it can keep any rows, and yet r_filtered is 100%.

      Also, it's unclear how the plan comes up with rows==2000 while the table has 1000 rows.

      Initially the issue popped up in the course of MDEV-26519 testing, when it turned out that filtered rows with JSON_HB (close to 0) was very far from both r_filtered (100) and from filtered with DOUBLE_PREC_HB (close to 100). However, I'm not sure it's even a JSON_HB problem, given that r_filtered=100% is hard to understand here. Please feel free to adjust the issue as needed: close if not a bug (but please with an explanation how to interpret r_filtered value in this case), or handle as a maintenance problem, or make it MDEV-26519-related if it is.

      preview-10.8-MDEV-26519-json-histograms 9a86900b

      SET histogram_type=JSON_HB;
      ANALYZE TABLE t PERSISTENT FOR ALL;
      Table	Op	Msg_type	Msg_text
      test.t	analyze	status	Engine-independent statistics collected
      test.t	analyze	status	Table is already up to date
      ANALYZE FORMAT=JSON
      SELECT * FROM t WHERE 100 = b AND a = 100 OR a IS NULL AND a > 100;
      ANALYZE
      {
        "query_block": {
          "select_id": 1,
          "r_loops": 1,
          "r_total_time_ms": 0.109424208,
          "nested_loop": [
            {
              "table": {
                "table_name": "t",
                "access_type": "ref_or_null",
                "possible_keys": ["a"],
                "key": "a",
                "key_length": "5",
                "used_key_parts": ["a"],
                "ref": ["const"],
                "r_loops": 1,
                "rows": 2000,
                "r_rows": 0,
                "r_table_time_ms": 0.072815926,
                "r_other_time_ms": 0.02403827,
                "filtered": 0.100000001,
                "r_filtered": 100,
                "index_condition": "t.a = 100",
                "attached_condition": "t.b = 100"
              }
            }
          ]
        }
      }
      

      Attachments

        Issue Links

          Activity

            People

              psergei Sergei Petrunia
              elenst Elena Stepanova
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.