|
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 |
|
+----+--------------------+----------+-----------------+--------------------------------------+--------------+---------+-------------------------------------+--------+-----------------------------+
|
|
LPexportBug914569.xml