Query optimizers: time to rethink the contract?

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.

[1]  Patricia G. Selinger,et al.  Access path selection in a relational database management system , 1979, SIGMOD '79.

[2]  Guy M. Lohman,et al.  R* optimizer validation and performance evaluation for local queries , 1986, SIGMOD '86.

[3]  Donald D. Chamberlin,et al.  Access Path Selection in a Relational Database Management System , 1989 .

[4]  Yannis E. Ioannidis,et al.  Randomized algorithms for optimizing large join queries , 1990, SIGMOD '90.

[5]  Doron Rotem,et al.  Random Sampling from Database Files: A Survey , 1990, SSDBM.

[6]  Stavros Christodoulakis,et al.  On the propagation of errors in the size of join results , 1991, SIGMOD '91.

[7]  Hamid Pirahesh,et al.  Extensible/rule based query rewrite optimization in Starburst , 1992, SIGMOD '92.

[8]  G. Antoshenkov,et al.  Dynamic query optimization in Rdb/VMS , 1993, Proceedings of IEEE 9th International Conference on Data Engineering.

[9]  Goetz Graefe,et al.  Optimization of dynamic query evaluation plans , 1994, SIGMOD '94.

[10]  G. Graefe.  The Cascades Framework for Query Optimization , 1995, IEEE Data Eng. Bull..

[11]  Jeffrey F. Naughton,et al.  Selectivity and Cost Estimation for Joins Based on Random Sampling , 1996, J. Comput. Syst. Sci..

[12]  Shlomo Zilberstein,et al.  Using Anytime Algorithms in Intelligent Systems , 1996, AI Mag..

[13]  Laurent Amsaleg,et al.  Cost-based query scrambling for initial delays , 1998, SIGMOD '98.

[14]  David J. DeWitt,et al.  Efficient mid-query re-optimization of sub-optimal query execution plans , 1998, SIGMOD '98.

[15]  Cost Based Query Scrambling for Initial Delays , 1998, SIGMOD Conference.

[16]  Surajit Chaudhuri,et al.  An overview of query optimization in relational systems , 1998, PODS.

[17]  Sridhar Ramaswamy,et al.  Join synopses for approximate query answering , 1999, SIGMOD '99.

[18]  Rajeev Motwani,et al.  On random sampling over joins , 1999, SIGMOD '99.

[19]  Surajit Chaudhuri,et al.  Self-tuning histograms: building histograms without looking at data , 1999, SIGMOD '99.

[20]  Joseph M. Hellerstein,et al.  Eddies: continuously adaptive query processing , 2000, SIGMOD '00.

[21]  Volker Markl,et al.  LEO - DB2's LEarning Optimizer , 2001, VLDB.

[22]  Ben Taskar,et al.  Selectivity estimation using probabilistic models , 2001, SIGMOD '01.

[23]  Surajit Chaudhuri,et al.  Exploiting statistics on query expressions for optimization , 2002, SIGMOD '02.

[24]  M. Affenzeller,et al.  Generic Heuristics for Combinatorial Optimization Problems , 2002 .

[25]  Yannis E. Ioannidis,et al.  The History of Histograms (abridged) , 2003, VLDB.

[26]  César A. Galindo-Legaria,et al.  Statistics on Views , 2003, VLDB.

[27]  Benoît Dageville,et al.  Automatic SQL Tuning in Oracle 10g , 2004, VLDB.

[28]  Peter J. Haas,et al.  Consistently Estimating the Selectivity of Conjuncts of Predicates , 2005, VLDB.

[29]  Jayant R. Haritsa,et al.  Analyzing Plan Diagrams of Database Query Optimizers , 2005, VLDB.

[30]  Surajit Chaudhuri,et al.  Towards a robust query optimizer: a principled and practical approach , 2005, SIGMOD '05.

[31]  Peter J. Haas,et al.  ISOMER: Consistent Histogram Construction Using Query Feedback , 2006, 22nd International Conference on Data Engineering (ICDE'06).

[32]  Adaptive Query Processing , 2006 .

[33]  Zachary G. Ives,et al.  Adaptive query processing: Why, How, When, and What Next? , 2007, VLDB.

[34]  Jayant R. Haritsa,et al.  On the Production of Anorexic Plan Diagrams , 2007, VLDB.

[35]  Surajit Chaudhuri,et al.  Diagnosing Estimation Errors in Page Counts Using Execution Feedback , 2008, 2008 IEEE 24th International Conference on Data Engineering.

[36]  Surajit Chaudhuri,et al.  A pay-as-you-go framework for query execution feedback , 2008, Proc. VLDB Endow..

[37]  Surajit Chaudhuri,et al.  Power Hints for Query Optimization , 2009, 2009 IEEE 25th International Conference on Data Engineering.