[MDEV-30878] Weird optimizer trace content for the LATERAL DERIVED table/access path - Jira

Details

Type: Bug
Status: Confirmed (View Workflow)
Priority: Critical
Resolution: Unresolved
Affects Version/s: 10.4.28
Fix Version/s: 10.5, 10.6
Component/s: Optimizer
Labels:
- split_materialized

Description

In the optimizer trace from the related ~~MDEV-30877~~ we can see that the following query:

MariaDB [(none)]> analyze

    -> SELECT

    ->     esd.esd_c1,

    ->     esd.esd_c2,

    ->     esd.esd_c3,

    ->     es.es_c1,

    ->     es.es_c2,

    ->     es.es_c3,

    ->     es.es_c4,

    ->     es.es_c5,

    ->     es.es_c6,

    ->     es.es_c7,

    ->     es.es_c8

    -> FROM

    ->     DB1.t1 esd

    ->         INNER JOIN

    ->     DB1.t2 es ON esd.t1Id = es.t1Id

    ->         INNER JOIN

    ->     (SELECT

    ->         esd.esd_c1, MAX(es.es_c1) AS last_set

    ->     FROM

    ->         DB1.t1 esd

    ->     INNER JOIN DB1.t2 es ON esd.t1Id = es.t1Id

    ->     GROUP BY esd.esd_c1) q ON esd.esd_c1 = q.esd_c1

    -> WHERE

    ->     es.es_c1 = q.last_set;

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

| id   | select_type     | table      | type  | possible_keys             | key       | key_len | ref                                          | rows  | r_rows   | filtered | r_filtered | Extra                    |

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

|    1 | PRIMARY         | esd        | index | PRIMARY,UNIQUEMSG,Index_3 | UNIQUEMSG | 137     | NULL                                         | 15197 | 15197.00 |   100.00 |     100.00 | Using where; Using index |

|    1 | PRIMARY         | <derived2> | ref   | key0                      | key0      | 33      | DB1.esd.esd_c1                          | 2     | 0.98     |   100.00 |     100.00 | Using where              |

|    1 | PRIMARY         | es         | ref   | PRIMARY                   | PRIMARY   | 9       | DB1.esd.t1Id,q.last_set | 157   | 21.26    |   100.00 |     100.00 |                          |

|    2 | LATERAL DERIVED | esd        | ref   | PRIMARY,UNIQUEMSG,Index_3 | UNIQUEMSG | 33      | DB1.esd.esd_c1                          | 7     | 17.40    |   100.00 |     100.00 | Using index              |

|    2 | LATERAL DERIVED | es         | ref   | PRIMARY                   | PRIMARY   | 4       | DB1.esd.t1Id            | 875   | 783.76   |   100.00 |     100.00 | Using index              |

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

5 rows in set (1 min 12.157 sec)

We see many entries like these:

