Aggregate on the Join Key Before Joining the Wide Table
When you need one total per customer, grouping the big detail table by its customer_id and then joining that small result to the customer table is far cheaper than joining everything first and grouping by customer name, address and email. Grouping by the key keeps the grouping input narrow and the group count small, and it avoids dragging wide columns through the aggregation. The plan makes the difference obvious: the aggregate's input row count collapses.
Questions this Concept answers
- Why is aggregating before the join cheaper than joining first and grouping by wide columns?
This Concept is waiting for its first lesson!
When you need one total per customer, grouping the big detail table by its customer_id and then joining that small result to the customer table is far cheaper than joining everything first and grouping by customer name, address and email. Grouping by the key keeps the grouping input narrow and the group count small, and it avoids dragging wide columns through the aggregation. The plan makes the difference obvious: the aggregate's input row count collapses.
Are you a teacher? Sign in to start contributing.
Sign In