robust query processing through progressive optimization volker markl, vijayshankar raman, david...
Post on 21-Dec-2015
216 views
TRANSCRIPT
![Page 1: Robust Query Processing through Progressive Optimization Volker Markl, Vijayshankar Raman, David Simmen, Guy Lohman, Hamid Pirahesh, Miso Cilimdzic Presented](https://reader036.vdocuments.us/reader036/viewer/2022062516/56649d595503460f94a38e45/html5/thumbnails/1.jpg)
Robust Query Processing through Progressive OptimizationVolker Markl, Vijayshankar Raman, David Simmen, Guy Lohman,
Hamid Pirahesh, Miso Cilimdzic
Presented by Duc Duong and Aruna Apuri
![Page 2: Robust Query Processing through Progressive Optimization Volker Markl, Vijayshankar Raman, David Simmen, Guy Lohman, Hamid Pirahesh, Miso Cilimdzic Presented](https://reader036.vdocuments.us/reader036/viewer/2022062516/56649d595503460f94a38e45/html5/thumbnails/2.jpg)
Motivation
Current optimizers depend heavily upon the cardinality estimations
What if there errors in those estimations? Errors can occur due to …
Inaccurate statistics Invalid assumptions (e.g. attribute independence)
![Page 3: Robust Query Processing through Progressive Optimization Volker Markl, Vijayshankar Raman, David Simmen, Guy Lohman, Hamid Pirahesh, Miso Cilimdzic Presented](https://reader036.vdocuments.us/reader036/viewer/2022062516/56649d595503460f94a38e45/html5/thumbnails/3.jpg)
Overview of Talk …
Contribution of the paper Progressive Query Optimization(POP) CHECK and its variants Performance analysis A real-world experiment results
![Page 4: Robust Query Processing through Progressive Optimization Volker Markl, Vijayshankar Raman, David Simmen, Guy Lohman, Hamid Pirahesh, Miso Cilimdzic Presented](https://reader036.vdocuments.us/reader036/viewer/2022062516/56649d595503460f94a38e45/html5/thumbnails/4.jpg)
Contribution
Concept of CHECK and its various flavors Method for determining validity ranges for
QEPs Performance analysis of prototype of POP
![Page 5: Robust Query Processing through Progressive Optimization Volker Markl, Vijayshankar Raman, David Simmen, Guy Lohman, Hamid Pirahesh, Miso Cilimdzic Presented](https://reader036.vdocuments.us/reader036/viewer/2022062516/56649d595503460f94a38e45/html5/thumbnails/5.jpg)
Evaluating a Re-optimization Scheme Risk Vs Opportunity Risk:
Extent to which re-optimization is not worthwhile leads to performance regression.
Regression may occur when Re-optimization of query results in selection of same or even worse plan.
Regression may occur when Query execution needs to be repeated
![Page 6: Robust Query Processing through Progressive Optimization Volker Markl, Vijayshankar Raman, David Simmen, Guy Lohman, Hamid Pirahesh, Miso Cilimdzic Presented](https://reader036.vdocuments.us/reader036/viewer/2022062516/56649d595503460f94a38e45/html5/thumbnails/6.jpg)
Evaluating a re-optimization scheme Risk Vs Opportunity Opportunity: Refers to the aggressiveness Higher the number of CHECK operators
higher the opportunity for re-optimization Opportunity directly correlated to risk
![Page 7: Robust Query Processing through Progressive Optimization Volker Markl, Vijayshankar Raman, David Simmen, Guy Lohman, Hamid Pirahesh, Miso Cilimdzic Presented](https://reader036.vdocuments.us/reader036/viewer/2022062516/56649d595503460f94a38e45/html5/thumbnails/7.jpg)
Progressive Query Optimization(POP)
![Page 8: Robust Query Processing through Progressive Optimization Volker Markl, Vijayshankar Raman, David Simmen, Guy Lohman, Hamid Pirahesh, Miso Cilimdzic Presented](https://reader036.vdocuments.us/reader036/viewer/2022062516/56649d595503460f94a38e45/html5/thumbnails/8.jpg)
Architecture of POP 1
Find out valid ranges Location of CHECKs Executing CHECKs Interpret CHECK Exploit intermediate results
![Page 9: Robust Query Processing through Progressive Optimization Volker Markl, Vijayshankar Raman, David Simmen, Guy Lohman, Hamid Pirahesh, Miso Cilimdzic Presented](https://reader036.vdocuments.us/reader036/viewer/2022062516/56649d595503460f94a38e45/html5/thumbnails/9.jpg)
Architecture of POP 2
![Page 10: Robust Query Processing through Progressive Optimization Volker Markl, Vijayshankar Raman, David Simmen, Guy Lohman, Hamid Pirahesh, Miso Cilimdzic Presented](https://reader036.vdocuments.us/reader036/viewer/2022062516/56649d595503460f94a38e45/html5/thumbnails/10.jpg)
Computation on Validity Ranges Validity range: is an upper and lower bound
which when violated, guarantees that the current plan is sub-optimal wrt to the optimizers cost model
No need to enumerate all possible optimal plans beforehand
Uses modified Newton-Raphson method to find validity ranges
![Page 11: Robust Query Processing through Progressive Optimization Volker Markl, Vijayshankar Raman, David Simmen, Guy Lohman, Hamid Pirahesh, Miso Cilimdzic Presented](https://reader036.vdocuments.us/reader036/viewer/2022062516/56649d595503460f94a38e45/html5/thumbnails/11.jpg)
Exploiting Intermediate Results All the intermediate results are stored as
temporary MVs Not necessarily written out to disk In the end, all these temporary MVs needs to
be deleted (extra overhead?)
![Page 12: Robust Query Processing through Progressive Optimization Volker Markl, Vijayshankar Raman, David Simmen, Guy Lohman, Hamid Pirahesh, Miso Cilimdzic Presented](https://reader036.vdocuments.us/reader036/viewer/2022062516/56649d595503460f94a38e45/html5/thumbnails/12.jpg)
Variants of CHECK
Lazy checking Lazy checking with eager materialization Eager checking without compensation Eager checking with buffering Eager checking with deferred compensation
![Page 13: Robust Query Processing through Progressive Optimization Volker Markl, Vijayshankar Raman, David Simmen, Guy Lohman, Hamid Pirahesh, Miso Cilimdzic Presented](https://reader036.vdocuments.us/reader036/viewer/2022062516/56649d595503460f94a38e45/html5/thumbnails/13.jpg)
Lazy Checking
Adding CHECKs above a materialization point (SORT, TEMP etc)
As, no results have been output yet And materialized results can be re-used
![Page 14: Robust Query Processing through Progressive Optimization Volker Markl, Vijayshankar Raman, David Simmen, Guy Lohman, Hamid Pirahesh, Miso Cilimdzic Presented](https://reader036.vdocuments.us/reader036/viewer/2022062516/56649d595503460f94a38e45/html5/thumbnails/14.jpg)
Lazy checking with eager materialization Insert materialization point if it does not exists
already Typically done only for nested-loop join
![Page 15: Robust Query Processing through Progressive Optimization Volker Markl, Vijayshankar Raman, David Simmen, Guy Lohman, Hamid Pirahesh, Miso Cilimdzic Presented](https://reader036.vdocuments.us/reader036/viewer/2022062516/56649d595503460f94a38e45/html5/thumbnails/15.jpg)
Eager Checking
EC without Compensation: CHECK is pushed down the MP
EC with buffering CHECK and buffer
![Page 16: Robust Query Processing through Progressive Optimization Volker Markl, Vijayshankar Raman, David Simmen, Guy Lohman, Hamid Pirahesh, Miso Cilimdzic Presented](https://reader036.vdocuments.us/reader036/viewer/2022062516/56649d595503460f94a38e45/html5/thumbnails/16.jpg)
EC with Deferred Compensation Only SPJ queries Identifier of all rows returned to the user are
stored in a table S, which is used later in the new plan for anti-join with the new-result stream
![Page 17: Robust Query Processing through Progressive Optimization Volker Markl, Vijayshankar Raman, David Simmen, Guy Lohman, Hamid Pirahesh, Miso Cilimdzic Presented](https://reader036.vdocuments.us/reader036/viewer/2022062516/56649d595503460f94a38e45/html5/thumbnails/17.jpg)
CHECK Placement
![Page 18: Robust Query Processing through Progressive Optimization Volker Markl, Vijayshankar Raman, David Simmen, Guy Lohman, Hamid Pirahesh, Miso Cilimdzic Presented](https://reader036.vdocuments.us/reader036/viewer/2022062516/56649d595503460f94a38e45/html5/thumbnails/18.jpg)
Performance Analysis
Robustness
![Page 19: Robust Query Processing through Progressive Optimization Volker Markl, Vijayshankar Raman, David Simmen, Guy Lohman, Hamid Pirahesh, Miso Cilimdzic Presented](https://reader036.vdocuments.us/reader036/viewer/2022062516/56649d595503460f94a38e45/html5/thumbnails/19.jpg)
Risk Analysis
Risk Analysis
![Page 20: Robust Query Processing through Progressive Optimization Volker Markl, Vijayshankar Raman, David Simmen, Guy Lohman, Hamid Pirahesh, Miso Cilimdzic Presented](https://reader036.vdocuments.us/reader036/viewer/2022062516/56649d595503460f94a38e45/html5/thumbnails/20.jpg)
Opportunity Analysis
![Page 21: Robust Query Processing through Progressive Optimization Volker Markl, Vijayshankar Raman, David Simmen, Guy Lohman, Hamid Pirahesh, Miso Cilimdzic Presented](https://reader036.vdocuments.us/reader036/viewer/2022062516/56649d595503460f94a38e45/html5/thumbnails/21.jpg)
POP in Action
22 Vs 17
![Page 22: Robust Query Processing through Progressive Optimization Volker Markl, Vijayshankar Raman, David Simmen, Guy Lohman, Hamid Pirahesh, Miso Cilimdzic Presented](https://reader036.vdocuments.us/reader036/viewer/2022062516/56649d595503460f94a38e45/html5/thumbnails/22.jpg)
Conclusions
POP gives us a robust mechanism for re-optimization through inserting of CHECK (in its various flavors)
Higher opportunity at low risk
![Page 23: Robust Query Processing through Progressive Optimization Volker Markl, Vijayshankar Raman, David Simmen, Guy Lohman, Hamid Pirahesh, Miso Cilimdzic Presented](https://reader036.vdocuments.us/reader036/viewer/2022062516/56649d595503460f94a38e45/html5/thumbnails/23.jpg)
Reference
Volker Markl, Vijayshankar Raman, David Simmen, Guy Lohman, Hamid Pirahesh, Miso Cilimdzic, Robust Query Processing through Progressive Optimization, SIGMOD 2004, June 13–18, 2004, Paris, France.
www.cse.iitb.ac.in/dbms/Data/Courses/CS632/Talks/POP.ppt