Many-query join: efficient shared execution of relational joins on modern hardware

Makreshanski, Darko; Giannikis, Georgios; Alonso, Gustavo; Kossmann, Donald

doi:10.1007/s00778-017-0475-4

Cited by 13 publications

(10 citation statements)

References 25 publications

Supporting

Mentioning

Contrasting

Order By: Relevance

“…In many real-world evaluations, join and aggregation are big contributors to the overall workload [37,77]. Consequently, there is a large body of related work that optimizes hash joins [52,63,71] and hash aggregations [51,67,80]. Re-using hash partitions, and even whole hash tables is a well-known optimization [18,36].…”

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

“…Balkesen et al [3][4][5] revisited partitioned and non-partitioned joins and optimized the implementation of Blanas [7] with write-combining and streaming instructions [3,39,46]. Fang et al and Makreshanski et al [12,23] built theoretical models for the two hash joins and identified the tuple size as the most critical performance factor saturating the memory bandwidth.…”

Section: Related Workmentioning

confidence: 99%

To Partition, or Not to Partition, That is the Join Question in a Real System

Bandle

Giceva

Neumann

2021

Proceedings of the 2021 International Conference on Management of Data

View full text Add to dashboard Cite

An efficient implementation of a hash join has been a highly researched problem for decades. Recently, the radix join has been shown to have superior performance over the alternatives (e.g., the non-partitioned hash join), albeit on synthetic microbenchmarks. Therefore, it is unclear whether one can simply replace the hash join in an RDBMS or use the radix join as a performance booster for selected queries. If the latter, it is still unknown when one should rely on the radix join to improve performance.In this paper, we address these questions, show how to integrate the radix join in Umbra, a code-generating DBMS, and make it competitive for selective queries by introducing a Bloom-filter based semi-join reducer. We have evaluated how well it runs when used in queries from more representative workloads like TPC-H. Surprisingly, the radix join brings a noticeable improvement in only one out of all 59 joins in TPC-H. Thus, with an extensive range of microbenchmarks, we have isolated the effects of the most important workload factors and synthesized the range of values where partitioning the data for the radix join pays off. Our analysis shows that the benefit of data partitioning quickly diminishes as soon as we deviate from the optimal parameters, and even late materialization rarely helps in real workloads. We thus, conclude that integrating the radix join within a code-generating database rarely justifies the increase in code and optimizer complexity and advise against it for processing real-world workloads. CCS CONCEPTS• Information systems → Main memory engines; Join algorithms.

show abstract

“…These multi-query optimization techniques later expanded to involve query rewriting, query result caches, materialized views, and intermediate query results for relational database systems [ 28 , 29 , 30 , 31 , 32 , 33 , 34 , 35 , 36 ] and streaming processing systems [ 37 , 38 , 39 ]. Many applications involving high-load conditions have proven that batch processing algorithms can significantly reduce the query processing time for multiple simultaneous queries [ 19 , 30 , 31 , 32 , 33 , 34 , 35 , 36 , 37 , 38 , 39 , 40 , 41 , 42 , 43 ]. Furthermore, multi-query optimization techniques have received significant attention in spatial databases.…”

Section: Related Workmentioning

confidence: 99%

A Unified Approach to Spatial Proximity Query Processing in Dynamic Spatial Networks

Cho¹

2021

Sensors

View full text Add to dashboard Cite

Nearest neighbor (NN) and range (RN) queries are basic query types in spatial databases. In this study, we refer to collections of NN and RN queries as spatial proximity (SP) queries. At peak times, location-based services (LBS) need to quickly process SP queries that arrive simultaneously. Timely processing can be achieved by increasing the number of LBS servers; however, this also increases service costs. Existing solutions evaluate SP queries sequentially; thus, such solutions involve unnecessary distance calculations. This study proposes a unified batch algorithm (UBA) that can effectively process SP queries in dynamic spatial networks. With the proposed UBA, the distance between two points is indicated by the travel time on the shortest path connecting them. The shortest travel time changes frequently depending on traffic conditions. The goal of the proposed UBA is to avoid unnecessary distance calculations for nearby SP queries. Thus, the UBA clusters nearby SP queries and exploits shared distance calculations for query clusters. Extensive evaluations using real-world roadmaps demonstrated the superiority and scalability of UBA compared with state-of-the-art sequential solutions.

show abstract

Many-query join: efficient shared execution of relational joins on modern hardware

Cited by 13 publications

References 25 publications

Practical planning and execution of groupjoin and nested aggregates

Practical planning and execution of groupjoin and nested aggregates

To Partition, or Not to Partition, That is the Join Question in a Real System

A Unified Approach to Spatial Proximity Query Processing in Dynamic Spatial Networks

Contact Info

Product

Resources

About