Finish Estimate Size of Various Algebra Operations
Intro to Cost-Based Plan Selection
Quiz
Choosing Join Order
Introduction
Last week, we were talking about how query processing is implemented in DBMSs.
We said the basic steps are: the stages of query processing: SQL -> Parsed Query -> Logical Query Plan -> Physical Plan -> Execute.
We said a logical plan usually consists of a tree of relational algebra operations. We then gave in-memory (one pass), and external memory (two pass) algorithms for
each of these operations. Many of these algorithms involved either a sort step, hash step, or index-scan step and many of the operations had more than had more than one algorithmic implementation.
We discussed how SQL parsing is done using grammar rules and recursive descent to make an abstract syntax tree/parse tree. This tree is then preprocessed to make sure this trees relations and attributes make semantic sense.
This parse tree is then traversed to get an initial logical query plan.
We went over standard algebraic heuristics to improve this plan such as pushing projections and selections.
We went over relational algebra laws that can be used to generate equivalent plans, and we introduced cost metrics for operations to measure how good a given plan is.
Finally, as an example, we applied such a metric to the case of doing a particular projection of a table.
We begin today by looking at how to estimate the size of a selection.
Estimating the Size of a Selection
For `S= sigma_{A=c}(R)`. We estimate `T(S)=(T(R))/(V(R,A))`.
For `S= sigma_{A lt c}(R)` or `S= sigma_{A gt c}(R)`. We estimate `T(S)=(T(R))/3`, the somewhat bogus intuition being that people tend
to write queries looking for something more selective than half of the tuples.
For `S= sigma_{A ne c}(R).` We estimate `T(S)=T(R)`.
A more accurate estimate is `T(S) = (T(R)(V(R,A) -1))/(V(R,A))` as `(V(R,A) -1)/(V(R,A))` give the odds
that an attribute does not have a particular value.
Estimating the Size of a Join
We only consider natural joins, such as $R(X,Y) \bowtie S(Y,Z)$, since other joins
can be estimated from natural join together with estimates for selections and projections.
To simplify the task of estimating natural joins, we assume, even though it is not really accurate, that:
If `V(R,Y) le V(S,Y)` then every `Y` value of `R` is a `Y` value of `S`.
If `A` is an attribute of `R` not involved in the join, then $V(R \bowtie S, A) = V(R,A)$. Similarly for `S`.
Under these assumptions, every tuple `t` of `R` has a `1/(V(S,Y))` chance of joining with a tuple of `S`.
So the expected number of tuples `t` will join with is `(T(S))/(V(S,Y))`.
So the total size of the join would be `(T(R) cdot T(S))/(V(S,Y))`.
To make our estimate symmetric, we estimate:
$T(R \bowtie S)$ `= (T(R) cdot T(S))/(max(V(R,Y),V(S,Y)))`.
Natural Joins with Multiple Join Attributes
Suppose we want to join `R(x,y_1,y_2)` with `S(y_1,y_2,z)`.
We can generalize our previous reasoning to obtain the estimate:
`(T(R) cdot T(S))/([max(V(R,y_1), V(S,y_1)) cdot max(V(R,y_2), V(S,y_2))])`
Joins of Many Relations
Suppose now we have the join
$J = R_1 \bowtie R_2 \bowtie R_3 ... \bowtie R_n$.
To estimate the result we first multiply the size of the relations.
Then we look at all the attributes `A` appearing at least twice,
divide by all but the least of the `V(R,A)`.
For example, if had $R(a,b,c) \bowtie S(b,c,d) \bowtie U(b,e)$:
If `T(R)=1000`, `T(S)=2000`, and `T(U)= 5000`, then would compute `1000 times 2000 times 5000`.
If `V(R,b) = 20`, `V(S,b) = 50`, and `V(U,b) = 200`, we would divide by `V(S,b)` and `V(U,b)`.
This handles the attribute `b` that appears more than twice. We finally have to handle the variable `c` that appears twice.
This can be done as in the previous slides -- we divide by the larger of `V(R,c)=6` and `V(S,c) = 10`, so 10.
The final answer is 1,000,000.
Cost-Based Plan Selection
The cost of a whole query can be estimated as the sum of the estimated costs of the operations
in it.
The Query optimizer often chooses from several possible plans for a given query.
The number of I/Os of the plan we choose is influenced by:
The particular logical operators chosen to implement the query.
The sizes of intermediate relations.
The physical operators used to implement the logical operators.
The ordering of similar operations such as joins.
The method of passing arguments from one physical operator to another.
Obtaining Estimates for Size Parameters
The DBMS stores values of `V(R,A)` in its catalog.
This is not always updated, but can be explicitly updated by the DBA.
For example, in Oracle (mainly up to 10g), Mysql, we can use
ANALYZE TABLE some_table COMPUTE STATISTICS;
Oracle also has a more modern dbms_stats utility for this purpose.
In Postgres and sqlite, the command is just ANALYZE or ANALYZE table_name.
The DBMS may compute a histogram rather than just `V(R,A)`. That is, a plot of a range of values for an attribute versus number of objects in that range.
Usually such a histogram has equal-width, can also compute equal height statistics, and most frequent value statistics.
Most of these statistics can be computed by a table scan.
Quiz
Which of the following statements is true?
Computing nested loop joins always involves a sorting step.
In the hashed-based, duplicate elimination algorithm we first hash the relation/file to buckets, then do duplicate elimination within each bucket.
In our approach to parsing queries, the query pre-processor runs before the parser.
Incremental Computation of Statistics
Once statistics have been computed, we want to be able to maintain them without having to recompute everything.
For example, to maintain `T(R)`, we can just add +1 on an insert and -1 on a delete.
If there is a B-tree index on any attribute `R`, we could also estimate `T(R)` using the number of leaves in B-tree and assuming each is 3/4 full. (Don't have to change each insert.)
If there in an index on `A`, then `V(R,A)` can also be calculated as we insert/delete from the index.
In the particular case that `A` is a key, then `T(R)=V(R,A)`.
In the no index case, one might use a rudimentary structure that holds each value of `A`.
One might also sample data and assume the data is according to some distribution: uniform, Zipfian, etc.
Heuristics for Reducing the Cost of Logical Query Plans
We might try things like pushing selects down the tree.
As the preferred logical query plan is being generated, we consider plans generated by applying several such heuristics.
Then we compute the cost before and after each transformation.
If the transformation doesn't help, we don't do it.
Approaches to Enumerating Physical Plans
There are two standard approaches:
Top down: Work from the root down. For each possible implementation of the operation at the root, consider each possible way to evaluate its arguments. Compute the cost of each possibility and choose the least.
Bottom up: For each sub-expression, compute the cost of all possible ways to compute that sub-expression. Choose the one of least cost.
We'll focus on bottom up approach.
More on Enumerating Physical Plans
Heuristic selection - Try different heuristics to improve cost. For example:
We can try using an index if we are doing a select.
If we are doing a join and there is an index on the join attribute, then we can try using an index join.
If we are doing a join and one attribute of the join is sorted, then we can try using a sort join.
If we are doing a set operation, we can try grouping smallest relations first.
Branch and Bound Plan Enumeration - Use heuristics to first find a good plan, say with cost `C`. Then enumerate plans for sub-expressions. If any cost for a sub-expression is above `C`, can discard all plans that involve this way to compute the sub-expression. Hopefully, using this can speed up over exhaustive search.
Hill Climbing - make small local changes to plan, if this improves cost, we switch the plan. If no small change in plan yields an improvement within a timeout period for considering plans, then output the best current plan.
Dynamic Programming - say more about in a minute, mainly for join orderings.
Selinger-Style Optimization - modifies dynamic programming. Keeps low cost plan as well as several non-low cost sub-expressions which might make up their slowness by how they update their data. For example, a non-low cost sub-expression might compute its output in sorted order and this might be useful later in the query. Details can be found in Selinger et al. (1979).
Join Order - Significance of Left and Right Join Arguments
When ordering joins, we should remember our algorithms are often asymmetric in terms of cost.
It is possible that $A \bowtie B$ is cheaper than $B \bowtie A$.
We should when performing a:
Nested-loop join use the smaller relation as the outer loop.
Index-join use as the right argument the table with the index attribute.
Join Trees
Consider $(A \bowtie B \bowtie C)$. We could order this as:
$((A \bowtie B) \bowtie C)$
or as
$(A \bowtie (B \bowtie C))$.
There are exponentially many ways to take the natural join of `n` tables.
It would take too long if we had to enumerate all of them.
Left-Deep Join Trees
An ordering like $(((A_1 \bowtie A_2) \bowtie A_3) \ldots)$ is called a left
deep ordering.
Note $(((A_3 \bowtie A_1) \bowtie A_2) \ldots)$ computes the same join and is
also left deep.
One can define a right deep ordering in a similar fashion by associating to the right.
A join like $((A_1 \bowtie A_2) \bowtie (A_3 \bowtie A_4))$ uses neither a left or right ordering and is called bushy.
Even if we allow non-join operations, we might still call it a left-deep ordering, provided binary operators are parenthesized in above way.
In general, there is only one left-deep tree shape for `n` relations.
However, one is able to put the relations into this shape in `n!` ways.
The number of tree shapes is given by
`T(1)=1`,
`T(n) = \sum_{i=1}^{n- 1}T(i)T(n-i)`
Some Advantages of Left Deep Join Trees
They tend to produce efficient plans because:
If one pass join algorithms are used, and the build relation is on the left, then the amount of memory needed at any one time tends to be smaller than if used a right -deep or bushy tree.
If nested-loop joins are used, implemented by iterators, then we avoid having to construct any intermediate relation more than once.
