[#MDEV-28869] Eliminated tables are not shown in EXPLAIN [FORMAT=JSON] output

[MDEV-28869] Eliminated tables are not shown in EXPLAIN [FORMAT=JSON] output Created: 2022-06-16 Updated: 2023-11-28
Status:	In Review
Project:	MariaDB Server
Component/s:	None
Affects Version/s:	None
Fix Version/s:	10.11

Type:

Bug

Priority:

Major

Reporter:

Oleg Smirnov

Assignee:

Sergei Petrunia

Resolution:

Unresolved

Votes:

Labels:

None

Issue Links:

Relates
relates to	~~MDEV-26278~~	Table elimination does not work acros...	Closed

Description

Currently eliminated tables (both regular and derived) are not displayed in the

EXPLAIN/ANALYZE [FORMAT=JSON]

output at all. This can be confusing to the user. Those tables must be displayed in the following way:

+------+-------------+-------+------+---------------+------+---------+------+------+------------+

| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra      |

+------+-------------+-------+------+---------------+------+---------+------+------+------------+

|    1 | SIMPLE      | t1    | NULL | NULL          | NULL | NULL    | NULL | NULL | Eliminated |

+------+-------------+-------+------+---------------+------+---------+------+------+------------+

In case of FORMAT=JSON they must be displayed like this:

  "table": "t1"

  "Eliminated": true

Comments

Comment by Oleg Smirnov [ 2022-06-27 ]

The proposed format of the output is as follows. Suggestions/corrections are welcome.

----------------------------

-- REGULAR TABLES

----------------------------

create table t1 (a int);

insert into t1 values (0),(1),(2),(3);

create table t2 (a int primary key, b int)

  as select a, a as b from t1 where a in (1,2);

create table t3 (a int primary key, b int)

  as select a, a as b from t1 where a in (1,3);

-- Elimination of a single table

explain select t1.a from t1 left join t2 on t2.a=t1.a;

+------+-------------+-------+------+---------------+------+---------+------+------+------------+

| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra      |

+------+-------------+-------+------+---------------+------+---------+------+------+------------+

|    1 | SIMPLE      | t1    | ALL  | NULL          | NULL | NULL    | NULL | 2    |            |

| NULL | NULL        | t2    | NULL | NULL          | NULL | NULL    | NULL | NULL | Eliminated |

+------+-------------+-------+------+---------------+------+---------+------+------+------------+

2 rows in set (1.924 sec)

-- Elimination of multiple tables

explain select t1.a from t1 left join (t2 join t3) on t2.a=t1.a and t3.a=t1.a;

+------+-------------+-------+------+---------------+------+---------+------+------+------------+

| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra      |

+------+-------------+-------+------+---------------+------+---------+------+------+------------+

|    1 | SIMPLE      | t1    | ALL  | NULL          | NULL | NULL    | NULL | 2    |            |

| NULL | NULL        | t3    | NULL | NULL          | NULL | NULL    | NULL | NULL | Eliminated |

| NULL | NULL        | t2    | NULL | NULL          | NULL | NULL    | NULL | NULL | Eliminated |

+------+-------------+-------+------+---------------+------+---------+------+------+------------+

3 rows in set (1.626 sec)

----------------------------

-- DERIVED TABLES

----------------------------

create table t11 (a int not null, b int, key(a));

insert into t11 select A.seq, A.seq+B.seq from seq_1_to_10 A, seq_1_to_100 B;

create table t12 (pk int primary key,  col1 int);

insert into t12 select seq, seq from seq_1_to_1000;

-- Whole derived table is eliminated

explain select t1.* from t1 left join

  (select t11.a as a, count(*) as b

   from t11 left join t12 on t12.pk=t11.b

   group by t11.a) v2b on v2b.a=t1.a;

+------+-------------+------------+------+---------------+------+---------+------+------+------------+

| id   | select_type | table      | type | possible_keys | key  | key_len | ref  | rows | Extra      |

+------+-------------+------------+------+---------------+------+---------+------+------+------------+

|    1 | PRIMARY     | t1         | ALL  | NULL          | NULL | NULL    | NULL | 2    |            |

| NULL | NULL        | <derived2> | NULL | NULL          | NULL | NULL    | NULL | NULL | Eliminated |

| NULL | NULL        | t12        | NULL | NULL          | NULL | NULL    | NULL | NULL | Eliminated |

| NULL | NULL        | t11        | NULL | NULL          | NULL | NULL    | NULL | NULL | Eliminated |

+------+-------------+------------+------+---------------+------+---------+------+------+------------+

-- Only one table from the derived one is eliminated

explain select t1.* from t1 left join

  (select t11.a as a, count(*) as b

   from t11 left join t12 on t12.pk=t11.b

   group by t11.a) v2b on v2b.b=t1.a;

+------+-------------+------------+-------+---------------+------+---------+----------------+------+-------------+

| id   | select_type | table      | type  | possible_keys | key  | key_len | ref            | rows | Extra       |

+------+-------------+------------+-------+---------------+------+---------+----------------+------+-------------+

|    1 | PRIMARY     | t1         | ALL   | NULL          | NULL | NULL    | NULL           | 2    |             |

|    1 | PRIMARY     | <derived2> | ref   | key0          | key0 | 8       | mdev28869.t1.a | 10   | Using where |

|    2 | DERIVED     | t11        | index | NULL          | a    | 4       | NULL           | 1000 |             |

| NULL | NULL        | t12        | NULL  | NULL          | NULL | NULL    | NULL           | NULL | Eliminated  |

+------+-------------+------------+-------+---------------+------+---------+----------------+------+-------------+

Same queries explained in JSON format

-- Elimination of a single table

explain format=JSON select t1.a from t1 left join t2 on t2.a=t1.a;

| {

  "query_block": {

    "select_id": 1,

    "const_condition": "1",

    "nested_loop": [

        "table": {

          "table_name": "t1",

          "access_type": "ALL",

          "rows": 2,

          "filtered": 100

},

        "table": {

          "table_name": "t2",

          "eliminated": true

} |

-- Elimination of multiple tables

explain format=JSON select t1.a from t1 left join (t2 join t3) on t2.a=t1.a and t3.a=t1.a;

| {

  "query_block": {

    "select_id": 1,

    "const_condition": "1",

    "nested_loop": [

        "table": {

          "table_name": "t1",

          "access_type": "ALL",

          "rows": 2,

          "filtered": 100

},

        "table": {

          "table_name": "t3",

          "eliminated": true

},

        "table": {

          "table_name": "t2",

          "eliminated": true

} |

-- Whole derived table is eliminated (note that "eliminated" is printed instead of "materialized" for the derived table)

explain format=JSON select t1.* from t1 left join

  (select t11.a as a, count(*) as b

   from t11 left join t12 on t12.pk=t11.b

   group by t11.a) v2b on v2b.a=t1.a;

| {

  "query_block": {

    "select_id": 1,

    "const_condition": "1",

    "nested_loop": [

        "table": {

          "table_name": "t1",

          "access_type": "ALL",

          "rows": 2,

          "filtered": 100

},

        "table": {

          "table_name": "<derived2>",

          "eliminated": {

            "nested_loop": [

                "table": {

                  "table_name": "t12",

                  "eliminated": true

},

                "table": {

                  "table_name": "t11",

                  "eliminated": true

} |

-- Only one table from the derived one is eliminated

explain format=JSON select t1.* from t1 left join

  (select t11.a as a, count(*) as b

   from t11 left join t12 on t12.pk=t11.b

   group by t11.a) v2b on v2b.b=t1.a;

| {

  "query_block": {

    "select_id": 1,

    "const_condition": "1",

    "nested_loop": [

        "table": {

          "table_name": "t1",

          "access_type": "ALL",

          "rows": 2,

          "filtered": 100

},

        "table": {

          "table_name": "<derived2>",

          "access_type": "ref",

          "possible_keys": ["key0"],

          "key": "key0",

          "key_length": "8",

          "used_key_parts": ["b"],

          "ref": ["mdev28869.t1.a"],

          "rows": 10,

          "filtered": 100,

          "attached_condition": "trigcond(v2b.b = t1.a and trigcond(t1.a is not null))",

          "materialized": {

            "query_block": {

              "select_id": 2,

              "const_condition": "1",

              "nested_loop": [

                  "table": {

                    "table_name": "t11",

                    "access_type": "index",

                    "key": "a",

                    "key_length": "4",

                    "used_key_parts": ["a"],

                    "rows": 1000,

                    "filtered": 100

},

                  "table": {

                    "table_name": "t12",

                    "eliminated": true

} |

If the proposed format is OK I'll move on with updating test cases and cleaning up the code.

Comment by Sergei Petrunia [ 2022-06-28 ]

A more interesting question is what to show when the derived table is eliminated (~~MDEV-26278~~ makes it possible):

Consider this query:

explain

select t1.*

from

  t1 left join

  (select t11.a as a, count(*) as b

   from

      t11 left join t12 on t12.pk=t11.b

   group by

     t11.a

   ) as TBL on TBL.a=t1.a;

After ~~MDEV-26278~~ in 10.10, it prints this:

+------+-------------+------------+-------+---------------+------+---------+-----------+------+-------------+

| id   | select_type | table      | type  | possible_keys | key  | key_len | ref       | rows | Extra       |

+------+-------------+------------+-------+---------------+------+---------+-----------+------+-------------+

|    1 | PRIMARY     | t1         | ALL   | NULL          | NULL | NULL    | NULL      | 10   |             |

+------+-------------+------------+-------+---------------+------+---------+-----------+------+-------------+

The question is should it print this:

+------+-------------+------------+-------+---------------+------+---------+-----------+------+-------------+

| id   | select_type | table      | type  | possible_keys | key  | key_len | ref       | rows | Extra       |

+------+-------------+------------+-------+---------------+------+---------+-----------+------+-------------+

|    1 | PRIMARY     | t1         | ALL   | NULL          | NULL | NULL    | NULL      | 10   |             |

|    1 | PRIMARY     | <derived2> | NULL  | NULL          | NULL | NULL    | NULL      | NULL | Eliminated  |

+------+-------------+------------+-------+---------------+------+---------+-----------+------+-------------+

Or also print the subquery contents:

+------+-------------+------------+-------+---------------+------+---------+-----------+------+-------------+

| id   | select_type | table      | type  | possible_keys | key  | key_len | ref       | rows | Extra       |

+------+-------------+------------+-------+---------------+------+---------+-----------+------+-------------+

|    1 | PRIMARY     | t1         | ALL   | NULL          | NULL | NULL    | NULL      | 10   |             |

|    1 | PRIMARY     | <derived2> | NULL  | NULL          | NULL | NULL    | NULL      | NULL | Eliminated  |

|    2 | DERIVED     | t11        | index | NULL          | a    | 4       | NULL      | 1000 |             |

+------+-------------+------------+-------+---------------+------+---------+-----------+------+-------------+

Comment by Sergei Petrunia [ 2022-06-28 ]

... and re-reading the first comment by oleg.smirnov, I see that it's neither of the above. It is (copy-pasting):

-- Whole derived table is eliminated

explain select t1.* from t1 left join

  (select t11.a as a, count(*) as b

   from t11 left join t12 on t12.pk=t11.b

   group by t11.a) v2b on v2b.a=t1.a;

+------+-------------+------------+------+---------------+------+---------+------+------+------------+

| id   | select_type | table      | type | possible_keys | key  | key_len | ref  | rows | Extra      |

+------+-------------+------------+------+---------------+------+---------+------+------+------------+

|    1 | PRIMARY     | t1         | ALL  | NULL          | NULL | NULL    | NULL | 2    |            |

| NULL | NULL        | <derived2> | NULL | NULL          | NULL | NULL    | NULL | NULL | Eliminated |

| NULL | NULL        | t12        | NULL | NULL          | NULL | NULL    | NULL | NULL | Eliminated |

| NULL | NULL        | t11        | NULL | NULL          | NULL | NULL    | NULL | NULL | Eliminated |

+------+-------------+------------+------+---------------+------+---------+------+------+------------+

That is, the subquery is printed but we can only see "Eliminated" for all tables in it.

Comment by Oleg Smirnov [ 2022-06-30 ]

After discussion we decided that the output must look like this.

MariaDB [mdev28869]> explain select t1.a from t1 left join t2 on t2.a=t1.a;

+------+-------------+-------+------------+---------------+------+---------+------+------+-------+

| id   | select_type | table | type       | possible_keys | key  | key_len | ref  | rows | Extra |

+------+-------------+-------+------------+---------------+------+---------+------+------+-------+

|    1 | SIMPLE      | t1    | ALL        | NULL          | NULL | NULL    | NULL | 2    |       |

|    1 | SIMPLE      | t2    | Eliminated | NULL          | NULL | NULL    | NULL | NULL | NULL  |

+------+-------------+-------+------------+---------------+------+---------+------+------+-------+

2 rows in set (0.028 sec)

MariaDB [mdev28869]> explain select t1.* from t1 left join

    ->   (select t11.a as a, count(*) as b

    ->    from t11 left join t12 on t12.pk=t11.b

    ->    group by t11.a) v2b on v2b.a=t1.a;

+------+-------------+------------+------------+---------------+------+---------+------+------+-------+

| id   | select_type | table      | type       | possible_keys | key  | key_len | ref  | rows | Extra |

+------+-------------+------------+------------+---------------+------+---------+------+------+-------+

|    1 | PRIMARY     | t1         | ALL        | NULL          | NULL | NULL    | NULL | 2    |       |

|    1 | PRIMARY     | <derived2> | Eliminated | NULL          | NULL | NULL    | NULL | NULL |       |

|    2 | DERIVED     | t12        | Eliminated | NULL          | NULL | NULL    | NULL | NULL |       |

|    2 | DERIVED     | t11        | Eliminated | NULL          | NULL | NULL    | NULL | NULL |       |

+------+-------------+------------+------------+---------------+------+---------+------+------+-------+

MariaDB [mdev28869]> explain format=JSON select t1.a from t1 left join t2 on t2.a=t1.a;

| {

  "query_block": {

    "select_id": 1,

    "const_condition": "1",

    "nested_loop": [

        "table": {

          "table_name": "t1",

          "access_type": "ALL",

          "rows": 2,

          "filtered": 100

},

        "table": {

          "table_name": "t2",

          "access_type": "Eliminated"

} |

MariaDB [mdev28869]> explain format=JSON select t1.* from t1

    left join

    (select t11.a as a, count(*) as b

    from t11 left join t12 on t12.pk=t11.b group by t11.a) v2b on v2b.a=t1.a;

| {

  "query_block": {

    "select_id": 1,

    "const_condition": "1",

    "nested_loop": [

        "table": {

          "table_name": "t1",

          "access_type": "ALL",

          "rows": 2,

          "filtered": 100

},

        "table": {

          "table_name": "<derived2>",

          "access_type": "Eliminated",

          "materialized": {

            "query_block": {

              "select_id": 2,

              "eliminated": true,

              "nested_loop": [

                  "table": {

                    "table_name": "t12",

                    "access_type": "Eliminated"

},

                  "table": {

                    "table_name": "t11",

                    "access_type": "Eliminated"

} |

Generated at Thu Feb 08 10:04:05 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.

[MDEV-28869] Eliminated tables are not shown in EXPLAIN [FORMAT=JSON] output Created: 2022-06-16 Updated: 2023-11-28