Picasso Database Query Optimizer Visualizer
©Indian Institute of Science, Bangalore,
India
INTRODUCTION
Welcome to the Picasso
database query optimizer visualizer software developed at the Database Systems Lab, Indian Institute of Science, Bangalore, India,
by this team.
The Picasso
tool, written entirely in Java, is operational on a rich suite of
industrial-strength database query optimizers (currently DB2, Oracle, SQL
Server, Sybase ASE and PostgreSQL are supported). It is in use at a host of
academic and industrial labs world-wide, and can be employed as a
- query optimizer analysis, debugging, and redesign
aid by system developers,
- query optimization test-bed by database researchers,
and
- query optimizer pedagogical support by database
instructors and students.
Given an SQL
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
- Plan Diagram: A pictorial enumeration of
the execution plan choices.
- Cost Diagram: A visualization of the
associated estimated plan execution costs.
- Cardinality
Diagram:
A visualization of the associated estimated query result cardinalities.
- 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.
- Schematic Plan-tree
Diagram:
A tree visualization of a selected plan in the plan diagram.
- Plan-difference
Diagram: Highlights
the schematic differences between a selected pair of plans that appear in
the plan diagram.
- 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.
- Foreign Plan-tree
Diagram:
At a given location in a plan diagram produced on a database engine, a
tree visualization of the plan produced by another engine (or the same
engine at another optimization level) at this location.
- 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
- Execution Cost
Diagram: A
visualization of the runtime query response times.
- Execution Cardinality
Diagram:
A visualization of the runtime query result cardinalities.
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.
A history of
the Picasso versions, listing the bug fixes and the new functionalities
incorporated in each version, is available in Code
History.
Documentation Home