Beyond Equi-joins: Ranking, Enumeration and Factorization

Abstract

We study theta-joins in general and join predicates with conjunctions and disjunctions of inequalities in particular, focusing on ranked enumeration where the answers are returned incrementally in an order dictated by a given ranking function. Our approach achieves strong time and space complexity properties: with n denoting the number of tuples in the database, we guarantee for acyclic full join queries with inequality conditions that for every value of k, the k top-ranked answers are returned in $\mathcal{O}(n\text{polylog}n+k\log k)$ time. This is within a polylogarithmic factor of $\mathcal{O}(n+k\log k)$, i.e., the best known complexity for equi-joins, and even of $\mathcal{O}(n+k)$, i.e., the time it takes to look at the input and return k answers in any order. Our guarantees extend to join queries with selections and many types of projections (namely those called “free-connex” queries and those that use bag semantics). Remarkably, they hold even when the number of join results is n^ℓ for a join of ℓ relations. The key ingredient is a novel $\mathcal{O}(n\text{poly}\log n)$-size factorized representation of the query output, which is constructed on-the-fly for a given query and database. In addition to providing the first non-trivial theoretical guarantees beyond equi-joins, we show in an experimental study that our ranked-enumeration approach is also memory-efficient and fast in practice, beating the running time of state-of-the-art database systems by orders of magnitude.

1. INTRODUCTION

Join processing is one of the most fundamental topics in database research, with recent work aiming at strong asymptotic guarantees [47, 58, 61, 62]. Work on constant-delay (unranked) enumeration [10, 19, 42, 74] strives to pre-process the database for a given query on-the-fly so that the first answer is returned in linear time (in database size), followed by all other answers with constant delay (i.e., independent of database size) between them. Together, linear pre-processing and constant delay guarantee that all answers are returned in time linear in input and output size, which is optimal.

Ranked enumeration.

Ranked enumeration [78] generalizes the heavily studied top-k paradigm [35, 45] by continuously returning join answers in ranking order. This enables the output consumer to select the cut-off k on-the-fly while observing the answers. For top-k, the value of k must be chosen in advance, before seeing any query answer. Unfortunately, non-trivial complexity guarantees of previous top-k techniques, including the celebrated Threshold Algorithm [35], are limited to the “middleware” cost model, which only accounts for the number of distinct data items accessed [78]. While some of those top-k algorithms can be applied to joins with general predicates, they do not provide non-trivial guarantees in the standard RAM model of computation, and their time complexity for a join of ℓ relations can be $\mathcal{O}(n^{\ell })$.

The goal of this paper is to design ranked-enumeration algorithms for general theta joins with strong space and time guarantees in the standard RAM model of computation. Tight upper complexity bounds are essential for ensuring predictable performance, no matter the given database instance (e.g., in terms of data skew) or the query’s total output size. Notice that it already takes $\mathcal{O}(n+k)$ time to simply look at n input tuples as well as create and return k output tuples. Since polylogarithmic factors are generally considered small or even negligible for asymptotic analysis [5, 27], we aim for time bounds that are within such polylogarithmic factors of $\mathcal{O}(n+k)$. At the same time, we want space complexity to be reasonable; e.g., for small k to be within a polylogarithmic factor of $\mathcal{O}(n)$, which is the required space to hold the input.

While state-of-the-art commercial and open-source DBMSs do not yet support ranked enumeration, it is worth taking a closer look at their implementation of top-k join queries. (Here k is specified in a SQL clause like FETCH FIRST or LIMIT.) While we tried a large variety of inputs, indexes on the input relations, join queries, and values of k, the optimizer of PostgreSQL and two other widely used commercial DBMSs always chose to execute the join before applying the ranking and top-k condition on the join results.¹ This implies that their overall time complexity to return even the top-1 result cannot be better than the worst-case join output size, which can be $\mathcal{O}(n^{\ell })$ for a join of ℓ relations.

Beyond equi-joins.

Recent work on ranked enumeration [30, 32, 77, 78, 86, 87] achieves much stronger worst-case guarantees, but only considers equi-joins. However, big-data analysis often also requires other join conditions [31, 34, 48, 52] such as inequalities (e.g., S. age < T. age), non-equalities (e.g., S. id ≠ T. id), and band predicates (e.g., ∣S. time − T. time∣ < ε). For these joins, two major challenges must be addressed. First, the join itself must be computed efficiently in the presence of complex conditions, possibly consisting of conjunctions and disjunctions of such predicates. Second, to avoid having to produce the entire output, ranking has to be pushed deep into the join itself.

Example 1. A concrete application of ranked enumeration for inequality joins concerns graph-based approaches for detecting “lateral movement” between infected computers in a network [53]. By modeling computers as nodes and connections as timestamped edges, these approaches search for anomalous access patterns that take the form of paths (or more general subgraphs) ranked by the probability of occurrence according to historical data. The inequalities arise from a time constraint: the timestamps of two consecutive edges need to be in ascending order. Concretely, consider the relation G(From, To, Time, Prob). Valid 2-hop paths can be computed with a self-join (where G₁, G₂ are aliases of G) where the join condition is an equality G₁.To = G₂.From and an inequality G₁.Time < G₂.Time, while the score of a path is G₁.Prob · G₂.Prob. Existing approaches are severely limited computationally in terms of the length of the pattern, since the number of paths in a graph can be extremely large. Thus, they usually resort to a search over very small paths (e.g., only 2-hop). With the techniques developed in this paper, patterns of much larger size can be retrieved efficiently in ranked order without considering all possible instantiations of the pattern.

Main contributions.

We provide the first comprehensive study on ranked enumeration for joins with conditions other than equality, notably general theta-joins and conjunctions and disjunctions of inequalities and equalities. While such joins are expensive to compute [48, 52], we show that for many of them the top-ranked answers can always be found in time complexity that only slightly exceeds the complexity of sorting the input. This is remarkable, given that the input may be heavily skewed and the output size of a join of ℓ relations is $\mathcal{O}(n^{\ell })$. We achieve this with a carefully designed factorized representation of the join output that can be constructed in relatively small time and space. Then the ranking function determines the traversal order on this representation.

Recall that ranked-enumeration algorithms must continuously output answer tuples in order and the goal is to achieve non-trivial complexity guarantees no matter at which value of k the algorithm is stopped. Hence we express algorithm complexity as a function of k: TT(k) and MEM(k) denote the algorithm’s time and space complexity, respectively, until the moment it returns the k-th answer in ranking order. Our main contributions (see also Figure 1) are:

Figure 1: Preprocessing time $\mathcal{P}(n)$ and space complexity $\mathcal{S}(n)$ of our approach for various join conditions. Our novel factorized representation allows ranked enumeration to return the k top-ranked results in time (“Time-To”) $\text{TT}(k)=\mathcal{O}(\mathcal{P}(n)+k\log k)$, using $\text{MEM}(k)=\mathcal{O}(\mathcal{S}(n)+k)$ space.

(1) We generalize an equi-join-specific ranked-enumeration construction [77] by representing the overall join structure as a tree of joining relations and then introducing a join-condition-sensitive abstraction between each pair of adjacent relations in the tree. For the latter, we propose the “Tuple-Level Factorization Graph” (TLFG, Section 3), a novel factorized representation for any theta-join between two relations, and show how its size and depth affect the complexity of ranked enumeration. Interestingly, some TLFGs can be used to transform a given theta-join to an equi-join, a property we leverage for ranked enumeration for cyclic join queries.

(2) For join conditions that are a DNF of inequalities (Section 4), we propose concrete TLFGs with space and construction-time complexity $\mathcal{O}(n\text{poly}\log n)$. Using them for acyclic joins, our algorithm guarantees $\text{TT}(k)=\mathcal{O}(n\text{poly}\log n+k\log k)$, which is within a polylogarithmic factor of the equi-join case, where $\text{TT}(k)=\mathcal{O}(n+k\log k)$ [77], and even the lower bound of $\mathcal{O}(n+k)$.

