|
Set to critical, because in 11.0 JSON_HB is planned to become a default value, so it will become a regression. At least its extent needs to be analyzed.
CREATE DATABASE dbt3;
|
USE dbt3;
|
|
set default_storage_engine=Aria;
|
|
--source include/dbt3_s001.inc
|
|
ANALYZE TABLE part, partsupp, supplier, lineitem, orders, customer PERSISTENT FOR ALL;
|
|
--start_timer
|
SELECT COUNT(*) AS cnt FROM part LEFT JOIN partsupp ON ( p_partkey = ps_partkey ) JOIN supplier ON (s_suppkey = ps_suppkey) LEFT JOIN lineitem ON ( ps_suppkey = l_suppkey ) JOIN orders ON ( l_orderkey = o_orderkey ) JOIN customer ON ( o_custkey = c_custkey ) WHERE ps_partkey < 130 AND ps_comment IS NOT NULL HAVING cnt < 0;
|
--end_timer
|
|
EXPLAIN EXTENDED
|
SELECT COUNT(*) AS cnt FROM part LEFT JOIN partsupp ON ( p_partkey = ps_partkey ) JOIN supplier ON (s_suppkey = ps_suppkey) LEFT JOIN lineitem ON ( ps_suppkey = l_suppkey ) JOIN orders ON ( l_orderkey = o_orderkey ) JOIN customer ON ( o_custkey = c_custkey ) WHERE ps_partkey < 130 AND ps_comment IS NOT NULL HAVING cnt < 0;
|
|
DROP DATABASE dbt3;
|
With DOUBLE_PREC_HB, the query takes 0.14 sec on my machine, on 10.8.6 release build. With JSON_HB it is 1.8 sec.
Plans:
|
10.8.6 with DOUBLE_PREC_HB
|
+------+-------------+----------+--------+--------------------------------------------------------+-------------+---------+---------------------------------------------+------+----------+--------------------------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+------+-------------+----------+--------+--------------------------------------------------------+-------------+---------+---------------------------------------------+------+----------+--------------------------------------------------------------+
|
| 1 | SIMPLE | supplier | index | PRIMARY | PRIMARY | 4 | NULL | 10 | 100.00 | Using index |
|
| 1 | SIMPLE | lineitem | ref | PRIMARY,i_l_suppkey,i_l_orderkey,i_l_orderkey_quantity | i_l_suppkey | 5 | test.supplier.s_suppkey | 600 | 100.00 | |
|
| 1 | SIMPLE | orders | eq_ref | PRIMARY,i_o_custkey | PRIMARY | 4 | test.lineitem.l_orderkey | 1 | 100.00 | Using where |
|
| 1 | SIMPLE | customer | eq_ref | PRIMARY | PRIMARY | 4 | test.orders.o_custkey | 1 | 100.00 | Using index |
|
| 1 | SIMPLE | part | range | PRIMARY | PRIMARY | 4 | NULL | 129 | 100.00 | Using where; Using index; Using join buffer (flat, BNL join) |
|
| 1 | SIMPLE | partsupp | eq_ref | PRIMARY,i_ps_partkey,i_ps_suppkey | PRIMARY | 8 | test.part.p_partkey,test.supplier.s_suppkey | 1 | 99.22 | Using where |
|
+------+-------------+----------+--------+--------------------------------------------------------+-------------+---------+---------------------------------------------+------+----------+--------------------------------------------------------------+
|
| Note | 1003 | select count(0) AS `cnt` from `test`.`part` join `test`.`partsupp` join `test`.`supplier` join `test`.`lineitem` join `test`.`orders` join `test`.`customer` where `test`.`customer`.`c_custkey` = `test`.`orders`.`o_custkey` and `test`.`orders`.`o_orderkey` = `test`.`lineitem`.`l_orderkey` and `test`.`lineitem`.`l_suppkey` = `test`.`supplier`.`s_suppkey` and `test`.`partsupp`.`ps_suppkey` = `test`.`supplier`.`s_suppkey` and `test`.`partsupp`.`ps_partkey` = `test`.`part`.`p_partkey` and `test`.`part`.`p_partkey` < 130 and `test`.`partsupp`.`ps_comment` is not null |
|
|
10.8.6 with JSON_HB
|
+------+-------------+----------+--------+--------------------------------------------------------+--------------+---------+--------------------------+------+----------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+------+-------------+----------+--------+--------------------------------------------------------+--------------+---------+--------------------------+------+----------+-------------+
|
| 1 | SIMPLE | supplier | index | PRIMARY | PRIMARY | 4 | NULL | 10 | 100.00 | Using index |
|
| 1 | SIMPLE | partsupp | ref | PRIMARY,i_ps_partkey,i_ps_suppkey | i_ps_suppkey | 4 | test.supplier.s_suppkey | 70 | 40.00 | Using where |
|
| 1 | SIMPLE | part | eq_ref | PRIMARY | PRIMARY | 4 | test.partsupp.ps_partkey | 1 | 100.00 | Using index |
|
| 1 | SIMPLE | lineitem | ref | PRIMARY,i_l_suppkey,i_l_orderkey,i_l_orderkey_quantity | i_l_suppkey | 5 | test.supplier.s_suppkey | 600 | 100.00 | |
|
| 1 | SIMPLE | orders | eq_ref | PRIMARY,i_o_custkey | PRIMARY | 4 | test.lineitem.l_orderkey | 1 | 100.00 | Using where |
|
| 1 | SIMPLE | customer | eq_ref | PRIMARY | PRIMARY | 4 | test.orders.o_custkey | 1 | 100.00 | Using index |
|
+------+-------------+----------+--------+--------------------------------------------------------+--------------+---------+--------------------------+------+----------+-------------+
|
| Note | 1003 | select count(0) AS `cnt` from `test`.`part` join `test`.`partsupp` join `test`.`supplier` join `test`.`lineitem` join `test`.`orders` join `test`.`customer` where `test`.`customer`.`c_custkey` = `test`.`orders`.`o_custkey` and `test`.`orders`.`o_orderkey` = `test`.`lineitem`.`l_orderkey` and `test`.`partsupp`.`ps_suppkey` = `test`.`supplier`.`s_suppkey` and `test`.`lineitem`.`l_suppkey` = `test`.`supplier`.`s_suppkey` and `test`.`part`.`p_partkey` = `test`.`partsupp`.`ps_partkey` and `test`.`partsupp`.`ps_partkey` < 130 and `test`.`partsupp`.`ps_comment` is not null |
|
|
mdev30514-bb-11.0-try-good-join-order.txt
