Details
-
Bug
-
Status: Open (View Workflow)
-
Critical
-
Resolution: Unresolved
-
10.10
-
None
Description
A performance degradation can be observed with new default setting for 'optimizer_prune_level' in 10.10 for the following simple query over the dbt3 tables:
select l_orderkey, l_linenumber, l_tax from lineitem |
where l_orderkey in (select o_orderkey from orders |
where o_custkey in |
(select c_custkey from customer |
where c_nationkey in |
(select n_nationkey from nation |
where n_name='PERU')) |
and |
o_orderDATE between '1992-06-30' and '1992-12-31') |
and |
(l_partkey, l_suppkey) in |
(select p_partkey, s_suppkey from part, supplier |
where p_retailprice between 901 and 1000 and |
s_nationkey in (select n_nationkey from nation |
where n_name='PERU')); |
The query was run on a Release build of the latest 10.10 for the the database created with these mtr commands
--disable_warnings
|
DROP DATABASE IF EXISTS dbt3_s001; |
--enable_warnings
|
|
|
CREATE DATABASE dbt3_s001; |
|
|
use dbt3_s001; |
|
|
--disable_query_log
|
--disable_result_log
|
--disable_warnings
|
--source include/dbt3_s001.inc
|
--enable_warnings
|
--enable_result_log
|
--enable_query_log
|
|
|
create index i_n_name on nation(n_name); |
analyze table nation; |
The default setting for 'optimizer_prune_level' is 2 10.10 while before this version the default setting for this variable was 1.
The performance degradation for the reported query can be observed for MyISAM. For InnoDB we don't see such degradation for this particular query.
Here's what I got when running the above query for MyISAM (all time measurements were taken from ANALYZE stmt)
1. set optimizer_prune_level=default
|
1st run: "r_total_time_ms": 5.942242744
|
2nd run: "r_total_time_ms": 5.795177193
|
3rd run: "r_total_time_ms": 5.734443905
|
2. set optimizer_prune_level=1
|
1st run: "r_total_time_ms": 4.177749859
|
2nd run: "r_total_time_ms": 3.995446738
|
3rd run: "r_total_time_ms": 4.384639953
|
3. set optimizer_prune_level=0
|
1st run: "r_total_time_ms": 4.269302585
|
2nd run: "r_total_time_ms": 3.914398888
|
3rd run: "r_total_time_ms": 3.921989294
|
With the above settings for optimizer_prune_level the optimizer choses the following execution plans:
1. set optimizer_prune_level=default
|
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
1 PRIMARY nation ref PRIMARY,i_n_name i_n_name 26 const 1 Using index
|
condition
|
1 PRIMARY nation ref PRIMARY,i_n_name i_n_name 26 const 1 Using index condition
|
1 PRIMARY supplier ref PRIMARY,i_s_nationkey i_s_nationkey 5 dbt3_s001.nation.n_nationkey 2
|
1 PRIMARY lineitem ref PRIMARY,i_l_suppkey_partkey,i_l_partkey,i_l_suppkey,i_l_orderkey,i_l_orderkey_quantity i_l_suppkey5dbt3_s001.supplier.s_suppkey 100 Using where
|
1 PRIMARY part eq_ref PRIMARY PRIMARY 4 dbt3_s001.lineitem.l_partkey 1 Using where
|
1 PRIMARY orders eq_ref|filter PRIMARY,i_o_orderdate,i_o_custkey PRIMARY|i_o_orderdate 4|4 dbt3_s001.lineitem.l_orderkey 1 (7%) Using where; Using rowid filter
|
1 PRIMARY customer eq_ref PRIMARY,i_c_nationkey PRIMARY 4 dbt3_s001.orders.o_custkey 1 Using where
|
|
|
2. set optimizer_prune_level=1
|
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
1 PRIMARY nation ref PRIMARY,i_n_name i_n_name 26 const 1 Using index condition
|
1 PRIMARY nation ref PRIMARY,i_n_name i_n_name 26 const 1 Using index condition
|
1 PRIMARY supplier ref PRIMARY,i_s_nationkey i_s_nationkey 5 dbt3_s001.nation.n_nationkey 2
|
1 PRIMARY lineitem ref PRIMARY,i_l_suppkey_partkey,i_l_partkey,i_l_suppkey,i_l_orderkey,i_l_orderkey_quantity i_l_suppkey5dbt3_s001.supplier.s_suppkey 100 Using where
|
1 PRIMARY orders eq_ref|filter PRIMARY,i_o_orderdate,i_o_custkey PRIMARY|i_o_orderdate 4|4 dbt3_s001.lineitem.l_orderkey 1 (7%) Using where; Using rowid filter
|
1 PRIMARY part eq_ref PRIMARY PRIMARY 4 dbt3_s001.lineitem.l_partkey 1 Using where
|
1 PRIMARY customer eq_ref PRIMARY,i_c_nationkey PRIMARY 4 dbt3_s001.orders.o_custkey 1 Using where
|
|
|
3. set optimizer_prune_level=0
|
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
1 PRIMARY nation ref PRIMARY,i_n_name i_n_name 26 const 1 Using index condition
|
1 PRIMARY supplier ref PRIMARY,i_s_nationkey i_s_nationkey 5 dbt3_s001.nation.n_nationkey 2
|
1 PRIMARY lineitem ref PRIMARY,i_l_suppkey_partkey,i_l_partkey,i_l_suppkey,i_l_orderkey,i_l_orderkey_quantity i_l_suppkey5dbt3_s001.supplier.s_suppkey 100
|
1 PRIMARY orders eq_ref|filter PRIMARY,i_o_orderdate,i_o_custkey PRIMARY|i_o_orderdate 4|4 dbt3_s001.lineitem.l_orderkey 1 (7%) Using where; Using rowid filter
|
1 PRIMARY customer eq_ref PRIMARY,i_c_nationkey PRIMARY 4 dbt3_s001.orders.o_custkey 1 Using where
|
1 PRIMARY nation eq_ref|filter PRIMARY,i_n_name PRIMARY|i_n_name 4|26 dbt3_s001.customer.c_nationkey 1 (4%) Using where; Using rowid filter
|
1 PRIMARY part ALL PRIMARY NULL NULL NULL 200 Using where; Using join buffer (flat, BNL join)
|
We see that with different settings for optimizer_prune_level the optimizer chooses different join orders:
1. set optimizer_prune_level=default
nation, nation, supplier, lineitem, part, orders, customer
2. set optimizer_prune_level=1
nation, nation, supplier, lineitem, orders, part, customer
3. set optimizer_prune_level=0
nation, supplier, lineitem, orders, customer, nation, part
For InnoDB I got
1. set optimizer_prune_level=default
|
1st run: "r_total_time_ms": 2.05392333
|
2nd run: "r_total_time_ms": 2.169093801
|
3rd run "r_total_time_ms": 1.988759027
|
2. set optimizer_prune_level=1
|
1st run: "r_total_time_ms": 1.904479043
|
2nd run: "r_total_time_ms": 2.059541392
|
3rd run: "r_total_time_ms": 1.961143374
|
3. set optimizer_prune_level=0
|
1st run: "r_total_time_ms": 1.391450822
|
2st run: "r_total_time_ms": 1.450423518
|
3rd run: "r_total_time_ms": 1.565808954
|
For InnoDB with the above settings for optimiser_prune_level the optimizer chooses the following execution plans:
1. set optimizer_prune_level=default
|
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
1 PRIMARY nation ref PRIMARY,i_n_name i_n_name 26 const 1 Using where; Using index
|
1 PRIMARY supplier ref PRIMARY,i_s_nationkey i_s_nationkey 5 dbt3_s001.nation.n_nationkey 1 Using index
|
1 PRIMARY nation ref PRIMARY,i_n_name i_n_name 26 const 1 Using where; Using index
|
1 PRIMARY customer ref PRIMARY,i_c_nationkey i_c_nationkey 5 dbt3_s001.nation.n_nationkey 1 Using index
|
1 PRIMARY orders ref PRIMARY,i_o_orderdate,i_o_custkey i_o_custkey 5 dbt3_s001.customer.c_custkey 1 Using where
|
1 PRIMARY lineitem ref PRIMARY,i_l_suppkey_partkey,i_l_partkey,i_l_suppkey,i_l_orderkey,i_l_orderkey_quantity PRIMARY 4 dbt3_s001.orders.o_orderkey 1 Using where
|
1 PRIMARY part eq_ref PRIMARY PRIMARY 4 dbt3_s001.lineitem.l_partkey 1 Using where
|
|
|
2. set optimizer_prune_level=1
|
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
1 PRIMARY nation ref PRIMARY,i_n_name i_n_name 26 const 1 Using where; Using index
|
1 PRIMARY supplier ref PRIMARY,i_s_nationkey i_s_nationkey 5 dbt3_s001.nation.n_nationkey 1 Using index
|
1 PRIMARY nation ref PRIMARY,i_n_name i_n_name 26 const 1 Using where; Using index
|
1 PRIMARY customer ref PRIMARY,i_c_nationkey i_c_nationkey 5 dbt3_s001.nation.n_nationkey 1 Using index
|
1 PRIMARY orders ref PRIMARY,i_o_orderdate,i_o_custkey i_o_custkey 5 dbt3_s001.customer.c_custkey 1 Using where
|
1 PRIMARY lineitem ref PRIMARY,i_l_suppkey_partkey,i_l_partkey,i_l_suppkey,i_l_orderkey,i_l_orderkey_quantity PRIMARY 4 dbt3_s001.orders.o_orderkey 1 Using where
|
1 PRIMARY part eq_ref PRIMARY PRIMARY 4 dbt3_s001.lineitem.l_partkey 1 Using where
|
|
|
3. set optimizer_prune_level=0
|
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
1 PRIMARY nation ref PRIMARY,i_n_name i_n_name 26 const 1 Using where; Using index
|
1 PRIMARY customer ref PRIMARY,i_c_nationkey i_c_nationkey 5 dbt3_s001.nation.n_nationkey 1 Using index
|
1 PRIMARY orders ref PRIMARY,i_o_orderdate,i_o_custkey i_o_custkey 5 dbt3_s001.customer.c_custkey 1 Using where
|
1 PRIMARY lineitem ref PRIMARY,i_l_suppkey_partkey,i_l_partkey,i_l_suppkey,i_l_orderkey,i_l_orderkey_quantity PRIMARY 4 dbt3_s001.orders.o_orderkey 1 Using where
|
1 PRIMARY part eq_ref PRIMARY PRIMARY 4 dbt3_s001.lineitem.l_partkey 1 Using where
|
1 PRIMARY nation ref PRIMARY,i_n_name i_n_name 26 const 1 Using where; Using index
|
1 PRIMARY supplier eq_ref PRIMARY,i_s_nationkey PRIMARY 4 dbt3_s001.lineitem.l_suppkey 1 Using where
|
analyze format=json select l_orderkey, l_linenumber, l_tax from lineitem
|
Here with different settings for optimizer_prune_level we see that the optimizer chooses such join orders:
1. set optimizer_prune_level=default
nation, supplier, nation, customer, orders, lineitem, part
2. set optimizer_prune_level=1
nation, supplier, nation, customer, orders, lineitem, part
3. set optimizer_prune_level=0
nation, customer, orders, lineitem, part, nation, supplier
Here for Innodb we don't see that the execution plans for settings of optimizer_prune_level to 2 (default) and 1 differ. However setting of this variable to 0 gives us a better plan.