zabetak commented on code in PR #6746:
URL: https://github.com/apache/hive/pull/6746#discussion_r4135877204
##########
ql/src/test/results/clientpositive/perf/tpcds30tb/tez/cbo_query16.q.out:
##########
@@ -3,22 +3,21 @@ HiveProject(order count=[$0], total shipping cost=[$1], total
net profit=[$2])
HiveAggregate(group=[{}], agg#0=[count(DISTINCT $4)], agg#1=[sum($5)],
agg#2=[sum($6)])
HiveAntiJoin(condition=[=($4, $14)], joinType=[anti])
HiveSemiJoin(condition=[AND(=($4, $14), <>($3, $13))], joinType=[semi])
- HiveProject(cs_ship_date_sk=[$2], cs_ship_addr_sk=[$3],
cs_call_center_sk=[$4], cs_warehouse_sk=[$5], cs_order_number=[$6],
cs_ext_ship_cost=[$7], cs_net_profit=[$8], d_date_sk=[$9], d_date=[$10],
ca_address_sk=[$0], ca_state=[$1], cc_call_center_sk=[$11], cc_county=[$12])
- HiveJoin(condition=[=($4, $11)], joinType=[inner], algorithm=[none],
cost=[not available])
- HiveJoin(condition=[=($3, $0)], joinType=[inner],
algorithm=[none], cost=[not available])
- HiveProject(ca_address_sk=[$0], ca_state=[CAST('NY'):CHAR(2)
CHARACTER SET "UTF-16LE"])
- HiveFilter(condition=[=($8, 'NY')])
- HiveTableScan(table=[[default, customer_address]],
table:alias=[customer_address])
- HiveJoin(condition=[=($0, $7)], joinType=[inner],
algorithm=[none], cost=[not available])
- HiveProject(cs_ship_date_sk=[$1], cs_ship_addr_sk=[$9],
cs_call_center_sk=[$10], cs_warehouse_sk=[$13], cs_order_number=[$16],
cs_ext_ship_cost=[$27], cs_net_profit=[$32])
- HiveFilter(condition=[AND(IS NOT NULL($9), IS NOT NULL($1),
IS NOT NULL($10))])
- HiveTableScan(table=[[default, catalog_sales]],
table:alias=[cs1])
- HiveProject(d_date_sk=[$0], d_date=[$2])
- HiveFilter(condition=[BETWEEN(false, CAST($2):TIMESTAMP(9),
2001-04-01 00:00:00:TIMESTAMP(9), 2001-05-31 00:00:00:TIMESTAMP(9))])
- HiveTableScan(table=[[default, date_dim]],
table:alias=[date_dim])
- HiveProject(cc_call_center_sk=[$0], cc_county=[$25])
- HiveFilter(condition=[IN($25, 'Daviess County':VARCHAR(30)
CHARACTER SET "UTF-16LE", 'Franklin Parish':VARCHAR(30) CHARACTER SET
"UTF-16LE", 'Huron County':VARCHAR(30) CHARACTER SET "UTF-16LE", 'Levy
County':VARCHAR(30) CHARACTER SET "UTF-16LE", 'Ziebach County':VARCHAR(30)
CHARACTER SET "UTF-16LE")])
- HiveTableScan(table=[[default, call_center]],
table:alias=[call_center])
Review Comment:
For Q16, the join sequence hasn't changed so intermediate results size
should be the same. Both plans for this query (before and after) join the
catalog_sales with date_dim table first, then customer_address, and finally
with call_center. I cannot really tell if the new plan for Q16 is better on
paper. In fact, I would say that its roughly the same. Is there some Hive logic
where it would make a difference if the customer_address table appears on the
left or on the right side of the join?
The reply focuses a lot on Q16 but I was mainly interested to have more
precise information for the remaining plan changes.
Regarding the precision of the selectivity, conceptually it should be better
but I am wondering if this is a reality for TPC-DS queries. Can you provide
some evidence on why the new TPC-DS plans are better?
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
