The VLDB Journal

, Volume 26, Issue 1, pp 107–124 | Cite as

Multi-objective parametric query optimization

  • Immanuel TrummerEmail author
  • Christoph Koch
Special Issue Paper


Classical query optimization compares query plans according to one cost metric and associates each plan with a constant cost value. In this paper, we introduce the multi-objective parametric query optimization (MPQO) problem where query plans are compared according to multiple cost metrics and the cost of a given plan according to a given metric is modeled as a function that depends on multiple parameters. The cost metrics may, for instance, include execution time or monetary fees; a parameter may represent the selectivity of a query predicate that is unspecified at optimization time. MPQO generalizes parametric query optimization (which allows multiple parameters but only one cost metric) and multi-objective query optimization (which allows multiple cost metrics but no parameters). We formally analyze the novel MPQO problem and show why existing algorithms are inapplicable. We present a generic algorithm for MPQO and a specialized version for MPQO with piecewise-linear plan cost functions. We prove that both algorithms find all relevant query plans and experimentally evaluate the performance of our second algorithm in multiple scenarios.


Query optimization Multi-objective optimization Parametric query optimization 


  1. 1.
    Abhirama, M., Bhaumik, S., Dey, A.: On the stability of plan costs and the costs of plan stability. VLDB 3(1), 1137–1148 (2010)Google Scholar
  2. 2.
    Abul-Basher, Z., Feng, Y., Godfrey, P.: Alternative query optimization for workload management. In: Database and Expert Systems Applications (2012)Google Scholar
  3. 3.
    Agarwal, S., Iyer, A., Panda, A.: Blink and it’s done: interactive queries on very large data. VLDB 5, 1902–1905 (2012)Google Scholar
  4. 4.
    Babcock, B., Chaudhuri, S.: Towards a robust query optimizer: a principled and practical approach. In: SIGMOD, pp. 119–130 (2005)Google Scholar
  5. 5.
    Babu, S., Bizarro, P., DeWitt, D.: Proactive re-optimization. In: SIGMOD, pp. 107–118, New York, New York, USA, ACM Press (2005)Google Scholar
  6. 6.
    Bemporad, A., Fukuda, K., Torrisi, F.: Convexity recognition of the union of polyhedra. Comput. Geom. 18(3), 141–154 (2001)MathSciNetCrossRefzbMATHGoogle Scholar
  7. 7.
    Bizarro, P., Bruno, N., DeWitt, D.: Progressive parametric query optimization. KDE 21(4), 582–594 (2009)Google Scholar
  8. 8.
    Bruno, N.: Polynomial heuristics for query optimization. In: ICDE, pp. 589–600 (2010)Google Scholar
  9. 9.
    Bruno, N., Nehme, R.V.: Configuration-parametric query optimization for physical design tuning. SIGMOD (2008)Google Scholar
  10. 10.
    Chaudhuri, S., Dalvi, N., Kaushik, R.: Robust cardinality and cost estimation for skyline operator. In: ICDE, pp. 64–73 (2006)Google Scholar
  11. 11.
    Chu, F., Halpern, J., Gehrke, J.: Least expected cost query optimization: what can we expect? SIGMOD (2002)Google Scholar
  12. 12.
    Cole, R., Graefe, G.: Optimization of dynamic query evaluation plans. In: SIGMOD, pp. 150–160 (1994)Google Scholar
  13. 13.
    Dey, A., Bhaumik, S., Haritsa, J.: Efficiently approximating query optimizer plan diagrams. In: VLDB, pp. 1325–1336 (2008)Google Scholar
  14. 14.
    Ganguly, S.: Design and analysis of parametric query optimization algorithms. In: VLDB, pp. 228–238 (1998)Google Scholar
  15. 15.
    Ganguly, S., Hasan, W., Krishnamurthy, R.: Query optimization for parallel execution. In: SIGMOD, pp. 9–18 (1992)Google Scholar
  16. 16.
    Garofalakis, M., Ioannidis, Y.: Multi-dimensional resource scheduling for parallel queries. In: SIGMOD (1996)Google Scholar
  17. 17.
    Graefe, G., Ward, K.: Dynamic query evaluation plans. In: SIGMOD, pp. 358–366 (1989)Google Scholar
  18. 18.
    Hulgeri, A., Sudarshan, S.: Parametric query optimization for linear and piecewise linear cost functions. In: VLDB, pp. 167–178 (2002)Google Scholar
  19. 19.
    Hulgeri, A., Sudarshan, S.: AniPQO: almost non-intrusive parametric query optimization for nonlinear cost functions. In: VLDB, pp. 766–777 (2003)Google Scholar
  20. 20.
    Ioannidis, Y.E., Ng, R.T., Shim, K., Sellis, T.K.: Parametric query optimization. VLDBJ 6(2), 132–151 (1997)CrossRefGoogle Scholar
  21. 21.
    Kaibel, V., Pfetsch, M.: Some algorithmic problems in polytope theory. Algebra, Geometry and Software Systems 1 (2003)Google Scholar
  22. 22.
    Kambhampati, S., Nambiar, U., Nie, Z., Vaddi, S.: Havasu: A multi-objective. Adaptive Query Processing Framework for Web Data Integration, ASU CSE (2002)Google Scholar
  23. 23.
    Kllapi, H., Sitaridi, E., Tsangaris, M.M., Ioannidis, Y.E.: Schedule optimization for data processing flows on the cloud. In: SIGMOD, (2011)Google Scholar
  24. 24.
    Muralikrishna, M.: Improved unnesting algorithms for join aggregate SQL queries. VLDB, pp. 91–102 (1992)Google Scholar
  25. 25.
    Ono, K., Lohman, G.: Measuring the complexity of join enumeration in query optimization. In: VLDB, pp. 314–325 (1990)Google Scholar
  26. 26.
    Papadimitriou, C., Yannakakis, M.: Multiobjective query optimization. In: PODS, pp. 52–59 (2001)Google Scholar
  27. 27.
    Reddy, N., Haritsa, J.: Analyzing plan diagrams of database query optimizers. VLDB, pp. 1228–1239 (2005)Google Scholar
  28. 28.
    Selinger, P.G., Astrahan, M.M., Chamberlin, D.D., Lorie, R.A., Price, T.G.: Access path selection in a relational database management system. In: SIGMOD, pp. 23–34 (1979)Google Scholar
  29. 29.
    Shang, H., Kitsuregawa, M.: Parametric query optimization. VLDB 6(9), 649–660 (2013)Google Scholar
  30. 30.
    Simitsis, A., Vassiliadis, P., Sellis, T.: State-space optimization of ETL workflows. Trans. KDE 17(10), 1404–1419 (2005)Google Scholar
  31. 31.
    Simitsis, A., Wilkinson, K., Castellanos, M., Dayal, U.: Optimizing analytic data flows for multiple execution engines. SIGMOD (2012)Google Scholar
  32. 32.
    Steinbrunn, M., Moerkotte, G., Kemper, A.: Heuristic and randomized optimization for the join ordering problem. VLDB J. 6(3), 191–208 (1997)CrossRefGoogle Scholar
  33. 33.
    Trummer, I., Koch, C.: Approximation schemes for many-objective query optimization. In: SIGMOD, pp. 1299–1310 (2014)Google Scholar
  34. 34.
    Trummer, I., Koch, C.: Multi-objective parametric query optimization. VLDB 8(3), 221–232 (2015)Google Scholar
  35. 35.
    Xu, Z., Tu, Y.C., Wang, X.: PET: reducing database energy cost via query optimization. VLDB 5(12), 1954–1957 (2012)Google Scholar

Copyright information

© Springer-Verlag Berlin Heidelberg 2016

Authors and Affiliations

  1. 1.Ecole Polytechnique Fèdèrale de LausanneLausanneSwitzerland
  2. 2.Cornell UniversityIthacaUSA

Personalised recommendations