(3) Our experiments (Section 6) on synthetic and real datasets show orders-of-magnitude improvements over highly optimized top-k implementations in state-of-the-art DBMSs, as well as over an idealized competitor that is not charged for any join-related cost.

Due to space constraints, formal proofs and several details of improvements to our core techniques (Section 5) are in the full version of this paper [79]. Our project website contains more information including source code: https://northeastern-datalab.github.io/anyk/.

2. PRELIMINARIES

2.1. Queries

Let [m] denote the set of integers {1, … , m}. A theta-join query in Datalog notation is a formula of the type

$$Q(\mathbf{Z}):-R_1({\mathbf{X}}_1),\dots ,R_{\ell }({\mathbf{X}}_{\ell }),{\theta }_1({\mathbf{Y}}_1),\dots ,{\theta }_q({\mathbf{Y}}_q)$$

where R_i are relational symbols, X_i are lists of variables (or attributes), Z, Y_i are subsets of X = ∪X_i, i ∈ [ℓ], j ∈ [q], and θ_j are Boolean formulas called join predicates. The terms R_i (X_i) are called the atoms of the query. Equality predicates are encoded by repeat occurrences of the same variable in different atoms; all other join predicates are encoded in the corresponding θ_j. If no predicates θ_j are present, then Q is an equi-join. The size ∣Q∣ of the query is equal to the number of symbols in the formula.

Query semantics.

Join queries are evaluated over a database that associates with each R_i a finite relation (or table) that draws values from a domain that we assume to be $\mathbb{R}$ for simplicity.² Without loss of generality, we assume that relational symbols in different atoms are distinct since self-joins can be handled with linear overhead by copying a relation to a new one. The maximum number of tuples in an input relation is denoted by n. We write R.A for an attribute A of relation R and r.A for the value of A in tuple r ∈ R_i. The semantics of a theta-join query is to (i) create the Cartesian product of the ℓ relations, (ii) select the tuples that satisfy the equi-join conditions and θ_j predicates, and (iii) project on the Z attributes. Consequently, each individual query answer can be represented as a combination of joining input tuples, one from each table R_i.

Projections.

In this paper, we focus on full queries, i.e., join queries without projections (Z = X). While our approach can handle projections by applying them in the end, the strong asymptotic TT(k) guarantees may not hold any more. The reason is that a projection could map multiple distinct output tuples to the same projected answer. In the strict relational model where relations are sets, those “duplicates” would have to be eliminated, creating larger gaps between consecutive answers returned to the user. Fortunately, our strong guarantees still hold for arbitrary projections in the presence of bag semantics, which is what DBMSs use when the SQL query has a SELECT clause instead of SELECT DISTINCT. Even for set semantics and SELECT DISTINCT queries, it is straightforward to extend our strong guarantees to non-full queries that are free-connex [10, 13, 17, 43].

Join trees for equi-joins.

An equi-join query is (alpha-)acyclic [39, 75, 89] if it admits a join tree. A join tree is a tree with the atoms (relations) as the nodes where for every attribute A appearing in an atom, all nodes containing A form a connected subtree. The GYO reduction [89] computes such a join tree for equi-joins.

Atomic join predicates.

We define the following types of predicates between attributes S.A and T.B: an inequality is S.A < T.B, S.A > T.B, S.A ≤ T.B, or S.A ≥ T.B, a non-equality is S.A ≠ T.B and a band is ∣S.A − T.B∣ < ε for some ε > 0. Our approach also supports numerical expressions over input tuples, e.g., f (S.A₁, S.A₂, …) < g(T.B₁, T.B₂, …), with f and g arbitrary $\mathcal{O}(1)$-time computable functions that map to $\mathbb{R}$. The join predicates θ_j are built with conjunctions and disjunctions of such atomic predicates. We assume there are no predicates on individual relations since they can be removed in linear time by filtering the corresponding input tables.

2.2. Ranked Enumeration

Ranked enumeration [78] returns distinct join answers one-at-a-time, in the order dictated by a given ranking function on the output tuples. Since this paradigm generalizes top-k (top-k for “any k” value, or “anytime top-k”), it is also called any-k [77, 86]. An obvious solution is to compute the entire join output, and then either batch-sort it or insert it into a heap data structure. Our goal is to find more efficient solutions for appropriate ranking functions.

For simplicity, in this paper we only discuss ranking by increasing sum-of-weights, where each input tuple has a real-valued weight and the weight of an output tuple is the sum of the weights of the input tuples that were joined to derive it. Ranked enumeration returns the join answers in increasing order of output-tuple weight. It is straightforward to generalize our approach to any ranking function that can be interpreted as a selective dioid [77]. Intuitively, a selective dioid [37] is a semiring that also establishes a total order on the domain. It has two operators (min and + for sum-of-weights) where one distributes over the other (+ distributes over min). These structures include even less obvious cases such as lexicographic ordering by relation attributes.

2.3. Complexity Measures

We consider in-memory computation and analyze all algorithms in the standard Random Access Machine (RAM) model with uniform cost measure. Following common practice, we treat query size ∣Q∣—intuitively, the length of the SQL string—as a constant. This corresponds to the classic notion of data complexity [80], where one is interested in scalability in the size of the input data, and not of the query (because users do not write arbitrarily large queries).

In line with previous work [15, 22, 38], we assume that it is possible to create in linear time an index that supports tuple lookups in constant time. In practice, hashing achieves those guarantees in an expected, amortized sense. We include all index construction times and index sizes in our analysis.

For the time complexity of enumeration algorithms, we measure the time until the k^th result is returned (TT(k)) for all values of k. In the full version [79], we further discuss the relationship of TT(k) to enumeration delay as complexity measures. Since we do not assume any given indexes, a trivial lower bound is $\text{TT}(k)=\mathcal{O}(n+k)$: the time to inspect each input tuple at least once and to return k output tuples. Our algorithms achieve that lower bound up to a polylogarithmic factor. For space complexity, we use MEM(k) to denote the required memory until the k^th result is returned.

3. GRAPH FRAMEWORK FOR JOINS

We summarize our recent work on ranked enumeration for equi-joins, then show our novel generalization to theta-joins.

3.1. Previous Work: Any-k for Equi-joins

Any-k algorithms [77] for acyclic equi-joins reduce ranked enumeration to the problem of finding the k^th-lightest trees in a layered DAG, which we call the enumeration graph. Its structure depends on the join tree of the given query; an example is depicted in Fig. 2a. The enumeration graph is a layered DAG in the sense that we associate it with a particular topological sort: (1) Conceptually, each node is labeled with a layer ID (not shown in the figure to avoid clutter). A layer is a set of nodes that share the same layer ID (depicted with rounded rectangles). (2) Each edge is directed, going from lower to higher layer ID. (3) All tuples from an input relation appear as (black-shaded) nodes in the same layer, called a relation layer. Each relation layer has a unique ID and for each join-tree edge (S,T), S has a lower layer ID than T. (4) If and only if two relations are adjacent in the join tree, then their layers are connected via a connection layer that contains (blue-shaded) nodes representing their join-attribute values. (5) The edges from a relation layer to a connection layer connect the tuples with their corresponding join-attribute values and vice-versa.

Figure 2: Overview of our approach. We generalize the equi-join-specific construction to theta-joins by introducing an abstraction (blue clouds) that factorizes binary joins. Some factorizations can also be used to reduce theta-joins to equi-joins.

(a) Equi-join enumeration graph [77].

(b) Theta-join enumeration graph and abstraction proposed in this paper.

(c) Reduction to equi-join.

