Another question, do replicas of segments matter on performance? Does it increase parallelism?

it should, if the query needs more parallelism. However if you have far more segments to read than you have Historicals in the cluster (which is usually the case), then I don't think it affects performance noticeably.

I am trying to maximize performance the windowing query for large data. I do not see spikes on cpu on historical. I followed basic cluster tuning.

Afaik Window Functions run primarily on the Broker, they are post-aggregate. but if you are seeing long segment scan times, can you tell us how many segments are being scanned for the query, and what the Avg and P95/P98 scan times are? And if you are using streaming ingestion, also if you can split out node type of Peon vs Historical.

# of segments are 30. let me look for P95/P98 scan times btw, thank you for your input.

I think avg and P95/P98 are both derived form the segment scan time metric ... the client tool must use those. So your segment scan times are around 8 sec? That's pretty slow. How big are the segments (rows, GB)?

700k rows and size around 400MB each.

how many processors on your Historicals? With only 30 segments, range partitioned, there is a chance that you are query pruning and opening up only a few segments, which won't have much parallelism. In which case I would think the make the segments smaller to reduce scan times.

24 core cpu for historical and 3 of them.

What is your segmentGranularity? and how many segments per time interval?

I changed to hash to distribute data equally. Performance is improved in GroupBy query.