In simplified terms, the following query gets accelerated
select * from A
But if I group on
select * from A as a subquery, it doesn’t get accelerated.
select Col1, count(1) from (select * from A) group by Col1
Raw and Aggregate reflections are available for the tables referred. I think the execution plan is vastly different for both the cases. If there is a specific way to write this query so as to force Dremio to pick up the reflection, it would be great to know. Else I have to download the data from the non-grouped query and use external product like Redash Cache or Excel to pivot on it. That is a bit cumbersome to ask of my team.
Below is the original query which gets accelerated:
with dau as ( select distinct user_profile_id , answered_date from core.answers a where answered_date <= timestampadd(day,-2,current_date) and answered_date >= timestampadd(day,-6,current_date) ) , signups as ( select signups.* from "core".signups where signups.user_profile_id in (select user_profile_id from dau) ) , subscribers as ( select * from "core".subscriptions where subscriptions.user_profile_id in (select user_profile_id from dau) and module_id in (1,2,6) ) , base as ( select dau.* , case when signups."klass" in ('5','6') then '5-6' when signups."klass" in ('7','8') then '7-8' when signups."klass" in ('9','10') then '9-10' else '11/12/12+' end as klass , case when subscribers.user_profile_id is null then 'free' else 'paid' end as subscription from dau inner join signups on dau.user_profile_id = signups.user_profile_id left join subscribers on subscribers.user_profile_id = dau.user_profile_id ) select * from base
In the above query, if I replace the last line (
select * from base) with the one below, it won’t be accelerated.
, base2 as (select * from base) select answered_date , klass, subscription , count(distinct user_profile_id) as dau from base2 group by answered_date, klass, subscription
The query plan is vastly different in both of them.
Query profile for the non-grouped & accelerated 04d0d19e-6f46-45e1-b984-191e0829d45a.zip (56.3 KB)
Query profile for the grouped & non-accelerated
0709016b-f280-458c-974a-0884243ad07b.zip (48.6 KB)