The enumeration graph is constructed on-the-fly and bottom-up, according to a join tree of the query (starting from U and T in the example). This phase essentially performs a bottom-up semi-join reduction that also creates the edges and join-attribute-value nodes. A tree solution is a tree that starts from the root layer and contains exactly 1 node from each relation layer. By construction, every tree solution corresponds to a query answer, and vice versa.

The any-k algorithm then goes through two phases on the enumeration graph. The first is a Dynamic Programming computation, where every graph node records for each of its outgoing edges the lowest weight among all subtrees that contain 1 node from each relation layer below. The minimum-subtree and input-tuple weights are not shown in Figure 2a to avoid clutter. For instance, the outgoing edge for R-node (2, 3) would store the smaller of the weights of U-tuples (2, 1) and (2, 2). Similarly, the left edge from S-node (2, 1) would store the sum of the weight of R-tuple (2, 3) and the minimum subtree weight from R-node (2, 3). The minimum-subtree weight for a node’s outgoing edge is obtained at a constant cost by pushing the minimum weight over all outgoing edges up to the node’s parent. Afterwards, enumeration is done in a second phase, where the enumeration graph is traversed top-down (from S in the example), with the traversal order determined by the layer IDs and minimum-subtree weights on a node’s outgoing edges.

The size of the enumeration graph and its number of layers determine space and time complexity of the any-k algorithm. The following lemma summarizes the main result from our previous work [77]. We restate it here in terms of data complexity (where query size ℓ is a constant) and using λ for the number of layers.³

Lemma 2 ([77]). Given an enumeration graph with ∣E∣ edges and λ layers, ranked enumeration of the k-lightest tree solutions can be performed with $\text{TT}(k)=\mathcal{O}(\mid E\mid +k\log k+k\lambda )$ and $\text{MEM}(k)=\mathcal{O}(\mid E\mid +k\lambda )$.

To extend the any-k framework beyond equi-joins, we generalize first the definition of a join tree and then the enumeration graph with an abstraction that is sensitive to the join conditions.

3.2. Theta-Join Tree

The join tree is essential for generating the enumeration graph. In contrast to equi-joins, for general join conditions there is no established methodology for how to define or find a join tree. We generalize the join tree definition as follows:

Definition 3 (Theta-join Tree). A theta-join tree for a theta-join query Q is a join tree for the equi-join Q′ that has all the θ_j predicates of Q removed, and every θ_j is assigned to an edge (S, T) of the tree such that S and T contain all the attributes referenced in θ_j.

We call a theta-join query acyclic if it admits a theta-join tree. In the theta-join tree, edge (S, T) represents the join S ⋈_θ T, where join condition θ is the conjunction of all predicates θ_j assigned to the edge, as well as the equality predicates S.A = T.A for every attribute A that appears in both S and T.

Example 4. Consider Q(A,B,C,D,E,F):- R(D,E), S(A, D), T(B, C), U(D, F), (A < B), (A > E),(E < F).^a This query is acyclic since we can construct the theta-join tree shown in Fig. 2b. Notice that all nodes containing attribute D are connected and each inequality is assigned to an edge whose adjacent nodes together contain all referenced attributes. For example, A < B is assigned to (S, T) (S contains A and T contains B). The join-tree edges represent join predicates θ₁ = S.A < T.B (edge (S,T)), θ₂ = S.A > R.E ∧ S.D = T.D (edge (S,R)), and θ₃ = R.E < U.F ∧ R.D = U.D (edge (R, U)).

^a SELECT * FROM R, S, T, U WHERE

R.D = S.D AND R.D = U.D AND S.A < T.B AND S.A > R.E AND R.E < U.F

We can construct the theta-join tree by first removing all θ_j predicates from the given query Q, turning it into an equi-join Q′. Then an algorithm like the GYO reduction can be used to find a join tree for Q′. For the query in Example 4, this join tree looks like the one in Figure 2b, but without the edge labels. Finally, we attempt to add each θ_j predicate to a join-tree edge: θ_j can be assigned to any edge where the two adjacent nodes contain all the attributes referenced in it. Note that there may exist different join trees for Q′, and we may have to try all possible options to obtain a theta-join tree. Fortunately, this computation depends only on the query, thus takes $\mathcal{O}(1)$ space and time in data complexity. If either the GYO algorithm fails to find a join tree for Q′ or no join tree allows us to assign the θ_j predicates to tree edges, then the query is cyclic and can be handled as discussed in Section 5.3. We discuss next how to create the enumeration graph for a given theta-join tree.

3.3. Factorized Join Representation

By relying on a join tree similar in structure to the equi-join case, we can establish a similar layered structure for the enumeration graph. In particular, each input relation appears in a separate layer and each join-tree edge is mapped to a subgraph implementing the join condition between the corresponding relation layers. This is visualized by the blue clouds in Figure 2b. In contrast to the equi-joins, we allow more general connection layers, possibly a single layer with a more complex connection pattern (like the S- to-T connection in the example) or even multiple layers (like the connection between R-node (2, 1) and U-node (2, 2)).

To be able to apply our any-k algorithms [77] to this generalized enumeration graph we must ensure that (1) each “blue cloud” can be mapped to a layered graph and (2) each tree solution corresponds to a join answer, and vice versa (like the one highlighted in Figure 2b which corresponds to joining input tuples s = (3, 2), t = (4, 3), r = (2,1), and u = (2, 2)). For (2) it is sufficient to ensure for each adjacent parent-child pair of relations in the theta-join tree that there exists a path from a node in the parent-relation layer to a node in the child-relation layer iff the corresponding input tuples join. In the example, there is a path from S-node (3, 2) via v₃ to T-node (4, 3), because the two tuples satisfy A = 3 < B = 4. Similarly, since s′ = (5,3) and t = (4, 3) violate A < B, there is no path from the former to the latter. For (1), it is sufficient to ensure that the “blue cloud” is a DAG with parent-relation nodes only having edges going into the cloud, while all child-relation edges must point out of the cloud. We formalize these properties with the notion of a Tuple-Level Factorization Graph (TLFG).

Definition 5 (TLFG). A Tuple-Level Factorization Graph of a theta-join S ⋈_θ T of relation S, called the source, and T, called the target, is a directed acyclic graph G(V, E) where:

1. V contains a distinct source node v_s for each tuple s ∈ S, a distinct target node v_t for each tuple t ∈ T, and possibly other intermediate nodes,

2. each source node v_s has only outgoing edges and each target node v_t has only incoming edges, and

3. for each s ∈ S, t ∈ T, there exists a path from v_s to v_t in G if and only if s and t satisfy join condition θ.

The size of a TLFG G(V, E) is ∣V∣ + ∣E∣ and its depth d is the maximum length of any path in G. The graphs depicted in Fig. 4a and Fig. 4b are valid TLFGs for equi-joins.

Figure 4: Factorization of Equality and Inequality conditions with our TLFGs. The S and T node labels indicate the values of the joining attributes. All TLFGs shown here have $\mathcal{O}(1)$ depth.

(a) Equality: naive construction with edges between all joining pairs. $\mathcal{O}(n^2)$ size, $\mathcal{O}(1)$ depth.

(b) Equality: grouping tuples with common join values together. $\mathcal{O}(n)$ size, $\mathcal{O}(1)$ depth.

(c) Inequality: naive construction with edges between all joining pairs. $\mathcal{O}(n^2)$ size, $\mathcal{O}(1)$ depth.

(d) Inequality: shared ranges. Middle nodes indicate a range. $\mathcal{O}(n)$ size, $\mathcal{O}(n)$ depth.

(e) Inequality: binary partitioning. Dotted lines indicate partitioning steps. $\mathcal{O}(n\log n)$ size, $\mathcal{O}(1)$ depth.

