Wander Join: Online Aggregation via Random Walks

lois-ondreau

Published 2026-06-15 · 37 slides · 0 views

↓ Download

Wander Join: Online Aggregation via Random Walks

Wander Join: Online Aggregation via Random Walks - slide 1 of 37

Wander Join: Online Aggregation via Random Walks - slide 2 of 37

Wander Join: Online Aggregation via Random Walks - slide 3 of 37

Wander Join: Online Aggregation via Random Walks - slide 4 of 37

Wander Join: Online Aggregation via Random Walks - slide 5 of 37

Wander Join: Online Aggregation via Random Walks - slide 6 of 37

Wander Join: Online Aggregation via Random Walks - slide 7 of 37

Wander Join: Online Aggregation via Random Walks - slide 8 of 37

Wander Join: Online Aggregation via Random Walks - slide 9 of 37

Wander Join: Online Aggregation via Random Walks - slide 10 of 37

Wander Join: Online Aggregation via Random Walks - slide 11 of 37

Wander Join: Online Aggregation via Random Walks - slide 12 of 37

Wander Join: Online Aggregation via Random Walks - slide 13 of 37

Wander Join: Online Aggregation via Random Walks - slide 14 of 37

Wander Join: Online Aggregation via Random Walks - slide 15 of 37

Wander Join: Online Aggregation via Random Walks - slide 16 of 37

Wander Join: Online Aggregation via Random Walks - slide 17 of 37

Wander Join: Online Aggregation via Random Walks - slide 18 of 37

Wander Join: Online Aggregation via Random Walks - slide 19 of 37

Wander Join: Online Aggregation via Random Walks - slide 20 of 37

Wander Join: Online Aggregation via Random Walks - slide 21 of 37

Wander Join: Online Aggregation via Random Walks - slide 22 of 37

Wander Join: Online Aggregation via Random Walks - slide 23 of 37

Wander Join: Online Aggregation via Random Walks - slide 24 of 37

Wander Join: Online Aggregation via Random Walks - slide 25 of 37

Wander Join: Online Aggregation via Random Walks - slide 26 of 37

Wander Join: Online Aggregation via Random Walks - slide 27 of 37

Wander Join: Online Aggregation via Random Walks - slide 28 of 37

Wander Join: Online Aggregation via Random Walks - slide 29 of 37

Wander Join: Online Aggregation via Random Walks - slide 30 of 37

Wander Join: Online Aggregation via Random Walks - slide 31 of 37

Wander Join: Online Aggregation via Random Walks - slide 32 of 37

Wander Join: Online Aggregation via Random Walks - slide 33 of 37

Wander Join: Online Aggregation via Random Walks - slide 34 of 37

Wander Join: Online Aggregation via Random Walks - slide 35 of 37

Wander Join: Online Aggregation via Random Walks - slide 36 of 37

Wander Join: Online Aggregation via Random Walks - slide 37 of 37

Description

Wander Join: Online Aggregation via Random Walks Feifei Li Bin Wu, Ke Yi Zhuoyue Zhao University of Utah Hong Kong University Shanghai Jiao Tong of Science and Technology University Slides from http:datagroup.cs.utah.eduseminars.php

Download this presentation From Below

"Wander Join: Online Aggregation via Random Walks" is the property of its rightful owner. Permission is granted to download and print the materials on this website for personal, non-commercial use only, and to display it on your personal computer provided you do not modify the materials and that you retain all copyright notices contained in the materials. By downloading content from our website, you accept the terms of this agreement.

Presentation Transcript

Wander Join: Online Aggregation via Random Walks Feifei Li Bin Wu, Ke Yi Zhuoyue Zhao
University of Utah Hong Kong University Shanghai Jiao Tong
of Science and Technology University Slides from http://datagroup.cs.utah.edu/seminars.php
Adapted for Duke DB Group by Brett Walenz
Added slides 3, 7-10, 12, 19-21

