[#MDEV-12359] TPC-C query regression using 'derived

[MDEV-12359] TPC-C query regression using 'derived_merge=on' Created: 2017-03-24 Updated: 2017-05-11 Resolved: 2017-05-11
Status:	Closed
Project:	MariaDB Server
Component/s:	Optimizer
Affects Version/s:	5.5, 10.0, 10.1, 10.1.22, 10.2
Fix Version/s:	5.5.57

Type:

Bug

Priority:

Major

Reporter:

VAROQUI Stephane

Assignee:

Igor Babaev

Resolution:

Fixed

Votes:

Labels:

None

Attachments:

mdev12359.dump

Description

explain select count(*) from(   SELECT   s_w_id, s_i_id, s_quantity         FROM bmsql_stock         WHERE s_w_id = 3 AND s_quantity < 16 AND s_i_id IN (            SELECT ol_i_id                 FROM bmsql_district                 JOIN bmsql_order_line ON ol_w_id = d_w_id                  AND ol_d_id = d_id                  AND ol_o_id >= d_next_o_id - 20                  AND ol_o_id < d_next_o_id                 WHERE d_w_id = 3 AND d_id = 8  )) as L;

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

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

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

|    1 | PRIMARY      | bmsql_stock      | ref   | PRIMARY       | PRIMARY | 4       | const       | 187638 | Using where |

|    3 | MATERIALIZED | bmsql_district   | const | PRIMARY       | PRIMARY | 8       | const,const |      1 |             |

|    3 | MATERIALIZED | bmsql_order_line | range | PRIMARY       | PRIMARY | 12      | NULL        |    191 | Using where |

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

if only running the inner query it gives without outer count

SELECT s_w_id, s_i_id, s_quantity         FROM bmsql_stock         WHERE s_w_id = 3 AND s_quantity < 16 AND s_i_id IN (            SELECT ol_i_id                 FROM bmsql_district                 JOIN bmsql_order_line ON ol_w_id = d_w_id                  AND ol_d_id = d_id                  AND ol_o_id >= d_next_o_id - 20                  AND ol_o_id < d_next_o_id                 WHERE d_w_id = 3 AND d_id = 8         )   ;

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

| s_w_id | s_i_id | s_quantity |

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

|      3 |  16009 |         14 |

|      3 |  16185 |         15 |

|      3 |  26154 |         12 |

|      3 |  36457 |         12 |

|      3 |  36490 |         15 |

|      3 |  42634 |         12 |

|      3 |  55333 |         13 |

|      3 |  76808 |         14 |

|      3 |  76918 |         13 |

|      3 |  76937 |         15 |

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

10 rows in set (0.00 sec)

now i just count this and the plan

[+------+--------------+------------------+-------+---------------+---------+---------+-------------+--------+-------------+

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

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

|    1 | PRIMARY      | bmsql_stock      | ref   | PRIMARY       | PRIMARY | 4       | const       | 187638 | Using where |

|    3 | MATERIALIZED | bmsql_district   | const | PRIMARY       | PRIMARY | 8       | const,const |      1 |             |

|    3 | MATERIALIZED | bmsql_order_line | range | PRIMARY       | PRIMARY | 12      | NULL        |    191 | Using where |

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

now disabling derived_merge goes back to regular plan
Is this expected that there is no computation of the materialization result in such case ?

set optimizer_switch='derived_merge=off';

Query OK, 0 rows affected, 1 warning (0.00 sec)

MariaDB [sabre]> explain select count(*) from(   SELECT   s_w_id, s_i_id, s_quantity         FROM bmsql_stock         WHERE s_w_id = 3 AND s_quantity < 16 AND s_i_id IN (            SELECT ol_i_id                 FROM bmsql_district                 JOIN bmsql_order_line ON ol_w_id = d_w_id                  AND ol_d_id = d_id                  AND ol_o_id >= d_next_o_id - 20                  AND ol_o_id < d_next_o_id                 WHERE d_w_id = 3 AND d_id = 8  )) as L;

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

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

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

|    1 | PRIMARY     | <derived2>       | ALL    | NULL                | NULL    | NULL    | NULL                                 |  191 |                                                                                  |

|    2 | DERIVED     | bmsql_district   | const  | PRIMARY             | PRIMARY | 8       | const,const                          |    1 |                                                                                  |

|    2 | DERIVED     | bmsql_order_line | range  | PRIMARY             | PRIMARY | 12      | NULL                                 |  191 | Using where; Start temporary                                                     |

|    2 | DERIVED     | bmsql_stock      | eq_ref | PRIMARY,s_item_fkey | PRIMARY | 8       | const,sabre.bmsql_order_line.ol_i_id |    1 | Using where; End temporary; Using join buffer (flat, BKA join); Key-ordered scan |

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

4 rows in set (0.01 sec)

Comments

Comment by Elena Stepanova [ 2017-05-01 ]

I've attached a data dump which allows to reproduce the described problem. The data is completely artificial, but the structures are taken from some benchmarksql I found online, I assume they're the same.

MariaDB [test]> source /data/tmp/mdev12359.dump

...

Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> analyze table bmsql_stock, bmsql_district, bmsql_order_line;

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

| Table                 | Op      | Msg_type | Msg_text |

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

| test.bmsql_stock      | analyze | status   | OK       |

| test.bmsql_district   | analyze | status   | OK       |

| test.bmsql_order_line | analyze | status   | OK       |

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

3 rows in set (0.32 sec)

MariaDB [test]> explain select count(*) from(

    ->    SELECT   s_w_id, s_i_id, s_quantity

    ->        FROM bmsql_stock

    ->        WHERE s_w_id = 3 AND s_quantity < 16 AND s_i_id IN (

    ->             SELECT ol_i_id

    ->                  FROM bmsql_district

    ->                  JOIN bmsql_order_line ON ol_w_id = d_w_id

    ->                   AND ol_d_id = d_id

    ->                   AND ol_o_id >= d_next_o_id - 20

    ->                   AND ol_o_id < d_next_o_id

    ->                  WHERE d_w_id = 3 AND d_id = 8

    ->   )) as L;

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

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

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

|    1 | PRIMARY      | bmsql_stock      | ref   | PRIMARY       | PRIMARY | 4       | const       |  274 | Using where |

|    3 | MATERIALIZED | bmsql_district   | const | PRIMARY       | PRIMARY | 8       | const,const |    1 |             |

|    3 | MATERIALIZED | bmsql_order_line | range | PRIMARY       | PRIMARY | 12      | NULL        |    1 | Using where |

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

3 rows in set (0.01 sec)

MariaDB [test]> set optimizer_switch='derived_merge=off';

Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> explain select count(*) from(

    ->    SELECT   s_w_id, s_i_id, s_quantity

    ->        FROM bmsql_stock

    ->        WHERE s_w_id = 3 AND s_quantity < 16 AND s_i_id IN (

    ->             SELECT ol_i_id

    ->                  FROM bmsql_district

    ->                  JOIN bmsql_order_line ON ol_w_id = d_w_id

    ->                   AND ol_d_id = d_id

    ->                   AND ol_o_id >= d_next_o_id - 20

    ->                   AND ol_o_id < d_next_o_id

    ->                  WHERE d_w_id = 3 AND d_id = 8

    ->   )) as L;

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

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

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

|    1 | PRIMARY     | <derived2>       | ALL    | NULL          | NULL    | NULL    | NULL                                |    2 |                              |

|    2 | DERIVED     | bmsql_district   | const  | PRIMARY       | PRIMARY | 8       | const,const                         |    1 |                              |

|    2 | DERIVED     | bmsql_order_line | range  | PRIMARY       | PRIMARY | 12      | NULL                                |    1 | Using where; Start temporary |

|    2 | DERIVED     | bmsql_stock      | eq_ref | PRIMARY       | PRIMARY | 8       | const,test.bmsql_order_line.ol_i_id |    1 | Using where; End temporary   |

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

4 rows in set (0.00 sec)

Comment by Igor Babaev [ 2017-05-09 ]

Elena,
Where is the regression here? Both conversion to semi-joins and merge of derived tables were introduced in 5.3.

Comment by Elena Stepanova [ 2017-05-09 ]

igor,

I think by "regression" stephane@skysql.com meant that the query behaves worse with (default) derived_merge=on than without it, not that there is a difference between versions; but I'll leave it to him to clarify that.

Comment by Igor Babaev [ 2017-05-11 ]

After the fix for the bugs mdev-12670, mdev-12675 we have in 5.5:

MariaDB [test]> explain

    -> SELECT COUNT(*) FROM (

    -> SELECT   s_w_id, s_i_id, s_quantity

    -> FROM bmsql_stock

    -> WHERE s_w_id = 3 AND s_quantity < 16 AND

    ->       s_i_id IN ( SELECT ol_i_id

    ->                   FROM bmsql_district

    ->                   JOIN bmsql_order_line

    ->                   ON ol_w_id = d_w_id

    ->                      AND ol_d_id = d_id

    ->                      AND ol_o_id >= d_next_o_id - 20

    ->                      AND ol_o_id < d_next_o_id

    ->                   WHERE d_w_id = 3 AND d_id = 8

    ->                 )

    -> ) as L;

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

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

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

|    1 | SIMPLE      | bmsql_district   | const  | PRIMARY       | PRIMARY | 8       | const,const                         |    1 |                              |

|    1 | SIMPLE      | bmsql_order_line | range  | PRIMARY       | PRIMARY | 12      | NULL                                |    1 | Using where; Start temporary |

|    1 | SIMPLE      | bmsql_stock      | eq_ref | PRIMARY       | PRIMARY | 8       | const,test.bmsql_order_line.ol_i_id |    1 | Using where; End temporary   |

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

MariaDB [test]> flush status;

MariaDB [test]> SELECT COUNT(*) FROM (

    -> SELECT   s_w_id, s_i_id, s_quantity

    -> FROM bmsql_stock

    -> WHERE s_w_id = 3 AND s_quantity < 16 AND

    ->       s_i_id IN ( SELECT ol_i_id

    ->                   FROM bmsql_district

    ->                   JOIN bmsql_order_line

    ->                   ON ol_w_id = d_w_id

    ->                      AND ol_d_id = d_id

    ->                      AND ol_o_id >= d_next_o_id - 20

    ->                      AND ol_o_id < d_next_o_id

    ->                   WHERE d_w_id = 3 AND d_id = 8

    ->                 )

    -> ) as L;

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

| COUNT(*) |

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

|        0 |

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

MariaDB [test]> show status like '%Handler_read%';

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

| Variable_name            | Value |

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

| Handler_read_first       | 0     |

| Handler_read_key         | 2     |

| Handler_read_last        | 0     |

| Handler_read_next        | 0     |

| Handler_read_prev        | 0     |

| Handler_read_rnd         | 0     |

| Handler_read_rnd_deleted | 0     |

| Handler_read_rnd_next    | 0     |

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

Now I turn off derived_merge and see that the execution plan for the SELECT specifying the derived table L remains the same:

MariaDB [test]> set optimizer_switch='derived_merge=off';

MariaDB [test]> explain

    -> SELECT COUNT(*) FROM (

    -> SELECT   s_w_id, s_i_id, s_quantity

    -> FROM bmsql_stock

    -> WHERE s_w_id = 3 AND s_quantity < 16 AND

    ->       s_i_id IN ( SELECT ol_i_id

    ->                   FROM bmsql_district

    ->                   JOIN bmsql_order_line

    ->                   ON ol_w_id = d_w_id

    ->                      AND ol_d_id = d_id

    ->                      AND ol_o_id >= d_next_o_id - 20

    ->                      AND ol_o_id < d_next_o_id

    ->                   WHERE d_w_id = 3 AND d_id = 8

    ->                 )

    -> ) as L;

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

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

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

|    1 | PRIMARY     | <derived2>       | ALL    | NULL          | NULL    | NULL    | NULL                                |    2 |                              |

|    2 | DERIVED     | bmsql_district   | const  | PRIMARY       | PRIMARY | 8       | const,const                         |    1 |                              |

|    2 | DERIVED     | bmsql_order_line | range  | PRIMARY       | PRIMARY | 12      | NULL                                |    1 | Using where; Start temporary |

|    2 | DERIVED     | bmsql_stock      | eq_ref | PRIMARY       | PRIMARY | 8       | const,test.bmsql_order_line.ol_i_id |    1 | Using where; End temporary   |

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

MariaDB [test]> flush status;

MariaDB [test]> SELECT COUNT(*) FROM (

    -> SELECT   s_w_id, s_i_id, s_quantity

    -> FROM bmsql_stock

    -> WHERE s_w_id = 3 AND s_quantity < 16 AND

    ->       s_i_id IN ( SELECT ol_i_id

    ->                   FROM bmsql_district

    ->                   JOIN bmsql_order_line

    ->                   ON ol_w_id = d_w_id

    ->                      AND ol_d_id = d_id

    ->                      AND ol_o_id >= d_next_o_id - 20

    ->                      AND ol_o_id < d_next_o_id

    ->                   WHERE d_w_id = 3 AND d_id = 8

    ->                 )

    -> ) as L;

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

| COUNT(*) |

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

|        0 |

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

MariaDB [test]> show status like '%Handler_read%';

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

| Variable_name            | Value |

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

| Handler_read_first       | 0     |

| Handler_read_key         | 2     |

| Handler_read_last        | 0     |

| Handler_read_next        | 0     |

| Handler_read_prev        | 0     |

| Handler_read_rnd         | 0     |

| Handler_read_rnd_deleted | 0     |

| Handler_read_rnd_next    | 1     |

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

To simulate the behaviour before the patch for the bug mdev-12670 I have to turn the flag 'semijoin' off:

MariaDB [test]> set optimizer_switch=default;

MariaDB [test]> set optimizer_switch='semijoin=off';

MariaDB [test]> explain

    -> SELECT COUNT(*) FROM (

    -> SELECT   s_w_id, s_i_id, s_quantity

    -> FROM bmsql_stock

    -> WHERE s_w_id = 3 AND s_quantity < 16 AND

    ->       s_i_id IN ( SELECT ol_i_id

    ->                   FROM bmsql_district

    ->                   JOIN bmsql_order_line

    ->                   ON ol_w_id = d_w_id

    ->                      AND ol_d_id = d_id

    ->                      AND ol_o_id >= d_next_o_id - 20

    ->                      AND ol_o_id < d_next_o_id

    ->                   WHERE d_w_id = 3 AND d_id = 8

    ->                 )

    -> ) as L;

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

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

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

|    1 | SIMPLE       | bmsql_stock      | ref   | PRIMARY       | PRIMARY | 4       | const       |  273 | Using where |

|    3 | MATERIALIZED | bmsql_district   | const | PRIMARY       | PRIMARY | 8       | const,const |    1 |             |

|    3 | MATERIALIZED | bmsql_order_line | range | PRIMARY       | PRIMARY | 12      | NULL        |    1 | Using where |

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

MariaDB [test]> flush status;

MariaDB [test]> SELECT COUNT(*) FROM (

    -> SELECT   s_w_id, s_i_id, s_quantity

    -> FROM bmsql_stock

    -> WHERE s_w_id = 3 AND s_quantity < 16 AND

    ->       s_i_id IN ( SELECT ol_i_id

    ->                   FROM bmsql_district

    ->                   JOIN bmsql_order_line

    ->                   ON ol_w_id = d_w_id

    ->                      AND ol_d_id = d_id

    ->                      AND ol_o_id >= d_next_o_id - 20

    ->                      AND ol_o_id < d_next_o_id

    ->                   WHERE d_w_id = 3 AND d_id = 8

    ->                 )

    -> ) as L;

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

| COUNT(*) |

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

|        0 |

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

MariaDB [test]> show status like '%Handler_read%';

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

| Variable_name            | Value |

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

| Handler_read_first       | 0     |

| Handler_read_key         | 3     |

| Handler_read_last        | 0     |

| Handler_read_next        | 274   |

| Handler_read_prev        | 0     |

| Handler_read_rnd         | 0     |

| Handler_read_rnd_deleted | 0     |

| Handler_read_rnd_next    | 0     |

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

So I close this mdev as resolved by the patch for the bug mdev-12670.

Generated at Thu Feb 08 07:57:06 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.

[MDEV-12359] TPC-C query regression using 'derived_merge=on' Created: 2017-03-24 Updated: 2017-05-11 Resolved: 2017-05-11