Manya0407 commented on code in PR #6746:
URL: https://github.com/apache/hive/pull/6746#discussion_r4121712997
##########
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:
@zabetak Yes — I went through the before/after plans for query16 (and the
same pattern in other perf/tpcds30tb/tez/cbo_query*.q.out updates).
Join order: CBO now builds Join(Join(cs1, date_dim), customer_address),
call_center) instead of pulling customer_address above cs1 ⨝ date_dim. With the
updated range / uniform MIN–MAX selectivity, the date filter on date_dim and
nullability filters on cs1 get ** tighter row estimates**, so the optimizer
prefers to join the fact to the selective time slice first, then
customer_address (NY), then call_center (counties) — which is the usual “shrink
the fact early” shape.
Better on paper? For query16, yes — the new tree defers larger dimension
joins until after cs1 + date_dim, so intermediate CBO cardinalities are not
larger than before at the main joins; the semi/anti subplans still sit on the
same cs1 spine. I also checked tez/query16.q.out: scan stats are unchanged and
map join is still chosen on the probe side, so the logical reorder did not
obviously break the execution strategy for this query.
More precise selectivity? The change uses column MIN/MAX +
uniform-over-range when there is no histogram
(hive.stats.filter.range.uniform), instead of a coarse default — so predicates
like date ranges and bounded filters should be closer to reality than “1/3 of
rows,” though it is still an assumption, not ground truth for skewed TPC-DS
columns.
Runtime: Agreed that needs benchmarks to claim perf; this reply is plan/cost
reasoning only.
--
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]
