Worst-Case Optimal Join Algorithms: Techniques, Results, and Open Problems

TL;DR

This paper introduces worst-case optimal join algorithms, highlighting their theoretical foundations, practical implementations, and open research problems, emphasizing their significance across multiple disciplines and real-world data systems.

Contribution

It provides a comprehensive overview of the design, analysis, and key techniques of worst-case optimal join algorithms, connecting theory with practical applications.

Findings

Algorithms match the worst-case output size bounds.

Connections between join algorithms and information theory.

Identification of fundamental open problems in the field.

Abstract

Worst-case optimal join algorithms are the class of join algorithms whose runtime match the worst-case output size of a given join query. While the first provably worst-case optimal join algorithm was discovered relatively recently, the techniques and results surrounding these algorithms grow out of decades of research from a wide range of areas, intimately connecting graph theory, algorithms, information theory, constraint satisfaction, database theory, and geometric inequalities. These ideas are not just paperware: in addition to academic project implementations, two variations of such algorithms are the work-horse join algorithms of commercial database and data analytics engines. This paper aims to be a brief introduction to the design and analysis of worst-case optimal join algorithms. We discuss the key techniques for proving runtime and output size bounds. We particularly focus on the fascinating connection between join algorithms and information theoretic inequalities, and the idea of how one can turn a proof into an algorithm. Finally, we conclude with a representative list of fundamental open problems in this area.

Tables

Table 1. Table 1. Summary of entropic and polymatroid size bounds for full conjunctive queries along with their tightness properties.

Bound	Entropic Bound	Polymatroid Bound
Definition	$\underset{𝑫\vDash \text{DC}}{sup}\log \left|Q\left(𝑫\right)\right|\le \underset{h\in {\overline{\Gamma }}_n^{\ast }\cap \text{HDC}}{\max }h\left(\left[n\right]\right)$ (See [30, 7])	$\underset{𝑫\vDash \text{DC}}{sup}\log \left|Q\left(𝑫\right)\right|\le \underset{h\in {\Gamma }_n\cap \text{HDC}}{\max }h\left(\left[n\right]\right)$ (See [30, 7])
DC contains only cardinality constraints	AGM bound [33, 12] (Tight [12])	AGM bound [33, 12] (Tight [12])
DC contains only cardinality and FD constraints	Entropic Bound for FD [30] (Tight [28])	Polymatroid Bound for FD [30] (Not tight [8])
DC is a general set of degree constraints	Entropic Bound for DC [7] (Tight [8])	Polymatroid Bound for DC [7] (Not tight [8])

Table 2. Table 2. Proof sequence to algorithmic steps. θ := N B ​ C ​ N C ​ D ​ N A ​ B ​ D | B ​ D N A ​ B ​ N A ​ C ​ D | A ​ C assign 𝜃 subscript 𝑁 𝐵 𝐶 subscript 𝑁 𝐶 𝐷 subscript 𝑁 conditional 𝐴 𝐵 𝐷 𝐵 𝐷 subscript 𝑁 𝐴 𝐵 subscript 𝑁 conditional 𝐴 𝐶 𝐷 𝐴 𝐶 \theta:=\sqrt{\frac{N_{BC}N_{CD}N_{ABD|BD}}{N_{AB}N_{ACD|AC}}}

Name	proof step	operation	action
decomposition	$h\left(BC\right)\to h\left(B\right)+h\left(BC|B\right)$	partition	$S\to S^{\text{heavy}}\cup S^{\text{light}}$
			$S^{\text{heavy}}\leftarrow \{(b,c)\in S:\left|{\sigma }_{B=b}R\right|>\theta \}$
			$S^{\text{light}}\leftarrow \{(b,c)\in S:\left|{\sigma }_{B=b}R\right|\le \theta \}$
submodularity	$h\left(CD\right)\to h\left(BCD|B\right)$	NOOP	$T(C,D)$ now “affiliated” with $h\left(BCD|B\right)$
composition	$h\left(B\right)+h\left(BCD|B\right)\to h\left(BCD\right)$	join	$I_1(B,C,D)\leftarrow S^{\text{heavy}}(B,C),T(C,D)$.
submodularity	$h\left(ABD|BD\right)\to h\left(ABCD|BCD\right)$	NOOP	$V(A,B,D)$ now “affiliated” with $h\left(ABCD|BCD\right)$
composition	$h\left(ABCD|BCD\right)+h\left(BCD\right)\to h\left(ABCD\right)$	join	${\text{output}}_1(A,B,C,D)\leftarrow V(A,B,D),I_1(B,C,D)$.
submodularity	$h\left(BC|B\right)\to h\left(ABC|AB\right)$	NOOP	$S^{\text{light}}$ now “affiliated” with $h\left(ABC|AB\right)$
composition	$h\left(AB\right)+h\left(ABC|AB\right)\to h\left(ABC\right)$	join	$I_2(A,B,C)\leftarrow R(A,B),S^{\text{light}}(B,C)$.
submodularity	$h\left(ACD|AC\right)\to h\left(ABCD|ABC\right)$	NOOP	$W(A,C,D)$ now “affiliated” with $h\left(ABCD|ABC\right)$
composition	$h\left(ABC\right)+h\left(ABCD|ABC\right)\to h\left(ABCD\right)$	join	${\text{output}}_2(A,B,C,D)\leftarrow I_2(A,B,C),W(A,C,D)$.

Equations

$$(\text{worst-case output size})$$

$$Q_{△}(A,B,C)\leftarrow R(A,B),S(B,C),T(A,C),$$

$$H[A,B,C]$$

$$H[A,B]$$

$$H[B,C]$$

$$H[A,C]$$

$$H[A,B,C]\le \alpha H[A,B]+\beta H[B,C]+\gamma H[A,C]$$

$$|Q_{△}|\le |R{|}^{\alpha }\cdot |S{|}^{\beta }\cdot |T{|}^{\gamma }.$$

$${\log }_2|Q_{△}|\le \min$$

$$\alpha +\beta \ge 1,$$

$$\alpha +\gamma \ge 1,$$

$$\beta +\gamma \ge 1,$$

$$\alpha ,\beta ,\gamma \ge 0.$$

$$|Q_{△}|$$

$$=\sum _a\sum _b{1}_{R(a,b)}\sum _c{1}_{S(b,c)}{1}_{T(a,c)}$$

$$\le \sum _a\sum _b{1}_{R(a,b)}\sqrt{\sum _c{1}_{S(b,c)}}\cdot \sqrt{\sum _c{1}_{T(a,c)}}$$

$$=\sum _a\sum _b{1}_{R(a,b)}\sqrt{|{\sigma }_{B=b}S|}\cdot \sqrt{|{\sigma }_{A=a}T|}$$

$$=\sum _a\sqrt{|{\sigma }_{A=a}T|}\sum _b{1}_{R(a,b)}\cdot \sqrt{|{\sigma }_{B=b}S|}$$

$$\le \sum _a\sqrt{|{\sigma }_{A=a}T|}\sqrt{\sum _b{1}_{R(a,b)}}\cdot \sqrt{\sum _b|{\sigma }_{B=b}S|}$$

$$=\sum _a\sqrt{|{\sigma }_{A=a}T|}\sqrt{|{\sigma }_{A=a}R|}\sqrt{|S|}$$

$$=\sqrt{|S|}\cdot \sum _a\sqrt{|{\sigma }_{A=a}T|}\sqrt{|{\sigma }_{A=a}R|}$$

$$\le \sqrt{|R|\cdot |S|\cdot |T|}.$$

$$\min \left\{|{\pi }_C{\sigma }_{B=b}S|,|{\pi }_C{\sigma }_{A=a}T|\right\}\le \sqrt{|{\sigma }_{B=b}S|\cdot |{\sigma }_{A=a}T|}.$$

$$\tilde{O}\left(\sum _a\sum _b{1}_{R(a,b)}\sqrt{|{\sigma }_{B=b}S|\cdot |{\sigma }_{A=a}T|}\right).$$

$$2H[A,B,C]\le H[A,B]+H[B,C]+H[A,C].$$

$$H[A,B]+H[B,C]+H[A,C]$$

$$=(H[A]+H[B,C])+(H[B|A]+H[A,C])$$

$$\ge (H[A|B,C]+H[B,C])+(H[B|A,C]+H[A,C])$$

$$=2H[A,B,C].$$

$$|R^{\text{heavy}}\bowtie S|\le \frac{|R|\cdot |S|}{\theta }=\sqrt{|R|\cdot |S|\cdot |T|}.$$

$$Q({\mathbf{A}}_{[n]})\leftarrow \underset{F\in \mathcal{E}}{\bigwedge }{R}_F({\mathbf{A}}_F),$$

$${\mathrm{deg}}_F({\mathbf{A}}_Y|{\mathbf{A}}_X):=\underset{\mathbf{t}}{\max }|{\Pi }_{{\mathbf{A}}_Y}({\sigma }_{{\mathbf{A}}_X=\mathbf{t}}(R_F))|\le N_{Y|X}.$$

$$\text{FECP}(\mathcal{H}):=\left\{\mathbf{\delta }=({\delta }_F{)}_{F\in \mathcal{E}}|\mathbf{\delta }\ge 0\wedge \sum _{F:v\in F}{\delta }_F\ge 1,\forall v\in [n]\right\}.$$

