Skip to main content
SpringerLink
Log in

Multi-objective parametric query optimization

  • Special Issue Paper
  • Published:
The VLDB Journal Aims and scope Submit manuscript

Abstract

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.

This is a preview of subscription content, log in via an institution to check access.

Access this article

Log in via an institution

Price excludes VAT (USA)
Tax calculation will be finalised during checkout.

Instant access to the full article PDF.

Institutional subscriptions

Fig. 1
Fig. 2
Fig. 3
Fig. 4
Fig. 5
Fig. 6
Fig. 7
Fig. 8
Fig. 9
Fig. 10
Fig. 11
Fig. 12
Fig. 13
Fig. 14

Similar content being viewed by others

Notes

  1. To simplify the pseudo-code, we made the strong assumption that the cost function of the final join is always linear in parameter space regions in which the cost functions of the two sub-plans are linear. This is not true in general, but the code can easily be generalized by first accumulating the cost of the sub-plans, and then accumulating the resulting cost and the join cost in a second step.

  2. http://www.postgresql.org/

  3. http://aws.amazon.com/de/ec2/

  4. http://www.gurobi.com/

References

  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. Abul-Basher, Z., Feng, Y., Godfrey, P.: Alternative query optimization for workload management. In: Database and Expert Systems Applications (2012)

  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. Babcock, B., Chaudhuri, S.: Towards a robust query optimizer: a principled and practical approach. In: SIGMOD, pp. 119–130 (2005)

  5. Babu, S., Bizarro, P., DeWitt, D.: Proactive re-optimization. In: SIGMOD, pp. 107–118, New York, New York, USA, ACM Press (2005)

  6. Bemporad, A., Fukuda, K., Torrisi, F.: Convexity recognition of the union of polyhedra. Comput. Geom. 18(3), 141–154 (2001)

    Article  MathSciNet  MATH  Google Scholar 

  7. Bizarro, P., Bruno, N., DeWitt, D.: Progressive parametric query optimization. KDE 21(4), 582–594 (2009)

    Google Scholar 

  8. Bruno, N.: Polynomial heuristics for query optimization. In: ICDE, pp. 589–600 (2010)

  9. Bruno, N., Nehme, R.V.: Configuration-parametric query optimization for physical design tuning. SIGMOD (2008)

  10. Chaudhuri, S., Dalvi, N., Kaushik, R.: Robust cardinality and cost estimation for skyline operator. In: ICDE, pp. 64–73 (2006)

  11. Chu, F., Halpern, J., Gehrke, J.: Least expected cost query optimization: what can we expect? SIGMOD (2002)

  12. Cole, R., Graefe, G.: Optimization of dynamic query evaluation plans. In: SIGMOD, pp. 150–160 (1994)

  13. Dey, A., Bhaumik, S., Haritsa, J.: Efficiently approximating query optimizer plan diagrams. In: VLDB, pp. 1325–1336 (2008)

  14. Ganguly, S.: Design and analysis of parametric query optimization algorithms. In: VLDB, pp. 228–238 (1998)

  15. Ganguly, S., Hasan, W., Krishnamurthy, R.: Query optimization for parallel execution. In: SIGMOD, pp. 9–18 (1992)

  16. Garofalakis, M., Ioannidis, Y.: Multi-dimensional resource scheduling for parallel queries. In: SIGMOD (1996)

  17. Graefe, G., Ward, K.: Dynamic query evaluation plans. In: SIGMOD, pp. 358–366 (1989)

  18. Hulgeri, A., Sudarshan, S.: Parametric query optimization for linear and piecewise linear cost functions. In: VLDB, pp. 167–178 (2002)

  19. Hulgeri, A., Sudarshan, S.: AniPQO: almost non-intrusive parametric query optimization for nonlinear cost functions. In: VLDB, pp. 766–777 (2003)

  20. Ioannidis, Y.E., Ng, R.T., Shim, K., Sellis, T.K.: Parametric query optimization. VLDBJ 6(2), 132–151 (1997)

    Article  Google Scholar 

  21. Kaibel, V., Pfetsch, M.: Some algorithmic problems in polytope theory. Algebra, Geometry and Software Systems 1 (2003)

  22. Kambhampati, S., Nambiar, U., Nie, Z., Vaddi, S.: Havasu: A multi-objective. Adaptive Query Processing Framework for Web Data Integration, ASU CSE (2002)

  23. Kllapi, H., Sitaridi, E., Tsangaris, M.M., Ioannidis, Y.E.: Schedule optimization for data processing flows on the cloud. In: SIGMOD, (2011)

  24. Muralikrishna, M.: Improved unnesting algorithms for join aggregate SQL queries. VLDB, pp. 91–102 (1992)

  25. Ono, K., Lohman, G.: Measuring the complexity of join enumeration in query optimization. In: VLDB, pp. 314–325 (1990)

  26. Papadimitriou, C., Yannakakis, M.: Multiobjective query optimization. In: PODS, pp. 52–59 (2001)

  27. Reddy, N., Haritsa, J.: Analyzing plan diagrams of database query optimizers. VLDB, pp. 1228–1239 (2005)

  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)

  29. Shang, H., Kitsuregawa, M.: Parametric query optimization. VLDB 6(9), 649–660 (2013)

    Google Scholar 

  30. Simitsis, A., Vassiliadis, P., Sellis, T.: State-space optimization of ETL workflows. Trans. KDE 17(10), 1404–1419 (2005)

    Google Scholar 

  31. Simitsis, A., Wilkinson, K., Castellanos, M., Dayal, U.: Optimizing analytic data flows for multiple execution engines. SIGMOD (2012)

  32. Steinbrunn, M., Moerkotte, G., Kemper, A.: Heuristic and randomized optimization for the join ordering problem. VLDB J. 6(3), 191–208 (1997)

    Article  Google Scholar 

  33. Trummer, I., Koch, C.: Approximation schemes for many-objective query optimization. In: SIGMOD, pp. 1299–1310 (2014)

  34. Trummer, I., Koch, C.: Multi-objective parametric query optimization. VLDB 8(3), 221–232 (2015)

    Google Scholar 

  35. Xu, Z., Tu, Y.C., Wang, X.: PET: reducing database energy cost via query optimization. VLDB 5(12), 1954–1957 (2012)

    Google Scholar 

Download references

Author information

Authors and Affiliations

  1. Ecole Polytechnique Fèdèrale de Lausanne, Lausanne, Switzerland

    Immanuel Trummer & Christoph Koch

  2. Cornell University, Ithaca, NY, USA

    Immanuel Trummer

Authors
  1. Immanuel Trummer
    View author publications

    You can also search for this author in PubMed Google Scholar

  2. Christoph Koch
    View author publications

    You can also search for this author in PubMed Google Scholar

Corresponding author

Correspondence to Immanuel Trummer.

Rights and permissions

Reprints and permissions

About this article

Check for updates. Verify currency and authenticity via CrossMark

Cite this article

Trummer, I., Koch, C. Multi-objective parametric query optimization. The VLDB Journal 26, 107–124 (2017). https://doi.org/10.1007/s00778-016-0439-0

Download citation

  • Received:

  • Revised:

  • Accepted:

  • Published:

  • Issue Date:

  • DOI: https://doi.org/10.1007/s00778-016-0439-0

Keywords

Search

Navigation