Visual Explain is a graphical tool for visualizing the output of PostgreSQL EXPLAIN and EXPLAIN ANALYZE commands. These commands show the query plan that a PostgreSQL backend produces for a given optimizable query. The default form of this plan is a plain-text tree that, while easy to read for simple queries becomes progressively more difficult to interpret as query complexity increases. Visual Explain provides a graphical representation of these trees that is easy to navigate and interpret.
The SQL commands that are optimizable and can be analyzed by EXPLAIN are:
SELECT
INSERT
UPDATE
DELETE.
Query response time for a database depends partly on its design. You can learn how the structure of the database affects performance by using the PostgreSQL EXPLAIN and EXPLAIN ANALYZE commands to show the query plan for an optimizable query.
For more information on the EXPLAIN and EXPLAIN ANALYZE commands, refer to the PostgreSQL 7.3 Reference Manual.
For more information on performance tuning, see Performance Tips in the PostgreSQL 7.3 Administrator's Guide.
EXPLAIN measures the performance cost of queries in units of disk-page fetches, which include a guess of the CPU effort converted into disk-page units as well. EXPLAIN gives estimates of:
The start-up cost (the time expended before an output scan can start). For example, the time to do the sorting in a SORT node.
The total cost, if all tuples are retrieved (which they may not be; for example, a query with a LIMIT will stop short of the total cost).
The number of rows output by this plan node (again, without regard for any LIMIT clause).
The average width (in bytes) of rows output by this plan node.
EXPLAIN does not estimate the time spent transmitting result tuples to the frontend as this is a fixed cost that depends only on the size of the result set.
Before you run Visual Explain, ensure that your system has the prerequisite software and configuration.
To run Visual Explain you must have a Java2 Runtime Environment (JRE) of version 1.3 or greater installed (1.4.1 or greater is recommended).
Visual Explain also requires that the PostgreSQL - Red Hat Edition JDBC driver package (rh-postgresql-jdbc) be installed.
The JAVA_HOME environment variable must point to the desired Java JRE. You must make sure that the java command returned by which java is the one from the same JRE. There may be other java commands in your path, so you need to make sure that the correct bin JRE subdirectory is ahead of any others in your PATH environment variable. In particular, a /usr/bin/java path may exist that uses the GNU Java runtime, not the Sun or IBM ones.
If your database server has the rh-postgresql-test package installed and you have run the regression tests, you can perform the examples that appear later in this chapter. If it has not been installed, you might want to install it now and then run the regression tests. (Refer to Section 7.18 Running Regression Tests on a Database Cluster.)