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:
Prints a detailed report of the execution plan.
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)