Description:
Another suboptimal query plan is chosen for executing query 16 in TPC-H benchmark.
$ mysql tpch < 16.sql
EXPLAIN
-> Sort: supplier_cnt DESC, PART.P_BRAND, PART.P_TYPE, PART.P_SIZE (actual time=1698..1699 rows=18333 loops=1)
-> Stream results (cost=2.59e+9 rows=1.58e+6) (actual time=154..1682 rows=18333 loops=1)
-> Group aggregate: count(distinct PARTSUPP.PS_SUPPKEY) (cost=2.59e+9 rows=1.58e+6) (actual time=154..1677 rows=18333 loops=1)
-> Nested loop antijoin (cost=785e+6 rows=7.85e+9) (actual time=154..1633 rows=119078 loops=1)
-> Nested loop inner join (cost=149457 rows=785940) (actual time=148..1583 rows=119136 loops=1)
-> Sort: PART.P_BRAND, PART.P_TYPE, PART.P_SIZE (cost=20430 rows=198301) (actual time=148..151 rows=29784 loops=1)
-> Filter: ((PART.P_BRAND <> 'Brand#34') and (not((PART.P_TYPE like 'LARGE BRUSHED%'))) and (PART.P_SIZE in (48,19,12,4,41,7,21,39))) (cost=20430 rows=198301) (actual time=0.132..113 rows=29784 loops=1)
-> Table scan on PART (cost=20430 rows=198301) (actual time=0.126..66.8 rows=200000 loops=1)
-> Covering index lookup on PARTSUPP using PRIMARY (PS_PARTKEY=PART.P_PARTKEY) (cost=0.636 rows=3.96) (actual time=0.0472..0.0478 rows=4 loops=29784)
-> Single-row index lookup on <subquery2> using <auto_distinct_key> (s_suppkey=PARTSUPP.PS_SUPPKEY) (cost=3342..3342 rows=1) (actual time=311e-6..311e-6 rows=487e-6 loops=119136)
-> Materialize with deduplication (cost=3338..3338 rows=9982) (actual time=5.93..5.93 rows=4 loops=1)
-> Filter: (SUPPLIER.S_SUPPKEY is not null) (cost=1038 rows=9982) (actual time=0.231..5.91 rows=4 loops=1)
-> Filter: (SUPPLIER.S_COMMENT like '%Customer%Complaints%') (cost=1038 rows=9982) (actual time=0.23..5.91 rows=4 loops=1)
-> Table scan on SUPPLIER (cost=1038 rows=9982) (actual time=0.02..1.87 rows=10000 loops=1)
0.01s user 0.00s system 0% cpu 2.152 total
If we apply this patch, we can see a performance improvement:
diff --git a/sql/sql_optimizer.cc b/sql/sql_optimizer.cc
index 2b8f6c523aa..3a45074151d 100644
--- a/sql/sql_optimizer.cc
+++ b/sql/sql_optimizer.cc
@@ -3575,10 +3575,6 @@ static bool setup_join_buffering(JOIN_TAB *tab, JOIN *join,
We allow join buffering for the remaining tables of the materialized
semi-join nest.
*/
- if (tab->first_sj_inner() == tab->idx()) {
- assert(tab->use_join_cache() == JOIN_CACHE::ALG_NONE);
- goto no_join_cache;
- }
break;
case SJ_OPT_DUPS_WEEDOUT:
EXPLAIN
-> Sort: supplier_cnt DESC, PART.P_BRAND, PART.P_TYPE, PART.P_SIZE (actual time=798..800 rows=18333 loops=1)
-> Stream results (actual time=738..785 rows=18333 loops=1)
-> Group aggregate: count(distinct PARTSUPP.ps_suppkey) (actual time=738..782 rows=18333 loops=1)
-> Sort: PART.P_BRAND, PART.P_TYPE, PART.P_SIZE (actual time=738..746 rows=119078 loops=1)
-> Stream results (cost=314e+6 rows=3.14e+9) (actual time=12.3..631 rows=119078 loops=1)
-> Nested loop antijoin (cost=314e+6 rows=3.14e+9) (actual time=12.3..611 rows=119078 loops=1)
-> Nested loop inner join (cost=90448 rows=314380) (actual time=0.393..558 rows=119136 loops=1)
-> Filter: ((PART.P_BRAND <> 'Brand#34') and (not((PART.P_TYPE like 'LARGE BRUSHED%'))) and (PART.P_SIZE in (48,19,12,4,41,7,21,39))) (cost=20931 rows=79321) (actual time=0.259..214 rows=29784 loops=1)
-> Table scan on PART (cost=20931 rows=198301) (actual time=0.252..174 rows=200000 loops=1)
-> Covering index lookup on PARTSUPP using PRIMARY (PS_PARTKEY=PART.P_PARTKEY) (cost=0.48 rows=3.96) (actual time=0.0107..0.0113 rows=4 loops=29784)
-> Single-row index lookup on <subquery2> using <auto_distinct_key> (s_suppkey=PARTSUPP.PS_SUPPKEY) (cost=3387..3387 rows=1) (actual time=336e-6..336e-6 rows=487e-6 loops=119136)
-> Materialize with deduplication (cost=3383..3383 rows=9982) (actual time=11.9..11.9 rows=4 loops=1)
-> Filter: (SUPPLIER.S_SUPPKEY is not null) (cost=1083 rows=9982) (actual time=1.03..11.9 rows=4 loops=1)
-> Filter: (SUPPLIER.S_COMMENT like '%Customer%Complaints%') (cost=1083 rows=9982) (actual time=1.03..11.9 rows=4 loops=1)
-> Table scan on SUPPLIER (cost=1083 rows=9982) (actual time=0.331..6.9 rows=10000 loops=1)
0.00s user 0.01s system 0% cpu 0.851 total
The output result remains the same, but the execution time is reduced by more than 60%. In the query plan, it seems the operation `SORT` is executed in different locations.
Query 16:
EXPLAIN ANALYZE
select
p_brand,
p_type,
p_size,
count(distinct ps_suppkey) as supplier_cnt
from
PARTSUPP,
PART
where
p_partkey = ps_partkey
and p_brand <> 'Brand#34'
and p_type not like 'LARGE BRUSHED%'
and p_size in (48, 19, 12, 4, 41, 7, 21, 39)
and ps_suppkey not in (
select
s_suppkey
from
SUPPLIER
where
s_comment like '%Customer%Complaints%'
)
group by
p_brand,
p_type,
p_size
order by
supplier_cnt desc,
p_brand,
p_type,
p_size;
TPC-H data scale: 1GB
How to repeat:
The data is exactly the same as the one used in https://bugs.mysql.com/bug.php?id=116309
Description: Another suboptimal query plan is chosen for executing query 16 in TPC-H benchmark. $ mysql tpch < 16.sql EXPLAIN -> Sort: supplier_cnt DESC, PART.P_BRAND, PART.P_TYPE, PART.P_SIZE (actual time=1698..1699 rows=18333 loops=1) -> Stream results (cost=2.59e+9 rows=1.58e+6) (actual time=154..1682 rows=18333 loops=1) -> Group aggregate: count(distinct PARTSUPP.PS_SUPPKEY) (cost=2.59e+9 rows=1.58e+6) (actual time=154..1677 rows=18333 loops=1) -> Nested loop antijoin (cost=785e+6 rows=7.85e+9) (actual time=154..1633 rows=119078 loops=1) -> Nested loop inner join (cost=149457 rows=785940) (actual time=148..1583 rows=119136 loops=1) -> Sort: PART.P_BRAND, PART.P_TYPE, PART.P_SIZE (cost=20430 rows=198301) (actual time=148..151 rows=29784 loops=1) -> Filter: ((PART.P_BRAND <> 'Brand#34') and (not((PART.P_TYPE like 'LARGE BRUSHED%'))) and (PART.P_SIZE in (48,19,12,4,41,7,21,39))) (cost=20430 rows=198301) (actual time=0.132..113 rows=29784 loops=1) -> Table scan on PART (cost=20430 rows=198301) (actual time=0.126..66.8 rows=200000 loops=1) -> Covering index lookup on PARTSUPP using PRIMARY (PS_PARTKEY=PART.P_PARTKEY) (cost=0.636 rows=3.96) (actual time=0.0472..0.0478 rows=4 loops=29784) -> Single-row index lookup on <subquery2> using <auto_distinct_key> (s_suppkey=PARTSUPP.PS_SUPPKEY) (cost=3342..3342 rows=1) (actual time=311e-6..311e-6 rows=487e-6 loops=119136) -> Materialize with deduplication (cost=3338..3338 rows=9982) (actual time=5.93..5.93 rows=4 loops=1) -> Filter: (SUPPLIER.S_SUPPKEY is not null) (cost=1038 rows=9982) (actual time=0.231..5.91 rows=4 loops=1) -> Filter: (SUPPLIER.S_COMMENT like '%Customer%Complaints%') (cost=1038 rows=9982) (actual time=0.23..5.91 rows=4 loops=1) -> Table scan on SUPPLIER (cost=1038 rows=9982) (actual time=0.02..1.87 rows=10000 loops=1) 0.01s user 0.00s system 0% cpu 2.152 total If we apply this patch, we can see a performance improvement: diff --git a/sql/sql_optimizer.cc b/sql/sql_optimizer.cc index 2b8f6c523aa..3a45074151d 100644 --- a/sql/sql_optimizer.cc +++ b/sql/sql_optimizer.cc @@ -3575,10 +3575,6 @@ static bool setup_join_buffering(JOIN_TAB *tab, JOIN *join, We allow join buffering for the remaining tables of the materialized semi-join nest. */ - if (tab->first_sj_inner() == tab->idx()) { - assert(tab->use_join_cache() == JOIN_CACHE::ALG_NONE); - goto no_join_cache; - } break; case SJ_OPT_DUPS_WEEDOUT: EXPLAIN -> Sort: supplier_cnt DESC, PART.P_BRAND, PART.P_TYPE, PART.P_SIZE (actual time=798..800 rows=18333 loops=1) -> Stream results (actual time=738..785 rows=18333 loops=1) -> Group aggregate: count(distinct PARTSUPP.ps_suppkey) (actual time=738..782 rows=18333 loops=1) -> Sort: PART.P_BRAND, PART.P_TYPE, PART.P_SIZE (actual time=738..746 rows=119078 loops=1) -> Stream results (cost=314e+6 rows=3.14e+9) (actual time=12.3..631 rows=119078 loops=1) -> Nested loop antijoin (cost=314e+6 rows=3.14e+9) (actual time=12.3..611 rows=119078 loops=1) -> Nested loop inner join (cost=90448 rows=314380) (actual time=0.393..558 rows=119136 loops=1) -> Filter: ((PART.P_BRAND <> 'Brand#34') and (not((PART.P_TYPE like 'LARGE BRUSHED%'))) and (PART.P_SIZE in (48,19,12,4,41,7,21,39))) (cost=20931 rows=79321) (actual time=0.259..214 rows=29784 loops=1) -> Table scan on PART (cost=20931 rows=198301) (actual time=0.252..174 rows=200000 loops=1) -> Covering index lookup on PARTSUPP using PRIMARY (PS_PARTKEY=PART.P_PARTKEY) (cost=0.48 rows=3.96) (actual time=0.0107..0.0113 rows=4 loops=29784) -> Single-row index lookup on <subquery2> using <auto_distinct_key> (s_suppkey=PARTSUPP.PS_SUPPKEY) (cost=3387..3387 rows=1) (actual time=336e-6..336e-6 rows=487e-6 loops=119136) -> Materialize with deduplication (cost=3383..3383 rows=9982) (actual time=11.9..11.9 rows=4 loops=1) -> Filter: (SUPPLIER.S_SUPPKEY is not null) (cost=1083 rows=9982) (actual time=1.03..11.9 rows=4 loops=1) -> Filter: (SUPPLIER.S_COMMENT like '%Customer%Complaints%') (cost=1083 rows=9982) (actual time=1.03..11.9 rows=4 loops=1) -> Table scan on SUPPLIER (cost=1083 rows=9982) (actual time=0.331..6.9 rows=10000 loops=1) 0.00s user 0.01s system 0% cpu 0.851 total The output result remains the same, but the execution time is reduced by more than 60%. In the query plan, it seems the operation `SORT` is executed in different locations. Query 16: EXPLAIN ANALYZE select p_brand, p_type, p_size, count(distinct ps_suppkey) as supplier_cnt from PARTSUPP, PART where p_partkey = ps_partkey and p_brand <> 'Brand#34' and p_type not like 'LARGE BRUSHED%' and p_size in (48, 19, 12, 4, 41, 7, 21, 39) and ps_suppkey not in ( select s_suppkey from SUPPLIER where s_comment like '%Customer%Complaints%' ) group by p_brand, p_type, p_size order by supplier_cnt desc, p_brand, p_type, p_size; TPC-H data scale: 1GB How to repeat: The data is exactly the same as the one used in https://bugs.mysql.com/bug.php?id=116309