identify group by vs group each in advance

Question

Is there a way to figure out in advance (not by trial and error) whether a specific query should use GROUP BY or GROUP EACH BY? We currently saw that after a cardinality of ~60-70% we are asked to use Group EACH by. It is hard to predict as we generate the SQL.

score 5 · Answer 1 · edited May 23 '17 at 12:07

5

The usage of 'EACH' doesn't depend on the query, but on the data. Is there a small number of unique values for the group expression? Use GROUP BY. Is there a lot? Use GROUP EACH BY.

The best strategy is to use GROUP BY until you get an "over limits error".

To go deeper into the "why?", you can look at the Dremel paper that started it all. Basically GROUP BY runs in the mixers, while GROUP EACH BY gets pushed to the shards.

For other insights, check jcondit's answers at Resources Exceeded during query execution.

edited May 23 '17 at 12:07

Community

1
1

answered Jun 06 '13 at 01:30

Felipe Hoffa

54,922
16
151
325

thanks, but we can't let users queries just "get error" and then run it again :-). – user1516770 Jun 07 '13 at 02:54
I agree! Can you tell me more about the use case? – Felipe Hoffa Jun 07 '13 at 07:00
Is there an equivalent for the standard SQL? – Guig Jun 06 '18 at 20:31

identify group by vs group each in advance

1 Answers1