Using Visual Explain

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/Explain Analyze" icon (or from the Statement menu, select Explain or Explain Analyze). 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/Explain Analyze" icon. If you highlight more than one SQL statement, only the first is explained.

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

To load 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 or Explain Analyze).

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.

An Example: Analyzing the regression Database

1. To begin, EXPLAIN the following statement:

   SELECT * FROM hobbies_r;

   Visual Explain displays the "Scan" icon above the caption "Seq Scan on hobbies_r".

   

   Figure 5-2. Running a Simple SELECT

2. 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

3. 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

4. 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

5. 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.

Manipulating the Visual Explain Display

You can rotate the display by clicking on the "orientation" buttons (or from the View menu, select Orientation -> direction). You can zoom in or out by clicking on the "zoom" buttons (or from the View menu, select Zoom In or Zoom Out, or by choosing a value from the xx% drop-down list).

Depending on the amount of information in the plan and the size of the icons used in the display, you may not be able to view the entire plan at one time. To move the viewing area to another part of the plan, first open the Plan Overview window. The blue rectangle shows the current view in the main panel. To move the view to another area, click in that area in the Plan Overview. (Note that grayed areas are not accessible.)