Survey: Query Optimization Techniques

1
Survey Query Optimization Techniques

• Zhen Zhang

2
What is the survey about?
Factors involved
What composes search space
Cost Model
Search Space
Optimization/Search Algorithm
Techniques used
Optimal Query Evaluation Plan
3
Outline

• Introduction on search space
• A little about cost model
• A small history on query optimization
• Focus on optimization techniques
• Other query paradigms
• Findings interleaved with each part

4
Search Space

• Just for self-containment
• Set of all possible solutions - QEPs for queries
• Algebraic space
• Trees built with algebraic operator
• Define a schedule of operators
• Method-structure space
• Each nodes annotated with implementation method
• Define a execution of query

Hash join
Sequential scan
Index scan
5
Exponential Search Space
Sort merge
join
Sort merge
r4
join
Sort merge
r3
join
r1
r2
6
Cost Model

• Formula to define the cost
• CPU
• Disk access
• Communication
• Different application scenarios have different
  weight for those factors
• Two examples surveyed
• Findings
• Cost model must know the application scenario
• Must capture the dominant part, while ignore
  minute part

7
Statistics supporting cost estimation

• Highly cost model dependent
• Relational database histogram to support
  selectivity
• Approach to gather statistics
• Exhaustive accumulation
• Piggyback
• Sampling
• Pros and Cons

8
A little history

• Dark early days, manual optimization
• Birth of relational data model and declarative
  SQL shed lights
• Bottom up paradigm
• Design algorithm for subset of queries
• Generalize it to include more
• Top down paradigm
• Design a framework for query optimization
• Take advantage of known optimization techniques
  for specific query.
• The later wins

9
Heuristics in Optimization Techniques

• What is heuristic?
• Intuitive right, but not guaranteed.
• Heuristics explored
• Left deep tree
• No cross product
• Selection, projection push down
• Reduce intermediate results

10
Taxonomy

• Polynomial vs. Combinatorial
• Heuristics vs. Systematic
• Deterministic vs. Randomized
• Transformative vs. Constructive
• Orthogonal but related

11
Dynamic Programming

• Constructive
• Systematic
• Deterministic
• Combinatorial

4
3
.
2
.
12
Randomized Algorithm

• Randomized walk
• Uniformed sampling
• Genetic algorithm

Generation 1
Generation 2

13
Heuristic Approach

• Deterministically choose the next relation
  without search
• Criteria of choosing is based on heuristics
• Minimize intermediate result
• KBZ rank the relations somehow

14
Hybrid of heuristic and randomized

• Choose the initial plan with some heuristic
  approach

15
Hybrid of heuristic and Exhaustive

• Exhaustive search yield accuracy
• Heuristic search yield efficiency
• How about combine two
• Adapt to the resource available

16
Findings 1

• Conceptually independent
• Closely related
• Cost model and search algorithm
• Loosely
• impact what kind of heurist
• Search space and search algorithm
• Built-in
• Impact search algorithm greatly

17
Finding 2

• Heuristics are necessary
• What are good heuristics?
• How to use it?
• Purely heuristic
• Combination of heuristic and others
• Exhaustive search in heuristically pruned space

18

• Other Optimization Paradigm

19
Rule based optimizer

• Separation of search algorithm and search space
• Use rules to define the search space
• Generic search algorithms

20
Adaptive Query Optimization

• Interleaving of optimization and execution

Optimizer
Optimizer
partial/initial QEP
QEP
feedback
Executor
Executor
Result
Result
21
Semantic Query Optimization

• semantic based instead of cost based
• Constraint
• if emp.jobsr. programmar
• then emp.sal gt100k
• Query
• select emp.name, dept.floor from emp, dept where
  emp.jobsr.programmar emp.dnodept.dno
• Transform to
• select emp.name, dept.floor from emp, dept where
  emp.jobsr.programmar and emp.salgt100k and
  emp.dnodept.dno

22
Multiple Query Optimization

• Minimize overall cost
• May be suboptimal for individual query

Query 2
Query 3
Query
Query 1
Query 4
QEP
QEPs