Index Scans, Aggregates

General Query Optimizers

1. Apply blind heuristics (e.g., push down selections)
2. Enumerate all possible execution plans by varying (or for a reasonable subset)
   • Join/Union Evaluation Order (commutativity, associativity, distributivity)
   • Algorithms for Joins, Aggregates, Sort, Distinct, and others
   • Data Access Paths
3. Estimate the cost of each execution plan
4. Pick the execution plan with the lowest cost

Data Access Paths

Original Query: $\pi_A\left(\sigma_{B = 1 \wedge C < 3}(R)\right)$

Possible Implementations:

• Full Table Scan
• Index Scan on Tree/Hash Index over $B$
• Index Scan on Tree Index over $C$
• Index Scan on Tree Index over $B,C$

Full Table Scan

1. Project down to $A$, reading from...
2. Filter on $B = 1 \wedge C < 3$, reading from...
3. All of the rows in $R$

Index Scan on Tree/Hash Index over $B$

1. Project down to $A$, reading from...
2. Filter on $C < 3$, reading from...
3. The index which provides all rows where $R.B = 1$

Index Scan on Tree Index over $C$

1. Project down to $A$, reading from...
2. Filter on $B = 1$, reading from...
3. The index which provides all rows where $R.C < 3$

Index Scan on Tree Index over $B, C$

Lexical Sort: First sort by B, with C as a tiebreaker.

1. Project down to $A$, reading from...
2. The index which provides all rows between
   $\left\lt 1, -\infty\right\gt \lt \left\lt R.B, R.C\right\gt \lt \left\lt 1, 3\right\gt$

Which index to use (if several are available)?

Strategies for Implementing $(\ldots \bowtie_{c} S)$

Sort/Merge Join

Sort all of the data upfront, then scan over both sides.

In-Memory Index Join (1-pass Hash; Hash Join)

Build an in-memory index on one table, scan the other.

Partition Join (2-pass Hash; External Hash Join)

Partition both sides so that tuples don't join across partitions.

Index Nested Loop Join

Use an existing index instead of buildling one.

Index Nested Loop Join

1. Read One Row of $R$
2. Get the value of $R.A$
3. Start index scan on $S.B > [R.A]$
4. Return rows as normal

Aggregation

Normal Aggregates

SELECT COUNT(*) FROM R

SELECT SUM(A) FROM R

Group-By Aggregates

SELECT A, SUM(B) FROM R GROUP BY A

Distinct

SELECT DISTINCT A FROM R

SELECT A FROM R GROUP BY A

Normal Aggregates

Basic Aggregate Pattern

This is also sometimes called a "fold"

Init

Define a starting value for the accumulator

Fold(Accum, New)

Merge a new value into the accumulator

COUNT(*)

Init

$0$

Fold(Accum, New)

$Accum + 1$

SUM(A)

Init

$0$

Fold(Accum, New)

$Accum + New$

AVG(A)

Init

$\{ sum = 0, count = 0 \}$

Fold(Accum, New)

$\{ sum = Accum.sum + New, \\\;count = Accum.count + 1\}$

Finalize(Accum)

$\frac{Accum.sum}{Accum.count}$

Basic Aggregate Pattern

Init

Define a starting value for the accumulator

Fold(Accum, New)

Merge a new value into the accumulator

Finalize(Accum)

Extract the aggregate from the accumulator.

Basic Aggregate Types

Grey et. al. "Data Cube: A Relational Aggregation Operator Generalizing Group-By, Cross-Tab, and Sub-Totals

Distributive

Finite-sized accumulator and doesn't need a finalize (COUNT, SUM)

Algebraic

Finite-sized accumulator but needs a finalize (AVG)

Holistic

Unbounded accumulator (MEDIAN)

Group-By Aggregates

Naive Idea: Keep a separate accumulator for each group

What could go wrong?

Alternative Grouping Algorithms

2-pass Hash Aggregate

Like 2-pass Hash Join: Distribute groups across buckets, then do an in-memory aggregate for each bucket.

Sort-Aggregate

Like Sort-Merge Join: Sort data by groups, then group elements will be adjacent.