# Job doesnt get accelerated by aggregation reflection
**URL:** https://community.dremio.com/t/job-doesnt-get-accelerated-by-aggregation-reflection/10735
**Category:** Uncategorized
**Created:** [June 20, 2023, 8:18am UTC](https://community.dremio.com/t/job-doesnt-get-accelerated-by-aggregation-reflection/10735 "2023-06-20T08:18:33Z")
**Posts on this page:** 14
**Page:** 1
### Author:  [@vincent\_mayer](https://community.dremio.com/u/vincent_mayer)
#### Post date: [June 20, 2023, 8:18am UTC](https://community.dremio.com/t/job-doesnt-get-accelerated-by-aggregation-reflection/10735/1 "2023-06-20T08:18:33Z")
I am performing this sql query
select
l\_returnflag,
l\_linestatus,
sum(l\_quantity) as sum\_qty,
sum(l\_extendedprice) as sum\_base\_price,
sum(l\_extendedprice \* (1 - l\_discount)) as sum\_disc\_price,
sum(l\_extendedprice \* (1 - l\_discount) \* (1 + l\_tax)) as sum\_charge,
avg(l\_quantity) as avg\_qty,
avg(l\_extendedprice) as avg\_price,
avg(l\_discount) as avg\_disc,
count(\*) as count\_order
from
lineitem
where
l\_shipdate \<= date ‘1998-12-01’ - interval ‘90’ day
group by
l\_returnflag,
l\_linestatus
order by
l\_returnflag,
l\_linestatus;
This is the according job profile:
[b1621d6f-4bc2-4532-b963-7a91f170bb9d.zip](https://community.dremio.com/uploads/short-url/snPU6wpCraTEBarnWp55BP6RFll.zip) (23,2 KB)
I created one raw reflection and one aggregation reflection on the table lineitem. But only the raw reflection gets picked to accelerate the query.
This is the job profile for the raw reflection:
[4abc01b1-b814-498c-87bf-7f579607517a.zip](https://community.dremio.com/uploads/short-url/o4AcO5fF7jELULi70zShlY0oxo4.zip) (17,2 KB)
This is the job profile for the aggregation reflection:
[0d2a9c0c-0719-4de2-b4dc-710db7501bc7.zip](https://community.dremio.com/uploads/short-url/mRnJ9Li3h6EEmm8hwYpnRdho6S8.zip) (27,8 KB)
Can anyone help me so the sql query also gets accelerated by the aggregation reflection?
Thanks in advance!
---
### Author:  [@balaji.ramaswamy](https://community.dremio.com/u/balaji.ramaswamy)
#### Post date: [June 26, 2023, 10:29pm UTC](https://community.dremio.com/t/job-doesnt-get-accelerated-by-aggregation-reflection/10735/2 "2023-06-26T22:29:56Z")
@vincent_mayer I see in your query you have used the below dimensions
```auto
l_returnflag, l_linestatus,
```
But I see your Agg reflection has below dimensions
```auto
l_returnflag, l_shipdate
```
`l_linestatus` is missing,
Similarly, the Agg reflection has the below measures defined
```auto
l_quantity and l_extendedprice (SUM)
```
While the query is using the below easures
```auto
l_quantity, l_extendedprice, l_discount, l_extendedprice, l_tax
```
Also, 3 of the measures use Avg and this is not part of the reflection definition
Kindly, let me know if you have any questions
---
### Author:  [@vincent\_mayer](https://community.dremio.com/u/vincent_mayer)
#### Post date: [July 2, 2023, 1:45pm UTC](https://community.dremio.com/t/job-doesnt-get-accelerated-by-aggregation-reflection/10735/3 "2023-07-02T13:45:42Z")
@balaji.ramaswamy I did everything as you have described. But Dremio is still not using my agg reflections. Any idea why?
---
### Author:  [@balaji.ramaswamy](https://community.dremio.com/u/balaji.ramaswamy)
#### Post date: [July 2, 2023, 5:34pm UTC](https://community.dremio.com/t/job-doesnt-get-accelerated-by-aggregation-reflection/10735/4 "2023-07-02T17:34:49Z")
@vincent_mayer Let me review, can you please send me the latest profile after you have done the changes?
---
### Author:  [@vincent\_mayer](https://community.dremio.com/u/vincent_mayer)
#### Post date: [July 3, 2023, 7:10am UTC](https://community.dremio.com/t/job-doesnt-get-accelerated-by-aggregation-reflection/10735/5 "2023-07-03T07:10:56Z")
@balaji.ramaswamy This the job profile for the query I performed:
[284c9173-f202-4787-898d-b404eeb0896d.zip](https://community.dremio.com/uploads/short-url/9V2v90eiqG5sQa8Msp9CIgSMvEp.zip) (23,3 KB)
I have created one agg reflection for the dimensions l\_returnflag and l\_linestatus and one agg reflection for the measures SUM(l\_extendedprice) and SUM(l\_quantity).
[37715891-11c4-4b2b-bc7d-192ff833c9b8.zip](https://community.dremio.com/uploads/short-url/hLxr9mjmnEMVbe51OfhRn8FH4N7.zip) (19,1 KB)
[b284b304-4819-4027-bbf2-d38f76a07832.zip](https://community.dremio.com/uploads/short-url/6FvGV2NHgQCdgrKdEEzKwXDfRtj.zip) (4,8 KB)
Thanks for your help!
---
### Author:  [@balaji.ramaswamy](https://community.dremio.com/u/balaji.ramaswamy)
#### Post date: [July 6, 2023, 5:38am UTC](https://community.dremio.com/t/job-doesnt-get-accelerated-by-aggregation-reflection/10735/6 "2023-07-06T05:38:10Z")
@vincent_mayer The dimensions and measures used in the query needs to be selected in the same Aggregation reflection. I still see some missing ones. will send over the SQL for you to create the right Agg reflection
- Delete all the reflections on `postgres.public.lineitem`
- create a VDS say in a space called “orders” and call the VDS “lineitem\_vds” on lineitem that has the below columns in addition to the columns in lineitem
```auto
l_extendedprice * (1 - l_discount) as disc_price
l_extendedprice * (1 - l_discount) * (1 + l_tax) as charge
```
- run the below SQL or choose the below dimension and measures on `lineitem_vds` and create an agg reflection
```auto
alter dataset orders.lineitem_vds CREATE AGGREGATE REFLECTION lineitem_vds_agg1
using dimensions (l_returnflag,l_linestatus,l_shipdate) measures (l_quantity(sum, avg),
l_extendedprice(sum, avg), l_discount(avg), disc_price(sum), charge(sum), l_orderkey(count))
```
Once the Agg reflection is created, run the below SQL
```auto
select
l_returnflag,
l_linestatus,
sum(l_quantity) as sum_qty,
sum(l_extendedprice) as sum_base_price,
sum(l_extendedprice * (1 - l_discount)) as sum_disc_price,
sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) as sum_charge,
avg(l_quantity) as avg_qty,
avg(l_extendedprice) as avg_price,
avg(l_discount) as avg_disc,
count(*) as count_order
from
lineitem
where
l_shipdate <= date '1998-12-01' - interval '90' day
group by
l_returnflag,
l_linestatus
order by
l_returnflag,
l_linestatus;
```
---
### Author:  [@vincent\_mayer](https://community.dremio.com/u/vincent_mayer)
#### Post date: [July 6, 2023, 7:29am UTC](https://community.dremio.com/t/job-doesnt-get-accelerated-by-aggregation-reflection/10735/7 "2023-07-06T07:29:10Z")
> [@balaji.ramaswamy](#):
>
> ```auto
> alter dataset orders.lineitem_vds CREATE AGGREGATE REFLECTION lineitem_vds_agg1
> using dimensions (l_returnflag,l_linestatus,l_shipdate) measures (l_quantity(sum, avg),
> l_extendedprice(sum, avg), l_discount(avg), disc_price(sum), charge(sum), l_orderkey(count))
>
> ```
Thank you for the detailed explanation. Some of it I have already tried, e.g. dimensions and measures in on agg reflection.
I created everything as described but my query still doesnt get accelerated. Here is the job profile.
[08fc3397-7b8d-4915-a94a-ed10c60e21e4.zip](https://community.dremio.com/uploads/short-url/eaeeOJ0t7nEbrK9ksR0m3wMHidU.zip) (14,2 KB)
Any idea why?
Just for my understanding. I thought reflections get used by Dremios optimizer even if they only partly satisfy the query. Is that right?
---
### Author:  [@Benny\_Chow](https://community.dremio.com/u/Benny_Chow)
#### Post date: [July 6, 2023, 5:53pm UTC](https://community.dremio.com/t/job-doesnt-get-accelerated-by-aggregation-reflection/10735/8 "2023-07-06T17:53:34Z")
@vincent_mayer Can you provide a verbose profile for the last one you sent above? This will include a lot more logging in the acceleration tab which will explain why your reflection isn’t matching.
---
### Author:  [@vincent\_mayer](https://community.dremio.com/u/vincent_mayer)
#### Post date: [July 7, 2023, 8:56am UTC](https://community.dremio.com/t/job-doesnt-get-accelerated-by-aggregation-reflection/10735/9 "2023-07-07T08:56:43Z")
@Benny_Chow how can I create a verbose profile?
---
### Author:  [@Benny\_Chow](https://community.dremio.com/u/Benny_Chow)
#### Post date: [July 7, 2023, 2:20pm UTC](https://community.dremio.com/t/job-doesnt-get-accelerated-by-aggregation-reflection/10735/10 "2023-07-07T14:20:49Z")
You need to enable this support option and re-run the query.
[https://docs.dremio.com/software/jobs/raw-profile/analyzing-profiles/#considerations](https://docs.dremio.com/software/jobs/raw-profile/analyzing-profiles/#considerations)
---
### Author:  [@vincent\_mayer](https://community.dremio.com/u/vincent_mayer)
#### Post date: [July 8, 2023, 8:39am UTC](https://community.dremio.com/t/job-doesnt-get-accelerated-by-aggregation-reflection/10735/11 "2023-07-08T08:39:44Z")
@Benny_Chow Thanks for the link. This is the job profile with the verbose key activated.
[8a518acd-ed94-435d-9f2b-cef120aa1ac1.zip](https://community.dremio.com/uploads/short-url/9avbkYJgr10HgT1PcKQVuu03Gij.zip) (20,5 KB)
---
### Author:  [@Benny\_Chow](https://community.dremio.com/u/Benny_Chow)
#### Post date: [July 9, 2023, 6:32am UTC](https://community.dremio.com/t/job-doesnt-get-accelerated-by-aggregation-reflection/10735/12 "2023-07-09T06:32:30Z")
> [@vincent\_mayer](#):
>
> I thought reflections get used by Dremios optimizer even if they only partly satisfy the query. Is that right?
That’s right. Reflections can match into parts of the user query.
I looked at your verbose profile. The VDS orders.lineitem\_vds and the agg reflection lineitem\_vds\_agg1 look fine to me. The reflection should match into your query.
I see you are on version 20 which is from Dec 2021. Many reflection matching improvements have been made since. Is it possible to upgrade to version 24?
If you don’t want to upgrade, you can just take your original query, save it as a VDS and enable a raw reflection on it. Running the original query should match with the reflection even if you don’t explicitly reference the VDS in the query.
---
### Author:  [@vincent\_mayer](https://community.dremio.com/u/vincent_mayer)
#### Post date: [July 9, 2023, 3:41pm UTC](https://community.dremio.com/t/job-doesnt-get-accelerated-by-aggregation-reflection/10735/13 "2023-07-09T15:41:38Z")
@Benny_Chow Thanks for your help.
Atm I can not upgrade to a new community version because I dont have access to a NAS System, HDFS or Data lake storage to store the data reflections. As far as I know you can not use the pdf storage for your data reflections as of community version 21.0.0. Please correct me if I am wrong.
I ended up doing what you have described. I created a raw reflection on my query to accelerate it.
---
### Author:  [@Benny\_Chow](https://community.dremio.com/u/Benny_Chow)
#### Post date: [July 9, 2023, 6:34pm UTC](https://community.dremio.com/t/job-doesnt-get-accelerated-by-aggregation-reflection/10735/14 "2023-07-09T18:34:14Z")
Got to hear the workaround works for you.
As for PDFS, reflections in Iceberg and unlimited splits do not support this. There’s so many performance benefits with these features which is why we no longer support PDFS.