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

In non-explain queries, optimizer trace is flooded with join_execution nodes

    XMLWordPrintable

Details

    Description

      In non-explain queries, the optimizer trace can be flooded with join_execution nodes:

            "join_execution": {
              "select_id": 1,
              "steps": [
                {
                  "join_execution": {
                    "select_id": 2,
                    "steps": []
                  }
                },
                {
                  "join_execution": {
                    "select_id": 2,
                    "steps": []
                  }
                }
                ...
      

      The only use for those seems to be "Range checked for each record" plans:

      create table t1 (a int, b int);
      insert into t1 values (1, 999),(999, 1),(987,987);
      create table t2 (a int, b int, index(a),index(b));
      insert into t2 select seq, seq from seq_1_to_1000;
      

      set optimizer_trace=1;
      analyze format=json 
      select * 
      from 
        t1, t2
      where
        t2.a<t1.a and t2.b<t1.b;
      select * from information_schema.optimizer_trace;
      

      gives:

         ... 
            "join_execution": {
              "select_id": 1,
              "steps": [
                {
                  "table": "t2",
                  "range_analysis": { ... }
                }
                {
                  "table": "t2",
                  "range_analysis": { ... }
                }
                {
                  "table": "t2",
                  "range_analysis": { ... }
                }
      

      This content... might be useful one day.
      We will fix 95% of the problem by not tracing "join_execution" and tracing a "range-checked-for-each-record" node with a select_id.

      Attachments

        Issue Links

          Activity

            People

              bsrikanth Srikanth Bondalapati
              psergei Sergei Petrunia
              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.