Skip to main content
SpringerLink
Log in

Handling Estimation Inaccuracy in Query Optimization

  • Conference paper
  • First Online:
Web Technologies and Applications (APWeb 2016)

Part of the book series: Lecture Notes in Computer Science ((LNISA,volume 9932))

Included in the following conference series:

  • 1593 Accesses

Abstract

Cost-based Optimizers choose query execution plans using a cost model. The latter relies on the accuracy of estimated statistics. Unfortunately, compile-time estimates often differ significantly from run-time values, leading to a suboptimal plan choices. In this paper, we propose a compile-time strategy, wherein the optimization process is fully aware of the estimation inaccuracy. This is ensured by the use of intervals of estimates rather than single-point estimates of error-prone parameters. These intervals serve to identify plans that provide stable performance in several run-time conditions, so called robust. Our strategy relies on a probabilistic approach to decide which plan to choose to start the execution. Our experiments show that our proposal allows a considerable improvement of the ability of a query optimizer to produce a robust execution plan in case of large estimation errors.

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

Access this chapter

Log in via an institution
Chapter
USD 29.95
Price excludes VAT (USA)
  • Available as PDF
  • Read on any device
  • Instant download
  • Own it forever
eBook
USD 39.99
Price excludes VAT (USA)
  • Available as EPUB and PDF
  • Read on any device
  • Instant download
  • Own it forever
Softcover Book
USD 54.99
Price excludes VAT (USA)
  • Compact, lightweight edition
  • Dispatched in 3 to 5 business days
  • Free shipping worldwide - see info

Tax calculation will be finalised at checkout

Purchases are for personal use only

Institutional subscriptions

Similar content being viewed by others

Notes

  1. 1.

    We only focus on the Cost-based Query Optimizers.

References

  1. Abhirama, M., Bhaumik, S., Dey, A., Shrimal, H., Haritsa, J.R.: On the stability of plan costs and the costs of plan stability. Proc. VLDB Endow. 3, 1137–1148 (2010)

    Article  Google Scholar 

  2. Babcock, B., Chaudhuri, S.: Towards a robust query optimizer: a principled and practical approach. In: Proceedings of ACM SIGMOD International Conference on Management of Data, pp. 119–130 (2005)

    Google Scholar 

  3. Babu, S., Bizarro, P., DeWitt, D.: Proactive re-optimization. In: Proceedings of ACM SIGMOD International Conference on Management of Data, pp. 107–118 (2005)

    Google Scholar 

  4. Bizarro, P., Bruno, N., DeWitt, D.J.: Progressive parametric query optimization. IEEE Trans. Knowl. Data Eng. 21, 582–594 (2009)

    Article  Google Scholar 

  5. Bruno, N., Jain, S., Zhou, J.: Continuous cloud-scale query optimization and processing. PVLDB 6, 961–972 (2013)

    Google Scholar 

  6. Chaudhuri, S., Narasayya, V., Ramamurthy, R.: Estimating progress of long running SQL queries. In: Proceedings of ACM SIGMOD International Conference on Management of Data, pp. 803–814 (2004)

    Google Scholar 

  7. Chen, C.M., Roussopoulos, N.: Adaptive selectivity estimation using query feedback. In: Proceedings of ACM SIGMOD International Conference on Management of Data, pp. 161–172 (1994)

    Google Scholar 

  8. Christodoulakis, S.: Implications of certain assumptions in database performance evaluation. ACM Trans. Database Syst. 9, 163–186 (1984)

    Article  MathSciNet  MATH  Google Scholar 

  9. Chu, F.C., Halpern, J.Y., Seshadri, P.: Least expected cost query optimization: an exercise in utility. In: Proceedings of the Eighteenth ACM SIGACT-SIGMOD-SIGART Symposium on Principles of Database Systems, Philadelphia, pp. 138–147 (1999)

    Google Scholar 

  10. Cole, R.L., Graefe, G.: Optimization of dynamic query evaluation plans. In: Proceedings of ACM SIGMOD International Conference on Management of Data, pp. 150–160 (1994)

    Google Scholar 

  11. Deshpande, A., Garofalakis, M.N., Rastogi, R.: Independence is good: dependency-based histogram synopses for high-dimensional data. In: ACM SIGMOD Conference, pp. 199–210 (2001)

    Google Scholar 

  12. Dutt, A., Neelam, S., Haritsa, J.R.: Quest: an exploratory approach to robust query processing. Proc. VLDB Endow. 7, 1585–1588 (2014)

    Article  Google Scholar 

  13. Getoor, L., Taskar, B., Koller, D.: Selectivity estimation using probabilistic models. In: Proceedings of ACM SIGMOD International Conference on Management of Data, pp. 461–472 (2001)

    Google Scholar 

  14. Harish, D., Pooja, N.D., Jayant, R.H.: Identifying robust plans through plan diagram reduction. Proc. VLDB Endow. 1, 1124–1140 (2008)

    Article  Google Scholar 

  15. Hulgeri, A., Sudarshan, S.: Parametric query optimization for linear and piecewise linear cost functions. In: Proceedings of the 28th International Conference on Very Large Data Bases, pp. 167–178. VLDB Endowment (2002)

    Google Scholar 

  16. Ioannidis, Y.E., Christodoulakis, S.: On the propagation of errors in the size of join results. In: Proceedings of SIGMOD International Conference on Management of Data, pp. 268–277 (1991)

    Google Scholar 

  17. Kabra, N., DeWitt, D.J.: Efficient mid-query re-optimization of sub-optimal query execution plans. In: Proceedings of ACM SIGMOD International Conference on Management of Data, pp. 106–117 (1998)

    Google Scholar 

  18. Karanasos, K., Balmin, A., Kutsch, M., Ozcan, F., Ercegovac, V., Xia, C., Jackson, J.: Dynamically optimizing queries over large scale data platforms. In: Proceedings of ACM SIGMOD International Conference on Management of Data, pp. 943–954 (2014)

    Google Scholar 

  19. Markl, V., Raman, V., Simmen, D., Lohman, G., Pirahesh, H., Cilimdzic, M.: Robust query processing through progressive optimization. In: Proceedings of ACM SIGMOD International Conference on Management of Data, pp. 659–670 (2004)

    Google Scholar 

  20. Neumann, T., Galindo-Legaria, C.A.: Taking the edge off cardinality estimation errors using incremental execution. In: DBIS, Germany, pp. 73–92 (2013)

    Google Scholar 

  21. Papakonstantinou, J.M., Tapia, R.A.: Origin and evolution of the secant method in one dimension. Am. Math. Mon. 120(6), 500–518 (2013)

    Article  MathSciNet  MATH  Google Scholar 

  22. Poosala, V., Haas, P.J., Ioannidis, Y.E., Shekita, E.J.: Improved histograms for selectivity estimation of range predicates. In: Proceedings of ACM SIGMOD International Conference on Management of Data, pp. 294–305 (1996)

    Google Scholar 

  23. Poosala, V., Ioannidis, Y.E.: Selectivity estimation without the attribute value independence assumption. In: Proceedings of 23rd International Conference on Very Large Data Bases, pp. 486–495 (1997)

    Google Scholar 

  24. 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: Proceedings of ACM SIGMOD International Conference on Management of Data, pp. 23–34 (1979)

    Google Scholar 

  25. Moumen, C., Morvan, F., Hameurlain, A.: Estimation error-aware query optimization: an overview. Int. J. Comput. Syst. Sci. Eng. (2016, in press)

    Google Scholar 

  26. Moumen, C., Morvan, F., Hameurlain, A.: Handling estimation inaccuracy in query optimization. Research report (2016). www.irit.fr/~Riad.Mokadem/report%20Chiraz%20Moumen.pdf

    Google Scholar 

  27. Tzoumas, K., Deshpande, A., Jensen, C.S.: Lightweight graphical models for selectivity estimation without independence assumptions. In: PVLDB (2011)

    Google Scholar 

  28. Tzoumas, K., Deshpande, A., Jensen, C.S.: Efficiently adapting graphical models for selectivity estimation. VLDB J. 22, 3–27 (2013)

    Article  Google Scholar 

  29. Wiener, J.L., Kuno, H., Graefe, G.: Benchmarking query executionrobustness. In: TPC Technology Conference on Performance Evaluation and Benchmarking, pp. 153–166 (2009)

    Google Scholar 

  30. Yin, S., Hameurlain, A., Morvan, F.: Robust query optimization methods with respect to estimation errors: a survey. SIGMOD Rec. 44, 25–36 (2015)

    Article  Google Scholar 