$${\rho }^{\ast }(\mathcal{H}):=\min \left\{\sum _{F\in \mathcal{E}}{\delta }_F|\mathbf{\delta }\in \text{FECP}(\mathcal{H})\right\}$$

$$H[{\mathbf{A}}_F]:=\sum _{{\mathbf{a}}_F\in {\prod }_{i\in F}\text{Dom}(A_i)}\mathbb{P}[{\mathbf{A}}_F={\mathbf{a}}_F]\log \frac{1}{\mathbb{P}[{\mathbf{A}}_F={\mathbf{a}}_F]}$$

$${\text{supp}}_F(\mathcal{D}):=\left\{{\mathbf{x}}_F\in \prod _{i\in F}\text{Dom}(A_i)|\mathbb{P}[{\mathbf{A}}_F={\mathbf{x}}_F]>0\right\}.$$

$$H[Y|X]:=H[Y]-H[X].$$

$$H[\varnothing ]$$

$$H[X]$$

Full text

This paper aims to be a brief introduction to the design and analysis of worst-case optimal join algorithms. We discuss the key techniques for proving runtime and output size bounds. We particularly focus on the fascinating connection between join algorithms and information theoretic inequalities, and the idea of how one can turn a proof into an algorithm. Finally, we conclude with a representative list of fundamental open problems in this area.

1. Introduction

1.1. Overview

Relational database query evaluation is one of the most well-studied problems in computer science. Theoretically, even special cases of the problem are already equivalent to fundamental problems in other areas; for example, queries on one edge relation can already express various graph problems, conjunctive query evaluation is deeply rooted in finite model theory and constraint satisfaction [18, 43, 23, 65, 57], and the aggregation version is inference in discrete graphical models [5]. Practically, relational database management systems (RDBMS) are ubiquitous and commercially very successful, with almost 50 years of finely-tuned query evaluation algorithms and heuristics [61, 31, 13].

In the last decade or so there have emerged fundamentally new ideas on the three key problems of a relational database engine: (1) constructing query plans, (2) bounding intermediate or output size, and (3) evaluating (intermediate) queries. The new query plans are based on variable elimination and equivalently tree decompositions [5, 6, 29]. The new (tight) size bounds are information-theoretic, taking into account in a principled way input statistics and functional dependencies [32, 12, 30, 7, 8]. The new algorithms evaluate the multiway join operator in a worst-case optimal manner [52, 66, 51, 7, 8], which is provably asymptotically better than the one-pair-at-a-time join paradigm.

These fresh developments are exciting both from the theory and from the practical stand point. On the theory side, these results demonstrate beautiful synergy and interplay of ideas from many different research areas: algorithms, extremal combinatorics, parameterized complexity, information theory, databases, machine learning, and constraint satisfaction. We will briefly mention some of these connections in Sec. 1.2 below. On the practice side, these results offer their assistance “just in time” for the ever demanding modern data analytics workloads. The generality and asymptotic complexity advantage of these algorithms open wider the pandora box of true “in-database” graph processing, machine learning, large-scale inference, and constraint solving [50, 2, 3, 22, 25, 36, 11, 54, 55, 59, 45, 34].

The reader is referred to [5, 6] for descriptions of the generality of the types of queries the new style of query plans can help answer. In particular, one should keep in mind that the bounds and algorithms described in this paper apply to aggregate queries in a very general setting, of which conjunctive queries form a special case. The focus of this paper is on the other two developments: output size bounds and worst-case optimal join (WCOJ) algorithms.

Roughly speaking, a WCOJ algorithm is a join algorithm evaluating a full conjunctive query in time that is proportional to the worst-case output size of the query. More precisely, we are given a query $Q$ along with a set DC of “constraints” the input database $\bm{D}$ is promised to satisfy. The simplest form of constraints contain the sizes of input relations; these are called cardinality constraints. The second form of constraints is prevalent in RDBMSs, that of functional dependencies (FD). We shall refer to them as FD* constraints*. These constraints say that, if we fix the bindings of a set $X$ of variables, then there is at most one binding for every variable in another set $Y$. More generally, there are degree constraints, which guarantee that for any fixed binding of variables in $X$, there are at most some given number of bindings of variables in $Y$. Degree constraints generalize both cardinality and FD constraints, because cardinality constraints correspond to degree constraints when $X=\emptyset$.

We write $\bm{D}\models\text{\sf DC}$ to denote the fact that the database $\bm{D}$ satisfies the degree constraints DC. The worst-case output size problem is to determine the quantity

[TABLE]

and a WCOJ algorithm runs in time $\tilde{O}(|\bm{D}|+\sup_{\bm{D}\models\text{\sf DC}}|Q(\bm{D})|)$, where $\tilde{O}$ hides a $\log$ factor in the data size and some query-size dependent factor. In what follows we present a brief overview of the history of results on determining (1) and on associated WCOJ algorithms.

Independent of WCOJ algorithms, the role of bounding and estimating the output size in query optimizer is of great importance, as estimation errors propagate and sometimes are as good as random guesses, leading to bad query plans [39]. Hence, as we enrich the class of constraints allowable in the DC set (say from upper degree bounds to histogram information or more generally various statistical properties of the input), one should expect the problem of determining (1) or its expectation to gain more prominence in any RDBMS.

The role of determining and computing (1) in the design of WCOJ algorithms, on the other hand, has a fascinating and different bent: we can turn a mathematical proof of a bound for (1) into an algorithm; different proof strategies yield different classes of algorithms with their own pros and cons. Deriving the bound is not only important for analyzing the runtime of the algorithm, but also instrumental in how one thinks about designing the algorithm in the first place. Another significant role that the problem (1) plays is in its deep connection with information theory and (abstract algebraic) group theory. This paper aims to be a guided tour of these connections.

The notion of worst-case optimality has influenced a couple of other lines of inquiries, in parallel query processing [42, 44], and join processing in the IO model [37]. Furthermore, more than just paperware, WCOJ algorithms have found their way to academic data management and analytic systems [19, 1, 41, 10, 56], and are part of two commercial data analytic engines at LogicBlox [11] and RelationalAI.

The author is deeply indebted to Mahmoud Abo Khamis and Dan Suciu, whose insights, enthusiasm, and collaborative effort (both on [7, 8] and off official publication records) in the past few years have helped form the skeleton of the story that this article is attempting to tell. The technical landscape has evolved drastically from an early exposition on the topic [52].

1.2. A brief history of bounds and algorithms

We start our history tour with the bound (1) in the simple setting when all constraints in DC are cardinality constraints. Consider, for example, the following “triangle query”:

[TABLE]

While simple, this is not a toy query. In social network analysis, counting and enumerating the number of triangles in a large graph $G=(V,E)$ is an important problem, which corresponds to (2) with $R=S=T=E$. There is a large literature on trying to speed up this one query; see, e.g. [64, 63, 15] and references thereof.

One way to think about the output size bound is to think of $Q_{\triangle}$ as containing points $(a,b,c)$ in a three-dimensional space, whose projection onto the $(A,B)$-plane is contained in $R$, onto the $(B,C)$ plane is contained in $S$, and onto the $(A,C)$-plane is contained in $T$. There is a known geometric inequality shown by Loomis and Whitney in 1949 [46] which addresses a more general problem: bound the volume of a convex body in space whose shadows on the coordinate hyperplanes have bounded areas. The triangle query above corresponds to the discrete measure case, where “volume” becomes “count”. Specializing to the triangle, Loomis-Whitney states that $|Q_{\triangle}|\leq\sqrt{|R|\cdot|S|\cdot|T|}$. Thus, while studied in a completely different context, Loomis-Whitney’s inequality is our earliest known answer to determining (1) for a special class of join queries. In [51, 52], we referred to these as the Loomis-Whitney queries: those are queries where every input atom contains all but one variable.

In a different context, in 1981 Noga Alon [9] studied the problem (1) in the case where we want to determine the maximum number of occurrences of a given subgraph $H$ in a large graph $G$. ($H$ is the query’s body, and $G$ is the database.) Alon’s interest was to determine the asymptotic behavior of this count, but his formula was also asymptotically tight. In the triangle case, for example, Alon’s bound is $\Theta(N^{3/2})$, the same as that of Loomis-Whitney. Here, $N$ is the number of edges in $G$. Alon’s general bound is $\Theta(N^{\rho^{*}(H)})$, where $\rho^{*}$ denote the “fractional edge cover number” of $H$ (see Section 3). However, his results were not formulated in this more modern language.

A paper by Chung et al. [20] on extremal set theory was especially influential in our story. The paper proved the “Product Theorem” which uses the entropy argument connecting a count estimation problem to an entropic inequality. We will see this argument in action in Section 4. The Product theorem is proved using what is now known as Shearer’s lemma; a clean formulation and a nice proof of this lemma was given by Radhakrishnan [58].

In 1995, Bollobás and Thomason [16] proved a vast generalization of Loomis-Whitney’s result. Their bound, when specialized down to the discrete measure and our problem, implies what is now known as the AGM-bound (see below and Corollary 4.2). The equivalence was shown in Ngo et al. [51]. The key influence of Bollobás-Thomason’s result to our story was not the bound, which can be obtained through Shearer’s lemma already, but the inductive proof based on Hölder’s inequality. Their inductive proof suggests a natural recursive algorithm and its analysis, which lead to the algorithms in [51, 52].

