AnsweredAssumed Answered

Drill Optimizer - Partition Pruning on Joins - Star Query

Question asked by besen on Feb 9, 2017
Latest reply on Feb 24, 2017 by besen

Hi Drill Team,

 

I have a question regarding a STAR query optimization.

 

I have a fact table - SALES_FACT, which is partitioned by DATE. It is stored as a Parquet file.

 

The partitioned DATE column in the fact table joins with another DIMENSION table - DATE_DIMENSION

 

Now, when I issue a query as below, the optimizer is able to prune the partitions appropriately, since the filter is directly applied on the partitioned column

 

select

          date_dim.month_name,

           sum(sales.amount)

from

           sales inner join date_dim on sales.transaction_date = date_dim.date

where

           sales.transaction_date between '01-01-2017' and '01-31-2017'

group by

           date_dim.month_name

   

 

Now, when I change the query as below, the partitions aren't pruned.

 

select

           date_dim.month_name,

           sum(sales.amount)

from

           sales inner join date_dim on sales.transaction_date = date_dim.date

where

           date_dim.date between '01-01-2017' and '01-31-2017'

group by

           date_dim.month_name

Is there a way I can get the drill optimizer to get the partition pruning working for the 2nd query?

Outcomes