research-article

The Picasso database query optimizer visualizer

Editors: Elisa Bertino, Paolo Atzeni, Kian Lee Tan, Yi Chen, Y. C. Tay Author:

Jayant R. HaritsaAuthors Info & Claims

Proceedings of the VLDB Endowment, Volume 3, Issue 1-2

Pages 1517 - 1520

https://doi.org/10.14778/1920841.1921027

Published: 01 September 2010 Publication History

Abstract

Modern database systems employ a query optimizer module to automatically identify the most efficient strategies for executing the declarative SQL queries submitted by users. The efficiency of these strategies, called "plans", is measured in terms of "costs" that are indicative of query response times. Optimization is a mandatory exercise since the difference between the costs of the best execution plan, and a random choice, could be in orders of magnitude. The role of query optimizers has become especially critical during this decade due to the high degree of processing complexity characterizing current data warehousing and mining applications, as exemplified by the TPC-H and TPC-DS decision support benchmarks [20, 21].

References

[1]

M. Abhirama, S. Bhaumik, A. Dey, H. Shrimal and J. Haritsa, "On the Stability of Plan Costs and the Costs of Plan Stability", Proc. of 36th Intl. Conf. on Very Large Data Bases (VLDB), September 2010.

Digital Library

[2]

G. Antonshenkov, "Dynamic Query Optimization in Rdb/VMS", Proc. of 9th IEEE Intl. Conf. on Data Engineering (ICDE), April 1993.

Digital Library

[3]

F. Chu, J. Halpern and P. Seshadri, "Least Expected Cost Query Optimization: An Exercise in Utility", Proc. of ACM Symp. on Principles of Database Systms (PODS), May 1999.

Digital Library

[4]

F. Chu, J. Halpern and J. Gehrke, "Least Expected Cost Query Optimization: What Can We Expect", Proc. of ACM Symp. on Principles of Database Systems (PODS), May 2002.

Digital Library

[5]

A. Deshpande, Z. Ives and V. Raman, "Adaptive Query Processing", Foundations and Trends in Databases, Now Publishers, 1(1), 2007.

[6]

A. Dey, S. Bhaumik, Harish D. and J. Haritsa, "Efficient Generation of Approximate Plan Diagrams", Proc. of 34th Intl. Conf. on Very Large Data Bases (VLDB), August 2008.

[7]

Harish D., P. Darera and J. Haritsa, "On the Production of Anorexic Plan Diagrams", Proc. of 33th Intl. Conf. on Very Large Data Bases (VLDB), September 2007.

Digital Library

[8]

Harish D., P. Darera and J. Haritsa, "Robust Plans through Plan Diagram Reduction", Proc. of 34th Intl. Conf. on Very Large Data Bases (VLDB), August 2008.

[9]

A. Hulgeri and S. Sudarshan, "Parametric Query Optimization for Linear and Piecewise Linear Cost Functions", Proc. of 28th Intl. Conf. on Very Large Data Bases (VLDB), August 2002.

Digital Library

[10]

A. Hulgeri and S. Sudarshan, "AniPQO: Almost Non-intrusive Parametric Query Optimization for Nonlinear Cost Functions", Proc. of 29th Intl. Conf. on Very Large Data Bases (VLDB), August 2003.

Digital Library

[11]

N. Kabra and D. DeWitt, "Efficient Mid-Query Re-Optimization of Sub-Optimal Query Execution Plans", Proc. of ACM SIGMOD Intl. Conf. on Management of Data, May 1998.

Digital Library

[12]

N. Reddy and J. Haritsa, "Analyzing Plan Diagrams of Database Query Optimizers", Proc. of 31st Intl. Conf. on Very Large Data Bases (VLDB), August 2005.

Digital Library

[13]

M. Stillger, G. Lohman, V. Markl and M. Kandil, "LEO, DB2's LEarning Optimizer", Proc. of 27th Intl. Conf. on Very Large Data Bases (VLDB), August 2001.

Digital Library

[14]

www.artlex.com/ArtLex/c/cubism.html

[15]

www.ibm.com/db2

[16]

www.microsoft.com/sqlserver/2008/

[17]

www.oracle.com/technology/products/database/oracle11g/

[18]

www.sybase.com/linux/ase

[19]

www.postgresql.org

[20]

www.tpc.org/tpch

[21]

www.tpc.org/tpcds

[22]

dsl.serc.iisc.ernet.in/projects/PICASSO/

Cited By

Justen DRitter DFraser CLamb ALee ABodner THaddad MZeuch SMarkl VBoehm M(2024)POLAR: Adaptive and Non-invasive Join Order Selection via Plans of Least ResistanceProceedings of the VLDB Endowment10.14778/3648160.364817517:6(1350-1363)Online publication date: 1-Feb-2024
https://dl.acm.org/doi/10.14778/3648160.3648175
Anneser CPetruccelli MTatbul NCohen DXu ZPandian PLaptev NMarcus RKemper A(2023)QO-Insight: Inspecting Steered Query OptimizersProceedings of the VLDB Endowment10.14778/3611540.361158616:12(3922-3925)Online publication date: 1-Aug-2023
https://dl.acm.org/doi/10.14778/3611540.3611586
Wang HLi HBhowmick SXu BDas SPandis ISelçuk Candan KAmer-Yahia S(2023)ARENA: Alternative Relational Query Plan Exploration for Database EducationCompanion of the 2023 International Conference on Management of Data10.1145/3555041.3589713(107-110)Online publication date: 4-Jun-2023
https://dl.acm.org/doi/10.1145/3555041.3589713
Show More Cited By

The Picasso database query optimizer visualizer
1. Information systems
  1. Data management systems
    1. Database management system engines
  2. Information retrieval
