[ 
https://issues.apache.org/jira/browse/HIVE-30261?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

Krisztian Kasa updated HIVE-30261:
----------------------------------
        Parent: HIVE-30273
    Issue Type: Sub-task  (was: Improvement)

> Push a partial aggregation below an inner join
> ----------------------------------------------
>
>                 Key: HIVE-30261
>                 URL: https://issues.apache.org/jira/browse/HIVE-30261
>             Project: Hive
>          Issue Type: Sub-task
>          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)

Reply via email to