Optimization of Nested Queries using the NF2 Algebra

Hölsch, Jürgen; Grossniklaus, Michael; Scholl, Marc H.

doi:10.1145/2882903.2915241

Cited by 5 publications

(3 citation statements)

References 16 publications

Supporting

Mentioning

Contrasting

Order By: Relevance

“…Bellamkonda et al [6] describe the execution of correlated subqueries with window operations in Oracle. Hölsch et al [35] use an extended form of relational algebra to reason about nested queries and are able to express more transformation on aggregations. To incorporate unnested aggregations in cost models, practical implementations, e.g., in DB2, use statistical views [26].…”

Section: Related Workmentioning

confidence: 99%

Practical planning and execution of groupjoin and nested aggregates

2022

View full text Add to dashboard Cite

Groupjoins combine execution of a join and a subsequent group-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 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.

show abstract

Section: Related Workmentioning

confidence: 99%

Practical planning and execution of groupjoin and nested aggregates

2022

View full text Add to dashboard Cite

show abstract

“…Bellamkonda et al [4] describe the execution of correlated subqueries with window operations in Oracle. Hölsch et al [27] use an extended form of relational algebra to reason about nested queries and are able to express more transformation on aggregations. To incorporate unnested aggregations in cost models, practical implementations, e.g., in DB2, use statistical views [20].…”

Section: Related Workmentioning

confidence: 99%

A practical approach to groupjoin and nested aggregates

Fent

Neumann

2021

Proc. VLDB Endow.

View full text Add to dashboard Cite

Groupjoins, the combined execution of a join and a subsequent group by, are common in analytical queries, and occur in about 1/8 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, which limits their usefulness in a many-core system. Having an efficient implementation of groupjoins is highly desirable, as groupjoins are not only used to fuse group by and join but are also introduced by the unnesting component of the query optimizer to avoid nested-loops evaluation of aggregates. Furthermore, the query optimizer needs be able to reason over the result of aggregation in order to schedule it correctly. Traditional selectivity and cardinality estimations quickly reach their limits when faced with computed columns from nested aggregates, which leads to poor cost estimations and thus, suboptimal query plans. In this paper, we present techniques to efficiently estimate, plan, and execute groupjoins and nested aggregates. We propose two novel techniques, aggregate estimates to predict the result distribution of aggregates, and parallel groupjoin execution for a scalable execution of groupjoins. The resulting system has significantly better estimates and a contention-free evaluation of groupjoins, which can speed up some TPC-H queries up to a factor of 2.

show abstract

“…This is called a distributed partitioned join. While related approaches for query unnesting (Fegaras & Maier, 2000;Holsch et al, 2016) require many rewrite rules to handle various cases of query nesting, our method requires only one and is more general as it handles nested queries of any form and any number of nesting levels. Consider the following nested query over the bags X and Y :…”

Section: Converting Nested Cmaps To Joinsmentioning

confidence: 99%

An algebra for distributed Big Data analytics

Fegaras¹

2017

J. Funct. Prog.

View full text Add to dashboard Cite

We present an algebra for data-intensive scalable computing based on monoid homomorphisms that consists of a small set of operations that capture most features supported by current domain-specific languages for data-centric distributed computing. This algebra is being used as the formal basis of MRQL, which is a query processing and optimization system for large-scale distributed data analysis. The MRQL semantics is given in terms of monoid comprehensions, which support group-by and order-by syntax and can work on heterogeneous collections without requiring any extension to the monoid algebra. We present the syntax and semantics of monoid comprehensions and provide rules to translate them to the monoid algebra. We give evidence of the effectiveness of our algebra by presenting some important optimization rules, such as converting nested queries to joins.

show abstract

Optimization of Nested Queries using the NF2 Algebra

Cited by 5 publications

References 16 publications

Practical planning and execution of groupjoin and nested aggregates

Practical planning and execution of groupjoin and nested aggregates

A practical approach to groupjoin and nested aggregates

An algebra for distributed Big Data analytics

Contact Info

Product

Resources

About