Joins, Completing Plans, System Failures

CS157b

Chris Pollett

Mar 21, 2018

Outline

Suppose we want to compute $(R_1 \bowtie R_2 \bowtie \ldots \bowtie R_n)$
We can build a table that contains:
- Each table `R_i`, as well as its size and number of values for the join attribute
- For each subset of `R_{i_1} ... R_{i_j}` of the tables of size `j` an estimate of the total cost of performing the join of this subset.
The second kinds of entries are built from the best joins of subsets `j-1` tables by:
1. Go through these entries and joining one more table.
2. Check if this j-subset is already in the table.
3. If it is, compare its currently listed cost with the new way of joining.
4. If the new value is smaller, then replace its value in the table.

Suppose we want to compute the 4-way natural join,
$R_1(A_1,A_2) \bowtie R_2(A_2,A_3) \bowtie R_3(A_3,A_4) \bowtie R_4(A_4, A_5)$.
Table $R_i$ has $10^{i+6}$ rows, `V(R_i, A_i) = 10^i`, and `V(R_i, A_{i+1}) = 10^i`.
Build the table described on the previous slide for this situation.
Post your solution to the March 21 In-Class Exercise Thread.

To finish up creating a query plan, we need to consider the following issues:
- How to select algorithms to implement the operations of the query plan not done as part of some earlier step as in the dynamic programming for join order
- Decide whether to materialize intermediate results or pipeline them
- Notations for physical plan operators

What way should we use to evaluate `sigma_C(R)`?
Assuming there are no multidimensional attributes we can, if possible:
- Choose an attribute in `C`, with an index, and which is compared to a constant
- After using the index to find those things satisfying this part of `C`, apply the rest of the condition to complete the selection.
- If no such attribute exists, we need to do a table-scan
If there is more than one choice for `C`, we want to choose the most selective single indexed attribute, so we might do cost estimates over the different possibilities.

Request a nested loop join, if one can hope that the outer table can fit totally in memory (so maybe don't need to execute outer loop more than once).
Use a sort-join when one or both tables is already sorted on the join attribute. Or, if there are two or more tables joined on the same attribute (as then sort step can be amortized).
If there is an index on the join attribute and the results are expected to be small use an index join
Fall back to nested loop join if none of the above apply.

The naive way to evaluate queries is to write intermediate results back out to disk.
This is called materialization.
Pipelining is a technique whereby we pass the output of the sub-query computed so far to the next stage in the query evaluation. It is typically implemented using iterators.

Each node in logical query plan needs to be replaced by a physical operator.
Operators for leaves:
- TableScan(R)
- SortScan(R,L) - sort according to list L
- IndexScan(R,C) - look up index according to condition C
- IndexScan(R,A) - entire relation is retrieved via lookup on attribute A.
Operators for Selection:
- Filter(C) -- filter child results according to C.
- Some select might be an IndexScan followed by a Filter
Operator for Sorting:
- Sort(L) -- may occur anywhere interior to tree.
Other operators
- Join( type, # of buffers) - for example type might be two pass hash-join, and number of buffers might be 100

The physical query plan is typically represented as a tree.
Data must flow up the tree.
Since interior nodes may not be ancestors or descendants of each other in a bushy tree, we need to figure out an order to evaluate sub-trees of the same height.
To do this we evaluate sub-trees from the bottom up and evaluate sub-trees of the same
Execute all nodes of each sub-tree using a network of iterators.