Physical Query Plan Operators

SELECT title, birthdate 
FROM MovieStar, StarsIn 
WHERE year =1996 AND gender='F' AND starName = 'Starlet'

We have been looking at how databases are physically organized.
The choice of file structures for tables and index types for indexes used in this layout typically give the query optimizer multiple possible choices for data access methods used to retrieve the results of a query.
As we indicated last day, the optimizer uses cost estimations based on statistics in the database catalog and the parsed internal representation of a query (some kind of relational algebra tree), to decide on a final physical query plan.
Physical Query Plans are built out of operators each one of which would enable us to implement a relational operator operation (or at least part of one).
Some example physical operators are:
- Table-scan
- Index Scan (can be more specific to index type)
- Sort-Scan (tree-sort, main memory, merge)
- Nested Join

Seeing the Physical Query Plan

Most DBMSs support an EXPLAIN command to see the query plan it will use to evaluate a query.

As an example, in mysql you could do:

explain select * from my_table order by b asc;

which produces

+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+----------------+
|  1 | SIMPLE      | my_table | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |   100.00 | Using filesort |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+----------------+
1 row in set, 1 warning (0.00 sec)

In-Class Exercise

Make two tables with at least one column in common in Mysql or Sqlite.
Insert some rows into each.
Run the EXPLAIN command on the natural join of these two tables.
Post the results to the Oct 28 In-Class Exercise.

Model of Computation for Physical Operators

A basic model to compute the cost of a query is to just count the number of disk I/Os.
This ignores some issues a cost-based optimizer considers like the difference between access to blocks which are contiguous on disk versus not, the number of overflow blocks a file has, etc.
To this basic model, we might later add these things, and network communication costs.
We assume the arguments for any query come from disk, but results are left in main memory.
This is reasonable as the query processor will likely feed the results of one operation in the plan into the next.
I.e., The query processor will pipeline operations.

Parameters for Measuring Cost

M - number of main memory blocks available
B(R) - number of blocks in R. (Just B if R understood.)
T(R) - number of tuples in R. (Just T if R understood.)
V(R,a) - number of distinct values of attribute a.

I/O Cost for Scan Operators

