Red Hat Database: Graphical Tools Guide
Prev	Chapter 5. Visual Explain	Next

Using Visual Explain

To run Visual Explain, first open a connection to a database:

Click on the arrow of the combo box that is on the right side of the Visual Explain window in the Tool Bar. A list of the databases you configured in the Section called Configuring a Connection to a Database appears.

Select a database to connect to.

Depending on whether you configured the database connection to include a password and whether or not Save Database Passwords is enabled (discussed in the Section called Configuring Visual Explain's Operation), you may need to enter the database password in a pop-up window.

After this is done, if the connection is successful, the database's connection name appears in the Tool Bar.

Running and Storing SQL Commands

With a connection to a database open, type SQL commands, separated by semicolons, into the SQL Entry Area.

To run SQL commands, click on the blue "Explain" icon (or from the Statement menu, select Explain). Only the first SQL statement is explained.

To explain a statement other than the first, just highlight the command that you want to run and click on the blue "Explain" icon.

To store SQL commands for future use, click on the "save" icon. You are prompted for a filename.

To run SQL command files, click on the "open" icon. To run those commands, click on the blue "Explain" icon (or from the Statement menu, select Explain).

The output from the EXPLAIN command is displayed in the Visual Explain output window.

	Other Output Options
	You can view a text version of the contents of the Visual Explain output window by clicking on Window -> Show EXPLAIN Output (or Window -> Show EXPLAIN VERBOSE Output). You can save a text version of the contents of this window by right-clicking on it, and entering a file name when prompted. You can see an overview of the contents of the Visual Explain output window by clicking on Window -> Show Plan Overview.

Other Output Options

You can view a text version of the contents of the Visual Explain output window by clicking on Window -> Show EXPLAIN Output (or Window -> Show EXPLAIN VERBOSE Output). You can save a text version of the contents of this window by right-clicking on it, and entering a file name when prompted.

You can see an overview of the contents of the Visual Explain output window by clicking on Window -> Show Plan Overview.

An Example: Analyzing the regression Database

To begin, EXPLAIN the following statement:
SELECT * FROM hobbies_r;
Visual Explain displays the "Scan" icon above the caption "Seq Scan on hobbies_r".
Click on the "Scan" icon; the window now shows:
Figure 5-2. Running a Simple SELECT
Next, select a distinct entry from one of the tables:
SELECT DISTINCT (name) FROM hobbies_r;
The Visual Explain display now contains the unique and sort icons as well.
Figure 5-3. Selecting a Distinct Entry
Next, run the following command:
SELECT name, count (*) FROM hobbies_r group BY name;
Visual Explain now displays the group and aggregate icons instead of sort all unique.
Figure 5-4. Calculating an Aggregate
Run a command that lists all people, hobbies, and their equipment:
SELECT h.person, e.name FROM hobbies_r h, equipment_r e WHERE h.name=e.name;
Visual Explain displays a hash, a hash join, and some sequence scans.
Figure 5-5. Performing a Join

Finally, run the SQL command:

SELECT h.person, h.name FROM hobbies_r h WHERE (SELECT count(*) 
   FROM equipment_r e WHERE h.name=e.name) >= 2;

Visual Explain display now contans a subplan.

Figure 5-6. Query with a Subplan

Understanding the Visual Explain Output

The table that follows describes the Visual Explain icons.

Table 5-1. Visual Explain Icons

Node	Description	Icon
Aggregate	Produce one value from multiple input rows.
Append	Combine rows from multiple input nodes.
Group	Organize input rows into groups.
Hash	Select rows using a hash.
Initplan	Process an un-related sub-query.
Join	Combine rows from two input nodes.
Limit	Select a certain number of rows.
Materialize	Create a temporary table.
Result	Process constant qualifications.
Scan	Scan a relation for certain rows.
SetOp	Perform a set operation.
Sort	Place input rows in some order.
SubPlan	Process a sub-query.
Unique	Discard all duplicate rows.