Independently, in 1996 Friedgut and Kahn [27] generalized Alon’s earlier result from graphs to hypergraphs, showing that the maximum number of copies of a hypergraph $\mathcal{H}$ inside another hypergraph $\mathcal{G}$ is $\Theta(N^{\rho^{*}(\mathcal{H})})$. Their argument uses the product theorem from Chung et al. [20]. The entropic argument was used in Friedgut’s 2004 paper [26] to prove a beautiful inequality, which we shall call Friedgut’s inequality. In Theorem 35 we present an essentially equivalent version of Friedgut’s inequality formulated in a more database-friendly way, and prove it using the inductive argument from Bollobás-Thomason. Friedgut’s inequality not only implies the AGM-bound as a special case, but also can be used in analyzing the backtracking search algorithm presented in Section 5. Theorem 35 was stated and used in Beame et al. [14] to analyze parallel query processing; Friedgut’s inequality is starting to take roots in database theory.

Grohe and Marx ([32, 33], 2006) were pushing boundaries on the parameterized complexity of constraint satisfaction problems. One question they considered was to determine the maximum possible number of solutions of a sub-problem defined within a bag of a tree decomposition, given that the input constraints were presented in the listing representation. This is exactly our problem (1) above, and they proved the bound of $O(N^{\rho^{*}(Q)})$ using Shearer’s lemma, where $\rho^{*}(Q)$ denote the fractional edge cover number of the hypergraph of the query. They also presented a join-project query plan running in time $O(N^{\rho^{*}(Q)+1})$, which is almost worst-case optimal.

Atserias et al. ([12], 2008) applied the same argument to conjunctive queries showing what is now known as the AGM-bound. More importantly, they proved that the bound is asymptotically tight and studied the average-case output size. The other interesting result from [12] was from the algorithmic side. They showed that there is class of queries $Q$ for which a join-project plan evaluates them in time $O(N^{3})$ while any join-only plan requires $\Omega(N^{\Omega(\log k)})$, where $k$ is the query size. In particular, join-project plans are strictly better than join-only plans.

Continuing with this line of investigation, Ngo et al. (2012, [51]) presented the NPRR algorithm running in time $\tilde{O}(N^{\rho^{*}(Q)})$, and presented a class of queries for which any join-project plan is worse by a factor of $\Omega(N^{1-1/k})$ where $k$ is the query size. The class of queries contains the Loomis-Whitney queries. The NPRR algorithm and its analysis were overly complicated. Upon learning about this result, Todd Veldhuizen of LogicBlox realized that his Leapfrog-Triejoin algorithm (LFTJ) can also achieve the same runtime, with a simpler proof. LFTJ is the work-horse join algorithm for the LogicBlox’s datalog engine, and was already implemented since about 2009. Veldhuizen published his findings in 2014 [66]. Inspired by LFTJ and its simplicity, Ngo et al. [52] presented a simple recursive algorithm called Generic-Join which also has a compact analysis.

The next set of results extend DC to more than just cardinality constraints. Gottlob et al. [30] extended the AGM bound to handle FD constraints, using the entropy argument. They also proved that the bound is tight if all FD’s are simple FDs. Abo Khamis et al. [7] observed that the same argument generalizes the bound to general degree constraints, and addressed the corresponding algorithmic question. The bound was studied under the FD-closure lattice formalism, where they showed that the bound is tight if the lattice is a distributive lattice. This result is a generalization of Gottlob et al.’s result on the tightness of the bound under simple FDs. The connection to information theoretic inequalities and the idea of turning an inequality proof into an algorithm was also developed with the CSMA algorithm in [7]. However, the algorithm was also too complicated, whose analysis was a little twisted at places.

Finally, in [8] we developed a new collection of bounds and proved their tightness and looseness for disjunctive datalog rules, a generalization of conjunctive queries. It turns out that under general degree constraints there are two natural classes of bounds for the quantity (1): the entropic bounds are tight but we do not know how to compute them, and the relaxed versions called polymatroid bounds are computable via a linear program. When there is only cardinality constraints, these two bounds collapse into one (the AGM bound). We discuss some of these results in Section 4. The idea of reasoning about algorithms’ runtimes using Shannon-type inequalities was also developed to a much more mature and more elegant degree, with an accompanying algorithm called PANDA. We discuss what PANDA achieves in more details in Section 5.

1.3. Organization

The rest of the paper is organized as follows. Section 2 gently introduces two ways of bounding the output size and two corresponding algorithms using the triangle query. Section 3 presents notations, terminology, and a brief background materials on information theory and properties of the entropy functions. Section 4 describes two bounds and two methods for proving output size bounds on a query given degree constraints. This section contains some proofs and observations that have not appeared elsewhere. Section 5 presents two algorithms evolving naturally from the two bound-proving strategies presented earlier. Finally, Section 6 lists selected open problems arising from the topics discussed in the paper.

2. The triangle query

The simplest non-trivial example illustrating the power of WCOJ algorithms is the triangle query (2). We use this example to illustrate several ideas: the entropy argument, two ways of proving an output size bound, and how to derive algorithms from them. The main objective of this section is to gently illustrate some of the main reasoning techniques involved in deriving the bound and the algorithm; we purposefully do not present the most compact proofs. At the end of the section, we raise natural questions regarding the assumptions made on the bound and algorithm to motivate the more general problem formulation discussed in the rest of the paper.

The bound

Let $\text{\sf Dom}(X)$ denote the domain of attribute $X\in\{A,B,C\}$. Construct a distribution on $\text{\sf Dom}(A)\times\text{\sf Dom}(B)\times\text{\sf Dom}(C)$ where a triple $(a,b,c)$ is selected from the output $Q_{\triangle}$ uniformly. Let $H$ denote the entropy function of this distribution, namely for any $\bm{X}\subseteq\{A,B,C\}$, $H[\bm{X}]$ denotes the entropy of the marginal distribution on the variables $\bm{X}$. Then, the following hold:

[TABLE]

The first inequality holds because the support of the marginal distribution on $\text{\sf Dom}(A)\times\text{\sf Dom}(B)$ is a subset of $R(A,B)$, and the entropy is bounded by the $\log_{2}$ of the support (see Section 3.2). The other two inequalities hold for the same reason. Hence, whenever there are coefficients $\alpha,\beta,\gamma\geq 0$ for which

[TABLE]

holds for all entropy functions $H$, we can derive an output size bound for the triangle query:

[TABLE]

In Section 4 we will show that (3) holds for all entropy function $H$ if and only if $\alpha+\beta\geq 1$, $\beta+\gamma\geq 1$, and $\alpha+\gamma\geq 1$, for non-negative $\alpha,\beta,\gamma$. This fact is known as Shearer’s inequality, though Shearer’s original statement is weaker than what was just stated.

One consequence of Shearer’s inequality is that, to obtain the best possible bound, we will want to minimize the right-hand-side (RHS) of (4) subject to the above constraints:

[TABLE]

This bound is known as the AGM*-bound* for $Q_{\triangle}$. It is a direct consequence of Friedgut’s inequality (Theorem 35).

Algorithms

Let $N=\max\{|R|,|S|,|T|\}$, and $(\alpha^{*},\beta^{*},\gamma^{*})$ denote an optimal solution to the LP (5) above. A WCOJ algorithm needs to be able to answer $Q_{\triangle}$ in time $\tilde{O}(N+|R|^{\alpha^{*}}|S|^{\beta^{*}}|T|^{\gamma^{*}})$, where $\tilde{O}$ hides a single $\log N$ factor. The feasible region of (5) is a $3$-dimensional simplex. Without loss of generality, we can assume that $(\alpha^{*},\beta^{*},\gamma^{*})$ is one of the $4$ vertices of the simplex, which are $(1,1,0)$, $(1,0,1)$, $(0,1,1)$, and $(.5,.5,.5)$. If $(\alpha^{*},\beta^{*},\gamma^{*})=(1,1,0)$, then the traditional join plan $(R\Join S)\Join T$ has the desired runtime of $\tilde{O}(|R|\cdot|S|)=\tilde{O}(|R|^{\alpha^{*}}|S|^{\beta^{*}}|T|^{\gamma^{*}})$, modulo $\tilde{O}(N)$ preprocessing time.

Consequently, the only interesting case is when $(\alpha^{*},\beta^{*},\gamma^{*})=(.5,.5,.5)$. It is easy to see that this is optimal to LP (5) when the product of sizes of any two relations from $R$, $S$, and $T$ is greater than the size of the third relation. To design an algorithm running in $\tilde{O}(N+\sqrt{|R|\cdot|S|\cdot|T|})$-time, we draw inspiration from two different proofs of the bound (5).

First Algorithm. Write $\bm{1}_{E}$ to denote the indicator variable for the Boolean event $E$; for example $\bm{1}_{R(a,b)}$ is $1$ if $(a,b)\in R$ and [math] otherwise. Let $\sigma$ denote the relational selection operator. The Bollobás-Thomason’s argument for proving (5) goes as follows.

[TABLE]

All three inequalities follow from Cauchy-Schwarz. Tracing the inequalities back to $|Q_{\triangle}|$, Algorithm 1 emerges.

The analysis is based on only a single assumption, that we can loop through the intersection of two sets $X$ and $Y$ in time bounded by $\tilde{O}(\min\{|X|,|Y|\})$. This property can be satisfied with sort-merge or simple hash join when we iterate through the smaller of the two sets and look up in the hash table of the other. For a fixed binding $(a,b)$, the inner-most loop runs in time

