[ https://issues.apache.org/jira/browse/HIVE-21382?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16785155#comment-16785155 ]
Vineet Garg commented on HIVE-21382: ------------------------------------ Challenge with query23 is that group by has expressions (e.g. {{substr}}) which couldn't be tracked back to source table making it difficult for optimizer to determine if it is safe or not to remove such keys. I am attaching a patch which should otherwise work. It extends the existing logic to consider group by keys reduction even if columns originate from different source table. > Group by keys reduction optimization - keys are not reduced in query23 > ---------------------------------------------------------------------- > > Key: HIVE-21382 > URL: https://issues.apache.org/jira/browse/HIVE-21382 > Project: Hive > Issue Type: Improvement > Reporter: Vineet Garg > Assignee: Vineet Garg > Priority: Major > Attachments: HIVE-21382.1.patch > > > {code:sql} > explain cbo with frequent_ss_items as > (select substr(i_item_desc,1,30) itemdesc,i_item_sk item_sk,d_date > solddate,count(*) cnt > from store_sales > ,date_dim > ,item > where ss_sold_date_sk = d_date_sk > and ss_item_sk = i_item_sk > and d_year in (1999,1999+1,1999+2,1999+3) > group by substr(i_item_desc,1,30),i_item_sk,d_date > having count(*) >4) > select sum(sales) > from ((select cs_quantity*cs_list_price sales > from catalog_sales > ,date_dim > where d_year = 1999 > and d_moy = 1 > and cs_sold_date_sk = d_date_sk > and cs_item_sk in (select item_sk from frequent_ss_items))) subq > limit 100; > {code} > {code:sql} > HiveSortLimit(fetch=[100]) > HiveProject($f0=[$0]) > HiveAggregate(group=[{}], agg#0=[sum($0)]) > HiveProject(sales=[*(CAST($2):DECIMAL(10, 0), $3)]) > HiveSemiJoin(condition=[=($1, $5)], joinType=[inner]) > HiveJoin(condition=[=($0, $4)], joinType=[inner], algorithm=[none], > cost=[{2.0 rows, 0.0 cpu, 0.0 io}]) > HiveProject(cs_sold_date_sk=[$0], cs_item_sk=[$15], > cs_quantity=[$18], cs_list_price=[$20]) > HiveFilter(condition=[IS NOT NULL($0)]) > HiveTableScan(table=[[perf_constraints, catalog_sales]], > table:alias=[catalog_sales]) > HiveProject(d_date_sk=[$0]) > HiveFilter(condition=[AND(=($6, 1999), =($8, 1))]) > HiveTableScan(table=[[perf_constraints, date_dim]], > table:alias=[date_dim]) > HiveProject(i_item_sk=[$1]) > HiveFilter(condition=[>($3, 4)]) > HiveProject(substr=[$2], i_item_sk=[$1], d_date=[$0], $f3=[$3]) > HiveAggregate(group=[{3, 4, 5}], agg#0=[count()]) > HiveJoin(condition=[=($1, $4)], joinType=[inner], > algorithm=[none], cost=[{2.0 rows, 0.0 cpu, 0.0 io}]) > HiveJoin(condition=[=($0, $2)], joinType=[inner], > algorithm=[none], cost=[{2.0 rows, 0.0 cpu, 0.0 io}]) > HiveProject(ss_sold_date_sk=[$0], ss_item_sk=[$2]) > HiveFilter(condition=[IS NOT NULL($0)]) > HiveTableScan(table=[[perf_constraints, > store_sales]], table:alias=[store_sales]) > HiveProject(d_date_sk=[$0], d_date=[$2]) > HiveFilter(condition=[IN($6, 1999, 2000, 2001, 2002)]) > HiveTableScan(table=[[perf_constraints, date_dim]], > table:alias=[date_dim]) > HiveProject(i_item_sk=[$0], substr=[substr($4, 1, 30)]) > HiveTableScan(table=[[perf_constraints, item]], > table:alias=[item]) > {code} > Right side of HiveSemiJoin has an aggregate which could be reduce to have > only {{i_item_sk}} as group by key since {{i_item_sk}} is primary key. -- This message was sent by Atlassian JIRA (v7.6.3#76005)