Serious performance regression with default settings for Q20 from DBT-3

[Igor Babaev]

When Q20 is executed from DBT-3 with default settings a serious performance regression can be seen in mariadb-5.3 in comparison with mariadb-5.1/5.2 or mysql-5.1.

for a myisam scale factor 10 DBT-3 database I had the following execution time:
for mariadb-5.1/5.2 ~ 3 hrs
for mariadb-5.3 ~ 11 hrs.

With mariadb-5.1/5.2 the query execution plan was:

MariaDB [dbt3x10_myisam]> explain
    -> select sql_calc_found_rows
    -> s_name, s_address
    -> from supplier, nation
    -> where s_suppkey in (select ps_suppkey from partsupp
    -> where ps_partkey in (select p_partkey from part
    -> where p_name like 'forest%')
    -> and ps_availqty >
    -> (select 0.5 * sum(l_quantity)
    -> from lineitem
    -> where l_partkey = ps_partkey
    -> and l_suppkey = ps_suppkey
    -> and l_shipdate >= date('1994-01-01')
    -> and l_shipdate < date('1994-01-01') +
    -> interval '1' year ))
    -> and s_nationkey = n_nationkey
    -> and n_name = 'CANADA'
    -> order by s_name
    -> limit 10;
+----+--------------------+----------+-----------------+--------------------------------------+--------------+---------+-------------------------------------+--------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+----------+-----------------+--------------------------------------+--------------+---------+-------------------------------------+--------+-----------------------------+
| 1 | PRIMARY | supplier | ALL | i_s_nationkey | NULL | NULL | NULL | 100000 | Using where; Using filesort |
| 1 | PRIMARY | nation | eq_ref | PRIMARY | PRIMARY | 4 | dbt3x10_myisam.supplier.s_nationkey | 1 | Using where |
| 2 | DEPENDENT SUBQUERY | partsupp | index_subquery | i_ps_suppkey | i_ps_suppkey | 4 | func | 80 | Using where |
| 4 | DEPENDENT SUBQUERY | lineitem | ref | i_l_shipdate,i_l_partkey,i_l_suppkey | i_l_partkey | 5 | dbt3x10_myisam.partsupp.ps_partkey | 30 | Using where |
| 3 | DEPENDENT SUBQUERY | part | unique_subquery | PRIMARY | PRIMARY | 4 | func | 1 | Using where |
+----+--------------------+----------+-----------------+--------------------------------------+--------------+---------+-------------------------------------+--------+-----------------------------+

The same plan was chosen in 5.3 with settings:
MariaDB [dbt3x10_myisam]> set optimizer_switch='semijoin=off';
Query OK, 0 rows affected (0.00 sec)
MariaDB [dbt3x10_myisam]> set optimizer_switch='materialization=off';
Query OK, 0 rows affected (0.00 sec)

With default settings for 5.3 (optimizer_switch='semijoin=on,materailization=on') I had the following execution plan:

MariaDB [dbt3x10_myisam]> explain
    -> select sql_calc_found_rows
    -> s_name, s_address
    -> from supplier, nation
    -> where s_suppkey in (select ps_suppkey from partsupp
    -> where ps_partkey in (select p_partkey from part
    -> where p_name like 'forest%')
    -> and ps_availqty >
    -> (select 0.5 * sum(l_quantity)
    -> from lineitem
    -> where l_partkey = ps_partkey
    -> and l_suppkey = ps_suppkey
    -> and l_shipdate >= date('1994-01-01')
    -> and l_shipdate < date('1994-01-01') +
    -> interval '1' year ))
    -> and s_nationkey = n_nationkey
    -> and n_name = 'CANADA'
    -> order by s_name
    -> limit 10;
+----+--------------------+----------+--------+--------------------------------------+---------------+---------+------------------------------------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+----------+--------+--------------------------------------+---------------+---------+------------------------------------+------+----------------------------------------------+
| 1 | PRIMARY | nation | ALL | PRIMARY | NULL | NULL | NULL | 25 | Using where; Using temporary; Using filesort |
| 1 | PRIMARY | supplier | ref | PRIMARY,i_s_nationkey | i_s_nationkey | 5 | dbt3x10_myisam.nation.n_nationkey | 4000 | |
| 1 | PRIMARY | partsupp | ref | PRIMARY,i_ps_partkey,i_ps_suppkey | i_ps_suppkey | 4 | dbt3x10_myisam.supplier.s_suppkey | 80 | Using where |
| 1 | PRIMARY | part | eq_ref | PRIMARY | PRIMARY | 4 | dbt3x10_myisam.partsupp.ps_partkey | 1 | Using where; FirstMatch(supplier) |
| 4 | DEPENDENT SUBQUERY | lineitem | ref | i_l_shipdate,i_l_partkey,i_l_suppkey | i_l_partkey | 5 | dbt3x10_myisam.partsupp.ps_partkey | 30 | Using where |
+----+--------------------+----------+--------+--------------------------------------+---------------+---------+------------------------------------+------+--------------------------------------------

With the setting optimizer_switch='semijoin=off,materailization=on' the execution plan is the same
with mariadb-5.1/5.3

MariaDB [dbt3x10_myisam]> set optimizer_switch='semijoin=off';
Query OK, 0 rows affected (0.01 sec)

MariaDB [dbt3x10_myisam]> set optimizer_switch='materialization=on';
Query OK, 0 rows affected (0.00 sec)

MariaDB [dbt3x10_myisam]> explain
    -> select sql_calc_found_rows
    -> s_name, s_address
    -> from supplier, nation
    -> where s_suppkey in (select ps_suppkey from partsupp
    -> where ps_partkey in (select p_partkey from part
    -> where p_name like 'forest%')
    -> and ps_availqty >
    -> (select 0.5 * sum(l_quantity)
    -> from lineitem
    -> where l_partkey = ps_partkey
    -> and l_suppkey = ps_suppkey
    -> and l_shipdate >= date('1994-01-01')
    -> and l_shipdate < date('1994-01-01') +
    -> interval '1' year ))
    -> and s_nationkey = n_nationkey
    -> and n_name = 'CANADA'
    -> order by s_name
    -> limit 10;
+----+--------------------+----------+-----------------+--------------------------------------+--------------+---------+-------------------------------------+--------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+----------+-----------------+--------------------------------------+--------------+---------+-------------------------------------+--------+-----------------------------+
| 1 | PRIMARY | supplier | ALL | i_s_nationkey | NULL | NULL | NULL | 100000 | Using where; Using filesort |
| 1 | PRIMARY | nation | eq_ref | PRIMARY | PRIMARY | 4 | dbt3x10_myisam.supplier.s_nationkey | 1 | Using where |
| 2 | DEPENDENT SUBQUERY | partsupp | index_subquery | i_ps_suppkey | i_ps_suppkey | 4 | func | 80 | Using where |
| 4 | DEPENDENT SUBQUERY | lineitem | ref | i_l_shipdate,i_l_partkey,i_l_suppkey | i_l_partkey | 5 | dbt3x10_myisam.partsupp.ps_partkey | 30 | Using where |
| 3 | DEPENDENT SUBQUERY | part | unique_subquery | PRIMARY | PRIMARY | 4 | func | 1 | Using where |
+----+--------------------+----------+-----------------+--------------------------------------+--------------+---------+-------------------------------------+--------+-----------------------------+