[TABLE]

A binding $(a,b)$ gets in the inner loop only if $(a,b)\in R$, and so the total amount of work is

[TABLE]

Compare this with (13), and the runtime analysis is completed.

Second Algorithm. This algorithm is inspired by a proof of inequality (3), which implies (5). In this particular case (3) can be written as

[TABLE]

Using the chain rule (eq. (29)) and the submodularity rule (eq. (33)) for entropic functions, the inequality can be proved as follows.

[TABLE]

The first replacement $H[A,B]\to H[A]+H[B\ |\ A]$ is interpreted as a decomposition of the relation $R(A,B)$ into two parts “heavy” and “light”. After applying submodularity, two compositions are performed to obtain two copies of $H[A,B,C]$: $H[A\ |\ B,C]+H[B,C]\to H[A,B,C]$ and $H[B\ |\ A,C]+H[A,C]\to H[A,B,C]$. These correspond to join operators. Algorithm 2 has the pseudo-code. It is remarkable how closely the algorithm mimics the entropy proof.

The analysis is also compact. Note that $|R^{\text{\sf heavy}}|\leq|R|/\theta$ and thus

[TABLE]

In the other case, $|R^{\text{\sf light}}\Join T|\leq|T|\cdot\theta=\sqrt{|R|\cdot|S|\cdot|T|}.$ This completes the analysis.

Follow-up questions

In a more realistic setting, we know more about the input than just the cardinalities. In a database there may (and will) be FDs. In a graph we may know the maximum degree of a vertex. How do the bounds and algorithms change when we take such information into account? Which of the above two bounds and algorithms generalize better in the vastly more general setting? We explore these questions in the remainder of this paper.

3. Preliminaries

Throughout the paper, we use the following convention. The non-negative reals, rationals, and integers are denoted by $\mathbb{R}_{+},\mathbb{Q}_{+}$, and $\mathbb{N}$ respectively. For a positive integer $n$, $[n]$ denotes the set $\{1,\dots,n\}$.

Functions $\log$ without a base specified are base-$2$, i.e. $\log=\log_{2}$. Uppercase $A_{i}$ denotes a variable/attribute, and lowercase $a_{i}$ denotes a value in the discrete domain $\text{\sf Dom}(A_{i})$ of the variable. For any subset $S\subseteq[n]$, define $\mathbf{A}_{S}=(A_{i})_{i\in S}$, $\mathbf{a}_{S}=(a_{i})_{i\in S}\in\prod_{i\in S}\text{\sf Dom}(A_{i})$. In particular, $\mathbf{A}_{S}$ is a tuple of variables and $\mathbf{a}_{S}$ is a tuple of specific values with support $S$. We also use $\bm{X}_{S}$ to denote variables and $\bm{x}_{S}$, $\bm{t}_{S}$ to denote value tuples in the same way.

3.1. Queries and degree constraints

A multi-hypergraph is a hypergraph where edges may occur more than once. We associate a full conjunctive query $Q$ to a multi-hypergraph $\mathcal{H}:=([n],\mathcal{E})$, $\mathcal{E}\subseteq 2^{[n]}$; the query is written as

[TABLE]

with variables $A_{i}$, $i\in[n]$, and atoms $R_{F}$, $F\in\mathcal{E}$.

Definition 1** (Degree constraint).**

A degree constraint is a triple $(X,Y,N_{Y|X})$, where $X\subsetneq Y\subseteq[n]$ and $N_{Y|X}\in\mathbb{N}$. The relation $R_{F}$ is said to guard the degree constraint $(X,Y,N_{Y|X})$ if $Y\subseteq F$ and

[TABLE]

Note that a given relation may guard multiple degree constraints. Let DC denote a set of degree constraints. The input database $\bm{D}$ is said to satisfy DC if every constraint in DC has a guard, in which case we write $\bm{D}\models\text{\sf DC}$.

A cardinality constraint is an assertion of the form $|R_{F}|\leq N_{F}$, for some $F\in\mathcal{E}$; it is exactly the degree constraint $(\emptyset,F,N_{F|\emptyset})$ guarded by $R_{F}$. A functional dependency $\bm{A}_{X}\rightarrow\bm{A}_{Y}$ is a degree constraint with $N_{X\cup Y|X}=1$. In particular, degree constraints strictly generalize both cardinality constraints and FDs.

Our problem setting is general, where we are given a query of the form (25) and a set DC of degree constraints satisfied by the input database $\bm{D}$. The first task is to find a good upper bound, or determine exactly the quantity $\sup_{\bm{D}\models\text{\sf DC}}|Q(\bm{D})|$, the worst-case output size of the query given that the input satisfies the degree constraints. The second task is to design an algorithm running in time as close to the bound as possible.

Given a multi-hypergraph $\mathcal{H}=([n],\mathcal{E})$, define its corresponding “fractional edge cover polytope”:

[TABLE]

Every point $\bm{\delta}\in\text{\sf FECP}(\mathcal{H})$ is called a fractional edge cover of $\mathcal{H}$. The quantity

[TABLE]

is called the fractional edge cover number of $\mathcal{H}$.

3.2. Information theory

The books [21, 67] are good references on information theory. We extract only simple facts needed for this paper. Consider a joint probability distribution $\mathcal{D}$ on $n$ discrete variables $\bm{A}=(A_{i})_{i\in[n]}$ and a probability mass function $\mathbb{P}$. The entropy function associated with $\mathcal{D}$ is a function $H:2^{\bm{A}}\to\mathbb{R}_{+}$, where

[TABLE]

is the entropy of the marginal distribution on $\bm{A}_{F}$. To simplify notations, we will also write $H[F]$ for $H[\bm{A}_{F}]$, turning $H$ into a set function $H:2^{[n]}\to\mathbb{R}_{+}$. For any $F\subseteq[n]$, define the “support” of the marginal distribution on $\bm{A}_{F}$ to be

[TABLE]

Given $X\subseteq Y\subseteq[n]$, define the conditional entropy to be

[TABLE]

This is also known as the chain rule of entropy. The following facts are basic and fundamental in information theory:

[TABLE]

Inequality (31) follows from Jensen’s inequality and the concavity of the entropy function. Equality holds if and only if the marginal distribution on $X$ is uniform. Entropy measures the “amount of uncertainty” we have: the more uniform the distribution, the less certain we are about where a random point is in the space. Inequality (32) is the monotonicity property: adding more variables increases uncertainty. Inequality (33) is the submodularity property: conditioning on more variables reduces uncertainty.111$H[X\ |\ X\cap Y]\geq H[X\ |\ (X\cap Y)\cup(Y\setminus X)]=H[X\ |\ Y]=H[X\cup Y\ |\ Y]$.

A function $f:2^{\mathcal{V}}\to\mathbb{R}_{+}$ is called a (non-negative) set function on $\mathcal{V}$. A set function $f$ on $\mathcal{V}$ is modular if $f(S)=\sum_{v\in S}f(\{v\})$ for all $S\subseteq\mathcal{V}$, is monotone if $f(X)\leq f(Y)$ whenever $X\subseteq Y$, is subadditive if $f(X\cup Y)\leq f(X)+f(Y)$ for all $X,Y\subseteq\mathcal{V}$, and is submodular if $f(X\cup Y)+f(X\cap Y)\leq f(X)+f(Y)$ for all $X,Y\subseteq\mathcal{V}$. Let $n$ be a positive integer. A function $h:2^{[n]}\to\mathbb{R}_{+}$ is said to be entropic if there is a joint distribution on $\mathbf{A}_{[n]}$ with entropy function $H$ such that $h(S)=H[S]$ for all $S\subseteq[n]$. We will write $h(S)$ and $h(\mathbf{A}_{S})$ interchangeably, depending on context.

Unless specified otherwise, we will only consider non-negative and monotone set functions $f$ for which $f(\emptyset)=0$; this assumption will be implicit in the entire paper.

Definition 2**.**

Let $\text{\sf M}_{n}$, $\text{\sf SA}_{n}$, and $\Gamma_{n}$ denote the set of all (non-negative and monotone) modular, subadditive, and submodular set functions on $\mathcal{V}$, respectively. Let $\Gamma^{*}_{n}$ denote the set of all entropic functions on $n$ variables, and $\overline{\Gamma}^{*}_{n}$ denote its topological closure. The set $\Gamma_{n}$ is called the set of polymatroidal functions, or simply polymatroids.

The notations $\Gamma_{n},\Gamma^{*}_{n},\overline{\Gamma}^{*}_{n}$ are standard in information theory. It is known [67] that $\Gamma^{*}_{n}$ is a cone which is not topologically closed. And hence, when optimizing over this cone we take its topological closure $\overline{\Gamma}^{*}_{n}$, which is convex. The sets $\text{\sf M}_{n}$ and $\Gamma_{n}$ are clearly polyhedral cones.

