Cost-Based Plan Selection

Before the midterm, we were discussing how to approximately compute the number of I/O operations required for different physical query plan operations.
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.

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.

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.

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.

Which of the following statements is true?

Our cost estimate for index-based join was the same for sparse/clustered versus non clustered/dense indexes.
For `S= sigma_{A=c}(R)`, we gave in class a query cost estimate of `T(S)=(T(R))/(V(R,A))`.
In class we estimated $T(R \bowtie S)$ as `(T(R) cdot T(S))/(V(R,Y)+V(S,Y))`.

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).

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.

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.

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)`

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.