Practical planning and execution of groupjoin and nested aggregates
Philipp Fent, Altan Birler, Thomas Neumann · The VLDB Journal · 2022
Abstract Groupjoins combine execution of ajoinand a subsequentgroup-by. They are common in analytical queries and occur in about "Equation missing"of the queries in TPC-H and TPC-DS. While they were originally invented to improve performance, efficient parallel execution of groupjoins can be limited by contention in many-core systems. Efficient implementations of groupjoins are highly desirable, as groupjoins are not only used to fuse group-by and join, but are also useful to efficiently execute nested aggregates. For these, the query optimizer needs to reason over the result of aggregation to optimally schedule it. Traditional systems quickly reach their limits of selectivity and cardinality estimations over computed columns and often treat group-by as an optimization barrier. In this paper, we present techniques to efficiently estimate, plan, and execute groupjoins and nested aggregates. We propose four novel techniques,aggregate estimatesto predict the result distributions of aggregates,parallel groupjoin executionfor scalable execution of groupjoins,index groupjoins, and a greedyeager aggregationoptimization technique that introduces nested preaggregations to significantly improve execution plans. The resulting system has improved estimates, better execution plans, and a contention-free evaluation of groupjoins, which speeds up TPC-H and TPC-DS queries significantly.