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

Abstract

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.

Practical planning and execution of groupjoin and nested aggregates

“…In Fig. 23a, we place a preaggregation at 3 , where it calculates the initial aggregates, while Γ merges and finalizes them. Figure 23b shows an additional preaggregation placed in between at 2 , which merges the aggregates from its input.…”

“…Re-using hash partitions, and even whole hash tables is a well-known optimization [18,36]. One often discussed question is, if hash tables should be partitioned or nonpartitioned [3]. Our proposed approaches in Sect.…”

A practical approach to groupjoin and nested aggregates

“…Consequently, there is a large body of related work that optimizes hash joins [39,49,55] and hash aggregations [38,52,61]. One often discussed question is, if hash tables should be partitioned or non-partitioned [3]. Our proposed approaches in Section 3 try to use a non-partitioned hash table to avoid materializing data, while using thread-local partitioning for heavy-hitters.…”

“…Order Benchmark (JOB)3 : Since IMDb primarily stores facts as strings, we extract a separate table that contains the vote count and the user rating for movies, to allow statistics collection. On these columns, we define five additional aggregation queries that calculate statistics on the new numerical columns.…”

Efficiently Processing Data in Table With Billions of Records

“…This is an important problem because it occurs in every large database. The ideal solution would allow, despite the increase of the number of records in the tables, to perform operations on the database as quickly as at the time of its implementation [2].…”