CPS216: Advanced Database Systems Notes 08:Query Optimization (Plan Space, Query Rewrites)

1. CPS216: Advanced Database SystemsNotes 08:Query Optimization (Plan Space, Query Rewrites) Shivnath Babu

2. SQL query parse parse tree Query rewriting statistics logical query plan Physical plan generation physical query plan execute result Query Processing - In class order 2; 16.1 3; 16.2,16.3 4; 16.4—16.7 1; 13, 15

3. Roadmap • Query optimization: problem definition • Space of physical plans • Counting exercise • Approaches for query optimization • Heuristic-based (Oracle calls them rule-based) • Cost-based • Hybrid • Heuristics for query optimization (Query rewrites)

4. Query Optimization Problem Pick the best plan from the space of physical plans

5. The Space of Physical Plans is Very Large • Algebraic equivalences • Different physical operators for the same logical operator • nested loop join, hash join, sort-merge join • index-scan, table-scan • Different plumbing details - pipelining vs. materialization • Different tree shapes

6. A Plan Counting Exercise • Work on blackboard

7. Approaches for Query Optimization • Approach 1: Pick some plan • Bad plans can be really bad! • Approach 2: Heuristics • Ex: maximize use of indexes (MySQL) • Approach 3: Cost-based • “Enumerate”, find cost, pick best • Be smart about how you iterate through the plans. Why? • Hybrid

8. Query Optimization in Practice • Hybrid • Use heuristics, called query rewrite rules • eliminate many of the really bad plans • avoid eliminating good plans • Cost-based • Be smart about how you iterate through plans • Ex: dynamic programming, genetic search

9. SQL query Initial logical plan parse Rewrite rules parse tree Query rewriting Logical plan statistics logical query plan Physical plan generation “Best” logical plan physical query plan execute result

10. Why do we need Query Rewriting? • Pruning the HUGE space of physical plans • Eliminating redundant conditions/operators • Rules that will improve performance with very high probability • Preprocessing • Getting queries into a form that we know how to handle best • Reduces optimization time drastically without noticeably affecting quality

11. Query Rewrite Rules • Transform one logical plan into another • Do not use statistics • Equivalences in relational algebra • Push-down predicates • Do projects early • Avoid cross-products if possible • Use left-deep trees • Use of constraints, e.g., uniqueness

12. Example Query Select B,D From R,S Where R.A = “c”  R.C=S.C

13. Example: Parse Tree <Query> <SFW> SELECT <SelList> FROM <FromList> WHERE <Cond> <Attribute> <SelList> <RelName> <FromList> <Cond> AND <Cond> B <Attribute> R <RelName> <Attr> <Op> <Const> D S R.A = “c” Select B,D From R,S Where R.A = “c”  R.C=S.C <Attr> <Op> <Attr> R.C = S.C

14. Along with Parsing … • Semantic checks • Do the projected attributes exist in the relations in the From clause? • Ambiguous attributes? • Type checking, ex: R.A > 17.5 • Expand views

15. Initial Logical Plan B,D Select B,D From R,S Where R.A = “c”  R.C=S.C R.A = “c” Λ R.C = S.C X R S Relational Algebra: B,D [sR.A=“c” R.C = S.C (RXS)]

16. Apply Rewrite Rule (1) B,D B,D R.C = S.C R.A = “c” Λ R.C = S.C R.A = “c” X X R S R S B,D [sR.C=S.C [R.A=“c”(R X S)]]

17. Apply Rewrite Rule (2) B,D B,D R.C = S.C R.C = S.C R.A = “c” X R.A = “c” S X R S R B,D [sR.C=S.C [R.A=“c”(R)] X S]

18. Apply Rewrite Rule (3) B,D B,D R.C = S.C Natural join R.A = “c” X S R.A = “c” S R R B,D [[R.A=“c”(R)] S]

19. Equivalences in Relational Algebra R S = S R Commutativity (R S) T = R (S T) Associativity Also holds for: Cross Products, Union, Intersection R x S = S x R (R x S) x T = R x (S x T) R U S = S U R R U (S U T) = (R U S) U T

20. Rules: Project Let: X = set of attributes Y = set of attributes XY = X U Y pxy (R) = px [py (R)]

