Practical planning and execution of groupjoin and nested aggregates

Groupjoins combine execution of a join and a subsequent group-by . They are common in analytical queries and occur in about 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-...

Full description

Saved in:
Bibliographic Details
Published in:The VLDB journal Vol. 32; no. 6; pp. 1165 - 1190
Main Authors: Fent, Philipp, Birler, Altan, Neumann, Thomas
Format: Journal Article
Language:English
Published: Berlin/Heidelberg Springer Berlin Heidelberg 01.11.2023
Springer Nature B.V
Subjects:
Aggregates
Algorithms
Computer Science
Database Management
Estimates
Optimization
Queries
Semantics
Special Issue Paper
Query processing
Parallel processing
Query optimization
ISSN:1066-8888, 0949-877X
Online Access:Get full text
Tags: Add Tag
No Tags, Be the first to tag this record!
Description
Summary:Groupjoins combine execution of a join and a subsequent group-by . They are common in analytical queries and occur in about 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 estimates to predict the result distributions of aggregates, parallel groupjoin execution for scalable execution of groupjoins, index groupjoins , and a greedy eager aggregation optimization 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.
Bibliography:ObjectType-Article-1
SourceType-Scholarly Journals-1
ObjectType-Feature-2
content type line 14
ISSN:1066-8888
0949-877X
DOI:10.1007/s00778-022-00765-x