Adopting worst-case optimal joins in relational database systems

Abstract

Worst-case optimal join algorithms are attractive from a theoretical point of view, as they offer asymptotically better runtime than binary joins on certain types of queries. In particular, they avoid enumerating large intermediate results by processing multiple input relations in a single multiway join. However, existing implementations incur a sizable overhead in practice, primarily since they rely on suitable ordered index structures on their input. Systems that support worst-case optimal joins often focus on a specific problem domain, such as read-only graph analytic queries, where extensive precomputation allows them to mask these costs.In this paper, we present a comprehensive implementation approach for worst-case optimal joins that is practical within general-purpose relational database management systems supporting both hybrid transactional and analytical workloads. The key component of our approach is a novel hash-based worst-case optimal join algorithm that relies only on data structures that can be built efficiently during query execution. Furthermore, we implement a hybrid query optimizer that intelligently and transparently combines both binary and multi-way joins within the same query plan. We demonstrate that our approach far outperforms existing systems when worst-case optimal joins are beneficial while sacrificing no performance when they are not.

Relational e-matching

“…All linear patterns correspond to acyclic queries, but some nonlinear patterns correspond to cyclic ones. Recent research [Freitag et al 2020;Mhedhbi and Salihoglu 2019] has also experimented with combining traditional join algorithms with generic join, achieving good performance. In this paper we choose generic join for its simplicity, and future work may consider other join algorithms for relational e-matching.…”

Query Dictionary for Frequent Non-Indexed Queries in HTAP Databases

“…Materialized views can speed up frequently-used queries and views, lowering query processing costs. If the system often performs certain joins, it is always good to create join indexes for those joins [14]. One such technique for holding pre-joined tables are materialized views [15].…”

“…2) Binary Join Query_Dictionary -OLAP Binary joins provide significant flexibility and good performance on a wide range of workloads due to decades of optimization and fine-tuning. However, any binary join scheme performs poorly in pathological conditions [14]. The most significant disadvantage of binary joins is that they produce intermediate results that can be substantially larger than the query result.…”

Relational E-Matching

“…All linear patterns correspond to acyclic queries, but some nonlinear patterns correspond to cyclic ones. Recent research [9,11] has also experimented with combining traditional join algorithms with generic join, achieving good performance. In this paper we choose generic join for its simplicity, and future work may consider other join algorithms for relational e-matching.…”