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-...

Celý popis

Uloženo v:
Podrobná bibliografie
Vydáno v:The VLDB journal Ročník 32; číslo 6; s. 1165 - 1190
Hlavní autoři: Fent, Philipp, Birler, Altan, Neumann, Thomas
Médium: Journal Article
Jazyk:angličtina
Vydáno: Berlin/Heidelberg Springer Berlin Heidelberg 01.11.2023
Springer Nature B.V
Témata:
Aggregates
Algorithms
Computer Science
Database Management
Estimates
Optimization
Queries
Semantics
Special Issue Paper
Query processing
Parallel processing
Query optimization
ISSN:1066-8888, 0949-877X
On-line přístup:Získat plný text
Tagy: Přidat tag
Žádné tagy, Buďte první, kdo vytvoří štítek k tomuto záznamu!
Popis
Shrnutí: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.
Bibliografie: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