As mentioned above, entropic functions satisfy non-negativity, monotonicity, and submodularity. Linear inequalities regarding entropic functions derived from these three properties are called Shannon-type inequalities. For a very long time, it was widely believed that Shannon-type inequalities form a complete set of linear inequalities satisfied by entropic functions, namely $\overline{\Gamma}^{*}_{n}=\Gamma_{n}$. This indeed holds for $n\leq 3$, for example. However, in 1998, in a breakthrough paper in information theory, Zhang and Yeung [68] presented a new inequality which cannot be implied by Shannon-type inequalities. Their result proved that, $\overline{\Gamma}^{*}_{n}\subsetneq\Gamma_{n}$ for any $n\geq 4$. Lastly the following chain of inclusion is known [67]

[TABLE]

When $n\geq 4$, all of the containments are strict.

4. Output size bounds

This section addresses the following question: given a query $Q$ and a set of degree constraints DC, determine $\sup_{\bm{D}\models\text{\sf DC}}|Q(\bm{D})|$ or at least a good upper bound of it.

4.1. Cardinality constraints only

Friedgut’s inequality is essentially equivalent to Hölder’s inequality. Following Beame et al. [14], who used the inequality to analyze parallel query processing algorithms, we present here a version that is more database-friendly. We also present a proof of Friedgut’s inequality using Hölder’s inequality, applying the same induction strategy used in the proof of Bollobás-Thomason’s inequality [16] and the “query decomposition lemma” in [52].

Theorem 4.1** (Friedgut [26]).**

Let $Q$ denote a full conjunctive query with (multi-) hypergraph $\mathcal{H}=([n],\mathcal{E})$ and input relations $R_{F}$, $F\in\mathcal{E}$. Let $\bm{\delta}=(\delta_{F})_{F\in\mathcal{E}}$ denote a fractional edge cover of $\mathcal{H}$. For each $F\in\mathcal{E}$, let $w_{F}:\prod_{i\in F}\text{\sf Dom}(A_{i})\to\mathbb{R}_{+}$ denote an arbitrary non-negative weight function. Then, the following holds

[TABLE]

Proof.

We induct on $n$. When $n=1$, the inequality is exactly generalized Hölder inequality [35]. Suppose $n>1$, and – for induction purposes – define a new query $Q^{\prime}$ whose hypergraph is $\mathcal{H}^{\prime}=([n-1],\mathcal{E}^{\prime})$, new fractional edge cover $\bm{\delta}^{\prime}=(\delta^{\prime}_{F})_{F\in\mathcal{E}^{\prime}}$ for $\mathcal{H}^{\prime}$, and new weight functions $w^{\prime}_{F}$ for each $F\in\mathcal{E}^{\prime}$ as follows:

[TABLE]

Then, by noting that the tuple $\bm{a}=(a_{1},\dots,a_{n})\in\prod_{i=1}^{n}\text{\sf Dom}(A_{i})$ belongs to $Q$ if and only if $\prod_{F\in\mathcal{E}}\bm{1}_{R_{F}(\bm{a}_{F})}=1$, we have222We use the convention that $0^{0}=0$.

[TABLE]

The first inequality follows from Hölder’s inequality and the fact that $\bm{\delta}$ is a fractional edge cover; in particular, $\sum_{F\in\mathcal{E}_{n}}\delta_{F}\geq 1$. The second inequality is the induction hypothesis. ∎

By setting all weight functions to be identically $1$, we obtain

Corollary 4.2** (AGM-bound [12]).**

Given the same setting as that of Theorem 35, we have

[TABLE]

In particular, let $N=\max_{F\in\mathcal{E}}|R_{F}|$ then $|Q|\leq N^{\rho^{*}(\mathcal{H})}$.

4.2. General degree constraints

To obtain a bound in the general case, we employ the entropy argument, which by now is widely used in extremal combinatorics [40, 20, 58]. In fact, Friedgut [26] proved Theorem 35 using an entropy argument too. The particular argument below can be found in the first paper mentioning Shearer’s inequality [20], and a line of follow-up work [27, 58, 30, 7, 8].

Let $\bm{D}\models\text{\sf DC}$ be any database instance satisfying the input degree constraints. Construct a distribution $\mathcal{D}$ on $\prod_{i\in[n]}\text{\sf Dom}(A_{i})$ by picking uniformly a tuple $\bm{a}_{[n]}$ from the output $Q(\bm{D})$. Let $H$ denote the corresponding entropy function. Then, due to uniformity we have $\log_{2}|Q(\bm{D})|=H([n])$. Now, consider any degree constraint $(X,Y,N_{Y|X})\in\text{\sf DC}$ guarded by an input relation $R_{F}$. From (31) it follows that $H[Y\ |\ X]\leq\log N_{Y|X}$. Define the collection HDC of set functions satisfying the degree constraints DC:

[TABLE]

Then, the entropy argument immediately gives the following result, first explicitly formulated in [8]:

Theorem 4.3** (From [7, 8]).**

Let $Q$ be a conjunctive query and DC be a given set of degree constraints, then for any database $\bm{D}$ satisfying DC, we have

[TABLE]

Furthermore, the entropic bound is asymptotically tight and the polymatroid bound is not.

The polymatroid relaxation follows from the chain of inclusion (34); the relaxation is necessary because we do not know how to compute the entropic bound. Also from the chain of inclusion, we remark that while the set $\text{\sf SA}_{n}$ is not relevant to our story, we can further move from $\Gamma_{n}$ to $\text{\sf SA}_{n}$ and end up with the integral edge cover number [8].

Table 1, extracted from [8], summarizes our current state of knowledge on the tightness and looseness of these two bounds.

The entropic bound is asymptotically tight, i.e. there are arbitrarily large databases $\bm{D}\models\text{\sf DC}$ for which $\log|Q(\bm{D})|$ approaches the entropic bound. The polymatroid bound is not tight, i.e. there exist a query and degree constraints for which its distance from the entropic bound is arbitrarily large.

The tightness of the entropic bound is proved using a very interesting connection between information theory and group theory first observed in Chan and Yeung [17]. Basically, given any entropic function $h\in\overline{\Gamma}^{*}_{n}\in\text{\sf HDC}$, one can construct a database instance $\bm{D}$ which satisfies all degree constrains DC and $\log|Q(\bm{D})|\geq h([n])$. The database instance is constructed from a system of (algebraic) groups derived from the entropic function.

The looseness of the polymatroid bound follows from Zhang and Yeung result [68] mentioned in Section 3.2. In [8], we exploited Zhang-Yeung non-Shannon-type inequality and constructed a query for which the optimal polymatroid solution $h^{*}$ to problem (44) strictly belongs to $\Gamma_{n}-\overline{\Gamma}^{*}_{n}$. This particular $h^{*}$ proves the gap between the two bounds, which we can then magnify to an arbitrary degree by scaling up the degree constraints.

In addition to being not tight for general degree constraints, the polymatroid bound has another disadvantage: the linear program (44) has an exponential size in query complexity. While this is “acceptable” in theory, it is simply not acceptable in practice. Typical OLAP queries we have seen at LogicBlox or RelationalAI have on average $20$ variables; and $2^{20}$ certainly cannot be considered a “constant” factor, let alone analytic and machine learning workloads which have hundreds if not thousands of variables. We next present a sufficient condition allowing for the polymatroid bound to not only be tight, but also computable in polynomial time in query complexity.

Definition 3** (Acyclic degree constraints).**

Associate a directed graph $G_{\text{\sf DC}}=([n],E)$ to the degree constraints DC by adding to $E$ all directed edges $(x,y)\in X\times(Y-X)$ for every $(X,Y,N_{Y|X})\in\text{\sf DC}$. If $G_{\text{\sf DC}}$ is acyclic, then DC is said to be acyclic degree constraints, in which case any topological ordering (or linear ordering) of $[n]$ is said to be compatible with DC. The graph $G_{\text{\sf DC}}$ is called the constraint dependency graph associated with DC.

Note that if there are only cardinality constraints, then $G_{\text{\sf DC}}$ is empty and thus DC is acyclic. In particular, acyclicity of the constraints does not imply acyclicity of the query, and the cardinality constraints do not affect the acyclicity of the degree constraints. In a typical OLAP query, if in addition to cardinality constraints we have FD constraints including non-circular key-foreign key lookups, then DC is acyclic. Also, verifying if DC is acyclic can be done efficiently in $\text{\sf poly}(n,|\text{\sf DC}|)$-time.

Proposition 4.4**.**

Let $Q$ be a query with acyclic degree constraints DC; then the following hold:

[TABLE]

In particular, the polymatroid bound is tight and computable in $\text{\sf poly}(n,|\text{\sf DC}|)$-time.

Proof.

Let $h^{*}$ denote an optimal solution to the linear program $\max\{h([n])\ |\ h\in\Gamma_{n}\cap\text{\sf HDC}\}$. Because $\text{\sf M}_{n}\subseteq\overline{\Gamma}^{*}_{n}\subseteq\Gamma_{n}$, to prove (45) it is sufficient to exhibit a modular function $f\in\text{\sf M}_{n}\cap\text{\sf HDC}$ for which $f([n])=h^{*}([n])$.

Without loss of generality, assume the identity permutation is compatible with DC, i.e. for every $(X,Y,N_{Y|X})\in\text{\sf DC}$, we have $x<y$ for all $x\in X$ and $y\in Y-X$. Define a set function $f:2^{[n]}\to\mathbb{R}_{+}$ as follows:

[TABLE]

