Index-based Query Algorithms - Parsing and Optimization

CS157b

Chris Pollett

Mar 8, 2023

Outline

Index-based Query Algorithms
Parsing Queries
In-Class Exercise

Introduction

We have been talking about how DBMSs process queries.
We said SQL queries are parsed and transformed in a logical query plan, a tree annotated with relational operators.
We then said a physical plan is selected for each of those operators.
We said for some operators such a projection and selections, union, often an iterator based approach is used.
We then looked at one pass algorithms to make physical plans, usually fir the case where the relations involved can be kept in memory.
We talked about two pass algorithms either based on sorting or hashing for the case where the computations couldn't be completely held in memory.
So far, we haven't considered the role of indexes in the creating of physical plans.
We start today with an example use of indexes in query computation: joins.

Index-based Join

Examine each block of R. For each tuple t in that block, look up all things that join with it in S and output the joins.
Cost will approximately be `(T(R) cdot T(S))/(V(S,Y))`, where `Y` is the join attribute if `S` is nonclustered and `(T(R) cdot B(S))/(V(S,Y))` if it is clustered.

Parsing Queries

We now begin to discuss how queries are parsed. Below is a rough flow of the operations:

Syntax Analysis and Parse Trees

The job of the parser is to take SQL and convert it to a parse tree.
Nodes in this tree can be of the following types:
- Atoms -- keywords (like SELECT), names of attributes, constants, parentheses, operators. These are leaves in the tree
- Syntactic Categories -- names for families of query subpart. For example, <Condition> might represent a possible condition part in the query.

A Grammar for a Simple Subset of SQL

Here are some example rules a parser might use for SQL:

<Query> ::= <SFW>
<Query> ::= (<Query>) // '::=' should be read as 'can be expressed as' 
<SFW> ::= SELECT <SelList> FROM <FromList> WHERE <Condition> 
<SelList> ::= <Attribute>, <SelList>
<SelList> ::= <Attribute>
<FromList> ::= <Relation>, <FromList>
<FromList> ::= <Relation>
<Condition> ::= <Condition> AND <Condition>
<Condition> ::= <Tuple> IN <Query>
<Condition> ::= <Attribute> = <Attribute>
<Condition> ::= <Attribute> LIKE <Pattern> //etc....

Example

SELECT name
FROM MovieStar
WHERE birthdate LIKE '%1960'

Might yield parse tree:

In-Class Exercise

Suggest a parse tree for:

SELECT LNAME
FROM EMPLOYEE E, WORK_ON W
WHERE E.ID=W.EID AND W.PID=37

Please post your solution to the Mar 8 In-Class Exercise.

The Preprocessor

The Preprocessor does semantic checking. That is:
- It expands views to their corresponding query
- It checks that the items in the FROM clause are actual tables in the DB
- It checks that the attributes in the SELECT clause belong to some table and matches them to the correct table.
- It checks that attributes in the WHERE clause are being put in conditions that are appropriate for their type.

Algebraic Laws For Improving Query Plans

We are now going to consider different ways we can write the same query.
The hope being some ways of writing are faster to process than others.
For instance, it is probably faster to compute selects before taking a join, then vice versa.
The optimizer's job will be to consider several plans and usually heuristically choose the best one.

Commutative and Associative Laws

`R times S = S times R`; `(R times S) times T = R times (S times T)`.
$R \bowtie S =S \bowtie R$; $(R \bowtie S) \bowtie T = R \bowtie (S \bowtie T)$
`R cup S = S cup R`; `(R cup S) cup T = R cup (S cup T)`
`R cap S = S cap R`; `(R cap S) cap T = R cap (S cap T)`
Notice the intermediate tables in computing a sequence of joins can be considerable smaller depending on the order of execution.

Laws Involving Selection

`sigma_{mbox{C1 AND C2}}(R) = sigma_{C1} (sigma_{C2}(R)) = sigma_{C2} (sigma_{C1}(R))`
`sigma_{mbox{C1 OR C2}}(R) = sigma_{C1}(R) cup sigma_{C2}(R)` provided `R` is a set.
`sigma_C(R cup S) = sigma_C(R) cup sigma_C(S)`
`sigma_C(R-S) = sigma_C(R) - S`
Similar rules hold for joins and Cartesian products.

Pushing Selections

To reduce the cost of computing joins and products as much as possible, a good heuristic is to try to push selects as far down all branches in the query tree as possible
For instance,
$\sigma_{\mbox{year=1996}}(Movie \bowtie StarsIn)$
should be rewritten
$(\sigma_{\mbox{year=1996}}(Movie)) \bowtie (\sigma_{\mbox{year=1996}}(StarsIn))$.

Laws Involving Projection

$\pi_L(R \bowtie S)= \pi_L(\pi_M(R)\bowtie \pi_N(S))$ where `M` is those attributes are the join attributes and the attributes in `L` that are from R; `N` is are the join attributes and the attributes in `L` that are from `S`.
This also works for joins with conditions on them.
As with selections, it is often useful to try to push projections as far down the tree as possible.

Laws for Joins and Products

We have already seen that it is possible to rewrite a join using:
$R \bowtie_{Condition} S = \sigma_{Condition}(R \times S)$
A natural join can be written as:
$R \bowtie S = \pi_L(\sigma _C (R \times S))$ where `L` is the attributes from the natural join and `C` is the condition which matches identically named attributes of `R` and `S`.

From Parse Trees to Logical Query Plans

After calculating the parse tree for a query...
We replace the appropriate groups in the tree by relational operators.
- For example, we replace the node in a tree with the cartesian product of the tables listed under it.
Then try to optimize this relational algebra tree to create a physical query plan.
- Might apply heuristics like pushing selection and projections which we mentioned earlier.

What to select for each plan we generate

An order and grouping on the associative and commutative operations like join, unions, and intersections.
An algorithm for each operator in the logical plan. For example, we need to choose between nested-loop join and hash-join.
Additional operators - scanning, sorting, etc which are needed for the physical plan but are not present in the logical plan.
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.