Database Workloads Wander Join: Online Aggregation via Random Walks 2

Online Aggregation Goal: Analytical queries do not always need 100% accuracy. Can we return an approximate answer with improving ‘quality’ guarantee?

Concretely, how do we estimate an aggregate query that involves multiple joins?

Notion of quality: express in form of confidence intervals: that is, we’d like to be able to say that with high probability, the actual query answer is somewhere in a given interval (preferably small). Wander Join: Online Aggregation via Random Walks 3

Online Aggregation [Haas, Hellerstein, Wang SIGMOD’97] Wander Join: Online Aggregation via Random Walks 4 Confidence Interval Confidence Level

Complex Analytical Queries (TPC-H) SELECT SUM(l_extendedprice * (1 - l_discount))
FROM customer, lineitem, orders, nation, region
WHERE c_custkey = o_custkey
AND l_orderkey = o_orderkey
AND l_returnflag = 'R'
AND c_nationkey = n_nationkey
AND n_regionkey = r_regionkey
AND r_name = 'ASIA'

This query finds the total revenue loss due to returned orders in a given region. Wander Join: Online Aggregation via Random Walks 5

Ripple Join [Haas, Hellerstein, SIGMOD’99] Store tuples in each table in random order
In each step
Reads the next tuple from a table in a round-robin fashion
Join with sampled tuples from other tables
Estimate the aggregation value from samples, calculate confidence interval from estimator
Works well for full Cartesian product
But most joins are sparse … Wander Join: Online Aggregation via Random Walks 6

Ripple Join [Haas, Hellerstein, SIGMOD’99] Estimator for Wander Join: Online Aggregation via Random Walks 7 Is this estimator unbiased? Yes, since tuples pulled at random from EACH table.
Is this estimator consistent? Yes, the final result is the actual query result.

Ripple Join [Haas, Hellerstein, SIGMOD’99] How do we use this estimator to develop a confidence interval? Use the central limit theorem.
1.

2. Shift to a standard normal:

3. Find the area under this curve

4. Let

5. Then Wander Join: Online Aggregation via Random Walks 8

Ripple Join [Haas, Hellerstein, SIGMOD’99] How do we find

We need another estimator:

Ripple join is NOT independent: for every sample r, there are multiple samples s that may join. Thus the variance estimator needs to take into account the proportion of EACH table it has seen so far. Wander Join: Online Aggregation via Random Walks 9

Ripple Join [Haas, Hellerstein, SIGMOD’99] Wander Join: Online Aggregation via Random Walks 10 Now can use procedure for calculating confidence interval earlier.

A Running Example Wander Join: Online Aggregation via Random Walks 11 What’s the total revenue of all orders from customers in China?

Wander Join Take a randomly sampled tuple from ONLY one table
Conduct a random walk from that tuple to the neighbors (join tuples)
For queries with many join relations, there may be different walk paths
Can handle cyclical queries
Assumes indexes on other tables
Provide an unbiased estimator for each aggregator, calculate confidence intervals
Does not provide consistent result: must run full join in conjunction with wander join
Estimate and confidence interval converges faster than ripple join in experiments Wander Join: Online Aggregation via Random Walks 12

Join as a Graph Wander Join: Online Aggregation via Random Walks 13 Conceptual only
Never materialized

Join as a Graph Wander Join: Online Aggregation via Random Walks 14 Conceptual only
Never materialized

Join as a Graph Wander Join: Online Aggregation via Random Walks 15 SELECT SUM(Price)
FROM Customers C,
Orders O,
Items I
WHERE
C.Nation = ‘China’
C.CID = O.BuyerID
O.OrderID =
I.OrderID

Sampling by Random Walks Wander Join: Online Aggregation via Random Walks 16 SELECT SUM(Price)
FROM Customers C,
Orders O,
Items I
WHERE
C.Nation = ‘China’
C.CID = O.BuyerID
O.OrderID =
I.OrderID