The function $f$ is clearly modular because $h^{*}$ is monotone. The fact that $f([n])=h^{*}([n])$ follows from the telescoping sum. It remains to show that $f\in\text{\sf HDC}$. We will show by induction on $|Y-X|$ that $f(Y|X)\leq h^{*}(Y|X)$ for any degree constraint $(X,Y,N_{Y|X})\in\text{\sf DC}$. The base case when $Y=X$ holds trivially. Let $(X,Y,N_{Y|X})$ be any degree constraint in DC where $|Y-X|>0$. Let $j$ be the largest integer in $Y-X$. We have

[TABLE]

Inequality (48) follows from the induction hypothesis, (50) from submodularity of $h^{*}$, and (53) from the fact that $h^{*}\in\text{\sf HDC}$.

Lastly, the polymatroid bound is computable in $\text{\sf poly}(n,|\text{\sf DC}|)$-time because the linear program $\max\{h([n])\ |\ h\in\text{\sf M}_{n}\cap\text{\sf HDC}\}$ has polynomial size in $n$ and $|\text{\sf DC}|$. To see this, define a variable $v_{i}=h(i)$ for every $i\in[n]$, then the modular LP is

[TABLE]

∎

Associate a dual variable $\delta_{Y|X}$ for every $(X,Y,N_{Y|X})\in\text{\sf DC}$. In what follows for brevity we sometimes write $(X,Y)\in\text{\sf DC}$ instead of the lengthier $(X,Y,N_{Y|X})\in\text{\sf DC}$. The dual LP of (54) is the following

[TABLE]

This is exactly AGM-bound if DC contains only cardinality constraints, and hence our proposition is a generalization of AGM-bound and its tightness.

5. Algorithms

An algorithm evaluating $Q(\bm{D})$ under degree constraints DC is a WCOJ algorithm if it runs in time

[TABLE]

In general, we do not know how to even compute the entropic bound, in part because there is no finite set of linear inequalities characterizing the entropic cone [47]. Hence, thus far we have settled for designing algorithms meeting the polymatroid bound, running in time $\tilde{O}\left(|\bm{D}|+2^{\max_{h\in\Gamma_{n}\cap\text{\sf HDC}}h([n])}\right)$. This question is difficult enough, and in some cases (e.g. Proposition 4.4), the two bounds collapse. We we present two such algorithms in this section, the first algorithm is inspired by the proof of Friedgut’s inequality, and the second is guided by a proof of a particular type of information theoretic inequalities called Shannon-flow inequalities.

5.1. An algorithm for acyclic degree constraints

For simplicity, we first assume that there is a variable order compatible with DC, and w.l.o.g. we assume the order is $(1,\dots,n)$. Algorithm 3 is a backtracking search algorithm inspired by the inductive proof of Theorem 35. Our analysis is summarized in the following theorem. Note that the runtime expression does not hide any factor behind $\tilde{O}$.

Theorem 5.1**.**

Let $Q$ be a query with acyclic degree constraints DC. Suppose $(1,\dots,n)$ is compatible with DC. Let $\bm{D}\models\text{\sf DC}$ be a database instance. Then, Algorithm 3 runs in worst-case optimal time:

[TABLE]

Proof.

Let $\bm{\delta}$ denote an optimal solution to the LP (57). Then, by Proposition 4.4 and strong duality of linear programming, it is sufficient to show that Algorithm 3 runs in time

[TABLE]

The $O(n\cdot|\text{\sf DC}|\cdot|\bm{D}|\log|\bm{D}|)$ term in (61) comes from a preprocessing step where we precompute and index the projections $\pi_{Y}R$ in the algorithm. We show the remaining runtime by induction on $n$. When $n=1$, the only thing the algorithm does is compute the intersection

[TABLE]

The intersection can be computed in time proportional to the smallest set, up to a $\log|\bm{D}|$ factor. And thus, up to a $\log|\bm{D}|$ factor, the runtime is

[TABLE]

The first inequality follows because the minimum of a set of non-negative reals is bounded above by their geometric mean, and from the fact that $\bm{\delta}$ satisfies (58).

When $n>1$, the algorithm implicitly or explicitly computes $I$ in (62), which can be done within the budget time as shown in the base case. Then, for each binding $a_{1}\in I$, Algorithm 3 performs backtracking search on the remaining variables $(A_{2},\dots,A_{n})$. By induction, up to a $\log|\bm{D}|$ factor, this can be done in time

[TABLE]

The first inequality follows from Theorem 35 and the fact that $\bm{\delta}$ satisfies (58). The second inequality follows from the fact that $R$ guards $(\emptyset,Y,N_{Y|\emptyset})$. ∎

Algorithm 3 has several key advantages: (1) It is worst-case optimal when DC is acyclic; (2) It is very simple and does not require any extra memory (after pre-processing): we can iterate through the output tuples without computing intermediate results; (3) It is friendly to both hash or sort-merge strategies, as the only required assumption is that we can compute set intersection in time proportional to the smallest set.

What if DC is not acyclic? There are two solutions. The first solution is to find an acyclic collection $\text{\sf DC}^{\prime}$ of degree constraints giving the smallest worst-case output size bound and run Algorithm 3 on $\text{\sf DC}^{\prime}$. (The final output is semijoin-reduced against the guards of the original degree constraints DC.) The second solution is to run the more general and more sophisticated algorithm called PANDA we will present in Section 5.2.

We discuss in this section more details regarding the first strategy of constructing an acyclic $\text{\sf DC}^{\prime}$. How do we know that such an acyclic $\text{\sf DC}^{\prime}$ exists and is satisfied by the input database? And, how do we know that the corresponding worst-case output size bound is finite? For example, the first thought that comes to mind may be to try to remove one or more constraints from DC to make it acyclic. However, this naïve strategy may result in an infinite output size bound. Consider the following query

[TABLE]

The degree constraints given to us are $N_{A|\emptyset}$ guarded by $R$, $N_{B|A}$ guarded by $S$, $N_{C|B}$ guarded by $T$, and $N_{AD|C}$ guarded by $W$. In particular we do not know the sizes of $S$, $T$, and $W$. (They can be user-defined functions/relations, which need not be materialized.) It is easy to see that removing any of the input constraints will yield an infinite output size bound. (An infinite output size bound corresponds precisely to situations where some output variable is unbound and cannot be inferred from bound variables by chasing FDs.)

Proposition 5.2**.**

Let $Q$ be a full conjunctive query with degree constraints DC such that $\sup_{\bm{D}\models\text{\sf DC}}Q(\bm{D})$ is finite. Then, there exists an acyclic set of degree constraints $\text{\sf DC}^{\prime}$ for which

• (i)

For any database instance $\bm{D}$, if $\bm{D}\models\text{\sf DC}$ then $\bm{D}\models\text{\sf DC}^{\prime}$.

• (ii)

$\sup_{\bm{D}\models\text{\sf DC}^{\prime}}Q(\bm{D})$* is finite.*

Proof.

Let $V$ be the set of all variables occuring in $Q$. We define the set of bound variables recursively as follows. For any constraint $(X,Y,N_{Y|X})$, if all variables in $X$ are bound, then all variables in $Y$ are also bound. In particular, the cardinality constraint $(\emptyset,Y,N)\in\text{\sf DC}$ implies that all variables in $Y$ are bound. We make the following two claims.

Claim 1. $\sup_{\bm{D}\models\text{\sf DC}}|Q(\bm{D})|$ is finite if and only if all variables in $V$ are bound.

Claim 2. If DC is cyclic, then for any cycle $C$ in $G_{\text{\sf DC}}$ there is a variable $y\in C$ on the cycle for which the following holds. There is a constraint $(X,Y,N_{Y|X})$ with $y\in Y-X$ such that, if we replace $(X,Y,N_{Y|X})$ by $(X,Y-\{y\},N_{Y-\{y\}|X}:=N_{Y_{X}})$, then all variables in $V$ remain bound under the new set of constraints.

The two claims prove the proposition, because if DC is still cyclic, we can apply the above constraint replacement to obtain a new degree constraint set $\text{\sf DC}^{\prime}$ with $\sup_{\bm{D}\models\text{\sf DC}^{\prime}}Q(\bm{D})$ remains finite. Any relation $R$ guarding the degree constraint $(X,Y,N_{Y|X})$ is still a guard for the new degree constraint $(X,Y-\{y\},N_{Y-\{y\}|X})$. Hence, $\bm{D}\models\text{\sf DC}$ implies $\bm{D}\models\text{\sf DC}^{\prime}$. We can repeat this process until $\text{\sf DC}^{\prime}$ reaches acyclicity. We next prove the two claims.

Proof of Claim 1. For the forward directly, suppose $\sup_{\bm{D}\models\text{\sf DC}}|Q(\bm{D})|$ is finite. Let $B$ denote the set of bound variables, and $U$ denote the set of unbound variables. Assume to the contrary that $U\neq\emptyset$. Then, for any degree constraint $(X,Y,N_{Y|X})\in\text{\sf DC}$ we have $X\cap U=\emptyset$ implies $Y\cap U=\emptyset$ also. Let $h\in\overline{\Gamma}^{*}_{n}\cap\text{\sf HDC}$ denote an arbitrary entropic function. Let $c>0$ be an arbitrary constant. Define a new set function $f:2^{[n]}\to\mathbb{R}_{+}$ by

[TABLE]

