Outline

• Costs of Operations
• From logical plan to physical plan
• In-Class Exercise

Estimating Costs of Operations

• Last day we discussed how to transform a query into a logical query plan.
• From this we can compute a preferred logical query plan according to the heuristics discussed last day.
• 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.

In-Class Exercise

Suppose we had a table PERSON with 100 million rows. In these rows, the EYE_COLOR attribute can take one of six values. Further, in these rows, the ANNUAL_INCOME attributes take on 5000 distinct values. Solve the following problems:

1. You only want to date people that don't have red eyes. Write this as a relational algebra query, and estimate the number of tuples returned.
2. You only want to date people that have green eyes. Write this as a relational algebra query, and estimate the number of tuples returned.
3. You only want to date people that have annual incomes over 50000. Write this as a relational algebra query, and estimate the number of tuples returned.

Post your answers to the Mar 7 In-Class Exercise Thread.

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 $S = 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. Finally, 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.