Sampling by Random Walks Wander Join: Online Aggregation via Random Walks 17 SELECT SUM(Price)
FROM Customers C,
Orders O,
Items I
WHERE
C.Nation = ‘China’
C.CID = O.BuyerID
O.OrderID =
I.OrderID

Sampling by Random Walks Wander Join: Online Aggregation via Random Walks 18 SELECT SUM(Price)
FROM Customers C,
Orders O,
Items I
WHERE
C.Nation = ‘China’
C.CID = O.BuyerID
O.OrderID =
I.OrderID

Sampling by Random Walks Estimator of aggregate might be biased Wander Join: Online Aggregation via Random Walks 19 Idea: Penalize paths that are sampled with higher
probability proportionally.

Sampling by Random Walks Wander Join: Online Aggregation via Random Walks 20

Confidence Interval More complicated than it looks! This is just the normal variance formula. Estimator is more straightforward than ripple join. Wander Join: Online Aggregation via Random Walks 21

Sampling by Random Walks Wander Join: Online Aggregation via Random Walks 22 SELECT SUM(Price)
FROM Customers C,
Orders O,
Items I
WHERE
C.Nation = ‘China’
C.CID = O.BuyerID
O.OrderID =
I.OrderID

Walk Plan Optimization Structure of the data graph
Selection predicates
Starting table: use index
Table in the middle: reject random walk
Data distribution
Non-uniformitymay not be a badthing! Wander Join: Online Aggregation via Random Walks 23

Walk Plan Optimizer Enumerate all plans
Conduct ~ 100 trial random walks using each plan
Measure the variance of each plan
Select the best plan
All trials runs are still useful Wander Join: Online Aggregation via Random Walks 24

Convergence Comparison Wander Join: Online Aggregation via Random Walks 25

Wander Join in PostgreSQL Logarithmic growth due to B-tree lookup to find random neighbours Wander Join: Online Aggregation via Random Walks 26

Running on Insufficient Memory (4GB) Wander Join: Online Aggregation via Random Walks 27 Insufficient memory incurs a heavy, one-time penalty
Growth is still logarithmic
Fundamentally: Random sampling at odds with hard disks
But does it matter? Spark, In-Memory DB, RAM cloud…
The algorithm is embarrassingly parallel Turbo DBO [Dobra, Jermaine, Rusu, Xu, VLDB’09]

Wander Join vs Ripple Join Wander Join: Online Aggregation via Random Walks 28

Online Aggregation vs Data Cube Wander Join: Online Aggregation via Random Walks 29

Thank you!

Dealing with Selection Predicates One predicate
Little impact: Can start walk from that table
Multiple highly selective predicates
More random walks will fail
Running full query becomes faster
Can simply switch to full query when selectivity <1% (say) Wander Join: Online Aggregation via Random Walks 31

Index Ripple Join [Lipton, Naughton, Schneider, SIGMOD’90] Wander Join: Online Aggregation via Random Walks 32

Sampling from a B-tree [Olken, ’93] Wander Join: Online Aggregation via Random Walks 33 4 2 3 Sampling from an aggregate (ranked) B-tree is easy
But
incurs heavy cost for transactions
need to modify existing B-tree implementations

Rejection Sampling [Olken, ’93] Wander Join: Online Aggregation via Random Walks 34 Imagine each node has maximum fanout
Reject as soon as it walks out of bound

Non-Uniform Sampling Wander Join: Online Aggregation via Random Walks 35 As long as we can compute the sampling probability, wander join still works!

Compare with BlinkDB [Agarwal, Mozafari, Panda, Milner, Madden, Stoica, ’13] Wander Join: Online Aggregation via Random Walks 36

Accuracy Achieved in 1/10 Time of Full Join Wander Join: Online Aggregation via Random Walks 37

Wander Join: Online Aggregation via Random Walks

Related Topics

Share

Embed code

Download this presentation From Below

Presentation Transcript