Description:
For TPC-DS Query 7:
select i_item_id,
avg(ss_quantity) agg1,
avg(ss_list_price) agg2,
avg(ss_coupon_amt) agg3,
avg(ss_sales_price) agg4
from store_sales, customer_demographics, date_dim, item, promotion
where ss_sold_date_sk = d_date_sk and
ss_item_sk = i_item_sk and
ss_cdemo_sk = cd_demo_sk and
ss_promo_sk = p_promo_sk and
cd_gender = 'F' and
cd_marital_status = 'W' and
cd_education_status = 'Primary' and
(p_channel_email = 'N' or p_channel_event = 'N') and
d_year = 1998
group by i_item_id
order by i_item_id
limit 100;
Its execution time and query plan:
EXPLAIN
-> Limit: 100 row(s) (actual time=66095..66095 rows=100 loops=1)
-> Sort: item.i_item_id, limit input to 100 row(s) per chunk (actual time=66095..66095 rows=100 loops=1)
-> Table scan on <temporary> (actual time=66093..66094 rows=5181 loops=1)
-> Aggregate using temporary table (actual time=66093..66093 rows=5181 loops=1)
-> Nested loop inner join (cost=1.41e+6 rows=1967) (actual time=3.6..66070 rows=7702 loops=1)
-> Nested loop inner join (cost=1.38e+6 rows=39337) (actual time=1.23..20864 rows=535961 loops=1)
-> Nested loop inner join (cost=958290 rows=393367) (actual time=1.17..14766 rows=2.74e+6 loops=1)
-> Nested loop inner join (cost=233667 rows=2.07e+6) (actual time=1.15..8512 rows=2.88e+6 loops=1)
-> Table scan on item (cost=2417 rows=17565) (actual time=0.92..1760 rows=18000 loops=1)
-> Filter: ((store_sales.ss_promo_sk is not null) and (store_sales.ss_sold_date_sk is not null) and (store_sales.ss_cdemo_sk is not null)) (cost=1.38 rows=118) (actual time=0.195..0.366 rows=160 loops=18000)
-> Index lookup on store_sales using PRIMARY (ss_item_sk=item.i_item_sk) (cost=1.38 rows=118) (actual time=0.195..0.348 rows=160 loops=18000)
-> Filter: ((promotion.p_channel_email = 'N') or (promotion.p_channel_event = 'N')) (cost=0.25 rows=0.19) (actual time=0.00194..0.00202 rows=0.952 loops=2.88e+6)
-> Single-row index lookup on promotion using PRIMARY (p_promo_sk=store_sales.ss_promo_sk) (cost=0.25 rows=1) (actual time=0.00171..0.00174 rows=0.955 loops=2.88e+6)
-> Filter: (date_dim.d_year = 1998) (cost=0.979 rows=0.1) (actual time=0.00209..0.00211 rows=0.195 loops=2.74e+6)
-> Single-row index lookup on date_dim using PRIMARY (d_date_sk=store_sales.ss_sold_date_sk) (cost=0.979 rows=1) (actual time=0.0019..0.00193 rows=0.976 loops=2.74e+6)
-> Filter: ((customer_demographics.cd_education_status = 'Primary') and (customer_demographics.cd_marital_status = 'W') and (customer_demographics.cd_gender = 'F')) (cost=0.526 rows=0.05) (actual time=0.0842..0.0842 rows=0.0144 loops=535961)
-> Single-row index lookup on customer_demographics using PRIMARY (cd_demo_sk=store_sales.ss_cdemo_sk) (cost=0.526 rows=1) (actual time=0.0839..0.0839 rows=0.988 loops=535961)
If we apply this patch:
diff --git a/sql/sql_planner.cc b/sql/sql_planner.cc
index ad0c639abca..73d9e7cf9b1 100644
--- a/sql/sql_planner.cc
+++ b/sql/sql_planner.cc
@@ -1210,7 +1210,7 @@ void Optimize_table_order::best_access_path(JOIN_TAB *tab,
chosen. The filtering effect for all the scan types of access
(range/index scan/table scan) has already been calculated.
*/
- if (best_ref)
+ if (!best_ref)
filter_effect = calculate_condition_filter(
tab, best_ref, ~remaining_tables & ~excluded_tables, rows_fetched,
false, false, trace_access_scan);
The execution time is significantly reduced:
EXPLAIN
-> Limit: 100 row(s) (actual time=5479..5479 rows=100 loops=1)
-> Sort: item.i_item_id, limit input to 100 row(s) per chunk (actual time=5479..5479 rows=100 loops=1)
-> Table scan on <temporary> (actual time=5477..5478 rows=5181 loops=1)
-> Aggregate using temporary table (actual time=5477..5477 rows=5181 loops=1)
-> Nested loop inner join (cost=1.13e+6 rows=53277) (actual time=896..5466 rows=7702 loops=1)
-> Nested loop inner join (cost=843429 rows=53277) (actual time=894..5374 rows=39339 loops=1)
-> Nested loop inner join (cost=559367 rows=53277) (actual time=894..5283 rows=39339 loops=1)
-> Inner hash join (store_sales.ss_cdemo_sk = customer_demographics.cd_demo_sk) (cost=275305 rows=53277) (actual time=893..5193 rows=40389 loops=1)
-> Filter: ((store_sales.ss_promo_sk is not null) and (store_sales.ss_sold_date_sk is not null)) (cost=25873 rows=278735) (actual time=0.721..1944 rows=2.88e+6 loops=1)
-> Table scan on store_sales (cost=25873 rows=2.79e+6) (actual time=0.72..1760 rows=2.88e+6 loops=1)
-> Hash
-> Filter: ((customer_demographics.cd_education_status = 'Primary') and (customer_demographics.cd_marital_status = 'W') and (customer_demographics.cd_gender = 'F')) (cost=200448 rows=1.91) (actual time=0.412..885 rows=27440 loops=1)
-> Table scan on customer_demographics (cost=200448 rows=1.91e+6) (actual time=0.403..722 rows=1.92e+6 loops=1)
-> Filter: ((promotion.p_channel_email = 'N') or (promotion.p_channel_event = 'N')) (cost=0.523 rows=1) (actual time=0.00202..0.00209 rows=0.974 loops=40389)
-> Single-row index lookup on promotion using PRIMARY (p_promo_sk=store_sales.ss_promo_sk) (cost=0.523 rows=1) (actual time=0.00179..0.00181 rows=0.977 loops=40389)
-> Single-row index lookup on item using PRIMARY (i_item_sk=store_sales.ss_item_sk) (cost=0.523 rows=1) (actual time=0.00214..0.00216 rows=1 loops=39339)
-> Filter: (date_dim.d_year = 1998) (cost=0.523 rows=1) (actual time=0.00221..0.00223 rows=0.196 loops=39339)
-> Single-row index lookup on date_dim using PRIMARY (d_date_sk=store_sales.ss_sold_date_sk) (cost=0.523 rows=1) (actual time=0.00204..0.00206 rows=0.989 loops=39339)
I am not proposing a patch, but it seems the performance can be significantly improved by changing the usage of `calculate_condition_filter`. I am wondering whether we can optimize any logic to enable the second query plan, which seems much more efficient?
How to repeat:
I dumped the entire database: https://drive.google.com/file/d/1mCwATIQtNwYftxVvMCNz6xgjvRDCrhtq/view?usp=sharing
Compile MySQL in two versions, one is original and the other is with the changed IF condition. Then compare the execution of above query on both versions.
Description: For TPC-DS Query 7: select i_item_id, avg(ss_quantity) agg1, avg(ss_list_price) agg2, avg(ss_coupon_amt) agg3, avg(ss_sales_price) agg4 from store_sales, customer_demographics, date_dim, item, promotion where ss_sold_date_sk = d_date_sk and ss_item_sk = i_item_sk and ss_cdemo_sk = cd_demo_sk and ss_promo_sk = p_promo_sk and cd_gender = 'F' and cd_marital_status = 'W' and cd_education_status = 'Primary' and (p_channel_email = 'N' or p_channel_event = 'N') and d_year = 1998 group by i_item_id order by i_item_id limit 100; Its execution time and query plan: EXPLAIN -> Limit: 100 row(s) (actual time=66095..66095 rows=100 loops=1) -> Sort: item.i_item_id, limit input to 100 row(s) per chunk (actual time=66095..66095 rows=100 loops=1) -> Table scan on <temporary> (actual time=66093..66094 rows=5181 loops=1) -> Aggregate using temporary table (actual time=66093..66093 rows=5181 loops=1) -> Nested loop inner join (cost=1.41e+6 rows=1967) (actual time=3.6..66070 rows=7702 loops=1) -> Nested loop inner join (cost=1.38e+6 rows=39337) (actual time=1.23..20864 rows=535961 loops=1) -> Nested loop inner join (cost=958290 rows=393367) (actual time=1.17..14766 rows=2.74e+6 loops=1) -> Nested loop inner join (cost=233667 rows=2.07e+6) (actual time=1.15..8512 rows=2.88e+6 loops=1) -> Table scan on item (cost=2417 rows=17565) (actual time=0.92..1760 rows=18000 loops=1) -> Filter: ((store_sales.ss_promo_sk is not null) and (store_sales.ss_sold_date_sk is not null) and (store_sales.ss_cdemo_sk is not null)) (cost=1.38 rows=118) (actual time=0.195..0.366 rows=160 loops=18000) -> Index lookup on store_sales using PRIMARY (ss_item_sk=item.i_item_sk) (cost=1.38 rows=118) (actual time=0.195..0.348 rows=160 loops=18000) -> Filter: ((promotion.p_channel_email = 'N') or (promotion.p_channel_event = 'N')) (cost=0.25 rows=0.19) (actual time=0.00194..0.00202 rows=0.952 loops=2.88e+6) -> Single-row index lookup on promotion using PRIMARY (p_promo_sk=store_sales.ss_promo_sk) (cost=0.25 rows=1) (actual time=0.00171..0.00174 rows=0.955 loops=2.88e+6) -> Filter: (date_dim.d_year = 1998) (cost=0.979 rows=0.1) (actual time=0.00209..0.00211 rows=0.195 loops=2.74e+6) -> Single-row index lookup on date_dim using PRIMARY (d_date_sk=store_sales.ss_sold_date_sk) (cost=0.979 rows=1) (actual time=0.0019..0.00193 rows=0.976 loops=2.74e+6) -> Filter: ((customer_demographics.cd_education_status = 'Primary') and (customer_demographics.cd_marital_status = 'W') and (customer_demographics.cd_gender = 'F')) (cost=0.526 rows=0.05) (actual time=0.0842..0.0842 rows=0.0144 loops=535961) -> Single-row index lookup on customer_demographics using PRIMARY (cd_demo_sk=store_sales.ss_cdemo_sk) (cost=0.526 rows=1) (actual time=0.0839..0.0839 rows=0.988 loops=535961) If we apply this patch: diff --git a/sql/sql_planner.cc b/sql/sql_planner.cc index ad0c639abca..73d9e7cf9b1 100644 --- a/sql/sql_planner.cc +++ b/sql/sql_planner.cc @@ -1210,7 +1210,7 @@ void Optimize_table_order::best_access_path(JOIN_TAB *tab, chosen. The filtering effect for all the scan types of access (range/index scan/table scan) has already been calculated. */ - if (best_ref) + if (!best_ref) filter_effect = calculate_condition_filter( tab, best_ref, ~remaining_tables & ~excluded_tables, rows_fetched, false, false, trace_access_scan); The execution time is significantly reduced: EXPLAIN -> Limit: 100 row(s) (actual time=5479..5479 rows=100 loops=1) -> Sort: item.i_item_id, limit input to 100 row(s) per chunk (actual time=5479..5479 rows=100 loops=1) -> Table scan on <temporary> (actual time=5477..5478 rows=5181 loops=1) -> Aggregate using temporary table (actual time=5477..5477 rows=5181 loops=1) -> Nested loop inner join (cost=1.13e+6 rows=53277) (actual time=896..5466 rows=7702 loops=1) -> Nested loop inner join (cost=843429 rows=53277) (actual time=894..5374 rows=39339 loops=1) -> Nested loop inner join (cost=559367 rows=53277) (actual time=894..5283 rows=39339 loops=1) -> Inner hash join (store_sales.ss_cdemo_sk = customer_demographics.cd_demo_sk) (cost=275305 rows=53277) (actual time=893..5193 rows=40389 loops=1) -> Filter: ((store_sales.ss_promo_sk is not null) and (store_sales.ss_sold_date_sk is not null)) (cost=25873 rows=278735) (actual time=0.721..1944 rows=2.88e+6 loops=1) -> Table scan on store_sales (cost=25873 rows=2.79e+6) (actual time=0.72..1760 rows=2.88e+6 loops=1) -> Hash -> Filter: ((customer_demographics.cd_education_status = 'Primary') and (customer_demographics.cd_marital_status = 'W') and (customer_demographics.cd_gender = 'F')) (cost=200448 rows=1.91) (actual time=0.412..885 rows=27440 loops=1) -> Table scan on customer_demographics (cost=200448 rows=1.91e+6) (actual time=0.403..722 rows=1.92e+6 loops=1) -> Filter: ((promotion.p_channel_email = 'N') or (promotion.p_channel_event = 'N')) (cost=0.523 rows=1) (actual time=0.00202..0.00209 rows=0.974 loops=40389) -> Single-row index lookup on promotion using PRIMARY (p_promo_sk=store_sales.ss_promo_sk) (cost=0.523 rows=1) (actual time=0.00179..0.00181 rows=0.977 loops=40389) -> Single-row index lookup on item using PRIMARY (i_item_sk=store_sales.ss_item_sk) (cost=0.523 rows=1) (actual time=0.00214..0.00216 rows=1 loops=39339) -> Filter: (date_dim.d_year = 1998) (cost=0.523 rows=1) (actual time=0.00221..0.00223 rows=0.196 loops=39339) -> Single-row index lookup on date_dim using PRIMARY (d_date_sk=store_sales.ss_sold_date_sk) (cost=0.523 rows=1) (actual time=0.00204..0.00206 rows=0.989 loops=39339) I am not proposing a patch, but it seems the performance can be significantly improved by changing the usage of `calculate_condition_filter`. I am wondering whether we can optimize any logic to enable the second query plan, which seems much more efficient? How to repeat: I dumped the entire database: https://drive.google.com/file/d/1mCwATIQtNwYftxVvMCNz6xgjvRDCrhtq/view?usp=sharing Compile MySQL in two versions, one is original and the other is with the changed IF condition. Then compare the execution of above query on both versions.