2. Theory of computation
  1. Theory and algorithms for application domains
    1. Database theory

Recommendations

Developing a query optimizer for a federated database system
IIS '97: Proceedings of the 1997 IASTED International Conference on Intelligent Information Systems (IIS '97)

This paper concerns our experiences in developing a query optimizer for the Cyrano prototype federated database system developed at Virginia Tech. We used a bottom-up evaluation method commonly seen in deductive systems. In Cyrano, queries and stored ...
gFOV: A Full-Stack SPARQL Query Optimizer & Plan Visualizer
CIKM '23: Proceedings of the 32nd ACM International Conference on Information and Knowledge Management

SPARQL is the standard query language for RDF data. A SPARQL query consists of basic graph patterns (BGPs), which are matched onto the data graph, and graph pattern operators, which specify how to merge the matched results. Despite the prevalence of ...
The MemSQL query optimizer: a modern optimizer for real-time analytics in a distributed database

Real-time analytics on massive datasets has become a very common need in many enterprises. These applications require not only rapid data ingest, but also quick answers to analytical queries operating on the latest data. MemSQL is a distributed SQL ...

Comments

Please enable JavaScript to view thecomments powered by Disqus.

Information & Contributors

Information

Published In

cover image Proceedings of the VLDB Endowment

Proceedings of the VLDB Endowment Volume 3, Issue 1-2

September 2010

1658 pages

ISSN:2150-8097

Issue’s Table of Contents

Publisher

VLDB Endowment

Publication History

Published: 01 September 2010

Published in PVLDB Volume 3, Issue 1-2

Qualifiers

Research-article

Contributors

Other Metrics

View Article Metrics

Bibliometrics & Citations

Bibliometrics

Article Metrics

17
Total Citations
View Citations
265
Total Downloads

Downloads (Last 12 months)26
Downloads (Last 6 weeks)0

Reflects downloads up to 09 Nov 2024

Other Metrics

View Author Metrics

Citations

Cited By

Justen DRitter DFraser CLamb ALee ABodner THaddad MZeuch SMarkl VBoehm M(2024)POLAR: Adaptive and Non-invasive Join Order Selection via Plans of Least ResistanceProceedings of the VLDB Endowment10.14778/3648160.364817517:6(1350-1363)Online publication date: 1-Feb-2024
https://dl.acm.org/doi/10.14778/3648160.3648175
Anneser CPetruccelli MTatbul NCohen DXu ZPandian PLaptev NMarcus RKemper A(2023)QO-Insight: Inspecting Steered Query OptimizersProceedings of the VLDB Endowment10.14778/3611540.361158616:12(3922-3925)Online publication date: 1-Aug-2023
https://dl.acm.org/doi/10.14778/3611540.3611586
Wang HLi HBhowmick SXu BDas SPandis ISelçuk Candan KAmer-Yahia S(2023)ARENA: Alternative Relational Query Plan Exploration for Database EducationCompanion of the 2023 International Conference on Management of Data10.1145/3555041.3589713(107-110)Online publication date: 4-Jun-2023
https://dl.acm.org/doi/10.1145/3555041.3589713
Tan JYeo DNeoh RChua HBhowmick S(2022)MOCHAProceedings of the VLDB Endowment10.14778/3554821.355485415:12(3602-3605)Online publication date: 1-Aug-2022
https://dl.acm.org/doi/10.14778/3554821.3554854
Zhang WKim JRoss KSedlar EStadler L(2021)Adaptive code generation for data-intensive analyticsProceedings of the VLDB Endowment10.14778/3447689.344769714:6(929-942)Online publication date: 12-Apr-2021
https://dl.acm.org/doi/10.14778/3447689.3447697
Negi PInterlandi MMarcus RAlizadeh MKraska TFriedman MJindal ALi GLi ZIdreos SSrivastava D(2021)Steering Query Optimizers: A Practical Take on Big Data WorkloadsProceedings of the 2021 International Conference on Management of Data10.1145/3448016.3457568(2557-2569)Online publication date: 9-Jun-2021
https://dl.acm.org/doi/10.1145/3448016.3457568
Snodgrass RCurrim SSuh Y(2021)Have query optimizers hit the wall?The VLDB Journal — The International Journal on Very Large Data Bases10.1007/s00778-021-00689-y31:1(181-200)Online publication date: 20-Sep-2021
https://dl.acm.org/doi/10.1007/s00778-021-00689-y
Gatterbauer W(2020)Databases will visualize queries tooProceedings of the VLDB Endowment10.14778/3402755.34028054:12(1498-1501)Online publication date: 3-Jun-2020
https://dl.acm.org/doi/10.14778/3402755.3402805
Park KJeong TJeong CLee JLee DLee YMaier DPottinger RDoan ATan WAlawini ANgo H(2020)ProcAnalyzer: Effective Code Analyzer for Tuning Imperative Programs in SAP HANAProceedings of the 2020 ACM SIGMOD International Conference on Management of Data10.1145/3318464.3384686(2709-2712)Online publication date: 11-Jun-2020
https://dl.acm.org/doi/10.1145/3318464.3384686
Kersten MKoutsourakis PZhang Y(2018)Finding the Pitfalls in Query PerformanceProceedings of the Workshop on Testing Database Systems10.1145/3209950.3209951(1-6)Online publication date: 15-Jun-2018
https://dl.acm.org/doi/10.1145/3209950.3209951
Show More Cited By

View Options

Get Access

Login options

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

Full Access

Get this Article

View options

PDF

View or Download as a PDF file.

eReader

View online with eReader.

Media

Figures

Other

Tables

View Issue’s Table of Contents