Picasso Database Query Optimizer Visualizer

 Version 2.1 (February 2011)  

Database Systems Lab
Indian Institute of Science



[About] [Diagrams] [Download] [Publications] [Reports] [Contact] [Team] [Related]

[Acks]

About Picasso
Welcome to the Picasso database query optimizer visualizer software developed at the Database Systems Lab, Indian Institute of Science. The tool, written entirely in Java, is operational on a suite of industrial-strength database query optimizers (currently, DB2, Oracle, SQL Server, Sybase, PostgreSQL and MySQL are supported). Picasso can be used as a
  1. query optimizer analysis, debugging, and redesign aid by system developers,
  2. query optimization testbed by database researchers, and
  3. query optimizer pedagogical support by database instructors and students.

Given a query template that defines a relational selectivity space and a choice of database engine, the Picasso tool automatically generates a variety of diagrams that characterize the behavior of the engine's optimizer over this relational selectivity space. The diagrams include
  1. Plan Diagram: A pictorial enumeration of the execution plan choices.
  2. Cost Diagram: A visualization of the associated estimated plan execution costs.
  3. Cardinality Diagram: A visualization of the associated estimated query result cardinalities.
  4. Reduced Plan Diagram: Shows the extent to which the original plan diagram may be simplified (by replacing some of the plans with their siblings in the plan diagram) without increasing the cost of any individual query by more than a user-specified threshold value.
  5. Schematic Plan-tree Diagram: A tree visualization of a selected plan in the plan diagram.
  6. Plan-difference Diagram: Highlights the schematic differences between a selected pair of plans that appear in the plan diagram.
  7. Compiled Plan-tree Diagram: A tree visualization of a selected plan at a specific location in the plan diagram, annotated with cost and cardinality information.
  8. Foreign Plan-tree Diagram: At a given point in a plan diagram produced on a database engine, a tree visualization of the plan produced by another engine (or by the same engine at another optimization level) at this point.
  9. Abstract-Plan Diagram: A visualization of the behavior of a selected plan in the plan diagram, when the optimizer is requested to use this specific plan throughout the selectivity space.   [This feature is operational only on SQL Server and Sybase ASE.]

Apart from query compilation-related diagrams, Picasso also produces
  1. Execution Cost Diagram: A visualization of the runtime query response times.
  2. Execution Cardinality Diagram: A visualization of the runtime query result cardinalities.

The diagrams produced by Picasso with a representative set of industrial-strength optimizers, for query templates based on the TPC-H and TPC-DS benchmarks, have provided a variety of interesting insights. Specifically, that current optimizers make extremely fine-grained plan choices, which may often be supplanted by less efficient options without substantively affecting the quality; that the plan optimality regions may have highly intricate patterns and irregular boundaries, indicating strongly non-linear cost models; that non-monotonic cost behavior exists where increasing result cardinalities decrease the estimated cost; and, that the basic assumptions underlying the research literature on parametric query optimization often do not hold in practice.

The name of the tool stems from the observation that many plan diagrams appear similar to cubist paintings – the art genius, Pablo Picasso, was a founding-father of the cubist painting genre.

Version 1 of the tool was released in May 2007, Version 2 was released in February 2009, while Version 2.1 was released in February 2011.

Sample Picasso Diagrams
Follow these links to view Picasso Diagrams for a representative set of Industrial-Strength Optimizers on a variety of TPC-H-based two-dimensional query templates     (for legal reasons, the optimizers are not explicitly identified)
[Note: Optimizer diagrams are a function of not only the database contents and the query templates, but also the system configurations on which the diagrams were generated. This could result in variations in the diagrams obtained on different platforms.]

Download
Note: The Indian Institute of Science is the copyright owner on the Picasso software, as per this   Copyright Certificate   issued by Govt. of India, on May 1, 2006. Downloading the Picasso software automatically implies that you accept and acknowledge this copyright ownership by the Indian Institute of Science.

Publications
Analyzing Plan Diagrams of Database Query Optimizers
Naveen Reddy and Jayant Haritsa
Proc. of 31st Intl. Conf. on Very Large Data Bases (VLDB), Trondheim, Norway, September 2005.

On the Production of Anorexic Plan Diagrams
Harish D., Pooja Darera and Jayant Haritsa
Proc. of 33rd Intl. Conf. on Very Large Data Bases (VLDB), Vienna, Austria, September 2007.

Identifying Robust Plans through Plan Diagram Reduction
Harish D., Pooja Darera and Jayant Haritsa
Proc. of 34th Intl. Conf. on Very Large Data Bases (VLDB), Auckland, New Zealand, August 2008.

Efficiently Approximating Query Optimizer Plan Diagrams
Atreyee Dey, Sourjya Bhaumik, Harish D. and Jayant Haritsa
Proc. of 34th Intl. Conf. on Very Large Data Bases (VLDB), Auckland, New Zealand, August 2008.

On the Stability of Plan Costs and the Costs of Plan Stability
M. Abhirama, Sourjya Bhaumik, Atreyee Dey, Harsh Shrimal and Jayant Haritsa
Proc. of 36th Intl. Conf. on Very Large Data Bases (VLDB), Singapore, September 2010.

The Picasso Database Query Optimizer Visualizer
Jayant Haritsa
Proc. of 36th Intl. Conf. on Very Large Data Bases (VLDB), Singapore, September 2010.

PLAN DIAGRAMS: Visualizing Database Query Optimizers
Jayant Haritsa
Annals of Indian National Academy of Engineering (INAE), Volume VIII, April 2011, pgs. 59-68.