Then, we can verify that $f\in\overline{\Gamma}^{*}_{n}\cap\text{\sf HDC}$ as well. First, $f\in\overline{\Gamma}^{*}_{n}$ because it is a non-negative linear combination of two entropic functions333Recall that $\overline{\Gamma}^{*}_{n}$ is a convex cone.. Second, $f\in\text{\sf HDC}$ because $f(Y|X)=h(Y|X)$ for every constraints $(X,Y,N_{Y|X})\in\text{\sf DC}$:

[TABLE]

Note that $f(V)=h(V)+c$; and, since $c$ was arbitrary, $\sup_{\bm{D}\models\text{\sf DC}}|Q(\bm{D})|=\max_{h\in\overline{\Gamma}^{*}_{n}\cap\text{\sf HDC}}h(V)$ is unbounded.

Conversely, assume every variable is bound. Since $h(V)\leq\sum_{v}h(v)$ for every $h\in\overline{\Gamma}^{*}_{n}$, it is sufficient to show that $h(v)$ is finite for every $h\in\overline{\Gamma}^{*}_{n}\cap\text{\sf HDC}$. There must exist some cardinality constraint $(\emptyset,Y,N_{Y|\emptyset})$ in order for all variables to be bound. Then, $h(y)\leq h(Y)\leq\log N_{Y|\emptyset}$ for all $y\in Y$. Inductively, consider a degree constraint $(X,Y,N_{Y|X})$ for which $h(x)$ is finite for all $x\in X$, then for any $y\in Y$ we have $h(y)\leq h(Y)\leq h(X)+\log N_{Y|X})$.

Proof of Claim 2. A sequence $(X_{1}=\emptyset,Y_{1},N_{1}),(X_{2},Y_{2},N_{2}),\cdots,(X_{k},Y_{k},N_{k})$ of constraints is said to reach a vertex $v\in V$ if the following holds: for any $i\in[k]$, $X_{i}\subseteq\bigcup_{j=1}^{i-1}Y_{j}$, and $v\in Y_{k}$. From Claim 1, there is a sequence of degree constraints reaching every variable in $V$. Consider the shortest sequence of $k$ degree constraints reaching some vertex $y\in C$. Then, there is no vertex of $C$ in the set $Y_{1}\cup\cdots\cup Y_{k-1}$. And thus, because $X_{i}\subseteq\bigcup_{j=1}^{i-1}Y_{j}$, there is no vertex of $C$ in the set $X_{1}\cup\cdots\cup X_{k}$ either. Now, let $(X,Y,N_{Y|X})$ denote a degree constraint for which $(x,y)$ is on the cycle $C$, and $(x,y)\in X\times(Y-X)$. Then $(X,Y,N_{Y|X})$ is not part of the degree constraint sequence. Consequently, when we turn $(X,Y,N_{Y|X})$ into $(X,Y-\{y\},N_{Y-\{y\}|X}=N_{Y|X})$ all vertices of $V$ are still bound because the constraint change can only affect the boundedness of $y$, and $y$ can still be reached via the degree constraint sequence. ∎

The proof of the above proposition also suggests a simple brute-force algorithm for finding the best acyclic constraint set $\text{\sf DC}^{\prime}$. The algorithm runs in exponential time in query complexity. It also raises a natural question: when is the worst-case output size on the best acyclic $\text{\sf DC}^{\prime}$ the same as that of DC? We do not know the general answer to this question; however, there is one case when the answer is easy. Recall that a simple FD is an FD of the form $A_{i}\to A_{j}$ for two single variables $A_{i}$ and $A_{j}$. The following implies a result from Gottlob et al. [30].

Corollary 5.3**.**

If DC contains only cardinality constraints and simple FDs, then in polynomial time in query complexity, we can determine a subset $\text{\sf DC}^{\prime}\subseteq\text{\sf DC}$ so that $\text{\sf DC}^{\prime}$ is acyclic and, more importantly, $\sup_{\bm{D}\models\text{\sf DC}}Q(\bm{D})=\sup_{\bm{D}\models\text{\sf DC}^{\prime}}Q(\bm{D})$. In particular, Algorithm 3 is a WCOJ algorithm for $Q$.

Proof.

The constraints in the set HDC are either cardinality constraints of the form $h(Y)\leq N_{Y|\emptyset}$ or FD-constraints of the form $h(\{i,j\})=h(\{i\})$. Since equalities are transitive, if there was a cycle in $G_{\text{\sf DC}}$ we can remove one edge from the cycle without changing the feasible region defining HDC. Keep breaking directed cycles this way, we end up with the acyclic $\text{\sf DC}^{\prime}$ as desired. ∎

5.2. PANDA

Finally we informally present the main ideas behind the PANDA algorithm [8], which can achieve the polymatroid-bound runtime, modulo huge polylog and query-dependent factors. The algorithm actually solves a particular form of disjunctive datalog rules, of which conjunctive queries are a special case. Most importantly, it leads to algorithms meeting highly refined notions of “width parameters” over tree decompositions of the query. In summary, PANDA has far reaching theoretical implications in terms of the class of problems it helps solve and the insights it provides in designing and reasoning about join algorithms; at the same time, the hidden query-dependent and polylog in the data factors leave much room for desire. Materials in this section are exclusively from [8], with some simplification.

5.2.1. Understanding the polymatroid bound

The starting point of designing any algorithm meeting the polymatroid bound is to understand in detail what the bound entails. To this end, we establish some notations. For any $I,J\subseteq[n]$, we write $I\perp J$ to mean $I\not\subseteq J$ and $J\not\subseteq I$. In order to avoid rewriting $\log_{2}N_{Y|X}$ and $(X,Y,N_{Y|X})\in\text{\sf DC}$ over and over, we define

[TABLE]

Note that $|\mathcal{P}|=\sum_{i=0}^{n}\binom{n}{i}(2^{i}-1)=3^{n}-2^{n}$, and the vector $\bm{n}:=(n_{Y|X})$ lies in $\mathbb{R}_{+}^{\mathcal{P}}$. The polymatroid bound $\max_{h\in\Gamma_{n}\cap\text{\sf HDC}}h([n])$ is the optimal objective value of the following optimization problem:

[TABLE]

In addition to the degree constraints $h(Y)-h(X)\leq n_{Y|X}$, signifying $h\in\text{\sf HDC}$, the remaining constraints spell out the definition of a polymatroid: submodularity, monotonicity, and non-negativity, where monotonicity and non-negativity collapsed into one constraint $h(Y|X)\geq 0$. It is thus more notationally convenient to work on the space $\bm{h}=(h(Y|X))\in\mathbb{R}_{+}^{\mathcal{P}}$ instead of the original space of functions $h:2^{[n]}\to\mathbb{R}_{+}$.

Definition 4** (Conditional polymatroids).**

We refer to the vectors $\bm{h}=(h(Y|X))\in\mathbb{R}_{+}^{\mathcal{P}}$ that are feasible to the last two constraints of (68) and to (71) below as the conditional polymatroids. Also, we will write $h(Y)$ instead of $h(Y|\emptyset)$ for brevity.

The equivalent linear program in the space of conditional polymatroids is

[TABLE]

In the above, when performing the space transformation from polymatroids to conditional polymatroids, we impose the obvious extra “conservation” constraints $h(Y|\emptyset)=h(Y|X)+h(X|\emptyset)$. Note that this implies (and thus is equivalent to) the more general conservation constraints $h(Y|Z)=h(Y|X)+h(X|Z)$.

The (primal) linear program is very clean, but it does not give us a clear sense of the bound relative to the input statistics $n_{Y|X}$. To obtain this relationship, we look at the dual linear program. Associate dual variables $\bm{\delta}=(\delta_{Y|X})$ to the degree constraints, $\bm{\xi}=(\xi_{I,J})$ to the submodularity constraints, $\bm{\alpha}=(\alpha_{X,Y})$ to the extra conservation constraints, then the dual LP can be written as follows.

[TABLE]

where for any $(X,Y)\in\mathcal{P}$, ${\sf inflow}(X,Y)$ is defined by

[TABLE]

Note that there is no non-negativity requirement on $\bm{\alpha}$. The dual LP (72) is important in two ways. First, let $\bm{h}^{*}$ and $(\bm{\delta}^{*},\bm{\xi}^{*},\bm{\alpha}^{*})$ denote a pair of primal- and dual-optimal solutions, then from strong duality of linear programming [60] we have

[TABLE]

We are now able to “see” the input statistics contributions to the objective function. (The reader is welcome to compare this expression with that of (57).) Second, the dual formulation allows us to formulate a (vast) generalization of Shearer’s inequality, to deal with general degree constraints. We refer to this generalization as “Shannon-flow inequalities”, which is discussed next.

5.2.2. Shannon-flow inequalities

Definition 5** (Shannon-flow inequality).**

Let $\bm{\delta}\in\mathbb{R}_{+}^{\mathcal{P}}$ denote a non-negative coefficient vector. If the inequality

[TABLE]

holds for all conditional polymatroids $\bm{h}$, then it is called a Shannon-flow inequality.

It is important not to lose sight of the fact that conditional polymatroids are simply a syntactical shortcut to the underlying polymatroids, designed to simplify notations; they are not a new function class. We can rewrite (74) in a wordier form involving only polymatroids: $h([n])\leq\sum_{(X,Y)\in\mathcal{P}}\delta_{Y|X}(h(Y)-h(X)).$

Shannon-flow inequalities occur naturally in characterizing feasible solutions to the dual LP (72) using Farkas’ lemma [60]:

