EXPLAIN

The EXPLAIN statement reveals how PostgreSQL plans to execute a query without running it. The planner's output shows the tree of operations—scans, joins, sorts, aggregations—along with cost estimates expressed as arbitrary units. Understanding this output is the starting point for query optimization.

EXPLAIN syntax and options

EXPLAIN [ (option [, ...]) ] sql_statement;

Key options:

• ANALYZE: actually executes the statement and shows real runtime statistics alongside estimates. Output is discarded, but for write statements wrap in a transaction and roll back.
• BUFFERS: shows buffer usage (shared hits, reads, dirtied). Only valid with ANALYZE.
• VERBOSE: adds column-level output lists and per-node timing details.
• COSTS: includes startup and total cost estimates (default true).
• FORMAT: output format—TEXT (default), JSON, XML, or YAML.

Reading a simple EXPLAIN output

EXPLAIN SELECT * FROM film;

Output:

                       QUERY PLAN
---------------------------------------------------------
 Seq Scan on film  (cost=0.00..14.00 rows=1000 width=384)

The Seq Scan means PostgreSQL reads every row in the table. cost=0.00..14.00 shows startup cost (0.00) and total cost (14.00). rows=1000 is the planner's row estimate; width=384 is the estimated average row width in bytes.

When filtering by an indexed column, the planner switches to an index scan:

EXPLAIN SELECT * FROM film WHERE film_id = 100;

Output:

                                 QUERY PLAN
---------------------------------------------------------------------------
 Index Scan using film_pkey on film  (cost=0.28..8.29 rows=1 width=384)
   Index Cond: (film_id = 100)

Using EXPLAIN ANALYZE for real statistics

EXPLAIN (ANALYZE, BUFFERS)
SELECT f.film_id, f.title, c.name AS category_name
FROM film f
JOIN film_category fc ON fc.film_id = f.film_id
JOIN category c       ON c.category_id = fc.category_id
ORDER BY f.title;

With ANALYZE, each node shows actual rows and actual time alongside estimates:

 Sort  (cost=295.65..298.15 rows=1000 width=87)
         (actual time=12.301..12.549 rows=1000 loops=1)
   Sort Key: f.title
   Sort Method: quicksort  Memory: 167kB
   Buffers: shared hit=68
   ->  Hash Join  (cost=45.50..245.50 rows=1000 width=87)
         (actual time=0.934..11.102 rows=1000 loops=1)

To analyze a write statement safely, use a transaction:

BEGIN;
EXPLAIN ANALYZE DELETE FROM orders WHERE placed_at < now() - interval '1 year';
ROLLBACK;

Suppressing cost output

Use COSTS FALSE when you want to see the plan structure without the numbers—useful when comparing plans across different data volumes:

EXPLAIN (COSTS FALSE) SELECT * FROM film WHERE film_id = 100;

Output:

             QUERY PLAN
-------------------------------------
 Index Scan using film_pkey on film
   Index Cond: (film_id = 100)

Practical tips

• A large discrepancy between estimated and actual rows usually means stale statistics—run ANALYZE table_name to refresh them.
• A sequential scan on a large table is a flag for a missing index, but sequential scans on small tables are often the right choice.
• Use EXPLAIN (ANALYZE, BUFFERS) to see whether a query is I/O-bound (many buffer reads) or CPU-bound (high timing without many reads).
• The FORMAT JSON option produces machine-readable output that visualization tools like explain.depesz.com can parse.