Randomized Algorithms for optimizing large join queries

1
Randomized Algorithms for optimizing large join
queries
2
Algorithms

• Iterative improvement
• start at a random state
• move to random neighbor with less cost until at
  local minimum
• Simulated annealing
• start at a random state
• start at initial temperature
• move to a random neighbor
• if neighbor is cheaper, move
• if neighbor is more expensive move with some
  probability (controlled by temperature and cost)
• once the algorithm has reached equilibrium, lower
  temperature and repeat
• Two Phase Optimization
• Do Iterative Improvement for awhile
• Do SA using the output of II as a starting state

3
State space

• all possible execution strategies of a query form
  a state space
• a strategy is a join processing tree
• differences between strategies
• join order
• join method
• each point is connected to other points
• neighbors are a single change in strategy
• join method
• commutativity
• associativity
• left join exchange
• right join exchange
• each point in the space has an associated cost
  I/O cost only

4
Evaluation

• tree queries and star queries
• 5-100 joins
• 3 types of relation catalogs
• II is okay
• SA is bad at first, good in the long run
• 2PO is best of both worlds

5
Analysis

• Query size
• small queries (5-10) show no difference
• Variance in catalog parameters
• differences increase as size and variety of
  relations increase
• relation size has more impact than selectivity

6
State Space analysis

• Its a cup/ well