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

4

Hi @Benny_Chow can you please help here? thanks
Jaro

5

@jaroslav_marko it is probably cast(count(distinct("model_account_id")) as DOUBLE) + cast(max("C1") as DOUBLE) as "C1"

Have you started with a simpler agg query and does that match the Agg, then you can start adding these transformations one by and one and see which one cause it not to match

6

Can you provide the verbose profile? This has a lot more info in the acceleration profile. From the looks of it, I would have expected that agg reflection to have matched.

7

Hi @Benny_Chow, can you please guide me how to obtain verbose profile, please?
thanks
Jaro

8

You set this support option:

planner.verbose_profile

And re-run the query. Please make sure the profile doesn’t serve from plan cache which you can disable with:

planner.query_plan_cache_enabled