Rewrite optimization of SQL queries containing group-by
Weipeng Yan · 1996
Efficient processing of aggregation queries is essential for decision support applications. This thesis describes a class of query transformations, called eager aggregation and lazy aggregation, that allows a query optimizer to move group-by operations up and down the query tree. Eager aggregation partially pushes a group-by past a join. After a group-by is partially pushed down, the original group-by in the upper query block must still be performed. Eager aggregation reduces the number of input rows to the join and thus may result in a better overall plan. The reverse transformation, lazy aggregation, pulls a group-by above a join and combines two group-by operations into one. This transformation is typically of interest when an aggregation query references a grouped view (a view containing a group-by). A special case of eager/lazy aggregation is group-by push down and group-by pull up, in which we interchange the order of group-by and join. We formally define the problem, adhering strictly to the semantics of scSQL2, and prove necessary and sufficient condition for group-by and join commutation, and sufficient conditions for eager/lazy aggregation. Integration of the proposed techniques with existing optimizers is also presented. As a result of the approach, any scSQL query containing selection, join, projection and group-by can be transformed into a canonical form in which all group-bys are performed after all joins. Experimental results show that the techniques are very beneficial for queries in the TPC-D benchmark.