Zoltán Borók-Nagy created IMPALA-15661:
------------------------------------------
Summary: Calcite: CTE suggestions are split into
select-project-join parts, so shared subtrees with UNION, window or HAVING are
materialized as raw scans (TPC-DS q14_1 fails at SF3000)
Key: IMPALA-15661
URL: https://issues.apache.org/jira/browse/IMPALA-15661
Project: IMPALA
Issue Type: Improvement
Components: Frontend
Reporter: Zoltán Borók-Nagy
Since IMPALA-15334, CalciteOptimizer.runCTEProgram() passes every suggested
subtree through MaterializedViewSubTreeSplitter.maximalSubTrees() (master
1a26e1f778), so that the materialized-view rewrite can match it. The splitter
keeps only trees of TableScan, Project, Filter and INNER Join, optionally under
one simple Aggregate. A suggested subtree that contains a UNION, a window
function, an outer join, or an aggregate below a join is replaced by its valid
parts.
Effect on TPC-DS (PLANNER=CALCITE, CTE_THRESHOLD=1, MT_DOP=8, 8 executors):
* q14_1: the repeated subtrees are replaced by three raw fact x date_dim scans
(estimated 8.25B, 4.30B and 2.16B rows at SF3000), each with 15 consumers.
Their buffers reach 36.7 GB per host and the query fails (IMPALA-15313,
IMPALA-15656). At SF1000 it runs, but its consumers wait up to 13 s for the
producers.
* q14_2 computes avg_sales twice.
* q70: the "results" subquery contains the IN-subquery's window, so it is
rejected as a whole and computed three times (once per rollup level). Only the
inner aggregate and date_dim become CTEs.
*Proposal:* when a suggested subtree cannot be rewritten as a materialized
view, materialize it as a unit by digest: replace each identical occurrence
with a consumer of one producer, instead of splitting it into its SPJ parts.
Shared subtrees that contain an aggregate and have a small estimated output are
the most valuable.
Estimates for SF3000, not measured: q14_1 goes from failing to about 15-20 s,
q14_2 saves about 6 s, and q70 about 3 s.
At SF10000 (12 executors, best of 3), a prototype that materializes
aggregate-rooted shared subtrees, such as q14's avg_sales, as one unit lets
q14_1 complete (46 s with all prototyped changes on; it failed out of memory
before) and cuts q5 from 17.2 to 10.2 s and q80 from 14.3 to 9.7 s.
--
This message was sent by Atlassian Jira
(v10.3.26#10030026)