It is easy to see that any TLFG is a layered graph: Assign w.l.o.g. layer ID 0 to all source nodes v_s; each intermediate node v is assigned layer ID i, where i is the length of the longest path (measured in number of edges) from any source node to v. Here i is well-defined due to the TLFG’s acyclicity. All target-relation nodes are assigned to layer d, which is the maximum layer ID assigned to any intermediate node, plus 1. In the example in Figure 4d, node v₃ is in layer 3, because the longest path from any S-node to v₃ has 3 edges (from (1, 1) in the example). All T-nodes are in layer 6.

Since the entire generalized enumeration graph consists of ℓ relation layers and ℓ − 1 TLFGs (one for each edge of the theta-join tree), using Lemma 2 we can show:

Theorem 6. Given a theta-join Q of $\ell =\mathcal{O}(1)$ relations, a theta-join tree, and the corresponding enumeration graph G_Q, where for each edge of the theta-join tree the corresponding TLFG has $\mathcal{O}(\mid E\mid )$ size and $\mathcal{O}(d)$ depth, then ranked enumeration of the k-lightest tree solutions can be performed with $\text{TT}(k)=\mathcal{O}(\mid E\mid +k\log k+kd)$ and $\text{MEM}(k)=\mathcal{O}(\mid E\mid +kd)$.

The theorem states that worst-case size and depth of the TLFG determine the time and space complexity of enumerating the theta-join answers in weight order. Hence the main challenge is to encode join condition with the smallest and most shallow TLFG possible.

Direct TLFGs.

For any theta-join, a naive way to construct a TLFG is to directly connect each source node with all the target nodes it joins with. This results in $\mid E\mid =\mathcal{O}(n^2)$ and d = 1, thus $\text{TT}(k)=\mathcal{O}(n^2+k\log k)$ and $\text{MEM}(k)=\mathcal{O}(n^2+k)$, respectively. Hence even the top-ranked result requires quadratic time and space. To improve this complexity, we must find a TLFG with a smaller number of edges, while keeping the depth low. Our results are summarized in Figure 3, with details discussed in later sections.

Figure 3: We propose 4 different TLFGs for a single inequality. These trade off size with depth and 2 of them (in blue) achieve the equi-join guarantee up to a logarithmic factor.

Output duplicates.

A subtle issue with Theorem 6 is that two non-isomorphic tree solutions of the enumeration graph may contain the exact same input tuples (the relation-layer nodes), causing duplicate query answers. This happens if and only if a TLFG has multiple paths between the same source and destination node. While one would like to avoid this, it may not be possible to find a TLFG that is both efficient in terms of size and depth, and also free of duplicate paths. Among the inequality conditions studied in this paper, this only happens for disjunctions (Section 4.3).

Since duplicate join answers must be removed, the time to return the k top-ranked answers may increase. Fortunately, for our disjunction construction it is easy to show that the number of duplicates per output tuple is $\mathcal{O}(1)$, i.e., it does not depend on input size n. This implies that we can filter the duplicates on-the-fly without increasing the complexity of TT(k) (or MEM(k), for that matter): We maintain the top-k join answers returned so far in a lookup structure and, before outputting the next join answer, we check in $\mathcal{O}(1)$ time if the same output had been returned before.⁴

To prove that the number of duplicates per join answer is independent of input size, it is sufficient to show that for each TLFG the maximum number of paths from any source node v_s to any target node v_t, which we will call the duplication factor, is independent of input size. We show this to be the case for the only TLFG construction that could introduce duplicate paths: disjunctions (Section 4.3). A duplicate-free TLFG has a duplication factor equal to 1 (which is the case for most TLFGs we discuss).

3.4. Theta-join to Equi-join Reduction

The factorized representation of the output of a theta-join as an enumeration graph (using TLFGs to connect adjacent relation layers) enables a novel reduction from complex theta-joins to equi-joins.

Theorem 7. Let G = (V, E) be a TLFG of depth d for a theta-join S ⋈_θ T of relations S, T and X be the union of their attributes. For 0 < i ≤ d, let E_i be the set of edges from layer i − 1 to i. If E = ∪_i E_i, i.e., every edge connects nodes in adjacent layers, then S ⋈_θ T = π_X (S ⋈ E₁ ⋈ ⋯ ⋈ E_d ⋈ T) where π_X is an X-projection.

Intuitively, the theorem states that if no edge in the TLFG skips a layer, then the theta-join S ⋈_θ T can equivalently be computed as an equi-join between S, T, and d auxiliary relations. Each of those relations is the set of edges between adjacent layers of the TLFG.

The theorem is easy to prove by construction, which we explain using the example in Figure 2b. Consider the TLFG for S and T and notice that all edges are between adjacent layers and d = 2. In Figure 2c, the first tuple (1, 1, v₁) ∈ E₁ represents the edge from S-node (1, 1) to intermediate node v₁. (The tuple is obtained as the Cartesian product of the edge’s endpoints.) Similarly, the first tuple in E₂ represents the edge from v₁ to T-node (2, 1). It is easy to verify that S(A, D) ⋈_A<B T(B, C) = π_ADBC(S ⋈ E₁ ⋈ E₂ ⋈ T). The corresponding branch of the join tree is shown in Figure 2c. Compared to the theta-join tree in Figure 2b, the inequality condition disappeared from the edge and is replaced by new nodes E₁ (A, D, V₁) and E₂ (V₁, B, C).

QuadEqui for direct TLFGs.

Recall that any theta-join S ⋈_θ T between relations of size $\mathcal{O}(n)$ can be represented by a 1-layer TLFG that directly connects the joining S- and T-nodes. Since this TLFG satisfies the condition of Theorem 7, it can be reduced to equi-join S ⋈ E ⋈ T, where $\mid E\mid =\mathcal{O}(n^2)$. We refer to the algorithm that first applies this construction to each edge of the theta-join tree (and thus reducing the entire theta-join query between ℓ relations to an equi-join) and then uses the equi-join ranked-enumeration algorithm [77] as QuadEqui.

Below we will show that better constructions with smaller auxiliary relations E_i can be found for any join condition that is a DNF of inequalities. In particular, such joins can be expressed as S ⋈ E₁ ⋈ E₂ ⋈ T where E₁, E₂ are of size $\mathcal{O}(n\text{poly}\log n)$. Figure 2c shows a concrete instance. However, note that not all TLFGs satisfy the condition of Theorem 7. For example, Fig. 4d shows a TLFG which cannot be reduced to an equi-join with our theorem.

4. FACTORIZATION OF INEQUALITIES

We now show how to construct TLFGs of size $\mathcal{O}(n\text{poly}\log n)$ and depth $\mathcal{O}(1)$ when the join condition θ in a join S ⋈_θ T is a DNF⁵ of inequalities (and equalities). Starting with a single inequality, we then generalize to conjunctions and finally to DNF. Non-equalities and bands will be discussed in Section 5.

4.1. Single Inequality Condition

Efficient TLFGs for equi-joins exploit that equality conditions group input tuples into disjoint equivalence classes (Fig. 4b). For inequalities, this is generally not possible and therefore we need a different approach to leverage their structural properties (see Fig. 4c).

Binary partitioning.

Our binary-partitioning based TLFG is inspired by quicksort [40]. Consider condition S.A < T.B and a pivot value v. We partition relations S and T s.t. s.A < v for s ∈ S₁ and s.A ≥ v for s ∈ S₂, and similarly t.B < v for t ∈ T₁ and t.B ≥ v for t ∈ T₂. This guarantees that all A-values in S₁ are strictly less than all B-values in T₂. Instead of representing this with ∣S₁∣ · ∣T₂∣ direct edges (s_i ∈ S₁, t_j ∈ T₂), we introduce an intermediate “pivot node” v and use only ∣S₁∣ + ∣T₂∣ edges (s_i ∈ S₁, v) and (v, t_j ∈ T₂).

