Understanding Partial Group By: Reducing Join Costs with Aggregation Optimization
What is Partial Group By?
Joins are costly. Before the introduction of PARTIAL GROUP BY, the join would be performed first, and then the aggregated result would follow.
PARTIAL GROUP BY reduces the amount of data that must be redistributed or duplicated to all AMPs during join preparation by performing aggregations before the join, without altering the query's semantics.
The savings increase as the number of unique values in the GROUP BY columns decreases.
Want more practical data engineering analysis like this?
Join DWHPro Letters and get field-tested notes on Teradata, Snowflake, AI, migrations, performance, and enterprise data work. DWHPro Letters is free. Subscribe to get new issues by email.
Early GROUP BY and Partial GROUP BY are distinguishable solely based on their approach to aggregations in relation to the join process. While Early GROUP BY executes all aggregations before joining, Partial GROUP BY conducts a portion of the aggregation before and after the join. Nonetheless, this is the only point of differentiation.
This query is optimized for PARTIAL GROUP BY:
SELECT table1.key,SUM(table1.fact),SUM(table2.fact)
FROM table1 INNER JOIN table2 ON table1.key = table2.key
GROUP BY 1;The order of the join and aggregation is irrelevant in this scenario. The performance may vary based on the number of rows and distinct values in the "fact" column of each table.
Planning or surviving an enterprise data platform migration?
I write regularly about the performance, cost, architecture, and project mistakes that show up in real Teradata, Snowflake, Databricks, and enterprise data work.
Subscribe for free and keep launch access.
Written by Roland Wenzlofsky, founder of DWHPro and author of Teradata Query Performance Tuning. DWHPro has helped data warehouse practitioners for 15+ years.