Size Bounds and Query Plans for Relational Joins

Abstract

Relational joins are at the core of relational algebra, which in turn is the core of the standard database query language SQL. As their evaluation is expensive and very often dominated by the output size, it is an important task for database query optimisers to compute estimates on the size of joins and to find good execution plans for sequences of joins. We study these problems from a theoretical perspective, both in the worst-case model, and in an average-case model where the database is chosen according to a known probability distribution. In the former case, our first key observation is that the worst-case size of a query is characterised by the fractional edge cover number of its underlying hypergraph, a combinatorial parameter previously known to provide an upper bound. We complete the picture by proving a matching lower bound, and by showing that there exist queries for which the join-project plan suggested by the fractional edge cover approach may be substantially better than any join plan that does not use intermediate projections. On the other hand, we show that in the average-case model, every join-project plan can be turned into a plan containing no projections in such a way that the expected time to evaluate the plan increases only by a constant factor independent of the size of the database. Not surprisingly, the key combinatorial parameter in this context is the maximum density of the underlying hypergraph. We show how to make effective use of this parameter to eliminate the projections.

Size Bounds for Factorised Representations of Query Results

“…By Proposition 3.2, we can bound this number from above using the (key(A) ∪ A)-restriction of Q and D. This is a useful upper bound because any restriction of Q, as defined in Section 3, is an equi-join, and recent results [Atserias et al 2008] give tight asymptotic bounds on the size of results of equi-joins in terms of the input size. We next give an intuitive introduction to these asymptotic bounds.…”

“…Definition 7.8 [Atserias et al 2008]. For an equi-join query Q = σ ψ (R 1 × · · · × R n ), the fractional edge cover number ρ * (Q) is the cost of an optimal solution to the linear program with variables…”

“…Atserias et al [2008] generalise this idea and show that, given a fractional edge cover of the hypergraph of Q S where the edge R i has weight x R i , the result size |Q S (D S )| is bounded by the weighted product i |R i | x R i . The following lemma is an adaptation of this result.…”

Structural decompositions for problems with global constraints

“…This result has since been shown to be optimal -a classic CSP instance has polynomially many solutions in its size if and only if it has bounded fractional edge cover number [5].…”

Counting solutions to conjunctive queries

“…The "select" part of an SQL query allows users to specify a set of output variables, so that query answering amounts at enumerating all solutions rather than just deciding whether there is any. In [3], it has been shown that the enumeration problem where all variables are output variables (i.e., "SELECT *" queries where no variable is projected out) can be solved in polynomial time on a class C of queries if, and only if, the number of solutions is always polynomial, and that this is the case, if and only if, the queries in C have bounded fractional edge cover number. Similar tight worstcase bounds for conjunctive queries with arbitrary sets of output variables have been derived in [20].…”