Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Won't Fix
-
10.8(EOL), 10.9(EOL), 10.10(EOL), 10.11
Description
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 |
|