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

ANALYZE FORMAT=JSON: some time is unaccounted-for in BNL-H joins

    XMLWordPrintable

Details

    Description

      Consider this testcase:

      --source include/have_sequence.inc
       
      create table t1 (
        a int,
        col1 varchar(100),
        col2 varchar(100),
        col3 varchar(100)
      );
       
      insert into t1 select
        seq/100,
        concat('col1-', seq),
        concat('col1-', seq),
        concat('col1-', seq)
      from seq_1_to_10000;
       
      create table t2 (
        a int,
        col1 varchar(100),
        col2 varchar(100),
        col3 varchar(100)
      );
       
       
      insert into t2 select
        seq/100,
        concat('col1-', seq),
        concat('col1-', seq),
        concat('col1-', seq)
      from seq_1_to_20000;
       
      analyze table t1,t2;
      

      set join_cache_level=6;
      analyze format=json
      select * from t1, t2 
      where 
        t1.a=t2.a
        and concat(t1.col1, t1.col2, t1.col3)= concat(t2.col1, t2.col2, t2.col3);
       
      drop table t1,t2;
      

      Produces

      ANALYZE
      {
        "query_optimization": {
          "r_total_time_ms": 0.274550914
        },
        "query_block": {
          "select_id": 1,
          "cost": 1865.900178,
          "r_loops": 1,
          "r_total_time_ms": 1258.322335,
          "nested_loop": [
            {
              "table": {
                "table_name": "t1",
                "access_type": "ALL",
                "loops": 1,
                "r_loops": 1,
                "rows": 10000,
                "r_rows": 10000,
                "cost": 1.597164,
                "r_table_time_ms": 8.369960408,
                "r_other_time_ms": 10.40811593,
                "filtered": 100,
                "r_filtered": 100,
                "attached_condition": "t1.a is not null"
              }
            },
            {
              "block-nl-join": {
                "table": {
                  "table_name": "t2",
                  "access_type": "hash_ALL",
                  "key": "#hash#$hj",
                  "key_length": "5",
                  "used_key_parts": ["a"],
                  "ref": ["test.t1.a"],
                  "loops": 10000,
                  "r_loops": 2,
                  "rows": 20000,
                  "r_rows": 20000,
                  "cost": 1864.303014,
                  "r_table_time_ms": 57.63327534,
                  "r_other_time_ms": 16.21655048,
                  "filtered": 100,
                  "r_filtered": 100
                },
                "buffer_type": "flat",
                "buffer_size": "256Kb",
                "join_type": "BNLH",
                "attached_condition": "t2.a = t1.a and concat(t1.col1,t1.col2,t1.col3) = concat(t2.col1,t2.col2,t2.col3)",
                "r_filtered": 1.002505261,
                "r_unpack_time_ms": 135.8873441
              }
            }
          ]
        }
      }
      

      In more detail:

        "query_block": {
          "r_total_time_ms": 1258.322335,
          "nested_loop": [
            {
              "table": {
                "table_name": "t1",
                "r_table_time_ms": 8.369960408,
                "r_other_time_ms": 10.40811593,
              }
            },
            {
              "block-nl-join": {
                "table": {
                  "table_name": "t2",
                  "r_table_time_ms": 57.63327534,
                  "r_other_time_ms": 16.21655048,
                },
                "r_unpack_time_ms": 135.8873441
              }
            }
          ]
        }
      

      note that times do not add up to query_block.r_total_time_ms.

      Attachments

        Issue Links

          Activity

            People

              psergei Sergei Petrunia
              psergei Sergei Petrunia
              Votes:
              0 Vote for this issue
              Watchers:
              1 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.