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

In almost all EXPLAIN plans, you'll see a breakdown by steps, and each step can have
have child steps. Each step will have a reported cost that looks something like
cost=0.00..33.48 , as shown in Example 9-2 . The first number, 0.00 , is the estimated
startup cost, and the second number, 33.48 , is the total estimated cost of the step. The
startup is the time before retrieval of data and could include scanning of indexes, joins
of tables, etc. For sequential scan steps, the startup cost is zero because the planner
mindlessly pulls all data so retrieval begins right away.
Keep in mind that the cost measure is reported in arbitrary units, which vary based on
hardware and are largely controlled by planner cost constants. As such, it makes sense
to use only as an estimate when comparing different plans on same server. The planner's
job is to pick the plan with the lowest estimated overall costs.
Because we opted to include the ANALYZE argument in Example 9-1 , the planner will
run the query, and we're blessed with the actual timings as well.
From the plan in Example 9-2 , we can see that the planner elected a sequential scan
because it couldn't find any indexes. The additional tidbit of information Rows Removed
by Filter: 1477 is the number of rows that the planner examined before excluding
them from the output.
In PostgreSQL 9.4, the output makes a distinction between planning time and execution
time. Planning time is the amount of time it takes for the planner to come up with the
execution plan, whereas the execution time is everything that follows. The output in
version 9.4 would look as shown in Example 9-3 .
Example 9-3. EXPLAIN (ANALYZE) output in version 9.4
Seq Scan on hisp_pop
(cost=0.00..33.48 rows=1 width=16) (actual time=0.213..0.346 rows=1 loops=1)
Filter: ((tract_id)::text = '25025010103'::text)
Rows Removed by Filter: 1477
Planning time: 0.095 ms
Execution time: 0.381 ms
Let's now add back our primary key:
ALTER TABLE census . hisp_pop ADD CONSTRAINT hisp_pop_pkey PRIMARY KEY ( tract_id );
Repeating Example 9-1 , we now see the plan output in Example 9-4 (PostgreSQL 9.4
style).
Example 9-4. EXPLAIN (ANALYZE) output of index strategy plan
Index Scan using idx_hisp_pop_tract_id_pat on hisp_pop
(cost=0.28..8.29 rows=1 width=16) (actual time=0.018..0.019 rows=1 loops=1)
Index Cond: ((tract_id)::text = '25025010103'::text)
Planning time: 0.110 ms
Execution time: 0.046 ms