16
Eddies [AH’00] Plans considered by the optimizer pred2(S) SOutput select * from S where pred1(S) and pred2(S) pred1(S) SOutput pred2(S) Decision made apriori based on statistics Sort by (1-s)/c, where s = selectivity, c = cost Once this decision is made, all tuples are processed using the same order

18
Per-tuple State Executing the query using an Eddy select * from S where pred1(S) and pred2(S) pred2(S) pred1(S) Eddy S Output Two Bitmaps 1)Ready bits - which operators can a tuple be routed to next 2)Done bits - which operators has a tuple already been through Example: Ready(t1) = [1, 1] - can be routed to either Done(t1) = [0, 0] - not done either Example: Ready(t2) = [1, 0] - can be routed to pred1 Done(t2) = [0, 1] - done pred2 For selection queries, ready is a bit- complement of done

19
Eddies: Routing Policy Choosing which operator to route a given tuple to The brain of the eddy Lottery Scheduling [Avnur 00] Simplified Description 1. Maintain for each operator: tuples sent tuples returned cost per tuple 2. Choose (roughly) based on the above 3. Explore by randomly sending tuples in the wrong orders sent = 100 received = 2 sent = 30 received = 20 Pred2 is more selective Send here 99% of the time Send to the other operator 1% of the time pred2(S) pred1(S) Eddy S Output

22
Eddies w/ Joins Traditional join operators typically consume one relation entirely and then start reading the second relation E.g. hash join operator builds a hash table on one relation first, and then reads in the other relation This is problematic for eddies An eddy needs to see tuples from different relations in order to make its routing decisions Also, if the inner relations are pre-decided, not much options left for adapting the join order [Avnur, Hellerstein 00] discusses this issue in detail for traditional join operators

23
Symmetric Hash Join We will use a new join operator called symmetric hash join operator Also called doubly pipelined Other variants include ripple joins, Xjoins (disk-based) S E HashTable S.Name HashTable E.Name SE When a new S tuple arrives: (1)It is built into S.name hashtable (2)Probed into E.name hash table to find matches with already arrived E tuples (3)Matches are immediately output Symmetric Operation !!

27
Per-tuple State Here also we need to keep track of what operators a tuple has already been through Again use Ready bits - operators that can be applied next Done bits - operators that have already been applied Unlike selections, these are not bit- complements of each other

33
Execution Postmortem Can we talk about what exactly the eddy did during the execution ? Yes ! Eddy executes different plans for different parts of data This is where the adaptivity comes from

34
Routing policy Lottery scheduling unfortunately doesn’t work well with joins Just because a join operator does not return tuples right now doesn’t mean it won’t return more tuples later In other words, a join operator is state-ful Selection operators are state-less

39
Joins and Lottery Scheduling Lottery scheduling doesn’t work well with joins Not clear how any routing policy can work without reasonable knowledge of future Whatever the current state in the join operators, an adversary can send tuples to make it look very bad Two possible solutions: Allow manipulation of state (STAIRs) [DH’04] Don’t embed state in the operators (SteMs) [RDH’03]

41
STAIRs [DH’04] Expose join state to the eddy Provide state management primitives That guarantee correctness of execution That can be used to manipulate embedded state in the operators Also allow support for cyclic queries etc

47
e1c1 State Management Primitive: Promotion Replace a tuple in a STAIR with the result of joining it with other tuples HashTable E.Name STAIR Eddy S E C Output HashTable S.Name STAIR HashTable E.Course STAIR HashTable C.Course STAIR s1 s1e1 e2 e2c1 c1 e1 Two arguments: A tuple A join to be used to promote this tuple Can be thought of as precomputation of work Promoting e1 using E C e1 e1c1

48
STAIRs: Correctness Theorem: For any sequence of applications of the state management operations, STAIRs will produce the correct query output. STAIRs will produce every result tuple There will be no spurious duplicates

57
Alternative: SteMs [RDH’03] Don’t embed the state in the operators at all Note: Not the original motivation for SteMs Focus was on increasing opportunities for adaptivity by breaking up the join operators We will focus on a very simplistic version of the operator

60
Query Execution using SteMs State inside the operators is independent of previous routing decisions Because no intermediate tuples are ever stored Doesn’t have the same problem as the join or STAIR operators Optimal routing policy easy to write down Similarities to queries with only selections But not storing intermediate results increases the computation cost significantly

61
SteMs: Drawbacks Recomputation of intermediate result tuples Constrained plan choices Available plans depend highly on the arrival order

62
Eddy S E C S SteM E SteM C SteM S0S0 SETUP: E and C arrive early; S is delayed time |S E| |E C| >> S E C S0S0 E C S –S 0 can only be routed to E SteM for probing and is forced to be executed as (S Join E) Join C Under the mechanism, there is no way to execute the other plan for this setup

63
SteMs: Drawbacks Recomputation of intermediate result tuples Constrained plan choices Available plans depend highly on the arrival order Though more subtle, the second drawback might be the more important one

67
Implementation Details In PostgreSQL Database System code base In the context of TelegraphCQ project Highly efficient implementation [SIGREC’04] Eddy, SteMs, STAIRs export get_next() functions Routing decisions are made per batch Can control batch size Routing decisions made for all possible ready bitmaps Decisions are encoded in arrays that are indexed with ready bits Efficiently find the operator to route to

68
Results - Overheads (1) All plans have identical costs, so adaptivity plays no role

70
Policies used for experiments Routing policy: Observe: Selectivities of predicates on base tables Domain sizes of join attributes Compute join selectivities and use them to route tuples Migration policy: Tie state migration decisions to routing decisions Follow the routing policy decisions to make sure that most tuples are routed correctly Caveats : May end doing migrations late in the query execution May thrash

79
Continous Query Processing Eddies ideal for executing continuous queries over data streams Dynamic runtime conditions make a static plan unsuitable Queries typically executed over sliding windows Find average over last one week Note: Continuous vs Multi-query processing Not identical Data streams literature does not make this difference explicit Application environments tend to have a large number of simultaneous queries

80
Continuous Query Processing CACQ [Madden et al 2002] Focus on sharing work as much as adaptivity Uses SteMs augmented with a deletion operator To handle sliding windows Also uses predicate indexes For handling a large number of queries on the same set of streams but with different predicates E.g. millions of stock alerts over a few streams

82
Some open problems (1) Eddies for continuous query processing Much work since CACQ, but not a solved problem E.g. computational inefficiency of SteMs Many other proposed CQ architectures face the same problem MJoins (NiagaraCQ) Stanford STREAM processor (earlier version)  Later added intermediate result caches Note: These two don’t use eddies explicitly Routing policies for CQ still an open question Different from routing policies for non-CQ queries

83
Some open problems (2) Routing policies Whether eddies will succeed depends on the routing policies Little work so far... SteMs, STAIRs Theoretical analysis of optimization space, and practical viability analysis needed Especially in the context of continuous query processing

84
Some open problems (3) Eddies for multi-query processing (non-CQ) SteMs may be sufficient for CQ processing, but not for normal multi-query processing Parallel, distributed environments, P2P, Grid.. Disk: Flexibility demanded by adaptive techniques at odds against the careful scheduling typically done by DBMSs XJoins Very little work on understanding this

90
SETUP: E and C arrive early; S is delayed Example: Delayed Data Sources time A plan may have to be chosen without any statistical information about the data Earliest time sufficient information may be available to choose optimal plan |S E| |E C| >> S E C