EXPLAIN ANALYZE

EXPLAIN ANALYZE is the most powerful query-tuning tool in PostgreSQL. Unlike plain EXPLAIN, it actually runs the query and reports real execution time and row counts at every node in the plan tree. Comparing the "actual" numbers against the planner's "estimated" numbers quickly reveals where the optimizer is making wrong assumptions.

Basic EXPLAIN ANALYZE usage

EXPLAIN ANALYZE
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;

Each node shows both estimated and actual statistics:

 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
   ->  Hash Join  (cost=45.50..245.50 rows=1000 width=87)
                  (actual time=0.934..11.102 rows=1000 loops=1)
         Hash Cond: (fc.category_id = c.category_id)
 Planning Time: 0.512 ms
 Execution Time: 12.841 ms

Key fields to examine:

• actual time=X..Y: milliseconds from node startup (X) to last row returned (Y).
• rows=N: actual row count vs. the planner's estimate—large gaps indicate stale statistics.
• loops=N: how many times the node executed; multiply actual time by loops for total cost of that node.

Adding BUFFERS for I/O analysis

EXPLAIN (ANALYZE, BUFFERS)
SELECT customer_id, SUM(total_amount) AS total
FROM orders
WHERE placed_at >= now() - interval '30 days'
GROUP BY customer_id
ORDER BY total DESC
LIMIT 10;

With BUFFERS, each node reports:

   Buffers: shared hit=520 read=38

"Shared hit" means the data was found in the shared buffer cache. "Read" means PostgreSQL had to fetch pages from disk. A high "read" count relative to "hit" means the working set does not fit in cache—adding memory or an index can help.

Analyzing write statements safely

Because EXPLAIN ANALYZE executes the statement, always wrap DML inside a transaction when you want to measure without committing changes:

BEGIN;
EXPLAIN ANALYZE
UPDATE orders
SET status = 'archived'
WHERE placed_at < now() - interval '2 years';
ROLLBACK;  -- no rows are actually changed

Diagnosing row-count misestimates

When actual rows differ sharply from the estimate, the planner is working with stale or inaccurate statistics. Fix by running ANALYZE on the table:

ANALYZE orders;

-- Then re-run EXPLAIN ANALYZE to see updated estimates
EXPLAIN ANALYZE SELECT * FROM orders WHERE status = 'pending';

For highly skewed data distributions, increase the statistics target for the relevant column:

ALTER TABLE orders ALTER COLUMN status SET STATISTICS 500;
ANALYZE orders;

Practical tips

• Run EXPLAIN ANALYZE at least twice and compare results—the first run may include cold cache effects.
• A "loops" value greater than 1 in a nested-loop join multiplies the node cost; an index on the inner table's join key typically eliminates this.
• Use FORMAT JSON with a visualization tool like explain.depesz.com to navigate complex plans visually.
• The "Planning Time" at the bottom is the time spent choosing the plan; very high planning times on complex queries can sometimes be reduced by simplifying CTEs or using enable_* configuration to restrict plan choices.