What is the best, correct technique for task X, when Y is true?

1. How do you define Correct and Best?
2. What correct alternatives are available?
3. How do you find the best available alternative

So Far: Correct = The original RA for a query.

Only queries guaranteed to produce identical results are correct

What happens when Correct allows for a margin of error?

• Aggregate query with some margin of error.
• ORDER BY LIMIT (Top-K) with some margin of error.
• LIMIT without ORDER BY (Any-K).

Sacrifice a little accuracy for a lot of speed

Online Aggregation

Approximate Query Processing

                        SELECT SUM(A) FROM R
    

Naively, you need to see all values of R.A

Online Aggregation

$Avg(3,6,10,9,1,3,9,7,9,4,7,9,2,1,2,4,10,8,9,7) = 6$

$Avg(3,6,10,9,1) = 5.8$ $\approx 6$

$Sum\left(\frac{k}{N}\; Samples\right) \cdot \frac{N}{k} \approx Sum(*)$

Sampling lets you approximate aggregate values with orders of magnitude less data.

Question: How accurate is an estimate from $N$ samples

With $n$ tuples sampled uniformly with replacement

"Hoeffding's Bound"

See also "Chernoff's Bound" (similar) and "Serfling's Bound" (works without replacement).

What about non-sum-based aggregates?

Bootstrapping

Idea 1: Generate a bunch of samples of the same size and see how they're distributed.

The resulting histogram models the distribution of samples.

Problem: Generating samples is expensive!

Idea 2: Generate one sample, then resample to see how its distributed

The resulting histogram still models (in expectation) the distribution of samples.

Error Bounds

SUM, COUNT, AVG (sampling with replacement)

Hoeffding's Bound

Chernoff's Bound

SUM, COUNT, AVG (sampling without replacement)

Serfling's Bound

Any Other Aggregate

Bootstrapping

Keep adding samples until you reach a target accuracy

Keep adding samples until you run out of time

Generating Samples

Sampling From Disk

Random seeks are slow

Sampling For Group-By or Selections

Low-frequency events don't get sampled

Sampling From Joins

Fixing the Birthday Paradox

Sampling From Disk

Idea 1: Pick Randomly!

      for i from 1 to num_samples:
        sample_id = random(0, num_records)
        samples += [ table.where( rowid = sample_id ) ]
    

Problem: Random scans are EXPENSIVE.

Idea 2: Assume data already randomized!

Pick a random start record and read sequentially from there.

Problem: Sequential records are almost never IID.

Idea 3: Prebuild samples!

Shuffle data into fixed size sample buckets (e.g., BlinkDB).

Sampling From Group-By

      SELECT City, AVG(Salary) FROM NYS_Salaries;
    

Problem: Most data is about NYC. With $N$ samples taken uniformly, margins of error for other cities are much bigger

Stratified Sampling

Generate $\frac{N}{\texttt{COUNT}(\texttt{DISTINCT } City)}$ samples for each group

Use $\texttt{COUNT}(\texttt{DISTINCT } City)$ instead of $\texttt{COUNT}(*)$ as the total group size

Index Striding

Idea 2: Pre-generate sample buckets across a range of different strata (e.g., BlinkDB).

Sampling from Joins

The Birthday Paradox

Assume: $\texttt{UNIQ}(A, R) = \texttt{UNIQ}(A, S) = N$

It takes $O(\sqrt{N})$ samples from both $R$ and $S$
to get even one match.

Weighted Joins

Exploit Foreign Keys

Sample from the referencing table, join with full reference tables

Stratified Sampling

For many-many joins, stratify on the join attribute(s)

"Join Synopses"

$R \bowtie_B S \bowtie_C T$

• $R.B$ is a foreign key reference to $S.B$
• $S.C$ is a foreign key reference to $T.C$

Sample from R, Use all of S, T

"Join Synopses"

"Join Synopses"

• Foreign keys keep intermediate state small.
• Fast evaluation with INLJ or In-Mem Hash Join.
• Sampling is not biased.

Stratified Sampling

Stratified Sampling

For each tuple sampled from $R$, sample exactly one joining tuple from $S$

Question: Are we biasing the sampling process

Goal: Sample $r \bowtie s$ with probability $p((r\bowtie s) \in R\bowtie S)$

Actual: Sample $r \bowtie s$ with probability $p(r \in R) p((r\bowtie s) \in R\bowtie S | r \in R)$

Bayes' Theorem

$p(r \in R) p((r\bowtie s) \in R\bowtie S) | r \in R)$ $= p(r \in R, (r\bowtie s) \in R\bowtie S)$

$\neq p((r\bowtie s) \in R\bowtie S)$

Corrective Factor

$p(r \in R, (r\bowtie s) \in R\bowtie S)$ $\cdot n$ $= p((r\bowtie s) \in R\bowtie S)$

$n = \frac{ p((r\bowtie s) \in R\bowtie S) }{ p(r \in R, (r\bowtie s) \in R\bowtie S) }$

$ = \frac{ 1 }{ p(r \in R | (r\bowtie s) \in R\bowtie S) } $ $\approx |R| \cdot |\{\;s\;|s \in S, r.A = S.A\;\}$

The probability that a tuple in $R$ participates in a join.

Should I sample from $R$ or $S$ first?

(What if only one tuple from $R$ joins with every tuple from $S$)

"Wander Join"

Sample with all join orders at random.

Convergent Joins

Ripple Join

Incrementally increase the sample size

Turbo DBO Join

Re-use data loading to get "lucky" joins

Idea 1: Start small, keep making the sample size bigger

Ripple Join

1. $Q \leftarrow \emptyset$, $R_{sample} \leftarrow \emptyset$, and $S_{sample} \leftarrow \emptyset$
2. Sample $r \in R$
1. $Q \leftarrow r \bowtie S_{sample}$
2. $R_{sample} \leftarrow R_{sample} \uplus \{r\}$
3. Sample $s \in S$
1. $Q \leftarrow s \bowtie R_{sample}$
2. $S_{sample} \leftarrow S_{sample} \uplus \{s\}$
4. Goto 2

Ripple Join

Idea 2: Normal BNLJ, but piggyback sampling off of the results.