[ https://issues.apache.org/jira/browse/HIVE-23493?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Krisztian Kasa updated HIVE-23493: ---------------------------------- Resolution: Fixed Status: Resolved (was: Patch Available) > Rewrite plan to join back tables with many projected columns joined multiple > times > ---------------------------------------------------------------------------------- > > Key: HIVE-23493 > URL: https://issues.apache.org/jira/browse/HIVE-23493 > Project: Hive > Issue Type: New Feature > Components: CBO > Reporter: Krisztian Kasa > Assignee: Krisztian Kasa > Priority: Major > Labels: pull-request-available > Attachments: HIVE-23493.1.patch > > Time Spent: 1h 40m > Remaining Estimate: 0h > > Queries with a pattern where one or more tables joins with a fact table in a > CTE. Many columns are projected out those tables and then grouped in the CTE. > The main query joins multiple instances of the CTE and may project a subset > of these. > The optimization is to rewrite the CTE to include only key (PK, non null > Unique Key) columns and join the tables back to the resultset of the main > query to fetch the rest of the wide columns. This reduces the datasize of the > joined back tables that is broadcast/shuffled throughout the DAG processing. > Example query, tpc-ds query4 > {code} > with year_total as ( > select c_customer_id customer_id > ,c_first_name customer_first_name > ,c_last_name customer_last_name > ,c_preferred_cust_flag customer_preferred_cust_flag > ,c_birth_country customer_birth_country > ,c_login customer_login > ,c_email_address customer_email_address > ,d_year dyear > > ,sum(((ss_ext_list_price-ss_ext_wholesale_cost-ss_ext_discount_amt)+ss_ext_sales_price)/2) > year_total > ,'s' sale_type > from customer > ,store_sales > ,date_dim > where c_customer_sk = ss_customer_sk > and ss_sold_date_sk = d_date_sk > group by c_customer_id > ,c_first_name > ,c_last_name > ,c_preferred_cust_flag > ,c_birth_country > ,c_login > ,c_email_address > ,d_year > union all > select c_customer_id customer_id > ,c_first_name customer_first_name > ,c_last_name customer_last_name > ,c_preferred_cust_flag customer_preferred_cust_flag > ,c_birth_country customer_birth_country > ,c_login customer_login > ,c_email_address customer_email_address > ,d_year dyear > > ,sum((((cs_ext_list_price-cs_ext_wholesale_cost-cs_ext_discount_amt)+cs_ext_sales_price)/2) > ) year_total > ,'c' sale_type > from customer > ,catalog_sales > ,date_dim > where c_customer_sk = cs_bill_customer_sk > and cs_sold_date_sk = d_date_sk > group by c_customer_id > ,c_first_name > ,c_last_name > ,c_preferred_cust_flag > ,c_birth_country > ,c_login > ,c_email_address > ,d_year > union all > select c_customer_id customer_id > ,c_first_name customer_first_name > ,c_last_name customer_last_name > ,c_preferred_cust_flag customer_preferred_cust_flag > ,c_birth_country customer_birth_country > ,c_login customer_login > ,c_email_address customer_email_address > ,d_year dyear > > ,sum((((ws_ext_list_price-ws_ext_wholesale_cost-ws_ext_discount_amt)+ws_ext_sales_price)/2) > ) year_total > ,'w' sale_type > from customer > ,web_sales > ,date_dim > where c_customer_sk = ws_bill_customer_sk > and ws_sold_date_sk = d_date_sk > group by c_customer_id > ,c_first_name > ,c_last_name > ,c_preferred_cust_flag > ,c_birth_country > ,c_login > ,c_email_address > ,d_year > ) > select > t_s_secyear.customer_id > ,t_s_secyear.customer_first_name > ,t_s_secyear.customer_last_name > ,t_s_secyear.customer_birth_country > from year_total t_s_firstyear > ,year_total t_s_secyear > ,year_total t_c_firstyear > ,year_total t_c_secyear > ,year_total t_w_firstyear > ,year_total t_w_secyear > where t_s_secyear.customer_id = t_s_firstyear.customer_id > and t_s_firstyear.customer_id = t_c_secyear.customer_id > and t_s_firstyear.customer_id = t_c_firstyear.customer_id > and t_s_firstyear.customer_id = t_w_firstyear.customer_id > and t_s_firstyear.customer_id = t_w_secyear.customer_id > and t_s_firstyear.sale_type = 's' > and t_c_firstyear.sale_type = 'c' > and t_w_firstyear.sale_type = 'w' > and t_s_secyear.sale_type = 's' > and t_c_secyear.sale_type = 'c' > and t_w_secyear.sale_type = 'w' > and t_s_firstyear.dyear = 1999 > and t_s_secyear.dyear = 1999+1 > and t_c_firstyear.dyear = 1999 > and t_c_secyear.dyear = 1999+1 > and t_w_firstyear.dyear = 1999 > and t_w_secyear.dyear = 1999+1 > and t_s_firstyear.year_total > 0 > and t_c_firstyear.year_total > 0 > and t_w_firstyear.year_total > 0 > and case when t_c_firstyear.year_total > 0 then t_c_secyear.year_total / > t_c_firstyear.year_total else null end > > case when t_s_firstyear.year_total > 0 then > t_s_secyear.year_total / t_s_firstyear.year_total else null end > and case when t_c_firstyear.year_total > 0 then t_c_secyear.year_total / > t_c_firstyear.year_total else null end > > case when t_w_firstyear.year_total > 0 then > t_w_secyear.year_total / t_w_firstyear.year_total else null end > order by t_s_secyear.customer_id > ,t_s_secyear.customer_first_name > ,t_s_secyear.customer_last_name > ,t_s_secyear.customer_birth_country > limit 100; > {code} -- This message was sent by Atlassian Jira (v8.3.4#803005)