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

Add consideration of semi-join strategies to the optimizer trace

    Details

    • Type: Task
    • Status: Open (View Workflow)
    • Priority: Major
    • Resolution: Unresolved
    • Fix Version/s: 10.4
    • Component/s: Optimizer
    • Labels:
      None

      Description

      This task aims to print the semi-join strategy consideration for the join order inside the optimizer trace.
      Mysql has this already.

      An example would be:

      Dataset

      create table t0(a int);
      insert t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
      create table t1 (a int, b int, c int);
      insert t1 select A.a+B.a*10+C.a*100, A.a+B.a*10+C.a*100, A.a+B.a*10+C.a*100 from t0 A, t0 B, t0 C;
      create table t2 (a int, b int, c int);
      insert t2 select a,a,a from t0;
      create table t3 (a int, b int, c int, key(a));
      insert t3 select a,a,a from t0;
      

      Query is

      select * from t1, t2 where t1.a=t2.a and
                  t1.b in (select b from t3 where t3.c < 3) order by t1.b,t2.b desc limit 5;
      

      So the trace has output like this, where we consider all the semi-join strategies

       "semijoin_strategy_choice": [
        {
          "strategy": "MaterializeScan",
          "recalculate_access_paths_and_cost": {
            "tables": [
              {
                "table": "`t1`",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "rows_to_scan": 1000,
                      "access_type": "scan",
                      "using_join_cache": true,
                      "buffers_needed": 1,
                      "resulting_rows": 1000,
                      "cost": 6670,
                      "chosen": true
                    }
                  ]
                }
              }
            ]
          },
          "cost": 6685.3,
          "rows": 100,
          "duplicate_tables_left": true,
          "chosen": true
        },
        {
          "strategy": "DuplicatesWeedout",
          "cost": 6770.7,
          "rows": 100,
          "duplicate_tables_left": false,
          "chosen": false
        }
      ],
      

        Attachments

          Activity

            People

            • Assignee:
              varun Varun Gupta
              Reporter:
              varun Varun Gupta
            • Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

              • Created:
                Updated: