How to effectively accelerate specific query

Apache Iceberg
1

Hi team,

can you please advice how to effectively accelerate below query.
it is cca 30M rows and whatever reflection i create it scans all 30M rows.
I tried to created view and raw reflection on it with aggregation with the same result.

thanks.
Jaro

select “region”,
“priority”,
cast(count(distinct(“model_account_id”)) as DOUBLE) + cast(max(“C1”) as DOUBLE) as “C1”
from
(
select “fisc_wk_val”,
“fisc_qtr_val”,
“quarter”,
“lever”,
“lob”,
“region”,
“country”,
“global_segment”,
“unified_segment”,
“sls_bu_level2_desc”,
“sls_bu_level3_desc”,
“sls_bu_level4_desc”,
“mkt_lvl5”,
“model_account_id”,
“dell_acct_name”,
“org_acct_id”,
“priority”,
“selection_criteria”,
“exclusions”,
“selection_reason”,
“last_updated_datetime”,
“output_run_id”,
case
when “model_account_id” is null
then 1
else 0
end as “C1”
from "nessie.“table”
) as “ITBL”
group by “region”,
“priority”

2

Are you on OSS or CE?

If CE, then reflection matching is much better. You could create a view out of your SQL and let the planner algebraically match the reflection into your original SQL. You could also create a view out of ITBL and create an aggregate reflection in the UI where you pick the group by columns as dimensions and the two aggregates as measure with all measure types selected.

3

Hi @Benny_Chow, I have tried that but only the Raw reflection is selected, not the Aggregation…
Check the Profile.

b6c7f390-9fba-4c14-8563-ae700aec273e.zip (124.5 KB)

Any ideas, please?

Jaro