Query Optimizer Plan Diagrams: Production, Reduction and Applications(Tutorial)
Jayant Haritsa
Proc. of 27th IEEE Intl. Conf. on Data Engineering (ICDE), Hanover, Germany, April 2011.

Analyzing Plan Diagrams of XQuery Optimizers
Bruhathi H. S. and Jayant Haritsa
Proc. of 23rd Intl. Conf. on Database and Expert Systems Applications (DEXA), Vienna, Austria, September 2012.


Reports
Next Generation Relational Query Optimizers
Naveen Reddy
Master's Thesis, Dept. of Computer Science and Automation, Indian Institute of Science, June 2005.

Picasso: Design and Implementation of a Database Optimizer Analyzer
Mohammed Aslam
Master's Thesis, Dept. of Computer Science and Automation, Indian Institute of Science, July 2006.

Picasso: Analyzing and Characterizing Relational Query Optimizers
Akshat Nair
Master's Thesis, Dept. of Computer Science and Automation, Indian Institute of Science, July 2006.

Picasso 1.0: Design and Analysis
Tarun Ramsinghani
Master's Thesis, Dept. of Computer Science and Automation, Indian Institute of Science, July 2007.

Reduction of Query Optimizer Plan Diagrams
Pooja Darera
Master's Thesis, Supercomputer Education & Research Centre, Indian Institute of Science, December 2007.

SIGHT and SEER: Efficient Production and Reduction of Query Optimizer Plan Diagrams
Harish D
Master's Thesis, Dept. of Computer Science and Automation, Indian Institute of Science, July 2008.

Efficient Generation of Query Optimizer Diagrams
Sourjya Bhaumik
Master's Thesis, Dept. of Computer Science and Automation, Indian Institute of Science, June 2009.

Characterizing Plan Diagram Reduction Quality and Efficiency
Harsh Shrimal
Master's Thesis, Dept. of Computer Science and Automation, Indian Institute of Science, June 2009.

Design and Implementation of Picasso 2.0
Ravi Shetye
Master's Thesis, Dept. of Computer Science and Automation, Indian Institute of Science, July 2009.

On the Stability of Plan Costs and the Costs of Plan Stability
M. Abhirama
Master's Thesis, Dept. of Computer Science and Automation, Indian Institute of Science, August 2009.

Efficiently Approximating Query Optimizer Diagrams
Atreyee Dey
Master's Thesis, Supercomputer Education & Research Centre, Indian Institute of Science, August 2009.

Efficient Identification of Robust Plans and Efficient Generation of Plan Diagrams
Anshuman Dutt
Master's Thesis, Dept. of Computer Science and Automation, Indian Institute of Science, August 2010.

Exploring the Semantics of Plan Diagrams
Bruhathi H S
Master's Thesis, Supercomputer Education & Research Centre, Indian Institute of Science, February 2012.

Reduction of Query Optimizer Plan Diagrams
Harish D., Pooja Darera and Jayant Haritsa
Technical Report TR-2007-01, DSL/SERC, Indian Institute of Science, March 2007.

Robust Plans through Plan Diagram Reduction
Harish D., Pooja Darera and Jayant Haritsa
Technical Report TR-2007-02, DSL/SERC, Indian Institute of Science, November 2007.

Efficient Generation of Approximate Plan Diagrams
Atreyee Dey, Sourjya Bhaumik, Harish D. and Jayant Haritsa
Technical Report TR-2008-01, DSL/SERC, Indian Institute of Science, March 2008.

Stability-conscious Query Optimization
M. Abhirama, Sourjya Bhaumik, Atreyee Dey, Harsh Shrimal and Jayant Haritsa
Technical Report TR-2009-01, DSL/SERC, Indian Institute of Science, October 2009.


Contact
Email: picasso [AT] dsl [dot] serc [dot] iisc [dot] ernet [dot] in

Primary Picasso Contributors (in chronological order of participation)

  • Jayant Haritsa (Project Lead)
  • Naveen Reddy (ME, CSA, IISc)
  • Mohammed Aslam (ME, CSA, IISc)
  • Akshat Nair (ME, CSA, IISc)
  • Shruthi A (MS, SERC, IISc)
  • Vidya Bharat (PA)
  • Tarun Ramsinghani (ME, CSA, IISc)
  • Pooja Darera (MS, SERC, IISc)
  • Abhijit Pai (ME, CSA, IISc)
  • Atreyee Dey (MS, SERC, IISc)
  • Harish D (ME, CSA, IISc)
  • Sourjya Bhaumik (ME, CSA, IISc)
  • Abhirama M (ME, CSA, IISc)
  • Ravi Shetye (ME, CSA, IISc)
  • Harsh Shrimal (ME, CSA, IISc)
  • Anshuman Dutt (ME, CSA, IISc)
  • Mahesh Bale (ME, CSA, IISc)
  • Mayuresh Kunjir (ME, CSA, IISc)
  • Priyank Mehta (PA)
  • Rakshit Trivedi (PA)
  • Bruhathi HS (MS, SERC, IISc)
  • Santosh Sahu (ME, CSA, IISc)

Related Work
We have carried out exploratory work on the generation and analysis of Picasso diagrams on XQuery optimizers. Details are avaiable in Exploring the Semantics of Plan Diagrams.

A related software developed by the Database Systems Lab at IISc is CODD (Constructing Dataless Databases), a tool for the automated creation, verification, retention, scaling and porting of database meta-data configurations.


Acknowledgments
This work was supported in part by a Swarnajayanti Fellowship from the Dept. of Science & Technology, Govt. of India., by a research grant from the Dept. of Bio-technology, Govt. of India, and by generous grants from our industrial benefactors.