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

LATERAL DERIVED is not clearly visible in EXPLAIN FORMAT=JSON

    XMLWordPrintable

    Details

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

      Description

      Let's take a small example (from main/derived_split_innodb.test) and look how LATERAL DERIVED is shown in EXPLAIN:

      CREATE TABLE t1 (
      n1 int(10) NOT NULL,
      n2 int(10) NOT NULL,
      c1 char(1) NOT NULL,
      KEY c1 (c1),
      KEY n1_c1_n2 (n1,c1,n2)
      ) ENGINE=InnoDB;
      INSERT INTO t1 VALUES (0, 2, 'a'), (1, 3, 'a');
      ANALYZE TABLE t1;
      

      EXPLAIN SELECT t1.n1 FROM t1, (SELECT n1, n2 FROM t1 WHERE c1 = 'a' GROUP BY n1) as t                                                  
      WHERE t.n1 = t1.n1 AND t.n2 = t1.n2 AND c1 = 'a' GROUP BY n1;                                                                          
      +------+-----------------+------------+-------+---------------+----------+---------+-------------------+------+--------------------------+
      | id   | select_type     | table      | type  | possible_keys | key      | key_len | ref               | rows | Extra                    |
      +------+-----------------+------------+-------+---------------+----------+---------+-------------------+------+--------------------------+
      |    1 | PRIMARY         | t1         | index | c1,n1_c1_n2   | n1_c1_n2 | 9       | NULL              |    2 | Using where; Using index |
      |    1 | PRIMARY         | <derived2> | ref   | key0          | key0     | 8       | j5.t1.n1,j5.t1.n2 |    2 |                          |
      |    2 | LATERAL DERIVED | t1         | ref   | c1,n1_c1_n2   | n1_c1_n2 | 4       | j5.t1.n1          |    1 | Using where; Using index |
      +------+-----------------+------------+-------+---------------+----------+---------+-------------------+------+--------------------------+
      

      Good so far

      Now, try EXPLAIN FORMAT=JSON

      {
        "query_block": {
          "select_id": 1,
          "table": {
            "table_name": "t1",
            "access_type": "index",
            "possible_keys": ["c1", "n1_c1_n2"],
            "key": "n1_c1_n2",
            "key_length": "9",
            "used_key_parts": ["n1", "c1", "n2"],
            "rows": 2,
            "filtered": 100,
            "attached_condition": "t1.c1 <=> 'a' and t1.c1 = 'a'",
            "using_index": true
          },
          "table": {
            "table_name": "<derived2>",
            "access_type": "ref",
            "possible_keys": ["key0"],
            "key": "key0",
            "key_length": "8",
            "used_key_parts": ["n1", "n2"],
            "ref": ["j5.t1.n1", "j5.t1.n2"],
            "rows": 2,
            "filtered": 100,
            "materialized": {
              "query_block": {
                "select_id": 2,
                "table": {
                  "table_name": "t1",
                  "access_type": "ref",
                  "possible_keys": ["c1", "n1_c1_n2"],
                  "key": "n1_c1_n2",
                  "key_length": "4",
                  "used_key_parts": ["n1"],
                  "ref": ["j5.t1.n1"],
                  "rows": 1,
                  "filtered": 100,
                  "attached_condition": "t1.c1 = 'a'",
                  "using_index": true
                }
              }
            }
          }
        }
      }
      

      If one is attentive enough, they can see that "materialized" node has a ref access in it which refers to "j5.t1.n1". Table "t1"
      is outside the subquery, which means the subquery is using LATERAL DERIVED optimization.

      But this is not at all obvious. EXPLAIN FORMAT=JSON should make it clear what optimization was used.

        Attachments

          Activity

            People

            • Assignee:
              psergey Sergei Petrunia
              Reporter:
              psergey Sergei Petrunia
            • Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

              • Created:
                Updated: