Zoltán Borók-Nagy created IMPALA-15665:
------------------------------------------
Summary: Calcite: LoptOptimizeJoinRule also needs min-row join
orders (TPC-DS q72)
Key: IMPALA-15665
URL: https://issues.apache.org/jira/browse/IMPALA-15665
Project: IMPALA
Issue Type: Improvement
Components: Frontend
Reporter: Zoltán Borók-Nagy
For each starting factor, ImpalaLoptOptimizeJoinRule builds one join order: it
adds next the factor with the highest join weight (3 per equality, 2 per other
comparison), ignoring row counts.
In TPC-DS q72, inv_quantity_on_hand < cs_quantity raises the
catalog_sales-inventory weight to 5, so inventory joins before the d1 date
filter. At SF1000 that join matches on item only, produces 478M rows (estimated
11.6B) and takes 12.6-15 s.
*Prototype:* for each starting factor the rule also builds a second order that
adds next the connected factor whose join with the current tree has the fewest
estimated rows. The cost model chooses among all orders. Removable joins and
cross products fall back to the weight-based choice. The rule considers twice
as many orders.
q72 then joins d1 to d2 on d_week_seq first and matches catalog_sales on (date,
item); that join takes 1.5 s and outputs 9.1M rows.
Measured at SF1000 on 8 executors (PLANNER=CALCITE, CTE_THRESHOLD=1, MT_DOP=8),
over 3 runs:
* With BROADCAST_BYTES_LIMIT=1g, q72 went from 16.4 s to 8.9 s; q64 got 1.1 s
slower.
* Without that option the new plan broadcasts the 144M-row catalog_returns (6.7
s build), and q29, q64 and q72 together only went from 28.3 s to 27.7 s. The
broadcast comes from the composite-key join estimate: q72's (date, item) join
is estimated at 11.6B rows and returns 9.1M (see IMPALA-?).
Prototype: https://gerrit.cloudera.org/#/c/25091/
--
This message was sent by Atlassian Jira
(v10.3.26#10030026)
