The EXPLAIN command can be used to see the estimated time and execution plan the query optimizer uses to execute a sequel statement. The parameters for the EXPLAIN command are:

verbose

Prints a detailed report of the execution plan.

query

Any valid query statement.

The EXPLAIN command can help you calculate which queries are faster and easier for the query optimizer to perform. For example, this will print a detailed report of the time it takes to search for publisher names that begins with the letter 'C':

  EXPLAIN SELECT publ_name FROM publish
          WHERE  publ_name LIKE 'C%';

It prints the message:

 
NOTICE:  QUERY PLAN:
 
Seq Scan on publish  (cost=0.00..1.07 rows=1 width=12)
 
EXPLAIN
   

This uses the explain command can to calculate how much it will cost to use an aggregate function that calculates the total suggested price of books:

  EXPLAIN VERBOSE 
          SELECT SUM(sugg_price) 
          AS average FROM publish; 
   

The resulting output is:

NOTICE:  QUERY PLAN:
 
Aggregate  (cost=1.07..1.07 rows=1 width=4)
  ->  Seq Scan on publish  (cost=0.00..1.06 rows=6 width=4)
 
EXPLAIN
   

Notice that the it costs less to execute the first select statement which looks for a publisher name beginning with the letter 'C.' Executing a command which uses an aggregate function causes it to first perform the aggregate function on the column then a sequence scan on the table. As you can see, this can be time consuming if the table was much larger.

The psql command with the -E option also provides a great way to debug sequel statements. It displays the actual query that the internal commands generate to calculate the output. For instance, when you perform a \l inside of psql, it generates a query that extracts the information about the database relations and then displays all of that information. Using the psql command \l, the following is returned when the -E is turned on:

********* QUERY *********
SELECT pg_database.datname as "Database",
       pg_user.usename as "Owner"FROM pg_database, pg_user
WHERE pg_database.datdba = pg_user.usesysid
 
UNION
 
SELECT pg_database.datname as "Database",
       NULL as "Owner"FROM pg_database
WHERE pg_database.datdba NOT IN (SELECT usesysid FROM pg_user)
ORDER BY "Database"
*************************
 List of databases
 Database  |  Owner
-----------+----------
 booktown  | postgres
 template0 | postgres
 template1 | postgres
(3 rows)