21. [sp (R)] S R [sq (S)] Rules:s + combined Let p = predicate with only R attribs q = predicate with only S attribs m = predicate with only R,S attribs sp (R S) = sq (R S) =

22. Rules:s + combined (continued) spq (R S) = [sp (R)] [sq (S)] spqm (R S) = sm[(sp R) (sq S)] spvq (R S) = [(sp R) S] U [R(sq S)]

23. Which are “good” transformations? sp1p2 (R) sp1 [sp2 (R)] sp (R S)  [sp (R)] S R S  S R px [sp(R)] px {sp [pxz(R)]}

24. Conventional wisdom: do projects early Example: R(A,B,C,D,E) x={E} P: (A=3)  (B=“cat”) px {sp(R)} vs. pE {sp{pABE(R)}}

25. But: What if we have A, B indexes? B = “cat” A=3 Intersect pointers to get pointers to matching tuples

26. Bottom line: • No transformation is always good • Some are usually good: • Push selections down • Avoid cross-products if possible • Subqueries  Joins

27. More Query Rewrite Rules • Transform one logical plan into another • Do not use statistics • Equivalences in relational algebra • Push-down predicates • Do projects early • Avoid cross-products if possible • Use left-deep trees • Subqueries  Joins • Use of constraints, e.g., uniqueness

28. Avoid Cross Products (if possible) • Which join trees avoid cross-products? • If you can't avoid cross products, perform them as late as possible Select B,D From R,S,T,U Where R.A = S.B  R.C=T.C  R.D = U.D

29. Use Left Deep Plans Select B,D From R,S,T,U Where R.A = S.A  R.A=T.AR.A = U.A • What are some left-deep, right-deep, and bushy plans for this query? • Why is this heuristic useful? • Reason #1: We maximize the possibility of using indexes • Reason #2: Better for nested-loop join • What about hash joins? • Homework: Construct examples where (i) right-deep plan is best, (ii) where bushy is best

30. More Query Rewrite Rules • Transform one logical plan into another • Do not use statistics • Equivalences in relational algebra • Push-down predicates • Do projects early • Avoid cross-products if possible • Use left-deep trees • Subqueries  Joins • Use of constraints, e.g., uniqueness

31. SQL Query with an Uncorrelated Subquery Find the movies with stars born in 1960 MovieStar(name, address, gender, birthdate) StarsIn(title, year, starName) SELECT title FROM StarsIn WHERE starName IN ( SELECT name FROM MovieStar WHERE birthdate LIKE ‘%1960’ );

32. Parse Tree <Query> <SFW> SELECT <SelList> FROM <FromList> WHERE <Condition> <Attribute> <RelName> <Tuple> IN <Query> title StarsIn <Attribute> ( <Query> ) starName <SFW> SELECT <SelList> FROM <FromList> WHERE <Condition> <Attribute> <RelName> <Attribute> LIKE <Pattern> name MovieStar birthDate ‘%1960’

33. title Two-argument selection  StarsIn <condition> <tuple> IN name <attribute> birthdate LIKE ‘%1960’ starName MovieStar Generating Relational Algebra

34. Rewrite Rule for Two-argument Selection with Conditions Involving IN Two-argument selection  <condition>  X Lexp <condition> Lexp δ <tuple> IN Rexp Rexp

35. title starName=name  StarsInδ name birthdate LIKE ‘%1960’ MovieStar Applying the Rewrite Rule title  StarsIn <condition> <tuple> IN name <attribute> birthdate LIKE ‘%1960’ starName MovieStar

36. title title starName=name starName=name  StarsIn name StarsInδ birthdate LIKE ‘%1960’ name birthdate LIKE ‘%1960’ MovieStar MovieStar Improving the Logical Query Plan

37. SQL Query with an Correlated Subquery MovieStar(name, address, gender, birthdate) StarsIn(title, year, starName) SELECT title FROM StarsIn WHERE starName IN ( SELECT name FROM MovieStar WHERE name LIKE ‘Tom%’ and year = birthdate + 30 );

38. SQL query parse parse tree Query rewriting statistics logical query plan Physical plan generation physical query plan execute result Query Processing - In class order 2; 16.1 3; 16.2,16.3 4; 16.4—16.7 1; 13, 15