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.
E.g.
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)