Proactive Re-Optimization

1
Proactive Re-Optimization

• Shivnath Babu, Pedo Bizarro, David DeWitt
• SIGMOD 2005
• (presented by Steve Blundy Oleg Rekutin)

2
Overview

• Whats wrong with reactive?
• Proactive via 3 core techniques
• Experiments

3
Reactive Re-optimization

• select from R, S where R.aS.a and R.bgtK1 and
  R.cgtK2

s
s(R) actual
A
buffer
!
s(R) estimated
B
!
4
Single-Point Limitation
A
B
5
Limited Information for Re-opt

• select from R, S, T where R.aS.a and S.bT.b
  and R.cgtK1 and R.dK2

s(R) act
!
!
!
s(R) est
6
Choosing a plan

1. Compute bounding boxes
2. Use them to generate robust plans and switchable
   plans
3. Use randomization to collect statistics

7
Bounding Boxes

• Representing Uncertainty in Statistics
• Are the upper and lower bounds for each estimated
  statistic

8
Bounding Boxes
9
Optimal Plan

• 1 Plan is optimal for all 3 points
• Choice is easy

10
Robust Plan

• 1 plan is, or close to, optimal for all 3 points
• 1 plan can be safely chosen

11
Switchable Plan

• There is a plan with close to optimal cost plan
  at each point
• Additional Requirements
• The decision can be deferred
• Actual statistics lie must within bounding box
• It is possible to switch between the plans

12
What is a Switchable Plan

• Any two members of a switchable plan are said to
  be switchable with each other.

13
Collecting statistics

1. Each operator collects some in buffer
2. The eos(f) is emitted statistics are calculated
3. Plan is chosen from switch plan members or
   re-optimization is run
4. Query processing proceeds

14
Questions

• Prevalence of switchable plans vs. case 4
• How good is Rho at preventing re-optimizations
• How is Rho affected by large estimates

15
Experiments

• Traditional Optimizer (TRAD)
• Validity-Ranges Optimizer (VRO)

16
2-Way Join Queries Robust
s(A) est
17
2-Way Join Queries Switchable
s(A) est
s(A) b. box
18
3-Way Join Example

• Shows the use of a Switchable Plan
• Some re-optimization still necessary

19
Pt s1(A) TRAD VRO Rio Opt
A 6 MB P17a Inside range, P17a Outside box, re-optimize, P17a P17a
B 80 MB P17a Inside range, P17a Inside box, P17a P17a
C 160 MB P17a Outside range, re-optimize, P17d Inside box, P17d P17b
D 310 MB P17a Outside range, re-optimize, P17d Outside box, re-optimize, P17b P17b
20
(No Transcript)
21
Correlation-Based Mistakes
22
Query Complexity
23
Conclusion

• Rho refines statistics and uses switchable plans
  to forestall re-optimizations and prevent partial
  data loss
• Questions?