Download references

Author information

Authors and Affiliations

  1. IRIT Laboratory, Paul Sabatier University, 118 Route de Narbonne, 31062, Toulouse Cedex 9, France

    Chiraz Moumen, Franck Morvan & Abdelkader Hameurlain

Authors
  1. Chiraz Moumen
    View author publications

    You can also search for this author in PubMed Google Scholar

  2. Franck Morvan
    View author publications

    You can also search for this author in PubMed Google Scholar

  3. Abdelkader Hameurlain
    View author publications

    You can also search for this author in PubMed Google Scholar

Corresponding author

Correspondence to Chiraz Moumen .

Editor information

Editors and Affiliations

  1. School of Computing, University of Utah, Salt Lake City, Utah, USA

    Feifei Li

  2. School of Electrical Engineering, Seoul National University, Seoul, Korea (Republic of)

    Kyuseok Shim

  3. Soochow University , Suzhou, China

    Kai Zheng

  4. Soochow University , Suzhou, China

    Guanfeng Liu

Rights and permissions

Reprints and permissions

Copyright information

© 2016 Springer International Publishing Switzerland

About this paper

Cite this paper

Moumen, C., Morvan, F., Hameurlain, A. (2016). Handling Estimation Inaccuracy in Query Optimization. In: Li, F., Shim, K., Zheng, K., Liu, G. (eds) Web Technologies and Applications. APWeb 2016. Lecture Notes in Computer Science(), vol 9932. Springer, Cham. https://doi.org/10.1007/978-3-319-45817-5_28

Download citation

  • DOI: https://doi.org/10.1007/978-3-319-45817-5_28

  • Published:

  • Publisher Name: Springer, Cham

  • Print ISBN: 978-3-319-45816-8

  • Online ISBN: 978-3-319-45817-5

  • eBook Packages: Computer ScienceComputer Science (R0)

Publish with us

Policies and ethics

Search

Navigation