Then we continue recursively with the remaining partition pairs (S₁, T₁) and (S₂, T₂). (Note that (S₂, T₁) cannot contain joining tuples by construction.) Each recursive step will create a new intermediate node connecting a set of source and target nodes, therefore the TLFG has depth 2.

As the pivot, we use the median of the distinct join-attribute values appearing in the tuples in both input partitions. E.g., for multiset {1, 1, 1, 1, 2, 3, 3} the set of distinct values is {1, 2, 3} and hence the median is 2. This pivot is easy to find in $\mathcal{O}(n)$ time if the relations have been sorted on the join attributes beforehand. Since each partition step cuts the number of distinct values per partition in half, it takes $\mathcal{O}(\log n)$ steps until we reach the base case where all input tuples in a partition share the same join-attribute value and the recursion terminates. Overall, the algorithm takes time $\mathcal{O}(n\log n)$ to construct a TLFG of size $\mathcal{O}(n\log n)$ and depth 2. It is easy to see that there is exactly one path from each source to joining target node, hence the TLFG is duplicate-free.

Example 8. Figure 4e illustrates the approach, with dotted lines showing how the relations are partitioned. Initially, we create partitions containing the values {1, 2, 3} and {4, 5, 6} respectively. The source nodes containing A-values of the first partition are connected to target nodes containing B-values of the second partition via the intermediate node v₃. The first partition is then recursively split into {1} and {2, 3}. Even though these new partitions are uneven with 2 and 4 nodes respectively, they contain roughly the same number of distinct values (plus or minus one).

Other inequality types.

The construction for greater-than (>) is symmetric, connecting S₂ to T₁ instead of S₁ to T₂. For ≤ and ≥, we only need to modify handling of the base case of the recursion: instead of simply returning from the last call (when all tuples in a partition have the same join-attribute value), the algorithm connects the corresponding source and target nodes via an intermediate node (like for equality predicates).

Lemma 9. Let θ be an inequality predicate for relations S, T of total size n. A duplicate-free TLFG of S ⋈_θ T of size $\mathcal{O}(n\log n)$ and depth 2 can be constructed in $\mathcal{O}(n\log n)$ time.

4.2. Conjunctions

TLFG construction for conjunctions can be integrated elegantly into the recursive binary partitioning.

Example 10. Consider join condition S.A < T.C ∧ S.B > T.D for relations S(A, B),T(C, D) as shown in Fig. 5a. The algorithm initially considers the first inequality S.A < T.C, splitting the relations into S₁, T₁, S₂, T₂ as per the binary partitioning method (see Section 4.1). All pairs (s_i ∈ S₁, t_j ∈ T₂) satisfy S.A < T.C, but not all of them satisfy the other conjunct S.B > T.D. To correctly connect the source and target nodes, we therefore run the same binary partitioning algorithm on input partitions S₁ and T₂, but now with predicate S.B > T.D as illustrated by the diagonal blue edge in Fig. 5a; the resulting graph structure is shown in Fig. 5b. For the remaining partition pairs (S₁,T₁) and (S₂,T₂), the recursive call still needs to enforce both conjuncts as illustrated by the orange edges in Fig. 5a.

Figure 5: Example 10: Steps of the conjunction algorithm for two inequality predicates on S(A, B), T(C, D). Node labels depict A, B values (left) or C, D values (right).

(a) Binary partitioning and recursions.

(b) Handling the next predicate.

Strict inequalities.

The example generalizes in a straightforward way to the conjunction of any number of strict inequalities as shown in Algorithm 1. We note that the order in which the predicates are handled does not impact the asymptotic analysis, but in practice, handling the most selective predicates first is bound to give better performance. Whenever two partitions are guaranteed to satisfy a conjunct, that conjunct is removed from consideration in the next recursive call (Line 19). An intermediate node for the pivot and the corresponding edges connecting it to source and target nodes are only added to the TLFG when no predicates remain (Lines 14 to 16). Overall, we perform two recursions simultaneously. In one direction, we make recursive calls on smaller partitions of the data and the same set of predicates (Lines 21 and 22). In the other direction, when the current predicate is satisfied for a partition pair, nextPredicate() is called with one less predicate (Line 19). The recursion stops either when we are left with 1 join value (base case for binary partitioning) or we exhaust the predicate list (base case for conjunction). Finally, notice that each time a new predicate is processed by a recursive call, the join-attribute values in the corresponding partitions are sorted according to the new attributes (Line 6) to find the pivot.

Non-strict inequalities.

Like for a single predicate, we only need to modify handling of the base case when all join-attribute values in a partition are the same. While a strict inequality is not satisfied and thus no edges are added to the TLFG, the non-strict one is satisfied for all pairs of source and target nodes in the partition. Hence instead of exiting the recursive call (Line 10), the partition pair is treated like the (S₁, T₂) case (Lines 14 to 19).

Equalities.

If the conjunction contains both equality and inequality predicates, then we reduce the problem to an inequality-only conjunction by first partitioning the inputs into equivalence classes according to all equality predicates (see Fig. 4b). Then the inequality-only algorithm introduced above is executed on each of these partitions. Since the equality-based partitioning takes linear time and space, complexity is determined by the inequality predicates.

Lemma 11. Let θ be a conjunction of p inequality and any number of equality predicates for relations S, T of total size n. A duplicate-free TLFG of S ⋈_θ T of size $\mathcal{O}(n{\log }^{p}n)$ and depth 2 can be constructed in $\mathcal{O}(n{\log }^{p}n)$ time.

4.3. Disjunctions

Given a join condition that can be expressed as a disjunction P = ⋁_i P_i where G_i is the TLFG for P_i, we construct the TLFG G for P by simply “unioning” the G_i, i.e., G’s set of nodes and edges are the unions of the node and edge sets of the G_i, respectively. Note that the auxiliary “pivot” nodes added by the binary partitioning algorithm to the G_i are all distinct. Hence if there is a path from source s to target t in j of the individual G_i, then there are exactly j different paths from s to t in G. This creates duplicate join results when traversing G during the enumeration phase. Fortunately, since the number of “duplicate” paths depends only on the number of terms in P and hence query size (not input size), the number of duplicates per join output tuple is constant.

Lemma 12. Let θ be a disjunction of predicates θ₁, … , θ_p for relations S, T. If for each θ_i i ∈ [p] we can construct a duplicate-free TLFG of S ⋈θ_i T of size $\mathcal{O}({\mathcal{S}}_i)$ and depth d_i in $\mathcal{O}({\mathcal{T}}_i)$ time, then we can construct a TLFG of S ⋈_θ T of size $\mathcal{O}({\sum }_i{\mathcal{S}}_i)$ and depth max_i d_i in $\mathcal{O}({\sum }_i\mathcal{O}({\mathcal{T}}_i))$ time. The duplication factor of the latter is at most p.

We can now factorize any DNF of equality and inequality predicates by applying the conjunction construction to each conjunct, and then the union construction for their disjunction.

5. IMPROVEMENTS AND EXTENSIONS

We propose improvements that lead to our main result: strong worst-case guarantees for TT(k) and MEM(k) for acyclic join queries with inequalities, which we then extend to cyclic joins.

5.1. Improved Factorization Methods

We explore how to reduce the size of the TLFG for inequalities.

Multiway partitioning.

When the join predicate on an edge of the theta-join tree is a simple inequality like S.A < T.B, we can split the set of input tuples into $\mathcal{O}(\sqrt{n})$ partitions per step— instead of 2 partitions for binary partitioning (Section 4.1)—hence the name multiway partitioning. This results in a smaller TLFG of size $\mathcal{O}(n\log \log n)$ (vs. $\mathcal{O}(n\log n)$ for binary partitioning) and depth 3 (vs. 2). Unfortunately, it is unclear how to generalize this idea to a conjunction of inequalities.

