skip to main content
10.1145/1559845.1559955acmconferencesArticle/Chapter ViewAbstractPublication PagesmodConference Proceedingsconference-collections
research-article

Query optimizers: time to rethink the contract?

Published:29 June 2009Publication History

ABSTRACT

Query Optimization is expected to produce good execution plans for complex queries while taking relatively small optimization time. Moreover, it is expected to pick the execution plans with rather limited knowledge of data and without any additional input from the application. We argue that it is worth rethinking this prevalent model of the optimizer. Specifically, we discuss how the optimizer may benefit from leveraging rich usage data and from application input. We conclude with a call to action to further advance query optimization technology.

References

  1. Selinger, P. et.al.: Access Path Selection in a Relational Database Management System. SIGMOD 1979: 23--34 Google ScholarGoogle ScholarDigital LibraryDigital Library
  2. Graefe, G.: The Cascades Framework for Query Optimization. IEEE Data Eng. Bull. 18(3): 19--29 (1995)Google ScholarGoogle Scholar
  3. Pirahesh, H., Hellerstein, J.M., Hasan, W.: Extensible/Rule Based Query Rewrite Optimization in Starburst. SIGMOD Conference 1992: 39--48 Google ScholarGoogle ScholarDigital LibraryDigital Library
  4. Aboulnaga, A., Chaudhuri, S.: Self-tuning Histograms: Building Histograms Without Looking at Data. SIGMOD 1999: 181--192 Google ScholarGoogle ScholarDigital LibraryDigital Library
  5. Srivastava, U. et.al. ISOMER: Consistent Histogram Construction Using Query Feedback. ICDE 2006: 39 Google ScholarGoogle ScholarDigital LibraryDigital Library
  6. Stillger, M. et.al.: LEO -- DB2's LEarning Optimizer. VLDB 2001: 19--28 Google ScholarGoogle ScholarDigital LibraryDigital Library
  7. Haas, P. J. et al.: Selectivity and Cost Estimation for Joins Based on Random Sampling. JCSS. 52(3): 550--569 (1996) Google ScholarGoogle ScholarDigital LibraryDigital Library
  8. Kabra, N., DeWitt, D.: Efficient Mid-Query Re-Optimization of Sub-Optimal Query Execution Plans. SIGMOD 1998 Google ScholarGoogle ScholarDigital LibraryDigital Library
  9. Avnur, R., Hellerstein, J. M.: Eddies: Continuously Adaptive Query Processing SIGMOD Conference 2000: 261--272 Google ScholarGoogle ScholarDigital LibraryDigital Library
  10. Zilberstein, S.: Using Anytime Algorithms in Intelligent Systems. AI Magazine 17(3): 73--83 (1996)Google ScholarGoogle Scholar
  11. Ioannidis, Y.E., Kang, Y.C.: Randomized Algorithms for Optimizing Large Join Queries. SIGMOD Conference 1990 Google ScholarGoogle ScholarDigital LibraryDigital Library
  12. Mayrhofer, R.: Generic Heuristics for Combinatorial Optimization Problems. Proc. of the 9th International Conference on Operational Research 2002Google ScholarGoogle Scholar
  13. Markl, V. et.al. Consistently Estimating the Selectivity of Conjuncts of Predicates. VLDB 2005: 373--384 Google ScholarGoogle ScholarDigital LibraryDigital Library
  14. Bruno, N., Chaudhuri, S., Ramamurthy, R.: Power Hints for Query Optimization. IEEE ICDE 2009 Google ScholarGoogle ScholarDigital LibraryDigital Library
  15. Chaudhuri, S., Narasayya V.R., Ramamurthy R.: A Pay-As-You-Go framework for Query Execution Feedback. VLDB 2008 Google ScholarGoogle ScholarDigital LibraryDigital Library
  16. Deshpande, A., Ives, Z. G., Raman, V.: Adaptive Query Processing. Foundations and Trends in Databases, 2007 Google ScholarGoogle ScholarDigital LibraryDigital Library
  17. Ioannidis, Y.E.: The History of Histograms (abridged). VLDB 2003: 19--30 Google ScholarGoogle ScholarDigital LibraryDigital Library
  18. Antoshenkov, G. Dynamic Query Optimization in Rdb/VMS, IEEE ICDE 1993 Google ScholarGoogle ScholarDigital LibraryDigital Library
  19. Cole, R.L., Graefe, G.: Optimization of Dynamic Query Evaluation Plans. SIGMOD Conference 1994: 150--160 Google ScholarGoogle ScholarDigital LibraryDigital Library
  20. Urhan, T. Franklin, M.J., Amsaleg, L.: Cost Based Query Scrambling for Initial Delays. SIGMOD Conference 1998 Google ScholarGoogle ScholarDigital LibraryDigital Library
  21. Chaudhuri, S.: An Overview of Query Optimization in Relational Systems. PODS 1998: 34--43 Google ScholarGoogle ScholarDigital LibraryDigital Library
  22. Ioannidis, Y.E., Christodoulakis, S.: On the Propagation of Errors in the Size of Join Results. SIGMOD 1991: 268--277 Google ScholarGoogle ScholarDigital LibraryDigital Library
  23. Acharya, S. et.al. : Join Synopses for Approximate Query Answering. SIGMOD Conference 1999: 275--286 Google ScholarGoogle ScholarDigital LibraryDigital Library
  24. Chaudhuri, S., Motwani, R., Narasayya, V.R.: On Random Sampling over Joins. SIGMOD Conference 1999: 263--274 Google ScholarGoogle ScholarDigital LibraryDigital Library
  25. Reddy, N., Haritsa, J.: Analyzing Plan Diagrams of Database Query Optimizers. VLDB 2005: 1228--1240 Google ScholarGoogle ScholarDigital LibraryDigital Library
  26. Harish D., Darera, P.N., Haritsa, J: On the Production of Anorexic Plan Diagrams. VLDB 2007: 1081--1092 Google ScholarGoogle ScholarDigital LibraryDigital Library
  27. Olken F., Rotem D.: Random Sampling from Database Files: A Survey. SSDBM 1990: 92--111 Google ScholarGoogle ScholarDigital LibraryDigital Library
  28. Galindo-Legaria C., et.al.: Statistics on Views. VLDB 2003 Google ScholarGoogle ScholarDigital LibraryDigital Library
  29. Bruno N., Chaudhuri S.: Exploiting statistics on query expressions for optimization. SIGMOD 2002: 263--274 Google ScholarGoogle ScholarDigital LibraryDigital Library
  30. Lothar F. Mackert, Guy M. Lohman: R* Optimizer Validation and Performance Evaluation for Local Queries. SIGMOD 86 Google ScholarGoogle ScholarDigital LibraryDigital Library
  31. Dageville B. et.al.: Automatic SQL Tuning in Oracle 10g. VLDB 2004 Google ScholarGoogle ScholarDigital LibraryDigital Library
  32. Getoor L., Taskar B., and Koller D.: (2001). Using Probabilistic Models for Selectivity Estimation. SIGMOD 2001 Google ScholarGoogle ScholarDigital LibraryDigital Library
  33. Chaudhuri S., Narasayya V.R., Ramamurthy R.: Diagnosing Estimation Errors in Page Counts Using Execution Feedback. ICDE 2008. Google ScholarGoogle ScholarDigital LibraryDigital Library
  34. Babcock B., Chaudhuri S.: Towards a Robust Query Optimizer: A Principled and Practical Approach. SIGMOD 2005. Google ScholarGoogle ScholarDigital LibraryDigital Library

