Zoltán Borók-Nagy created IMPALA-15670:
------------------------------------------
Summary: Calcite: merge cross-joined scalar aggregates over the
same input into one scan (TPC-DS q9, q28, q88: 67.7 s -> 20.9 s)
Key: IMPALA-15670
URL: https://issues.apache.org/jira/browse/IMPALA-15670
Project: IMPALA
Issue Type: Improvement
Components: Frontend
Reporter: Zoltán Borók-Nagy
TPC-DS q9, q28 and q88 cross-join 5-8 single-row aggregates over store_sales
that differ only in their filters:
* q9: quantity ranges (5 scans);
* q28: quantity and price ranges (6 scans);
* q88: half-hour time windows, each over the same store_sales x
household_demographics x time_dim x store join (8 scans).
Impala runs every branch as a separate full scan. At SF3000 that is 8.64B rows
per scan, and all scans start together and compete for the cores (40-64 scan
threads per 16-vCPU host). Nothing can be pruned, because the data is not
sorted on the filter columns.
*Rewrite:* filter the shared input once, on the conjuncts common to all
branches plus the OR of the branch-specific ones, and give each aggregate CASE
WHEN <its filter> THEN x END as its argument. That is equivalent for COUNT,
SUM, AVG, MIN and MAX, DISTINCT included, because they ignore NULLs.
*Prototype:* a Calcite rule that runs after the expand step and merges any
cross join of scalar aggregates (optionally under a Project) over an identical
input. It also adds the per-column hull of the branches' range bounds as plain
conjuncts, e.g. ss_quantity BETWEEN 0 AND 30 for q28, so the scan can use them.
Measured at SF3000 on 8 executors (PLANNER=CALCITE, CTE_THRESHOLD=1, MT_DOP=8),
with identical results:
||Query||Separate scans||Merged||
|q9|15.4 s|6.3 s|
|q28|21.0 s|9.9 s|
|q88|31.3 s|4.7 s|
q90 also merges but stays at 0.9 s. In total the rule saves 46.8 s, matching
hand-written versions. For q28, grouping by the matching branch instead (one
scan, one aggregation keyed by branch) was faster still (7.6 s), because of its
distinct counts.
IMPALA-15489 reports q88 becoming 50% slower with its fix; merging the scans
removes q88's repeated fact-table reads either way.
Prototype: https://gerrit.cloudera.org/#/c/25097/
--
This message was sent by Atlassian Jira
(v10.3.26#10030026)
