Outline

• Costs of Operations
• From logical plan to physical plan
• Quiz

Estimating Costs of Operations

• Last day we discussed how to transform a query into a logical query plan.
• We said how to parse a SQL query into a parse tree.
• We said the preprocessor then checks the items parsed in the the SELECT FROM and WHERE clauses to make sure they exist, are of the right type, etc.
• The tree is annotated by relational algebra operators and transformed relational algebra expression tree, a logical query plan.
• From this, we can compute a preferred logical query plan according to the heuristics discussed last day. One such heuristic was to push selections towards the leaves of this stree.
• Today, we consider ways of coming up with a physical plan from this logical plan.
• Typically, we consider several plans generated from the logical plan, do an estimate of cost for each, and choose the plan of least cost. (Least cost estimation)

What to select for each plan we generate

1. An order and grouping on the associative and commutative operations like join, unions, and intersections.
2. An algorithm for each operator in the logical plan. For example, we need to choose between nested-loop join and hash-join.
3. Additional operators - scanning, sorting, etc which are needed for the physical plan but are not present in the logical plan.
4. The way in which arguments are passed from one operator to the next. (By intermediate results or by pipelining).

Estimating Sizes of Intermediate Relations

• Without computing the query itself, one cannot exactly determine the number of rows it will return.
• Nevertheless, we'd like to make an estimate of the number of rows which is:
  • Accurate
  • Easy to compute
  • Logically consistent -- the result should not depend on which way we calculate an intermediate result.
• Recall we are using the following notations:
  • B(R) -- number of blocks in R,
  • T(R)-- number of tuples in R,
  • V(R, a) -- number of distinct values for attribute a.

Estimating the Size of a Projection

• The number of rows returned by a projection will be the same as the original relation.
• Nevertheless, the space needed to store the relation could be less.
• For example, suppose the block size was 4096, where 96 bytes used for header info. Suppose had a 1,000,000 tuple relation. If tuples went from 40 bytes long to 20 bytes long after a projection. Then the number we could store in a block would go from 100 to 200 and the file would go from 10,000 blocks long to 5,000 blocks long.

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.

Quiz

Which of the following statements is true?

1. Disk-based sorting is typical done using Quicksort.
2. There are example relations `R, S` such that a two pass computation of `R cap S` cannot be done if `B(R) + B(S) < M^3`.
3. Two pass, hash-based, duplicate elimination algorithm makes use of a partition by hash step.

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 tuple `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)$:
  1. If `T(R)=1000`, `T(S)=2000`, and `T(U)= 5000`, then would compute `1000 times 2000 times 5000`.
  2. 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)`.
  3. This handles the attribute `b` that appears more than twice. We finally have to handle the variable `c` that appears twice. This can bbe done as in the previous slides -- we divide by the larger of `V(R,c)=6` and `V(S,c) = 10`, so 10.
  4. The final answer is 1,000,000.