Cost(Table-Scan(R)) = B(R)
Cost(Sort-Scan(R)) =
- B -- provided R fits in main memory.
- 3B -- if use multiway merge sort (we'll describe next slides) and only need one pass in Phase II.

Aside - Sorting in Secondary Storage

Example: Assume we have a table R consisting of 50,000,000 rows. Assume the block size is 16KB (2^{14} bytes) and that 100 records fit in a block. i.e., 1 record is roughly 160 bytes.
So `R` fits in 500,000 blocks.
On the other hand, if have 500MB of memory. Then can at most hold `500 \cdot 2^{20}/2^{14} = 32,000` blocks.
So if we want to sort on some field, we can't read everything into RAM.

Bottom up Merge Sort

Common main memory sort algorithm

Work in passes. In pass `i` merge adjacent runs of length `2^i` into runs of length `2^{i+1}`.

Ex 
4 2 5 1 6 3 (runs length 1)
2 4 1 5 3 6 (runs length 2)
1 2 4 5 3 6 (runs length 4)
1 2 3 4 5 6 (runs of length 8) -- done

So have about `log_2 n` passes and each pass is `O(n)` work. So `O(n\cdot\log_2 n)` time algorithm.

Two Phase Multiway Merge-Sort (TPMMS)

Phase I -- read in M blocks into main memory (number of blocks of main memory have for buffers). Sort these according to the desired field. Save these sorted sub- lists back to disk.
Phase II -- merge these sub-lists together to produce one sorted list. We will explain how this is done in a moment.

Phase I Example

Using the R from a couple slides ago...
- R was 500,000 blocks long and main memory holds 32,000 blocks.
- So would make 500000/32000 = 16 sub-lists.
- Each block from R needs to be read once from disk and written once to disk for a total of 1,000,000 I/Os.
- If an I/O takes 1/100 of second. This would take 10,000 seconds of 10000/3600 = 2.7 hours

Phase II

To do this phase we could do something like in the main memory case -- work in passes, read a block each from pairs of adjacent sorted sub-lists, merge these, and write to a twice as long output sub-list. Do this for all sub-lists. After `log_2 n` passes we would be done.
This is somewhat inefficient, since only have two blocks from the file in memory at a time.
Instead, we read from `M-1` different sub-lists one block. We merge these into one output block. When it fills write it disk and start filling it again.

Phase II -cont'd (finish sorting in secondary storage aside)

How much more efficient is this last idea than just merging two blocks?

Well, now it takes `log_{M - 1} n` passes rather than `log_2 n` passes.
As each pass can take a while this can mean significant savings.

Iterators for Implementing Physical Operators

Many physical operators are implemented using Iterators. An iterator is an object which supports:
- Open
- GetNext
- Close
Example: A one pass algorithm for `R cup S` might
1. First open `R` use GetNext in a loop to get each tuple in `R` till `R` done.
2. Then Open `S` and use GetNext in a loop to get each tuple of `S`.
3. Finally, Close both `R` and `S`.

One Pass Algorithms -- Tuple at a Time Operations

The basic format of these algorithms is:
1. Read blocks of R one at a time into an input buffer
2. Perform an operation on each tuple
3. Move result to the output buffer or perform next step in query process.
We just saw a one pass algorithm for union.
It is one pass because it only reads the records in the relation once.
It was also an example of a tuple-at-a-time algorithm, as when it was done processing the tuple it could be used by the next stage of the query processor.
Some other examples of one pass, tuple-at-a-time operations are `pi(R)`and `sigma(R)`.
Cost will typically be `B(R)` if `R` is on disk.

One-Pass Algorithms for Unary, Full-Relation Operations

Duplicate Elimination
- Read each block into memory.
- Use `M-1` blocks to store tuples seen so far.
- Before putting a tuple in the output block check if it is among those seen.
- To work this algorithm requires `B(delta(R)) le M-1`.
- To simplify future discussion, we will often approximate as `B(delta(R)) le M`.
Grouping
- For MIN(a) and MAX(a), we can scan and keep copy of current min or max of group so far.
- For COUNT, add one for satisfying value.
- SUM and AVG are similarly handled. Need to have a bound like above on number of distinct groups.

One Pass Algorithms for Binary Operations

Product and Natural Join (`R \times S` or $R \bowtie S$).
- If one of `R` and `S` fits into at most M-1 buffers, just read it entirely into main memory.
- Then concatenate each of its tuples with the appropriate tuples from the other relation.
- This takes time `B(R)` + `B(S)`.

Nested Loop Joins

What if neither of `R` or `S` doesn't fit into memory?
How do we do a join or product then?

Could do:

For each s in S do
    For each r in R do
        if r and s join make a tuple t then output t;

Notice this algorithm fits fairly well within the iterator framework.
However, if one is not being careful this could take time `T(R)cdot T(S)`. (`T(R)` = number of tuples in `R`.)
We'll now discuss some improvements.

More Nested Loop Join

Rather than being tuple oriented want to do our accesses on R and S in a block-wise manner.

Want to store as much as we can of one of two relations in main memory. Assume `B(S) le B(R)`. Then can do:

For each M-1 blocks of S do begin
    Read these blocks into main-memory buffers;
    Organize their tuples into a structure whose search
        attributes are the common attributes of R and S;
    For each block b of R do begin
        For each tuple t of b do begin
            find the tuples of S in main memory that join with t;
            output the join of t with each of these tuples;
        end;
    end;
end;

This is called nested block join.
It takes time `B(S) + (B(S) cdot B(R))/(M-1)`.

Two Pass Algorithms Based On Sorting

We'll next consider some algorithms based on sorting.
We'll assume sorting only takes two passes.
If we had `1GB = 10^{9}` bytes of memory for our DBMS buffers, then our n-way merge sort algorithm of last day could sort roughly `1 EB = 10^{18}` bytes of memory in two passes -- more data than what all but a small number of companies ever deal with.
Despite this, sometimes performing a single pass can take a while (days), as anyone who has ever tried to make a duplicate of a large hard drive can tell you.

Duplicate Elimination using Sorting

Given `R` we sort `R` and output distinct values.

Phase I creating sorted sublist of size `M` takes time `B(R)`.
Phase II can be done in one pass provided can merge all of these sublists together in one go. So `B(R)` must be less than `M \cdot (M - 1)`.
In which case, total cost is `3 cdot B(R)`.

Grouping and Aggregation using Sorting

To do grouping and aggregation:
1. Read the records into memory `M` blocks at a time.
2. Sort `M` blocks using the grouping attributes as the sort key
3. Write each sorted list to disk.
4. Use one main buffer for each sublist, and initially load the first block of each sublist into its buffer.
5. Repeatedly find the least value, `v`, of the (grouping attributes among the first available tuple in the buffer.
6. For this value, we (a) compute all aggregates for the group `v` belong to. (b) if a buffer becomes empty replace it with the next buffer from the same sublist.
7. If no more tuples for this v go on to next smallest `v`.
8. Notice take `3B(R)` time and works if `B(R) lt M^2`

Map Reduce and Sorting

Typically, when we do a map job, the input is in one or more files containing document records.
On the same machince, this map may be done in parallel or in threads.
The map job reads each such file in turn, record-by-record.
When it emits a (key, value) pair, the target machine for that key is determined and the pair is appended to an output file for that machine.
So from one input file, get several unsorted output files, one for each target machine.
These output files are then sorted by key to produce sorted files.
Before sending these sorted files to a destination machine a combiner might be used.
A combiner runs the reduce job on the local machine for each sorted file, producing sorted reduced files.
These are then sent to the target machines.
After receiving all the files needed for the reduce step, the target machine can merge the various sorted files it receives into a single file sorted file.
The reduce job is then run on this.

Observation on Map Reduce and Sorting

Notice the above process is faster if we don't have to use the two-pass sorting anywhere.
Since we hope the reduce/combiner steps will make for smaller files sizes, we tend to organize this process where is the output map file for a machhine cannot be fit into memory we start a new file for that machine.
For each such memory sized file, we run the combiner, hopefully producing a smaller file.
We then send all these smaller files to a given destined machine.
It concatenate these into memory sized files on it and runs the reduce job, producing several output files.
Only when we are sure that we are about to finish the whole job do we concatenate things together, to do a final reduce.

More Query Optimization

Outline