Index Terms

  1. Query optimizers: time to rethink the contract?

      Recommendations

      Comments

      Login options

      Check if you have access through your login credentials or your institution to get full access on this article.

      Sign in
      • Published in

        cover image ACM Conferences
        SIGMOD '09: Proceedings of the 2009 ACM SIGMOD International Conference on Management of data
        June 2009
        1168 pages
        ISBN:9781605585512
        DOI:10.1145/1559845

        Copyright © 2009 ACM

        Permission to make digital or hard copies of all or part of this work for personal or classroom use is granted without fee provided that copies are not made or distributed for profit or commercial advantage and that copies bear this notice and the full citation on the first page. Copyrights for components of this work owned by others than ACM must be honored. Abstracting with credit is permitted. To copy otherwise, or republish, to post on servers or to redistribute to lists, requires prior specific permission and/or a fee. Request permissions from [email protected]

        Publisher

        Association for Computing Machinery

        New York, NY, United States

        Publication History

        • Published: 29 June 2009

        Permissions

        Request permissions about this article.

        Request Permissions

        Check for updates

        Qualifiers

        • research-article

        Acceptance Rates

        Overall Acceptance Rate785of4,003submissions,20%

      PDF Format

      View or Download as a PDF file.

      PDF

      eReader

      View online with eReader.

      eReader