1 / 42

Analyzing Plan Diagrams of Database Query Optimizers

Analyzing Plan Diagrams of Database Query Optimizers. Naveen Reddy Jayant Haritsa Database Systems Lab Indian Institute of Science Bangalore, INDIA. Query Execution Plans. SQL, the standard database query language, is declarative in nature

gretel
Download Presentation

Analyzing Plan Diagrams of Database Query Optimizers

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Analyzing Plan Diagrams of Database Query Optimizers Naveen Reddy Jayant Haritsa Database Systems Lab Indian Institute of Science Bangalore, INDIA

  2. Query Execution Plans • SQL, the standard database query language, is declarative in nature • does not specify how the query should be evaluated • Example: selectStudentName, CourseName from STUDENT, COURSE, REGISTERwhereSTUDENT.RollNo = REGISTER.RollNo andREGISTER.CourseNo = COURSE.CourseNo join order and techniques are left unspecified • DBMS query optimizer identifies efficient execution strategy: “query execution plan” Picasso (VLDB)

  3. RETURN 201,689 HSJOIN 201,689 HSJOIN 26,571 TBSCAN 175,025 TBSCAN 26,512 TBSCAN 50 ORDERS CUSTOMER NATION Example Query and Plan select c_custkey, c_name, c_acctbal, c_address, c_phone, c_commentfrom customer, orders, nationwhere c_custkey = o_custkey and c_nationkey = n_nationkey ando_orderdate < date ('2001-11-09') and n_name = 'IRAQ' Picasso (VLDB)

  4. Query (Q) Query Optimizer (dynamic programming) Minimum CostPlan P(Q) DB catalogs Cost Model Query Plan Selection • Core technique • Cost difference between best plan choice and a sub-optimal choice canbe enormous (orders of magnitude) Picasso (VLDB)

  5. Plan and Cost Diagrams • Given a query, theoptimizer’s plan choiceis a function (among other factors) of the selectivitiesof the base relations participating in the query • selectivity is the estimated number of rows of a relation relevant to producing the final result • Aplan diagramis a pictorial enumeration of the plan choices of a database query optimizer over the relational selectivity space • Acost diagramis a visualization of the associated (estimated) plan execution costs over thesamerelational selectivity space Picasso (VLDB)

  6. Example Query [Q7 of TPC-H] select supp_nation, cust_nation, l_year, sum(volume) as revenue from (select n1.n_name as supp_nation, n2.n_name as cust_nation, extract(year from l_shipdate) as l_year, l_extendedprice * (1 - l_discount) as volume from supplier, lineitem, orders, customer, nation n1, nation n2where s_suppkey = l_suppkey and o_orderkey = l_orderkey and c_custkey = o_custkey and s_nationkey = n1.n_nationkeyand c_nationkey = n2.n_nationkey and ((n1.n_name = 'FRANCE' and n2.n_name = 'GERMANY') or (n1.n_name = 'GERMANY' and n2.n_name = 'FRANCE')) and l_shipdate between date '1995-01-01' and date '1996-12-31' ando_totalprice < C1andc_acctbal < C2 ) as shipping group by supp_nation, cust_nation, l_year order by supp_nation, cust_nation, l_year Picasso (VLDB)

  7. Example Plan Diagram Picasso (VLDB)

  8. Specific Plan Choices Picasso (VLDB)

  9. Example Cost Diagram Picasso (VLDB)

  10. PICASSO

  11. Picasso • A Java tool that, given a query template, automatically generatesplanandcostdiagrams • Fires queries at user-specified granularity (default 100x100 grid) • Currently restricted to 2-D plan diagrams and 3-D cost diagrams • Using the tool, enumerated the plan/cost diagrams produced byindustrial-strengthquery optimizers on TPC-H-based queries • IBM DB2 v8, Oracle 9i and Microsoft SQL Server 2000 • Plan diagrams appear similar tocubist paintings [ Pablo Picasso  founder of the cubist painting genre ] Picasso (VLDB)

  12. Picasso GUI Picasso (VLDB)

  13. Relation Cardinality REGION 5 NATION 25 SUPPLIER 10000 CUSTOMER 150000 PART 200000 PARTSUPP 800000 ORDERS 1500000 LINEITEM 6001215 Testbed Environment • Database • TPC-H database (1 GB scale) representing a manufacturing environment, featuring the following relations: • Query Set • Queries based on TPC-H benchmark [Q1 through Q22] • Uniform 100x100 grid (10000 queries) [0.5%, 0.5%] to [99.5%, 99.5%] • Relational Engines • Default installations (with alloptimization features on) • Stats on all columns, no extra indices • Computational Platform • Pentium-IV 2.4 GHz, 1GB RAM, Windows XP Professional Picasso (VLDB)

  14. RESULTS Optimizers randomly identified as Opt A, Opt B, Opt C NOT intended to make comparisons across optimizers Black-box testing our conclusions are speculative Full result listing at http://dsl.serc.iisc.ernet.in/projects/PICASSO/

  15. Smooth Plan Diagram [Q7, OptB] Picasso (VLDB)

  16. Complex Plan Diagram [Q8, OptA*] Highly irregular plan boundaries Increases to 80 plans with 300x300 grid ! Extremely fine-grained coverage (P68 ~ 0.02%) Intricate Complex Patterns Picasso (VLDB)

  17. Cost Diagram [Q8, OptA*] Picasso (VLDB)

  18. 8 8 31 16% 0.81 31 16% 0.81 25 25% 0.72 25 25% 0.72 38 18% 0.79 38 18% 0.79 9 9 63 9% 0.88 63 9% 0.88 44 27% 0.70 44 27% 0.70 41 12% 0.83 41 12% 0.83 18 5 60% 0.33 13 38% 0.57 5 20% 0.75 18 5 60% 0.33 13 38% 0.57 5 20% 0.75 Skew in Plan Space Coverage Opt A Plan 80% Gini Cardinality Coverage Index Opt B Plan 80% Gini Cardinality Coverage Index Opt C Plan 80% Gini Cardinality Coverage Index TPC-H Query 2 22 18% 0.76 14 21% 0.72 35 20% 0.77 5 21 19% 0.81 14 21% 0.74 18 17% 0.81 7 13 23% 0.73 6 50% 0.46 19 15% 0.79 10 24 16% 0.78 9 22% 0.69 8 25% 0.75 21 27 22% 0.74 6 17% 0.80 22 18% 0.81 Avg(dense) 28.7 17% 0.79 24.5 23% 0.72 28.8 16% 0.79 80-20 Rule Gini index > 0.5 Dense  Plan Cardinality  10 Picasso (VLDB)

  19. Cost Domination Principle Cost of executing any “foreign” query point in the first quadrant of qs is an upper bound on the cost of executing the foreign plan at qs qs Cost of executing qs with Plans P4 and P1 is less than 91and 90, respectively. Cost of Query point qs with plan P2 is 88 Picasso (VLDB)

  20. Cost Domination Principle • Dominating Point Given a pair of distinct points q1 (x1,y1) and q2(x2,y2) in2-D selectivity space, we say that q2 ≻ q1, if and only ifx2 ≥ x1, y2 ≥ y1and result cardinalityRq2 ≥ Rq1 • Cost Domination Principle If points q1 (x1,y1) and q2(x2,y2) are associated with distinct plans P1 and P2respectively, in the original space, the cost of executing query q1 with plan P2 is upper-bounded by the cost of executing q2 with P2, if and only if q2 ≻ q1 Picasso (VLDB)

  21. Plan Swallowing Algorithm • For each query point qs, look for replacements by “foreign” query points that are in the first quadrantrelative to qs as the origin. • For the foreign points that are within λ(e.g. λ=10%) threshold, choose point with lowest cost as potential replacement. • An entire plan is “swallowed” only if all its query points can be swallowed by a single plan or group of plans. • Order the plans in ascending order of size; go up the list, checking for possibility of swallowing each plan. Picasso (VLDB)

  22. Complex Plan Diagram [Q8, OptA*] Reduced Plan Diagram (λ=10%) Reduced to 7plans from 68 Note: Plan Reduction ≠ Change in Optimization Levels Picasso (VLDB)

  23. 8 8 87.6 0.49.4 87.6 0.4 9.4 84.0 0.9 9.1 84.0 0.99.1 86.8 1.2 8.4 86.8 1.28.4 Avg(dense) 67.4 0.9 6.4 56.9 0.7 6.1 71.4 0.9 6.4 Plan Cardinality Reduction by Swallowing Opt A % Avg Max Card Cost Cost Decrease Increase Increase Opt B % Avg Max Card Cost Cost Decrease Increase Increase Opt C % Avg Max Card Cost Cost Decrease Increase Increase TPC-H Query 1.0 2.6 0.1 0.4 1.6 0.8 0.1 0.0 0.9 0.6 0.1 0.4 0.9 1.4 0.5 3.7 0.9 0.7 3.2 0.2 1.1 1.2 2.1 0.4 0.0 0.7 0.9 2 59.2 1.0 4.4 64.2 0.6 5.9 77.1 3.2 6.4 5 67.3 2.6 8.1 42.9 0.1 0.6 61.1 0.2 8.1 7 46.1 0.1 9.5 16.6 0.4 0.7 54.5 1.1 9.5 9 84.4 1.6 8.6 36.4 1.4 8.9 80.5 2.1 8.3 10 67.6 0.8 4.4 44.4 0.5 6.1 62.5 0.4 2.4 18 40.0 0.1 0.5 46.2 3.7 9.6 00.0 0.0 0.0 21 59.8 0.0 0.2 66.7 0.9 2.5 68.2 0.7 6.9 Average Cost Increase < 2% Picasso (VLDB)

  24. Interesting Patterns Duplicates and Islands Plan Switch Points Footprint Pattern Speckle Pattern

  25. Duplicate locations of P3 P18 is an island within P6 Duplicate locations of P10 Duplicates and Islands [Q10, OptA] Picasso (VLDB)

  26. Three duplicates of P7, which are islands within P1 Duplicates and Islands [Q5, OptC] Picasso (VLDB)

  27. Databases Opt A 130 13 38 3 Opt B 80 15 1 0 Opt C 55 7 8 3 Duplicates and Islands Removal • With Plan Reduction by Swallowing,significant decrease in duplicates and islands # Duplicates Original Reduced # Islands Original Reduced Picasso (VLDB)

  28. Plan Switch Points [Q9, OptA] Plan Switch Point:line parallel to axis with a plan shift for all plans bordering the line. Hash-JoinsequencePARTSUPP►◄SUPPLIER►◄PARTis altered to PARTSUPP►◄PART►◄SUPPLIER Picasso (VLDB)

  29. Venetian Blinds [Q9, Opt B] Six plans simultaneously change with rapid alternations to produce a “Venetian blinds” effect. Left-deep hash join across NATION, SUPPLIER and LINEITEM relations gets replaced by a right-deep hash join. Picasso (VLDB)

  30. Footprint Pattern [Q7, Opt A] P7 exhibits a thin broken curved pattern in the middle of P2’s region. P2 has sort-merge-join at the top of the plan tree, while P7 has hash-join Picasso (VLDB)

  31. Speckle Pattern [Q17, Opt A*] An additional sort operation is present on PART relation in P2, whose cost is very low Picasso (VLDB)

  32. Non-Monotonic Cost Behavior Plan-Switch Non-Monotonic Costs Intra-Plan Non-Monotonic Costs

  33. Plan-Switch Non-Monotonic Costs [Q2, Opt A] Presence of Rules? Parameterized changes in search space? 50% Selectivity 26% Selectivity 26%: Cost decreases by a factor of 50 50%:Cost increases by a factor of 70 Cost Diagram Plan Diagram Picasso (VLDB)

  34. Intra-Plan Non-Monotonic Costs [Q21, Opt A] Plans P1, P3, P4 and P6 Nested loops join whose cost decreaseswith increasing input cardinalities Cost Diagram Plan Diagram Picasso (VLDB)

  35. Relationship to PQO

  36. PQO (Parametric Query Optimization) • Active research area for last 15 years • Identify the optimal set of plans for the entire relational selectivity space at compile time • At run time, use actual selectivity values to identify the appropriate plan choice • Assumptions • Plan Convexity: If a plan is optimal at two points, then it is optimal at all points on the straight line joining them • Plan Uniqueness: An optimal plan appears at only one contiguous region in the entire space • Plan Homogeneity: An optimal plan is optimal within the entire region enclosed by its plan boundaries Picasso (VLDB)

  37. Plan uniqueness is violated by P4 Validity of PQO [Q8, Opt A] Plan Homogeneity is violated by P14 Plan Convexity is severely violated by regions covered by P12(dark green) and P16 (light gray). Picasso (VLDB)

  38. Note: • PQO is a more viable proposition in the space of reduced plan diagrams due to the removal of most duplicates and islands Picasso (VLDB)

  39. Conclusions • Conceived and developed the Picasso toolfor automatically generating plan and cost diagrams • Presented and analyzed representative plan and cost diagrams on popular commercial query optimizers • Optimizers make fine grained choices • Complexity of plan diagrams can be drastically reducedwithout materially affecting the query processing quality • Plan optimality regions can have intricate patterns and complex boundaries • Non-Monotonic cost behavior exists where increasing result cardinalities decrease the estimated cost • Basic assumptions underlying research literature on PQO do not hold in practice; but hold approximately for reduced plan diagrams Picasso (VLDB)

  40. Future Work • Extend Picasso to generate higher-dimensionalplan and cost diagrams • Port Picasso to Sybase and PostgreSQL • Conduct a deeper investigation on the query features that result in high plan cardinalities • Investigate whether it is possible to simplify the optimizer, so as to be able to directly produce reducedplan diagrams • Public release of Picasso software (by end-2005) Picasso (VLDB)

  41. QUESTIONS ?

  42. END PICASSO

More Related