...

        "steps": [

            "join_execution": {

              "select_id": 2,

              "steps": []

},

            "join_execution": {

              "select_id": 2,

              "steps": []

},

            "join_execution": {

              "select_id": 2,

              "steps": []

},

            "join_execution": {

              "select_id": 2,

              "steps": []

},

...

that do not make much sense, do not add much value but lead to a huge optimizer trace. I think such output is a bug and should be changed to something more useful.

Attachments

- Sort By Name
- Sort By Date
- Ascending
- Descending
- Thumbnails
- List
- Download All

optimizer_trace_mangled.txt
1.87 MB
2023-03-19 15:23

Issue Links

relates to

MDEV-26300 Optimizer trace coverage of LATERAL DERIVED is insufficient

Confirmed

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

Closed

Activity

Valerii Kravchuk added a comment - 2024-05-24 17:03

Surely it is repeatable. Any query with LATERAL DERIVED in the plan will give you such weird and IMHO useless output in the optimizer_trace. For example:

Yuliyas-Air:mysql-test Valerii$ cat main/MDEV-30878.test

--source include/have_innodb.inc

--source include/default_optimizer_switch.inc

--source include/have_sequence.inc

SET @save_innodb_stats_persistent=@@GLOBAL.innodb_stats_persistent;

SET GLOBAL innodb_stats_persistent=0;

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');

insert into t1 select seq+1,seq+2,'c' from seq_1_to_1000;

ANALYZE TABLE t1;

Let $q=

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;

eval EXPLAIN $q;

set optimizer_trace="enabled=on";

eval $q;

select * from information_schema.OPTIMIZER_TRACE;

set optimizer_trace="enabled=off";

DROP TABLE t1;

If we put it into the proper directory and run with mtr:

Yuliyas-Air:mysql-test Valerii$ ./mtr MDEV-30878.test

Logging: ./mtr  MDEV-30878.test

VS config:

vardir: /Users/Valerii/dbs/maria10.6/mysql-test/var

Checking leftover processes...

Removing old var directory...

Creating var directory '/Users/Valerii/dbs/maria10.6/mysql-test/var'...

Checking supported features...

MariaDB Version 10.6.19-MariaDB

 - SSL connections supported

 - binaries built with wsrep patch

Collecting tests...

Installing system database...

==============================================================================

TEST                                      RESULT   TIME (ms) or COMMENT

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

worker[01] Using MTR_BUILD_THREAD 300, with reserved ports 16000..16019

SET @save_innodb_stats_persistent=@@GLOBAL.innodb_stats_persistent;

SET GLOBAL innodb_stats_persistent=0;

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');

insert into t1 select seq+1,seq+2,'c' from seq_1_to_1000;

ANALYZE TABLE t1;

Table	Op	Msg_type	Msg_text

test.t1	analyze	status	Engine-independent statistics collected

test.t1	analyze	status	OK

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	ref	c1,n1_c1_n2	c1	1	const	2	Using index condition; Using where; Using temporary; Using filesort

1	PRIMARY	<derived2>	ref	key0	key0	8	test.t1.n1,test.t1.n2	2

2	LATERAL DERIVED	t1	ref	c1,n1_c1_n2	n1_c1_n2	4	test.t1.n1	1	Using where; Using index

set optimizer_trace="enabled=on";

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;

n1

select * from information_schema.OPTIMIZER_TRACE;

QUERY	TRACE	MISSING_BYTES_BEYOND_MAX_MEM_SIZE	INSUFFICIENT_PRIVILEGES

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	{

  "steps": [

      "join_preparation": {

        "select_id": 1,

        "steps": [

            "derived": {

              "table": "t",

              "select_id": 2,

              "algorithm": "materialized"

},

...

      "join_execution": {

        "select_id": 1,

        "steps": [

            "join_execution": {

              "select_id": 2,

              "steps": []

},

            "join_execution": {

              "select_id": 2,

              "steps": []

}	0	0

set optimizer_trace="enabled=off";

DROP TABLE t1;

main.MDEV-30878 'innodb'                 [ pass ]     93

...

we can see the output at the end of the trace.

Valerii Kravchuk added a comment - 2024-05-24 17:03 Surely it is repeatable. Any query with LATERAL DERIVED in the plan will give you such weird and IMHO useless output in the optimizer_trace. For example: Yuliyas-Air:mysql-test Valerii$ cat main/MDEV-30878.test --source include/have_innodb.inc --source include/default_optimizer_switch.inc --source include/have_sequence.inc SET @save_innodb_stats_persistent=@@GLOBAL.innodb_stats_persistent; SET GLOBAL innodb_stats_persistent=0; 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'); insert into t1 select seq+1,seq+2,'c' from seq_1_to_1000; ANALYZE TABLE t1; Let $q= 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; eval EXPLAIN $q; set optimizer_trace="enabled=on"; eval $q; select * from information_schema.OPTIMIZER_TRACE; set optimizer_trace="enabled=off"; DROP TABLE t1; If we put it into the proper directory and run with mtr: Yuliyas-Air:mysql-test Valerii$ ./mtr MDEV-30878.test Logging: ./mtr MDEV-30878.test VS config: vardir: /Users/Valerii/dbs/maria10.6/mysql-test/var Checking leftover processes... Removing old var directory... Creating var directory '/Users/Valerii/dbs/maria10.6/mysql-test/var'... Checking supported features... MariaDB Version 10.6.19-MariaDB - SSL connections supported - binaries built with wsrep patch Collecting tests... Installing system database... ============================================================================== TEST RESULT TIME (ms) or COMMENT -------------------------------------------------------------------------- worker[01] Using MTR_BUILD_THREAD 300, with reserved ports 16000..16019 SET @save_innodb_stats_persistent=@@GLOBAL.innodb_stats_persistent; SET GLOBAL innodb_stats_persistent=0; 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'); insert into t1 select seq+1,seq+2,'c' from seq_1_to_1000; ANALYZE TABLE t1; Table Op Msg_type Msg_text test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK 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 ref c1,n1_c1_n2 c1 1 const 2 Using index condition; Using where; Using temporary; Using filesort 1 PRIMARY <derived2> ref key0 key0 8 test.t1.n1,test.t1.n2 2 2 LATERAL DERIVED t1 ref c1,n1_c1_n2 n1_c1_n2 4 test.t1.n1 1 Using where; Using index set optimizer_trace="enabled=on"; 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; n1 0 1 select * from information_schema.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES 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 { "steps": [ { "join_preparation": { "select_id": 1, "steps": [ { "derived": { "table": "t", "select_id": 2, "algorithm": "materialized" } }, ... { "join_execution": { "select_id": 1, "steps": [ { "join_execution": { "select_id": 2, "steps": [] } }, { "join_execution": { "select_id": 2, "steps": [] } } ] } } ] } 0 0 set optimizer_trace="enabled=off"; DROP TABLE t1; main.MDEV-30878 'innodb' [ pass ] 93 ... we can see the output at the end of the trace.

MariaDB Server

Weird optimizer trace content for the LATERAL DERIVED table/access path

Details

Description

Attachments

Attachments

Issue Links

Activity

People

Dates

Git Integration