Proposition 5.4** (From [8]).**

Let $\bm{\delta}\in\mathbb{R}_{+}^{\mathcal{P}}$ denote a non-negative coefficient vector. The inequality $h([n])\leq\left\langle\bm{\delta},\bm{h}\right\rangle$ is a Shannon-flow inequality if and only if there exist $\bm{\xi},\bm{\alpha}$ such that $(\bm{\delta},\bm{\xi},\bm{\alpha})$ is a feasible solution to the dual LP (72).

It is not hard to show that Shearer’s inequality is a consequence.

Corollary 5.5** (Shearer’s inequality).**

Let $\mathcal{H}=([n],\mathcal{E})$ be a hypergraph, and $\bm{\delta}=(\delta_{F})_{F\in\mathcal{E}}$ be a vector of non-negative coefficients. Then, the inequality $h([n])\leq\sum_{F\in\mathcal{E}}\delta_{F}h(F)$ holds for all polymatroids iff $\bm{\delta}$ is a fractional edge cover of $\mathcal{H}$.

5.2.3. Proof sequences and PANDA

We next explain how studying Shannon-flow inequalities leads to an algorithm called the Proof Assisted eNtropic Degree Aware (PANDA) algorithm whose runtime meets the polymatroid bound. At a high level, the algorithm consists of the following steps.

1. (1)

Obtain an optimal solution $(\bm{\delta}^{*},\bm{\xi}^{*},\bm{\alpha}^{*})$ to the dual LP (72). 2. (2)

Use the dual solution to derive a particular mathematical proof of the Shannon-flow inequality $h([n])\leq\left\langle\bm{\delta}^{*},\bm{h}\right\rangle$. This inequality is a Shannon-flow inequality thanks to Proposition 5.4. This mathematical proof has to be of a particular form, called the “proof sequence,” which we define below. 3. (3)

Finally, every step in the proof sequence is interpreted as a symbolic instruction to perform a relational operator (partition some relation, or join two relations). These symbolic instructions are sufficient to compute the final result.

The full version of PANDA is more complex than the three steps above, due to several technical hurdles we have to overcome. The reader is referred to [8] for the details. This section can only present a simplified high-level structure of the algorithm.

We next explain the proof sequence notion in some details. If we were to expand out a Shannon-flow inequality $h([n])\leq\left\langle\bm{\delta}^{*},\bm{h}\right\rangle$, it would be of the form $h([n])\leq\sum_{(X,Y)\in\mathcal{P}}\delta^{*}_{Y|X}h(Y|X),$ where $\delta^{*}_{Y|X}$ are all non-negative. Hence, we interpret the RHS of the above inequality as a set of “weighted” conditional polymatroid terms: the term $h(Y|X)$ is weighted by $\delta^{*}_{Y|X}$. To prove the inequality, one may attempt to apply either the submodularity inequality (70) or the equality (71) to some of the terms to start converting the RHS to the LHS. These applications lead to three types of rules:

• •

Suppose $\delta\cdot h(I|I\cap J)$ occurs on the RHS, then we may apply (70) with a weight of $w\in[0,\delta]$ to obtain a (new or not) weighted term $w\cdot h(I\cup J|J)$ while retain $(\delta-w)\cdot h(I|I\cap J)$ of the old term. In this scenario, we say that we have applied the submodularity rule $h(I|I\cap J)\to h(I\cup J|J)$ with a weight of $w$.

• •

The equality (71) can be used in two ways: either we replace $h(Y|\emptyset)$ by $h(Y|X)+h(X|\emptyset)$, resulting in a decomposition rule $h(Y|\emptyset)\to h(Y|X)+h(X|\emptyset),$ or the other way around where we’d get a composition rule $h(Y|X)+h(X|\emptyset)\to h(Y).$ These rules can be applied with a weight, as before.

A (weighted) proof sequence of a Shannon-flow inequality is a series of weighted rules such that at no point in time any weight is negative, and that in the end $h([n])$ occurs with a weight of at least $1$. We were able to prove the following result:

Theorem 5.6** (From [8]).**

There exists a proof sequence for every Shannon-flow inequality $h([n])\leq\left\langle\bm{\delta},\bm{h}\right\rangle$

The dual feasible solution $(\bm{\delta},\bm{\xi},\bm{\alpha})$ gives us an intuition already on which rule to apply. For example, $\xi_{I,J}>0$ indicates that we should apply the submodularity rule, $\alpha_{X,Y}>0$ indicates a decomposition rule, and $\alpha_{X,Y}<0$ hints at a composition rule. The technical issue we have to solve is to make sure that these steps serialize to a legitimate proof sequence.

Finally, after obtaining the proof sequence for the inequality $h([n])\leq\left\langle\bm{\delta}^{*},\bm{h}\right\rangle$, PANDA interprets the proof sequence as follows. Note that $\delta^{*}_{Y|X}>0$ implies $n_{Y|X}<\infty$, which means there is a guard for the corresponding constraint. We associate the guarding relation with the term $h(Y|X)$. Now, we look at each rule in turn: a decomposition rule corresponds to partitioning the relation associated with the conditional polymatroid term being decomposed; a composition rule corresponds to joining the two associated relations; and a submodularity rule is used to move the association map. These concepts are best illustrated with an example, which is a minor modification of an example from [7].

Example 1**.**

Consider the following query

[TABLE]

with the following degree constraints:

• •

$(\emptyset,AB,N_{AB})$ guarded by $R$,

• •

$(\emptyset,BC,N_{BC})$ guarded by $S$,

• •

$(\emptyset,CD,N_{CD})$ guarded by $T$,

• •

$(AC,ACD,N_{ACD|AC})$ guarded by $W$,

• •

$(BD,ABD,N_{ABD|BD})$ guarded by $V$.

We claim that the following is a Shannon-flow inequality:

[TABLE]

and PANDA can evaluate the query in time

[TABLE]

Inequality (1) holds for every polymatroid $h\in\Gamma_{4}$, because

[TABLE]

The proof above applied the proof sequence shown in Table 2, which also contains the step-by-step description of how to translate the proof sequence into an algorithm. The total runtime is within (75):

[TABLE]

6. Open problems

There are many interesting and challenging open questions arising from this line of inquiries: questions regarding the bounds, the algorithms, the desire to make them practical and extend their reach to more difficult or realistic settings. In terms of bounds, the most obvious question is the following:

Open Problem 1**.**

Is the entropic bound computable?

We know that the entropic bound is tight, but we do not know if it is decidable whether the bound is below a given threshold. This question is closely related to the question of determining whether a linear inequality is satisfied by all entropic functions or not.

Next, assuming we have to settle for the polymatroid bound, then the challenge is to find efficient algorithms for computing the polymatroid bound, which is a linear program with an exponential number of variables. As we have seen, there are classes of queries and degree constraints for which we can compute the polymatroid bound in polynomial time in query copmlexity. Even if computing the polymatroid bound is difficult in general, say it is $\mathbf{NP}$-hard or harder, it would be nice to be able to characterize larger classes of queries and constraints allowing for tractability.

Open Problem 2**.**

What is the computational (query) complexity of computing the polymatroid bound? Design an efficient algorithm computing it.

Another important line of research is to enlarge the class of degree constraints for which the polymatroid bound is tight, making PANDA a WCOJ algorithm (up to a large polylog factor). In these special cases, perhaps there are simpler algorithms than PANDA, such as Algorithm 3. The following two questions are along this direction.

Open Problem 3**.**

Characterize the class of queries and degree constraints for which the best constraint modification as dictated by Proposition 5.2 has the same worst-case output size bound as the original constraint set.

Open Problem 4**.**

Characterize the class of degree constraints DC for which the polymatroid bound is tight.

PANDA is a neat algorithm, which is capaable of answering the more general problem of evaluating a disjunctive datalog rule. Hence, perhaps there are faster algorithms without the large polylog factor, designed specifically for answering conjunctive queries:

Open Problem 5**.**

Find an algorithm running within the polymatroid bound that does not impose the poly-log (data) factor as in PANDA.

Reasoning about entropic inequalities has allowed us to gain deeper insights on both the algorithm design and bounding the worst-case output size. Entropy is, by definition, an expectation. And thus it should serve as a bridge to reasoning about average output size. A result from Atserias et al. [12] which has not been exploited further by the database community is their concentration result, a good starting point for the following question.

Open Problem 6**.**

Develop a theory and algorithms for average-case output size bound.

Average case bounds and complexity is only one way to go beyond worst-case. Another line of research is on the notion of instance-optimality for computing joins. Instance optimality is a difficult notion to define formally, let alone having an optimal algorithm under such stringent requirement. There are only a few known work on instance-optimality in database theory [49, 53, 4, 24]. After information theory perhaps geometric ideas will play a bigger role in answering this question:

Open Problem 7**.**

Develop a theory and practical algorithms for instance-optimal query evaluation.

Last but not least, traditional database optimizers have been designed on the “one join at a time” paradigm, influenced by relational algebra operators. It is this author’s strong belief that the time is ripe for the theory and practice of multiway join optimizers, based on information theoretic analysis and sampling strategies, taking into account systems requirements such as streaming, transactional constraints, incremental view maintenance, etc.

Open Problem 8**.**

Develop a theory and practical algorithms for an optimizer for the multiway join operator.