[MDEV-17568] LATERAL DERIVED is not clearly visible in EXPLAIN FORMAT=JSON - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Fixed
Affects Version/s: 10.3(EOL)
Fix Version/s: 10.3.23, 10.4.13, 10.5.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

Ascending order - Click to sort in descending order

Sergei Petrunia created issue - 2018-10-30 10:07

Sergei Petrunia made changes - 2018-10-30 10:19

Field	Original Value	New Value
Description	Let's take a small example (from main/derived_split_innodb.test) and look how LATERAL DERIVED is shown in EXPLAIN: {code:sql} 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; {code} {noformat} 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 \| +------+-----------------+------------+-------+---------------+----------+---------+-------------------+------+--------------------------+ {noformat} Good so far Now, try EXPLAIN FORMAT=JSON {noformat} { "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 } } } } } } {noformat}	Let's take a small example (from main/derived_split_innodb.test) and look how LATERAL DERIVED is shown in EXPLAIN: {code:sql} 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; {code} {noformat} 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 \| +------+-----------------+------------+-------+---------------+----------+---------+-------------------+------+--------------------------+ {noformat} Good so far Now, try EXPLAIN FORMAT=JSON {noformat} { "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 } } } } } } {noformat} 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.

Sergei Petrunia made changes - 2018-10-30 10:20

Assignee

Igor Babaev [ igor ]

Sergei Petrunia made changes - 2018-10-30 10:20

Summary

LATERAL DERIVED not clearly visible in EXPLAIN FORMAT=JSON

LATERAL DERIVED is not clearly visible in EXPLAIN FORMAT=JSON

Sergei Petrunia made changes - 2018-10-30 10:20

Assignee

Igor Babaev [ igor ]

Sergei Petrunia [ psergey ]

Sergei Golubchik made changes - 2019-03-29 12:04

Fix Version/s

10.4 [ 22408 ]

Sergei Petrunia made changes - 2020-05-04 14:55

Status

Open [ 1 ]

In Progress [ 3 ]

Sergei Petrunia made changes - 2020-05-04 14:55

Status

In Progress [ 3 ]

Stalled [ 10000 ]

Sergei Petrunia made changes - 2020-05-06 20:45

Fix Version/s		10.3.23 [ 24222 ]
Fix Version/s		10.4.13 [ 24223 ]
Fix Version/s		10.5.3 [ 24263 ]
Fix Version/s	10.3 [ 22126 ]
Fix Version/s	10.4 [ 22408 ]
Resolution		Fixed [ 1 ]
Status	Stalled [ 10000 ]	Closed [ 6 ]

Sergei Golubchik made changes - 2021-12-06 21:48

Workflow

MariaDB v3 [ 90304 ]

MariaDB v4 [ 155121 ]

People

Assignee:: Sergei Petrunia

Reporter:: Sergei Petrunia

Votes:: 0 Vote for this issue

Watchers:: 1 Start watching this issue

Dates

Created:: 2018-10-30 10:07

Updated:: 2020-05-12 11:58

Resolved:: 2020-05-06 20:45

Git Integration

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

MariaDB Server

Details

Description

Attachments

Activity

People

Dates

Git Integration