[
https://issues.apache.org/jira/browse/HIVE-30261?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
ASF GitHub Bot updated HIVE-30261:
----------------------------------
Labels: pull-request-available (was: )
> Push a partial aggregation below an inner join
> ----------------------------------------------
>
> Key: HIVE-30261
> URL: https://issues.apache.org/jira/browse/HIVE-30261
> Project: Hive
> Issue Type: Improvement
> Components: Physical Optimizer
> Reporter: Krisztian Kasa
> Assignee: Krisztian Kasa
> Priority: Major
> Labels: pull-request-available
>
> In a star join followed by a GROUP BY, the fact table rows go through the
> shuffle and the join before any aggregation happens. When the join keys and
> the GROUP BY keys have few distinct values on the fact side, the rows could
> be partially aggregated below the join first. Then far fewer rows are
> shuffled, joined and aggregated.
> h4. Criteria to meet for push-down:
> * The join is an inner join. Every input to the join is a ReduceSink
> operator, and for an n-way join, every condition is an inner join.
> * The GROUP BY is above the join with only Select operators between them. It
> has keys, so a full aggregate cannot be pushed down.
> * GROUP BYs with grouping sets and DISTINCT aggregations are not supported.
> * Only SUM, $SUM0, COUNT, MIN, and MAX aggregate functions are supported.
> These are the ones that support partial aggregation.
> * Every key and every argument is deterministic and references at most one
> join input.
> * Every aggregate function argument references the same join input, and the
> GROUP BY is pushed down to that input.
> * If no argument has columns (e.g., COUNT(\*)), the GROUP BY is pushed down
> to the larger join input.
> * The pushed GROUP BY keys are not empty, and there are no more of them than
> the original keys plus the columns of the CASE/IF conditions (see CASE / IF
> split).
> h4. Cost based criteria
> * Statistics are available: the row counts of the join and the input to
> which the GROUP BY is pushed down, and the column statistics of every pushed
> key. When no argument has columns (COUNT(\*)), the row counts of every join
> input are needed too.
> * The join outputs at most max.join.expansion × the rows of the input to
> which the GROUP BY is pushed down.
> * The join reduces the input to which the GROUP BY is pushed down by at most
> a factor of max.join.reduction. Hive has no dynamic filter on the probe side
> of a map join, so the pushed GROUP BY would also process every row the join
> discards.
> * The NDV of every pushed key is at most max.key.ndv.ratio × the pushed
> input rows.
> h4. CASE / IF split
> An aggregate function argument can be
> {code:java}
> CASE WHEN cond THEN value [ELSE [NULL|0|0.0]] END
> {code}
> or
> {code:java}
> IF(cond, value)
> IF(cond, value, [NULL|0|0.0])
> {code}
> where cond references one join input and value the other. For example, from
> TPC-DS query 43:
> {code:java}
> sum(case when d_day_name = 'Sunday' then ss_sales_price else null end)
> {code}
> * The value is aggregated below the join: sum(ss_sales_price).
> * The merge above the join applies the condition: sum(if(d_day_name =
> 'Sunday', partial, null)). COUNT merges with sum(if(cond, partial, 0)).
> Every row in a group created by the pushed-down GROUP BY has the same join
> key, so it joins with the same rows from the other input. For each of those
> rows, the condition evaluates to either true or false for the entire group.
> Restrictions:
> * The value expression can only contain columns, constants, +, -, *,
> negation, and casts. Without partial aggregate push-down, the value
> expression is evaluated only for rows where the condition is true. Since the
> pushed-down aggregate does not include the condition, all values are
> aggregated—even for rows where the condition is false—so evaluating them must
> not fail in order to preserve the original behavior.
> * ELSE must be NULL or omitted. 0 is also allowed for SUM and $SUM0.
> * A CASE expression can have only one WHEN clause.
--
This message was sent by Atlassian Jira
(v10.3.26#10030026)