Abstract | ||
---|---|---|
The automated optimization of declarative SQL queries is a classical problem that has been diligently addressed by the database community over several decades. However, due to its inherent complexities and challenges, the topic has largely remained a "black art", and the quality of the query optimizer continues to be a key differentiator between competing database products, with large technical teams involved in their design and implementation. Over the past few years, a fresh perspective on the behavior of modern query optimizers has arisen through the introduction and development of the "plan diagram" concept. A plan diagram is a visual representation of the plan choices made by the optimizer over a space of input parameters, such as relational selectivities. In this tutorial, we provide a detailed walk-through of plan diagrams, their processing, and their applications. We begin by showcasing a variety of plan diagrams that provide intriguing insights into current query optimizer implementations. A suite of techniques for efficiently producing plan diagrams are then outlined. Subsequently, we present a suite of post-processing algorithms that take optimizer plan diagrams as input, and output new diagrams with demonstrably superior query processing characteristics, such as robustness to estimation errors. Following up, we explain how these offline characteristics can be internalized in the query optimizer, resulting in an intrinsically improved optimizer that directly produces high quality plan diagrams. Finally, we enumerate a variety of open technical problems, and promising future research directions. All the plan diagrams in the tutorial are sourced from popular industrial-strength query optimizers operating on benchmark decision-support environments, and will be graphically displayed on the Picasso visualization platform. |
Year | DOI | Venue |
---|---|---|
2011 | 10.1109/ICDE.2011.5767959 | Data Engineering |
Keywords | Field | DocType |
SQL,data structures,data visualisation,optimisation,query processing,visual databases,Picasso visualization platform,automated optimization,benchmark decision-support environments,database community,database products,declarative SQL queries,query optimizer plan diagrams,query processing characteristics,visual representation | Query optimization,SQL,Data structure,Data mining,Data visualization,Information retrieval,Differentiator,Computer science,Robustness (computer science),Database | Conference |
ISSN | ISBN | Citations |
1063-6382 E-ISBN : 978-1-4244-8958-9 | 978-1-4244-8958-9 | 1 |
PageRank | References | Authors |
0.37 | 24 | 1 |
Name | Order | Citations | PageRank |
---|---|---|---|
Jayant R. Haritsa | 1 | 2004 | 228.38 |