This message was deleted.

I haven't heard of any post-aggregate pushdowns other than ORDER BY and LIMIT clauses ... ... but logically it makes sense that something like an upward bounded aggregate value filter could be pushed down, since the value would could only increase at the broker level ... e.g. "where count(*) < 10" could rule out the grouping if the count already exceeds 10 ... however the fact that the grouping is ruled out also needs to be sent up to the broker ... 😉

Thanks, I got it! I thought that since the

user_id

is set as the secondary partition key, and because one historical server have all row for each user_id, it might be possible for it to be pushed down at the historical level.

Druid already does pushdown for GROUP BY, LIMIT and ORDER BY ... I was referring to post-aggregate filters which I think are more restrictive so not an easy thing to implement ... although if used in a TopN then Druid also does pushdown and pruning on that. The link you referred to looks like it has an additional restriction that the GROUP BY has to be on the sharding key in order for it to be pushed down? In Druid that is called "perfect rollup", which is beneficial because it allows more of the aggregate operation to be parallelized ... however Druid will push down a GROUP BY on any columns, not just the partition key columns ... it uses multiple levels of rollup/aggregation to do this so if perfect rollup cannot be achieved then higher level secondary aggregations will be performed.

The link you referred to looks like it has an additional restriction that the GROUP BY has to be on the sharding key in order for it to be pushed down?

Yes. It's a restriction, but I think grouping by shard key is common pattern. For example, with user behavior log table sharded by user_id, common question is

who are the people viewing this page repeatedly?

I think "perfect rollup" is done at ingestion time, not query time... But I understand that druid also currently not support segment locality with shard key, so post-aggregation pushdown implementation is also not easy. Thanks!