Zoltán Borók-Nagy created IMPALA-15667:
------------------------------------------
Summary: Calcite: join single-row inputs into the table their
condition references before join ordering (TPC-DS q54 scans all of store_sales)
Key: IMPALA-15667
URL: https://issues.apache.org/jira/browse/IMPALA-15667
Project: IMPALA
Issue Type: Improvement
Components: Frontend
Reporter: Zoltán Borók-Nagy
TPC-DS q54 filters date_dim by d_month_seq BETWEEN (scalar subquery) + 1 AND
(scalar subquery) + 3. With PLANNER=CALCITE the two single-row subqueries are
joined by nested-loop joins above the store_sales x date_dim hash join. So the
date_dim build is the whole table (73K rows), the runtime filters on
ss_sold_date_sk span the full range, and partition pruning does nothing.
At SF3000 (8 executors, MT_DOP=8) the store_sales scan reads 8.25B rows and
returns 3.6M; it is about 90% of q54's 4.6 s. The original planner builds the
same shape.
*Why:* in ImpalaLoptOptimizeJoinRule the single-row factors are connected only
by >= and <= predicates. LoptMultiJoin.setFactorWeights() gives those weight 2
(equality gets 3), so getBestNextFactor() adds them after every equi-join
factor, on top of the fact join. Neither pushDownFactor() nor the min-rows
ordering (IMPALA-15665) moves them back to date_dim.
*Proposal:* a rule that runs before JOIN_TO_MULTI_JOIN: for Join(X, S) where S
has a max row count of at most 1 and the condition references a single leaf T
of X, rewrite it to X with T replaced by Join(T, S). An inner join with an
input of at most one row cannot increase the row count, so the rewrite is safe.
Alternatively, treat such a factor as part of the factor it references in
getBestNextFactor().
*Validation:* q54 with the subqueries replaced by their constant values
(month_seq bounds) puts the predicates on date_dim, turns the runtime filter
into a partition filter, and runs in 1.3 s instead of 4.6 s with identical
results.
--
This message was sent by Atlassian Jira
(v10.3.26#10030026)
