Query simplification: graceful degradation for join-order optimization

ABSTRACT

Join ordering is one of the most important, but also most challenging problems of query optimization. In general finding the optimal join order is NP-hard. Existing dynamic programming algorithms exhibit exponential runtime even for the restricted, but highly relevant class of star joins. Therefore, it is infeasible to find the optimal join order when the query includes a large number of joins. Existing approaches for large queries switch to greedy heuristics or randomized algorithms at some point, which can degrade query execution performance by orders of magnitude.

We propose a new paradigm for optimizing large queries: when a query is too complex to be optimized exactly, we simplify the query's join graph until the optimization problem becomes tractable within a given time budget. During simplification, we apply safe simplifications before more risky ones. This way join ordering problems are solved optimally if possible, and gracefully degrade with increasing query complexity.

This paper presents a general framework for query simplification and a strategy for directing the simplification process. Extensive experiments with different kinds of queries, different join-graph structures, and different cost functions indicate that query simplification is very robust and outperforms previous methods for join-order optimization.

REFERENCES

• 1: J. L. Bentley and A. C. C. Yao. An almost optimal algorithm for unbounded searching. Inf. Process. Lett., 5(3):82--87, 1976.
• 2: David DeHaan , Frank Wm. Tompa, Optimal top-down join enumeration, Proceedings of the 2007 ACM SIGMOD international conference on Management of data, June 11-14, 2007, Beijing, China [doi>10.1145/1247480.1247567]
• 3: Leonidas Fegaras, A New Heuristic for Optimizing Large Queries, Proceedings of the 9th International Conference on Database and Expert Systems Applications, p.726-735, August 24-28, 1998
• 4: César A. Galindo-Legaria , Arjan Pellenkoft , Martin L. Kersten, Fast, Randomized Join-Order Selection - Why Use Transformations?, Proceedings of the 20th International Conference on Very Large Data Bases, p.85-95, September 12-15, 1994
• 5: Goetz Graefe , William J. McKenna, The Volcano Optimizer Generator: Extensibility and Efficient Search, Proceedings of the Ninth International Conference on Data Engineering, p.209-218, April 19-23, 1993
• 6: Laura M. Haas , Michael J. Carey , Miron Livny , Amit Shukla, Sing the truth about ad hoc join costs, The VLDB Journal — The International Journal on Very Large Data Bases, v.6 n.3, p.241-256, August 1997 [doi>10.1007/s007780050043]
• 7: Toshihide Ibaraki , Tiko Kameda, On the optimal nesting order for computing N-relational joins, ACM Transactions on Database Systems (TODS), v.9 n.3, p.482-502, Sept. 1984 [doi>10.1145/1270.1498]
• 8: Y. E. Ioannidis , Younkyung Kang, Randomized algorithms for optimizing large join queries, Proceedings of the 1990 ACM SIGMOD international conference on Management of data, p.312-321, May 23-26, 1990, Atlantic City, New Jersey, United States
• 9: A. Kemper, D. Kossmann, and B. Zeller. Performance tuning for SAP R/3. IEEE Data Eng. Bull., 22(2):32--39, 1999.
• 10: Donald Kossmann , Konrad Stocker, Iterative dynamic programming: a new class of query optimization algorithms, ACM Transactions on Database Systems (TODS), v.25 n.1, p.43-82, March 2000 [doi>10.1145/352958.352982]
• 11: Ravi Krishnamurthy , Haran Boral , Carlo Zaniolo, Optimization of Nonrecursive Queries, Proceedings of the 12th International Conference on Very Large Data Bases, p.128-137, August 25-28, 1986
• 12: Chiang Lee , Chi-Sheng Shih , Yaw-Huei Chen, Optimizing Large Join Queries Using A Graph-Based Approach, IEEE Transactions on Knowledge and Data Engineering, v.13 n.2, p.298-315, March 2001 [doi>10.1109/69.917567]
• 13: Guido Moerkotte , Thomas Neumann, Analysis of two existing and one new dynamic programming algorithm for the generation of optimal bushy join trees without cross products, Proceedings of the 32nd international conference on Very large data bases, September 12-15, 2006, Seoul, Korea
• 14: Guido Moerkotte , Thomas Neumann, Dynamic programming strikes back, Proceedings of the 2008 ACM SIGMOD international conference on Management of data, June 09-12, 2008, Vancouver, Canada [doi>10.1145/1376616.1376672]
• 15: Kiyoshi Ono , Guy M. Lohman, Measuring the Complexity of Join Enumeration in Query Optimization, Proceedings of the 16th International Conference on Very Large Data Bases, p.314-325, August 13-16, 1990
• 16: David J. Pearce , Paul H. J. Kelly , Chris Hankin, Online Cycle Detection and Difference Propagation: Applications to Pointer Analysis, Software Quality Control, v.12 n.4, p.311-337, December 2004 [doi>10.1023/B:SQJO.0000039791.93071.a2]
• 17: Jun Rao , Bruce G. Lindsay , Guy M. Lohman , Hamid Pirahesh , David E. Simmen, Using EELs, a Practical Approach to Outerjoin and Antijoin Reordering, Proceedings of the 17th International Conference on Data Engineering, p.585-594, April 02-06, 2001
• 18: P. Griffiths Selinger , M. M. Astrahan , D. D. Chamberlin , R. A. Lorie , T. G. Price, Access path selection in a relational database management system, Proceedings of the 1979 ACM SIGMOD international conference on Management of data, May 30-June 01, 1979, Boston, Massachusetts [doi>10.1145/582095.582099]
• 19: Michael Steinbrunn , Guido Moerkotte , Alfons Kemper, Heuristic and randomized optimization for the join ordering problem, The VLDB Journal — The International Journal on Very Large Data Bases, v.6 n.3, p.191-208, August 1997 [doi>10.1007/s007780050040]
• 20: Arun Swami , Anoop Gupta, Optimization of large join queries, Proceedings of the 1988 ACM SIGMOD international conference on Management of data, p.8-17, June 01-03, 1988, Chicago, Illinois, United States
• 21: Bennet Vance , David Maier, Rapid bushy join-order optimization with Cartesian products, Proceedings of the 1996 ACM SIGMOD international conference on Management of data, p.35-46, June 04-06, 1996, Montreal, Quebec, Canada
• 22: Florian Waas , Arjan Pellenkoft, Join Order Selection - Good Enough Is Easy, Proceedings of the 17th British National Conferenc on Databases: Advances in Databases, p.51-67, July 03-05, 2000

INDEX TERMS

Primary Classification:
  H. Information Systems
  H.2 DATABASE MANAGEMENT
      H.2.4 Systems
          Subjects: Query processing

General Terms:
Algorithms, Theory

Keywords:
complex joins, hypergraphs, query optimization