Query Optimization - PostgreSQL Developer's Guide - page 131

IN (SELECT warehouse_id from record.warehouse_tbl);
QUERY PLAN
------------------------------------------------------------------
Aggregate (cost=3434597.62..3434597.63 rows=1 width=0)
-> Hash Join (cost=17.62..3309597.62 rows=50000000 width=0)
Hash Cond: (history.warehouse_id = warehouse_tbl.
warehouse_id)
-> Seq Scan on history (cost=0.00..1934580.00 rows=100000000
width=4)
-> Hash (cost=15.12..15.12 rows=200 width=4)
-> HashAggregate (cost=13.12..15.12 rows=200 width=4)
Group Key: warehouse_tbl. warehouse_id
-> Seq Scan on warehouse_tbl (cost=0.00..12.50 rows=250
width=4)
Planning time: 0.241 ms
(9 rows)
Working with EXPLAIN ANALYZE
EXPLAIN with the ANALYZE option shows the actual runtime of the query. This can be
explained with the following example:
warehouse_db=# EXPLAIN ANALYZE SELECT * FROM record.history;
QUERY PLAN
-----------------------------------------------------------------
Seq Scan on history (cost=0.00..1934580.00 rows=100000000
width=46) (actual time=0.013..371995.771 rows=100000000 loops=1)
Planning time: 0.413 ms
Execution time: 378752.345 ms
(3 rows)
In the preceding example, you can see the two rows; one is the same estimated costs,
which we have already discussed, and the second row is the actual runtime. The
estimated start up cost is 0.00 but actually it is not; queries require some time to
start. There is more information, which is loops . Some nodes execute multiple times
such as joins. In this case, loops is more than one and all costs parameters are per
loop and not for whole scans.
EXPLAIN ANALYZE can take a long time for large
tables and complex queries.