[CALCITE-366] Support Aggregate push down in bushy joins

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 1.2.0-incubating
• Labels: hive
• Assignee: Jesús Camacho Rodríguez
• Reporter: Mostafa Mokhtar
• Created: 08/Aug/14 21:56
• Updated: 27/Feb/24 21:59
• Resolved: 07/Apr/15 18:16

Description

Currently OPTIQ doesn't push down aggregates for bushy joins and this can be a performance limitation in some queries.

Ideally before we push down the aggregate we should take into the account the NDV and reduction of the join which we are trying to push the aggregate underneath.

On Hive to measure potential gains I wrote a query which has the following business question based on the TPC-DS schema:
Identify customers from specific gender with good credit rating that spent more than X amount of money during a specific year.

This query has 3 main components
1) Hub containing store_sales & date_dim
2) Hub containing customer and customer_demographics
3) Aggregate

And for that query I have 2 rewrites :
1) Rewrite that generates a bushy join plan
2) Rewrite that generates a bushy join plan and pushes the aggregate down

Results show that aggregate push down in a bushy plan cuts down the query runtime by half.
The aggregate push down reduces the intermediate data in the query from 384GB to 22GB.

Base query finishes in 4,781 seconds with 2,253 GB of intermediate data
Bushy query finished in 1,374 seconds with 384GB of intermediate data
Bushy query + aggregate push down finished in 605 with only 22GB of intermediate data.

Base query

"select 
    c_customer_sk, sum(ss_net_paid) as net_paid
from
    customer,
    customer_demographics,
    store_sales,
    date_dim
where
    c_current_cdemo_sk = cd_demo_sk
        and cd_credit_rating = 'Good'
        and cd_gender = 'F'
        and ss_customer_sk = c_customer_sk
        and ss_customer_sk is not null
        and d_year = 2001
        and d_date_sk = ss_sold_date_sk
group by c_customer_sk
having net_paid > 0
order by net_paid desc
limit 100;"

Bushy query

with ss as 
( 
select 
    ss_net_paid, ss_customer_sk
from
    store_sales,date_dim
where
    d_year = 2001
        and d_date_sk = ss_sold_date_sk
        and ss_customer_sk is not null
), cc as 
(
select 
    c_customer_sk
from
    customer,customer_demographics
where
    c_current_cdemo_sk = cd_demo_sk
        and cd_credit_rating = 'Good'
        and cd_gender = 'F'
)
select 
    c_customer_sk, sum(ss_net_paid) as net_paid
from 
    cc,ss
where
    ss_customer_sk = c_customer_sk
group by c_customer_sk
having net_paid > 0
order by net_paid desc
limit 100;

Bushy query + aggregate push down

Issue Links

• relates to: CALCITE-675 Enable AggregateProjectMergeRule in standard rule set
• relates to: CALCITE-676 AssertionError in GROUPING SETS query
• relates to: CALCITE-751 Push aggregate with aggregate functions through join