1 / 23

Proactive Re-Optimization

Proactive Re-Optimization. Shivnath Babu, Pedo Bizarro, David DeWitt SIGMOD 2005 (presented by Steve Blundy & Oleg Rekutin). Overview. What’s wrong with reactive? Proactive via 3 core techniques Experiments. Reactive Re-optimization.

lovie
Download Presentation

Proactive Re-Optimization

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Proactive Re-Optimization Shivnath Babu, Pedo Bizarro, David DeWitt SIGMOD 2005 (presented by Steve Blundy & Oleg Rekutin)

  2. Overview • What’s wrong with reactive? • Proactive via 3 core techniques • Experiments

  3. Reactive Re-optimization selectfrom R, S whereR.a=S.a and R.b>K1and R.c>K2 σ σ(R) actual A: buffer ! σ(R) estimated B: !

  4. Single-Point Limitation A: B:

  5. selectfrom R, S, T whereR.a=S.a and S.b=T.b and R.c>K1and R.d=K2 Limited Information for Re-opt σ(R) act ! ! ! σ(R) est

  6. Choosing a plan • Compute bounding boxes • Use them to generate robust plans and switchable plans • 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 • Each operator collects some % in buffer • The eos(f) is emitted & statistics are calculated • Plan is chosen from switch plan members or re-optimization is run • 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 σ(A) est

  17. 2-Way Join Queries: Switchable σ(A) est σ(A) b. box

  18. 3-Way Join Example • Shows the use of a Switchable Plan • Some re-optimization still necessary

  19. Correlation-Based Mistakes

  20. Query Complexity

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

More Related