Query Performance Tuning - PostgreSQL: Up and Running - page 162

Adding the VERBOSE argument, as in EXPLAIN (VERBOSE) , will report down to the col‐
umnar level. Adding the BUFFERS argument, which must be used in conjunction with
ANALYZE , as in EXPLAIN (ANALYZE, BUFFERS) , will report share hits . The higher this
number, the more records were already in memory from prior queries, meaning that
the planner did not have to go back to disk to reretrieve them.
An EXPLAIN that provides all details, including timing, output of columns, and buffers,
would look something like

EXPLAIN (ANALYZE, VERBOSE, BUFFERS)

your_query_here ; .
It goes without saying that to use graphical EXPLAIN , you need a GUI such as pgAdmin.
After launching graphical EXPLAIN via pgAdmin, compose the query as usual, but in‐
stead of executing it, choose EXPLAIN or EXPLAIN (ANALYZE) from the drop-down menu.
To those of you who pride yourself on being self-sufficient using only the command-
line, all we can say is: good for you!
If you use EXPLAIN (ANALYZE) on a data-changing statement such as UPDATE or IN
SERT and you want to see the plan without making the actual data change, wrap the
statement in a transaction that you abort: place BEGIN before the statement and ROLL
BACK after it.

Sample Runs and Output
Let's try an example. First we'll use the EXPLAIN (ANALYZE) command with a table we
created in Example 4-1 and Example 4-2 .
In order to ensure that the planner doesn't use an index, we first drop the primary key
from our table:

ALTER TABLE census . hisp_pop DROP CONSTRAINT IF EXISTS hisp_pop_pkey ;
This is so that by running the query in Example 9-1 , we can see the most basic of plans
in action, the sequential scan strategy.

Example 9-1. EXPLAIN (ANALYZE) of a sequential scan

EXPLAIN ( ANALYZE ) SELECT tract_id , hispanic_or_latino
FROM census . hisp_pop
WHERE tract_id = '25025010103' ;

Example 9-2 shows the output of Example 9-1 .

Example 9-2. EXPLAIN (ANALYZE) output

Seq Scan on hisp_pop
(cost=0.00..33.48 rows=1 width=16)
(actual time=0.205..0.339 rows=1 loops=1)
Filter: ((tract_id)::text = '25025010103'::text)
Rows Removed by Filter: 1477
Total runtime: 0.360 ms