Shared ranges.

A simple inequality can be encoded even more compactly with $\mathcal{O}(n)$ edges by exploiting the transitivity of “<” as illustrated in Figure 4d. Intuitively, our shared ranges method creates a hierarchy of intermediate nodes, each one representing a range of values. Each range is entirely contained in all those that are higher in the hierarchy, thus we connect the intermediate nodes in a chain. The resulting TLFG has size and depth $\mathcal{O}(n)$. The latter causes a high delay between consecutive join answers. From Theorem 6 and the fact that we need to sort to construct the TLFG, we obtain $\text{TT}(k)=\mathcal{O}(n\log n+n+k\log k+kn)=\mathcal{O}(n\log n+kn)$ and $\text{MEM}(k)=\mathcal{O}(n+kn)=\mathcal{O}(kn)$. Compared to binary partitioning’s $\mathcal{O}(n\log n+k\log k)$ and $\mathcal{O}(n\log n+k)$ (Theorem 6, Lemma 9), respectively, space complexity is reduced by about a factor log n, and without affecting time complexity, only for small k, i.e., k = o(log n). For larger k = Ω(n) both space and time complexity are worse by (almost) a factor n. (Recall that $k=\mathcal{O}(n^{\ell })$ for a join of ℓ relations.) Moreover, like for multiway partitioning, it is not clear how to generalize this construction to conjunctions of inequalities.

Non-Equality and Band Predicates.

A non-equality predicate can be expressed as a disjunction of 2 inequalities; a band predicate as a conjunction of 2 inequalities. Hence both can be handled by the techniques discussed in Section 4, at the cost of increasing query size by up to a constant factor. This can be avoided by a specialized construction that leverages the structure of these predicates. It is similar to the binary partitioning for an inequality (and hence omitted due to space constraints) and achieves the same size and depth guarantees for the TLFG.

5.2. Putting Everything Together

Using multiway partitioning and the specialized techniques for non-equality and band predicates yields:

Lemma 13. Let θ be a simple inequality, non-equality, or band predicate for relations S, T of size $\mathcal{O}(n)$. A duplicate-free TLFG for S ⋈_θ T of size $\mathcal{O}(n\log \log n)$ and depth 3 can be constructed in $\mathcal{O}(n\log n)$ time.

Applying the approach for a DNF of inequalities (Section 4), but using the specialized TLFGs for non-equality and band predicates and multiway partitioning for the base case of the conjunction construction (when only one predicate remains), we obtain:

Theorem 14 (Main Result). Let Q be a full acyclic theta-join query over a database D of size n where all the join conditions are DNF formulas of equality, inequality, non-equality, and band predicates. Let p be the maximum number of predicates, excluding equalities, in a conjunction of a DNF on any edge of the theta-join tree. Ranked enumeration of the answers to Q over D can be performed with $\text{TT}(k)=\mathcal{O}(n{\log }^{p}n+k\log k)$. The space requirement is $\text{MEM}(k)=\mathcal{O}(n{\log }^{p-1}n\cdot \log \log n+k)$.

5.3. Cyclic Queries

So far, we have focused only on acyclic queries, but our techniques are also applicable to cyclic queries with some modifications. Recall that acyclic queries admit a theta-join tree, which is found by assigning predicates to the edges of a join tree. If this procedure fails, we can handle the query as follows:

Post-processing filter.

An common practical solution for cyclic queries is to ignore some predicates during join processing, then apply them as a filter on the output. Specifically, we can remove θ_j predicates and equality conditions encoded by the same variable names until the query admits a theta-join tree, then apply our technique to the resulting acyclic query, and finally use the removed predicates as a filter. While this approach is simple to implement, it can suffer from large intermediate results. In the worst case, all answers to the acyclic join except the last one may be discarded, giving us $\text{TT}(k)=\mathcal{O}(n^{\ell }\log n)$ for an ℓ-relation cyclic join.

Transformation to equi-join.

An alternative approach with non-trivial guarantees is to apply our equi-join transformation to the cyclic query, and then use existing algorithms for ranked enumeration of cyclic equi-joins [77]. We deal with the case where each θ_j predicate is covered by at most 2 input relations; the general case is left for future work. To handle that case, we add edges to the join tree as needed (creating a cyclic theta-join graph) and assign predicates to covering edges. To achieve the equi-join transformation, we consider all pairs of connected relations in the join graph, build a TLFG according to the join condition, and then materialize relations “in the middle” as illustrated in Section 3.4. The resulting query contains only equality predicates, hence is a cyclic equi-join. Ranked enumeration for cyclic equi-joins is possible with guarantees that depend on a width measure of the query [77].

Example 15 (Inequality Cycle). The following triangle query variant joins three relations with inequalities in a cyclic way: Q(A,B,C,D,E,F) :- R(A,B), S(C,D), T(E,F), (B < C), (D < E), (F < A). Notice that there is no way to organize the relations in a tree with the inequalities over parent-child pairs. However, if we remove the last inequality (F < A), the query becomes acyclic and a generalized join tree can be constructed. Thus, we can apply our techniques on that query and filter the answers with the selection condition (F < A).

Alternatively, we can factorize the pairs of relations using our TLFGs, to obtain a cyclic equi-join. If we use binary partitioning, this introduces three new attributes V₁, V₂, V₃ and six new $\mathcal{O}(n\log n)$-size relations: E₁ (A, B, V₁), E₂(V₁, C, D), E₃ (C, D, V₂), E₄(V₂, E, F), E₅ (E, F, V₃), E₆ (V₃, A, B). The transformed query can be shown to have a submodular width [5, 56] of 5/3, making ranked enumeration possible with $\text{TT}(k)=\mathcal{O}((n\log n{)}^{5∕3}+k\log k)$.

6. EXPERIMENTS

We demonstrate the superiority of our approach for ranked enumeration against existing DBMSs, and even idealized competitors that receive the join output “for free" as an (unordered) array.

Algorithms.

We compare 5 algorithms: ① Factorized is our proposed approach. ② QuadEqui is an idealized version of the fairly straightforward reduction to equi-joins described in Section 3.4, which for each edge (S, T) of the theta-join tree uses the direct TLFG (no intermediate nodes) to convert S ⋈_θ T to equi-join S ⋈ E ⋈ T via the edge set E of the TLFG. Then previous ranked-enumeration techniques for equi-joins [77] can be applied directly. To avoid any concerns regarding the choice of technique for generating E, we provide it “for free.” Hence the algorithm is not charged for essentially executing theta-joins between all pairs of adjacent relations in the theta-join tree, meaning the QuadeEqui numbers reported here represent a lower bound of real-world running time. ③ Batch is an idealized version of the approach taken by state-of-the-art DBMSs. It computes the entire join output and puts it into a heap for ranked enumeration. To avoid concerns about the most efficient join implementation, we give Batch the entire join output “for free” as an in-memory array. It simply needs to read those output tuples (instead of having to execute the actual join) to rank them, therefore the numbers reported constitute a lower bound of real-world running time. We note that for a join of only ℓ = 2 relations, there is no difference between QuadEqui and Batch since they both receive all the query results; we thus omit QuadEqui for binary joins. ④ PSQL is the open-source PostgreSQL system. ⑤ System X is a commercial database system that is highly optimized for in-memory computation.

We also compare our factorization methods 1a Binary Partitioning, 1b Multiway Partitioning, and 1c Shared Ranges against each other. Recall that the latter two can only be applied to single-inequality type join conditions. Unless specified otherwise, Factorized is set to 1b Multiway Partitioning for the single-predicate cases and 1a Binary Partitioning for all others.

