[
https://issues.apache.org/jira/browse/IMPALA-15286?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Joe McDonnell updated IMPALA-15286:
-----------------------------------
Description:
For a SQL that does SUM(a * x + b), this can be rewritten as a * SUM x + b*
COUNT x . During evaluation, this only needs to compute the SUM and COUNT on a
row-by-row basis and can do the multiplication and addition at the end. This
reduces the cost of these types of aggregation. It also can share computation
between multiple SUMs that use the same variables.
ClickBench's SQL #30 has an extreme version of this:
{noformat}
SELECT SUM(ResolutionWidth),
SUM(ResolutionWidth + 1),
SUM(ResolutionWidth + 2),
SUM(ResolutionWidth + 3),
...
SUM(ResolutionWidth + 89)
FROM hits;{noformat}
This does a large number of individual aggregations. A rewrite of the SQL to do
this optimization allows the plan to only calculate the SUM(ResolutionWidth)
and COUNT(ResolutionWidth).
{noformat}
SELECT SUM(ResolutionWidth),
SUM(ResolutionWidth) + COUNT(ResolutionWidth),
SUM(ResolutionWidth) + 2 * COUNT(ResolutionWidth),
SUM(ResolutionWidth) + 3 * COUNT(ResolutionWidth),
...
SUM(ResolutionWidth) + 89 * COUNT(ResolutionWidth)
FROM hits;{noformat}
In my hand tests using the official ClickBench parquet file, this is about 8x
faster.
was:
For a SQL that does SUM(a * x + b), this can be rewritten as a * SUM x + b*
COUNT
!/jira/images/icons/emoticons/error.png|width=16,height=16,align=absmiddle! .
During evaluation, this only needs to compute the SUM and COUNT on a row-by-row
basis and can do the multiplication and addition at the end. This reduces the
cost of these types of aggregation. It also can share computation between
multiple SUMs that use the same variables.
ClickBench's SQL #30 has an extreme version of this:
{noformat}
SELECT SUM(ResolutionWidth),
SUM(ResolutionWidth + 1),
SUM(ResolutionWidth + 2),
SUM(ResolutionWidth + 3),
...
SUM(ResolutionWidth + 89)
FROM hits;{noformat}
This does a large number of individual aggregations. A rewrite of the SQL to do
this optimization allows the plan to only calculate the SUM(ResolutionWidth)
and COUNT(ResolutionWidth).
{noformat}
SELECT SUM(ResolutionWidth),
SUM(ResolutionWidth) + COUNT(ResolutionWidth),
SUM(ResolutionWidth) + 2 * COUNT(ResolutionWidth),
SUM(ResolutionWidth) + 3 * COUNT(ResolutionWidth),
...
SUM(ResolutionWidth) + 89 * COUNT(ResolutionWidth)
FROM hits;{noformat}
In my hand tests using the official ClickBench parquet file, this is about 8x
faster.
> Rewrite SUM of linear equations to linear equation of SUMs / COUNTs
> -------------------------------------------------------------------
>
> Key: IMPALA-15286
> URL: https://issues.apache.org/jira/browse/IMPALA-15286
> Project: IMPALA
> Issue Type: Improvement
> Components: Frontend
> Reporter: Joe McDonnell
> Priority: Major
>
> For a SQL that does SUM(a * x + b), this can be rewritten as a * SUM x + b*
> COUNT x . During evaluation, this only needs to compute the SUM and COUNT on
> a row-by-row basis and can do the multiplication and addition at the end.
> This reduces the cost of these types of aggregation. It also can share
> computation between multiple SUMs that use the same variables.
> ClickBench's SQL #30 has an extreme version of this:
> {noformat}
> SELECT SUM(ResolutionWidth),
> SUM(ResolutionWidth + 1),
> SUM(ResolutionWidth + 2),
> SUM(ResolutionWidth + 3),
> ...
> SUM(ResolutionWidth + 89)
> FROM hits;{noformat}
> This does a large number of individual aggregations. A rewrite of the SQL to
> do this optimization allows the plan to only calculate the
> SUM(ResolutionWidth) and COUNT(ResolutionWidth).
> {noformat}
> SELECT SUM(ResolutionWidth),
> SUM(ResolutionWidth) + COUNT(ResolutionWidth),
> SUM(ResolutionWidth) + 2 * COUNT(ResolutionWidth),
> SUM(ResolutionWidth) + 3 * COUNT(ResolutionWidth),
> ...
> SUM(ResolutionWidth) + 89 * COUNT(ResolutionWidth)
> FROM hits;{noformat}
> In my hand tests using the official ClickBench parquet file, this is about 8x
> faster.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
