Hi team, want to consult about one of our high cardinality use case and see how to set it up properly with pinot or whether it is a proper use case for pinot. We have a metrics table that contains hundreds of daily level metrics columns that associated with uuids, the data updates daily to add more than 50 millions unique rows every day (add one row for each uuid everyday, and there is millions uuids). A simplified table schema looks like:

uuid    date    group    metrics_1,    metrics_2.     … metrics_xxxx

And a typical simplified query we want to run on this table is selecting a bunch of metrics aggregation for certain groups of uuids across days and then aggregate them again by group, eg:

select 
   group,
   avg(m1),
   sum(m2),
   ...
   avg(mxxx)
from 
(
    select 
        uuid,
        group,
        avg(metrics_1) as m1,
        sum(metrics_2) as m2,
        …
        avg(metrics_xxx) as mxxx
    from metrics_table where group in (xxx) and date between aa and bb
    group by 1, 2
) group by 1

When we did preliminary testing previously, we ran into issues of simple aggregation query on uuid takes very long to return, or query return inaccurate approximations due to high cardinality, we want to get some suggestions about whether it is a good use case with pinot, and if it is how to model this with proper cluster config and index config, thank you! cc @Mingmin Xu

Group by on high cardinality will typically have this cpu+mem vs accuracy tradeoff regardless of the system you pick.

In that case do you think we can get a fast and accurate result by allocating enough amount of cpu and mem? For context we originally run this with presto/trino, we are trying to see if there is way for us to get a better perf in pinot with accurate result

Yes more cpu and mem will help.