Data.

S Our synthetic data generator creates relations S_i (A_i, A_i+1, W_i), i ≥ 1 by drawing A_i, A_i+1 from integers in [0 … 10⁴ – 1] uniformly at random with replacement, discarding duplicate tuples. The weights W_i are real numbers drawn from [0, 10⁴). T We also use the LINEITEM relation of the TPC-H benchmark [2], keeping the schema Item(OrderKey, PartKey, Suppkey, LineNumber, Quantity, Price, ShipDate, CommitDate, ReceiptDate).

R For real data, we use a temporal graph RedditTitles [51] whose 286k edges represent posts from a source community to a target community identified by a hyperlink in the post title. The schema is Reddit(From, To, Timestamp, Sentiment, Readability). B OceaniaBirds [1] reports bird observations from Oceania with schema Birds(ID, Latitude, Longitude, Count). We keep only the 452k observations with a non-empty Count attribute.

Queries.

We test queries with various join conditions and sizes. Figure 6 gives the Datalog notation and the ranking function. Some of the queries have the number of relations ℓ as a parameter; for those we only write the join conditions between the i^th and (i + 1)^st relations, with the rest similarly organized in a chain. In the full version [79] we give the equivalent SQL queries.

Figure 6: Queries used in our experiments expressed in Datalog. The head always contains all body variables (no projections). Length ℓ of queries range from 2 to 10. Indices i range from 1 to ℓ − 1.

On our synthetic data, Q_S1 is a single inequality join, while Q_S2 has a more complicated join condition that is a conjunction of a band and a non-equality. On TPC-H, Q_T finds a sequence of items sold by the same supplier with the quantity increasing over time, ranked by the price. To test disjunctions, we run query Q_TD, which puts the increasing time constraint on either of the three possible dates. Query Q_R1 computes temporal paths [84] on RedditTitles, and ranks them by a measure of sentiment such that sequences of negative posts are retrieved first. Query Q_R2 uses instead the sentiment in the join condition, keeping only paths along which the negative sentiment increases. For ranking, we use readability to focus on posts of higher quality. Last, Q_B is a spatial band join on OceaniaBirds that finds pairs of high-count bird sightings that are close based on proximity.

Details.

Our algorithms are implemented in Java 8 and executed on an Intel Xeon E5-2643 CPU running Ubuntu Linux. Queries execute in memory on a Java VM with 100GB of RAM. If that is exceeded, we report an Out-Of-Memory (OOM) error. The any-k algorithm used by Factorized and QuadEqui is Lazy [23, 77] which was found to outperform others in previous work. The version of PostgreSQL is 9.5.25. We set its parameters such that it is optimized for main-memory execution and system overhead related to logging or concurrency is minimized, as it is standard in the literature [12, 77]. To enable input caching for PSQL and System X, each execution is performed twice and we only measure the second one. Additionally, we create B-tree or hash indexes for each attribute of the input relations, while our methods do not receive these indexes. Even though the task is ranked enumeration, we still give the database systems a LIMIT clause whenever we measure a specific TT(k), and thus allow them to leverage the k value. All data points we show are the median of 5 measurements. We timeout any execution that does not finish within 2 hours.

6.1. Comparison Against Alternatives

We will show that our approach has a significant advantage over the competition when the size of the output is sufficiently large. We test three distinct scenarios for which large output can occur: (1) the size of the database grows, (2) the length of the query increases, and (3) the parameter of a band join increases.

6.1.1. Effect of Data Size.

We run queries Q_S1, Q_S2 for different input sizes n and two distinct query lengths. Figure 7 depicts the time to return the top k = 10³ results. We also plot how the size of the output grows with increasing n on a secondary y-axis. Even though QuadEqui and Batch are given precomputed join results for free and do not even have to resolve complicated join predicates, they still require a large amount of memory to store those. Thus, they quickly run out of memory even for relatively small inputs (Figure 7b). PSQL does not face a memory problem because it can resort to secondary storage, yet becomes unacceptably slow. The in-memory optimized System X is 10 times faster than PSQL, but still follows the same trend because it is materializing the entire output. In contrast, our Factorized approach scales smoothly across all tests and requires much less memory. For instance, in Figure 7b QuadEqui fails after 8k input size, while we can easily handle 2M. For very small input, the idealized methods QuadEqui and Batch are sometimes faster, but their real running time would be much higher if join computation was accounted for. Q_S2 has more join predicates and thus smaller output size (Figures 7c and 7d). Our advantage is smaller in this case, yet still significant for large n.

Figure 7: Section 6.1.1: Synthetic data with a growing database size n. While all four alternative methods either run out of memory (“OOM”) or exceed a reasonable running time our method scales quasilinearly ($\mathcal{O}(n\text{poly}\log n)$) with n.

(a) Query Q_S1, ℓ = 2.

(b) Query Q_S1, ℓ = 4.

(c) Query Q_S2, ℓ = 2.

(d) Query Q_S2, ℓ = 4.

We similarly run queries Q_T (Figure 8a) and Q_TD (Figure 8b) for ℓ = 3 with an increasing scale factor (which determines data size). Here, the equi-join condition on the supplier severely limits the blowup of the output compared to the input. Still, Factorized is again superior. Disjunctions in Q_TD increase the running time of our technique only slightly by a small constant factor.

Figure 8: Section 6.1.1: TPC-H data with increasing scale factor. Disjunctions do not affect the scaling of our algorithm.

(a) Query Q_T, ℓ = 3.

(b) Query Q_TD, ℓ = 3.

6.1.2. Effect of Query Length.

Next, we test the effect of query length on RedditTitles. We plot TT(k) for three values (k = 1, 10³, 10⁶) when the length is small (ℓ = 2, 3) and one value (k = 10³) for longer queries. Note that for k = 1, the time of Factorized is essentially the time required for building our TLFGs, and doing a bottom-up Dynamic Programming pass [77]. Figure 9 depicts our results for queries Q_R1, Q_R2. Increasing the value of k does not have a serious impact for most of the approaches except for System X, which for k = 10⁶ is not able to provide the same optimized execution. For binary-join Q_R1, our Factorized is faster than the Batch lower bound (Figure 9a), and its advantage increases for longer queries, since the output also grows (Figure 9c). Batch runs out of memory for ℓ = 3, PSQL times out, while QuadEqui and System X are more than 100 times slower (Figure 9b). Query Q_R2 has an additional join predicate, hence its output size is smaller. Thus, the Batch lower bound is slightly better than our approach for ℓ = 2 (Figure 9e), but we expect it to be significantly slower if the cost of computing and materializing the output was taken into account. Either way, for ℓ ≥ 3 (Figure 9g), our approach dominates even when compared against the lower bounds. PSQL again times out for ℓ = 3 (Figure 9f), and the highly optimized System X is outclassed by our approach.

Figure 9: Section 6.1.2: a,b,c,e,f,g: Section 6.1.3: d, h: Temporal paths of different lengths on RedditTitles (left), and spatial band-join on OceaniaBirds (right). Our method is robust to increasing query sizes and band-join ranges.

(a) Query Q_R1, ℓ = 2.

(b) Query Q_R1, ℓ = 3.

(c) Query Q_R1, different lengths ℓ.

(d) Query Q_B, fixed ε = 0.01.

(e) Query Q_R2, ℓ = 2.

(f) Query Q_R2, ℓ = 3.

(g) Query Q_R2, different lengths ℓ.

(h) Query Q_B, different bands ε.

6.1.3. Effect of Band Parameter.

