Zoltán Borók-Nagy created IMPALA-15668:
------------------------------------------
Summary: Calcite: push aggregations below fact-to-dimension joins
(TPC-DS q4, q11, q74)
Key: IMPALA-15668
URL: https://issues.apache.org/jira/browse/IMPALA-15668
Project: IMPALA
Issue Type: Improvement
Components: Frontend
Reporter: Zoltán Borók-Nagy
TPC-DS q4, q11 and q74 join a large fact table with customer and then group by
customer attributes (eight columns in q4, mostly strings). The join processes
every fact row, although grouping the fact rows by the customer key first would
shrink them a lot. At SF1000, a hand rewrite of q4 that first groups sales by
(customer_sk, year) ran in 5.6 s instead of 13.0 s, with identical results.
*Prototype:* a rule based on Calcite's AggregateJoinTransposeRule runs after
join ordering, before CTE substitution. It fires on an inner join only if the
aggregation groups by attributes of a side that is unique on its join key, and
the other side has at least 10M rows and is estimated to shrink to at most 5%
of them. CalciteTable now reports the table's primary key to Calcite, so the
unique side is recognized.
Measured at SF1000 on 8 executors (PLANNER=CALCITE, CTE_THRESHOLD=1, MT_DOP=8):
q4 13.0 s -> 7.6 s, q11 8.1 s -> 4.9 s, q74 6.5 s -> 3.9 s; the 104-query total
337.8 s -> 313.5 s, with identical results.
*Lessons from the prototype:*
* An earlier version ran after CTE substitution. It permuted q22's output
columns and changed the results of q36, q47, q57, q67a and q86 (rollup or
window queries over CTEs). Running the rule before CTE substitution and only on
fact-to-dimension joins gave no differences.
* At SF3000 the rule made q47 slower: the pushed-down aggregation groups by
ss_store_sk (7.03M groups) instead of store name and company. Disabling the
rule brings q47 from 10.1 s to 6.2 s but costs q4 14 s and q11 8 s. A guard
that refuses a transpose that multiplies the group count by more than 4 saves
3.1 s on the 17 queries it touches (q47 9.8 s -> 6.6 s, q4 19.9 s -> 17.7 s;
q31 loses 1.5 s), with identical results.
* Two alternatives were worse at SF3000: a stricter ratio limit (0.02) cost q4
4.1 s, and pushing through filter-only dimensions (for q70) gained 0.7 s on q70
but cost q77 4.9 s and q86 2.2 s.
*At SF10000* (12 executors, best of 3, prototypes):
* The rule threw on q23 because of a nullable COUNT type mismatch. With that
fixed it fires: q23_1 108 -> 78 s, q23_2 91 -> 70 s, on top of IMPALA-15651.
* The group growth guard, applied only when the dimension has at most 1M rows:
q47 22.7 -> 15.7 s.
Prototypes: https://gerrit.cloudera.org/#/c/25093/ (the rule) and
https://gerrit.cloudera.org/#/c/25098/ (the group growth guard, on top of it)
--
This message was sent by Atlassian Jira
(v10.3.26#10030026)
