Progressive Optimization: A Robust Approach to Query Re-optimization, Slides of Database Management Systems (DBMS)

Download Progressive Optimization: A Robust Approach to Query Re-optimization and more Slides Database Management Systems (DBMS) in PDF only on Docsity!

Robust Query Processing through

Progressive Optimization

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)

Contribution

• Concept of CHECK and its various flavors
• Method for determining validity ranges for QEPs
• Performance analysis of prototype of POP

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

Progressive Query Optimization(POP)

Architecture of POP 1

• Find out valid ranges
• Location of CHECKs
• Executing CHECKs
• Interpret CHECK
• Exploit intermediate results

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

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?)

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

Lazy checking with eager materialization

• Insert materialization point if it does not exists already
• Typically done only for nested-loop join

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

CHECK Placement

Risk Analysis

• Risk Analysis

Opportunity Analysis