We now test the band-join Q_B on the OceaniaBirds dataset with various band widths ε. Figure 9d shows that Factorized is superior for all tested k values for ε = 0.01. Increasing the band width yields more joining pairs and causes the size of the output to grow (Figure 9h). Hence, Batch consumes more memory and cannot handle ε ≥ 0.16. On the other hand, the performance of Factorized is mildly affected by increasing ε. PSQL and System X were not able to terminate within the time limit even for the smallest ε because they use only one of the indexes (for Longitude), searching over a huge number of possible results.

6.2. Comparison of our Variants

We now compare our 3 factorization methods 1a, 1b, 1c.

6.2.1. Delay and TT(k).

Since only Binary Partitioning is applicable to all types of join conditions considered, we compare the different methods on Q_S1, which has only one inequality-type predicate. Figure 10a depicts TT(k) for k = 1, 10⁴, 2 · 10⁴, 3 · 10⁴. Even though Shared Ranges starts returning results faster because its TLFG is constructed in a single pass (after sorting), it suffers from a high enumeration delay (linear in data size), and quickly deteriorates as k increases. The delay is also depicted in Figure 10b, where we observe that Binary Partitioning returns results with lower delay than Multiway Partitioning (recall that Multiway Partitioning has a depth of 3 vs Binary Partitioning’s 2). These results are a consequence of the size-depth tradeoff of the TLFGs (Fig. 3). Note that the higher delay observed in the beginning is due to lazy initialization of data structures needed by the any-k algorithm.

Figure 10: Section 6.2: Comparing different aspects of our factorization methods on query Q_S1, ℓ = 2.

(a)TT(k) for n = 2¹⁶. After preprocessing (no pattern), each bar represents 10⁴ results (alternating pattern).

(b) Delay between the first n consecutive results for n = 2¹⁶ . The delay is averaged in a window of size 10³.

(c) Size of the constructed TLFG for increasing n. Measured as the total number of nodes and edges in the graph.

(d) Time to the first result for n = 2¹⁶ and different domain sizes. The tuple values are sampled randomly.

6.2.2. Join Representation.

We show the sizes of the constructed representation in Figure 10c, using an implementation-agnostic measure. As n increases there is an asymptotic difference between the three methods ($\mathcal{O}(n\log n)$ vs $\mathcal{O}(n\log \log n)$ vs $\mathcal{O}(n)$) that manifests in our experiment. To see how the presence of the same domain values could affect the construction of the TLFG, we also measure the time to the first result for different domain sizes (Figure 10d). All three of our methods become faster when the domain is small and multiple occurrences of the same value are more likely. This is expected since the intermediate nodes of our TLFG essentially represent ranges in the domain and they are more compact for smaller domains. Domain size does not significantly impact running time once it exceeds sample size (around n = 2¹⁶) and the probability of sampling duplicate domain values approaches zero.

7. RELATED WORK

Enumeration for equi-joins.

Unranked enumeration for equi-joins has been explored in various contexts [13, 14, 19, 20, 33, 74], with a landmark result showing for self-join-free equi-joins that linear preprocessing and constant delay are possible if and only if the query is free-connex acyclic [10, 16]. For the more demanding task of ranked enumeration, a logarithmic delay is unavoidable [18, 30]. Our recently proposed any-k algorithms represent the state of the art for ranked enumeration for equi-joins [77]. Other work in this space focuses on practical implementations [32] and direct access [21, 22] to output tuples.

Non-Equality (≠) and inequality (<) joins.

Techniques for batch-computation of the entire output for joins with non-equality (also called inequality [49] or disequality [10]) predicates mainly rely on variations of color coding [8, 49, 71]. The same core idea is leveraged by the unranked enumeration algorithm of Bagan et al. [10]. Queries with negation can be answered by rewriting them with not-all-equal-predicates [46], a generalization of non-equality.

Khayatt et al. [48] provide optimized and distributed batch algorithms for up to two inequalities per join. Aggregate computation [3] and Unranked enumeration under updates [43] have been studied for inequality predicates by using appropriate index structures.

We are the first to consider ranked enumeration for non-equality and inequality predicates, including DNF conditions containing both types, and to prove strong worst-case guarantees for a large class of these queries.

Orthogonal range search.

Our binary partitioning method shares a similar intuition with index structures that have been devised for orthogonal range search [6, 25]. For unranked enumeration, it has been shown [7, 82, 83] how, for two relations, a range tree [29] can be used to identify pairs of matching tuple sets. This gives an alternative method to construct our depth-2 TLFGs because a pair of matching tuple sets can be connected via one intermediate node. Our approach supports ranking and it is simpler since it does not require building a range tree. Our TLFG abstraction is also more general: our other representations (such as multiway partitioning) do not have any obvious representation as range trees.

Factorized databases.

Factorized representations of query results [11, 66] have been proposed for equi-joins in the context of enumeration [68, 69], aggregate computation [11], provenance management [54, 67, 68] and machine learning [4, 50, 65, 70, 73]. Our novel TLFG approach to factorization complements this line of research and extends the fundamental idea of factorization to ranked enumeration for theta-joins. For probabilistic databases, factorization of non-equalities [63] and inequalities [64] is possible with OBDDs. Although these are for a different purpose, we note that the latter exploits the transitivity of inequality, as our Shared Ranges (Figure 4d) and other approaches for aggregates do [26].

Top-k queries.

Top-k queries [72] are a special case of ranked enumeration where the value of k is given in advance and its knowledge can be exploited. Fagin et al. [35] present the Threshold Algorithm, which is instance-optimal under a “middleware” cost model for a restricted class of 1-to-1 joins. Follow-up work generalizes the idea to more general joins [36, 44, 55, 85], including theta-joins [57]. Since all these approaches focus on the middleware cost model, they do not provide non-trivial worst-case guarantees when the join cost is taken into account [78]. Ilyas et al. [45] survey some of these approaches, along with some related ones such as building top-k indexes [24, 76] or views [28, 41].

Optimal batch algorithms for joins.

Acyclic equi-joins are evaluated optimally in $\mathcal{O}(n+\mid \text{out}\mid )$ by the Yannakakis algorithm [88], where ∣out∣ is the output size. This bound is unattainable for cyclic queries [61], thus worst-case optimal join algorithms [58, 61, 62, 81] settle for the AGM bound [9], i.e., the worst-case output size. (Hyper)tree decomposition methods [5, 38, 56] can improve over these guarantees, while a geometric perspective has led to even stronger notions of optimality [47, 60]. Ngo [59] recounts the development of these ideas. That line of work focuses on batch-computation, i.e., on producing all the query results, or on Boolean queries, while we explore ranked enumeration.

8. CONCLUSIONS AND FUTURE WORK

Theta- and inequality-joins of multiple relations are generally considered “hard” and even state-of-the-art commercial DBMSs struggle with their efficient computation. We developed the first ranked-enumeration techniques that achieve non-trivial worst-case guarantees for a large class of these joins: For small k, returning the k top-ranked join answers for full acyclic queries takes only slightly-more-than-linear time and space $(\mathcal{O}(n\text{polylog}n))$ for any DNF of inequality predicates. For general theta-joins, time and space complexity are quadratic in input size. These are strong worst-case guarantees, close to the lower time bound of $\mathcal{O}(n)$ and much lower than the $\mathcal{O}(n^{\ell })$ size of intermediate or final results traditional join algorithms may have to deal with. Our results apply to many cyclic joins (modulo higher pre-processing cost depending on query width) and all acyclic joins, even those with selections and many types of projections. In the future, we will study parallel computation and more general cyclic joins and projections.

Summary.

① Factorized is superior when the total output size is large, even when compared against a lower bound of the running time of the other methods. ② Qvadeqvi and ③ Batch require significantly more memory and are infeasible for many queries. ④ Psql and ⑤ System X, similarly to Batch, must produce the entire output, which is very costly. While System X is clearly faster than PSQL, it can be several orders of magnitude slower than our Factorized, and is outperformed across all tested queries.