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

Krisztian Kasa updated HIVE-30261:
----------------------------------
    Description: 
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 where 
the GROUP BY is pushed.
 * The join reduces the input where the GROUP BY is pushed 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.

  was:
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 of the input where 
the GROUP BY is pushed, 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 where 
the GROUP BY is pushed.
 * The join reduces the input where the GROUP BY is pushed 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.


> 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
>
> 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 where 
> the GROUP BY is pushed.
>  * The join reduces the input where the GROUP BY is pushed 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