Joins via Geometric Resolutions

Abstract

We present a simple geometric framework for the relational join. Using this framework, we design an algorithm that achieves the fractional hypertree-width bound, which generalizes classical and recent worst-case algorithmic results on computing joins. In addition, we use our framework and the same algorithm to show a series of what are colloquially known as beyond worst-case results. The framework allows us to prove results for data stored in Btrees, multidimensional data structures, and even multiple indices per table. A key idea in our framework is formalizing the inference one does with an index as a type of geometric resolution; transforming the algorithmic problem of computing joins to a geometric problem. Our notion of geometric resolution can be viewed as a geometric analog of logical resolution. In addition to the geometry and logic connections, our algorithm can also be thought of as backtracking search with memoization.

Depth Distribution in High Dimensions

“…In practice, multidimensional range queries are applied to a database of multidimensional points. This yields discrete variants of each of the problems previously discussed [1,25]. In the Discrete Klee's measure, the input is composed of not only a set B of n boxes, but also of a set S of m points.…”

“…Problems studied in Computational Geometry have found important applications in the processing and querying of massive databases [1], such as the computation of the Maxima of a set of points [2,4], or compressed data structures for Point Location and Rectangle Stabbing [3]. In particular, we consider cases where the input or queries are composed of axis-aligned boxes in d dimensions: in the context of databases it corresponds for instance to a search for cars within the intersection of ranges in price, availability and security ratings range.…”

Hypertree Decompositions

“…This is the case in [104], where a notion of geometric resolution is defined to support different kinds of indices and even multiple indices per table. By performing such resolutions, the proposed algorithm covers the whole multidimensional (tuple) space by distinguishing the output tuples (if any) and the other infeasible (non-matching) tuples.…”

LogiQL

“…Veldhuizen [27] subsequently showed that Leapfrog Triejoin, a much simpler multi-way join algorithm, which had already been in use at LogicBlox, is also worst-case optimal in the same sense. Since then, a number of other join algorithms have been proposed in this space [22], including Minesweeper [20] and Tetris [16] for which even stronger optimality theorems hold for various classes of queries.…”

“…Next, we overview leapfrog triejoin [27], the workhorse join algorithm in LogicBlox, and its associated incremental view maintenance algorithm [26]. Join evaluation is another topic in which there has been flurry of re-interest, with exciting recent successes [21,27,20,16] in developing algorithms having provable optimality guarantees of various flavors; leapfrog triejoin is one such algorithm. Finally, we give a brief overview of transaction repair [28], a lock-free concurrency control scheme achieving full serializability that uses LogiQL, incremental maintenance, and purely-functional data structures as essential ingredients.…”