How to Read EXPLAIN ANALYZE in PostgreSQL

EXPLAIN shows how PostgreSQL plans to run a query. EXPLAIN ANALYZE actually runs it and adds real timings. It's the most important tool for fixing slow SQL.

Run it

EXPLAIN (ANALYZE, BUFFERS)
SELECT o.id, o.total_paise
FROM orders o
WHERE o.customer_id = 42 AND o.status = 'paid'
ORDER BY o.created_at DESC
LIMIT 20;

Careful: ANALYZE executes the statement. For UPDATE or DELETE, wrap it in BEGIN; ... ROLLBACK;.

Reading the output

Limit  (cost=0.43..85.12 rows=20 width=16) (actual time=0.041..0.210 rows=20 loops=1)
  Buffers: shared hit=24
  ->  Index Scan using orders_customer_created_idx on orders o
        (cost=0.43..1210.55 rows=286 width=16) (actual time=0.040..0.205 rows=20 loops=1)
        Index Cond: (customer_id = 42)
        Filter: (status = 'paid'::text)
        Rows Removed by Filter: 3
Planning Time: 0.180 ms
Execution Time: 0.240 ms

• Read from the innermost (most indented) node outwards.
• actual time is in milliseconds: first row..last row, per loop.
• Compare estimated rows with actual rows; a big mismatch means bad statistics and possibly a bad plan.
• Buffers: shared hit are pages from memory; read are pages from disk.
• Rows Removed by Filter shows work wasted after fetching rows.

Common node types

• Seq Scan - reads the whole table. Fine for small tables or when most rows match; a red flag on big tables with selective filters.
• Index Scan / Index Only Scan - uses an index (index-only skips the table entirely).
• Bitmap Heap Scan - collects matches from one or more indexes, then reads the table.
• Nested Loop / Hash Join / Merge Join - join strategies; a nested loop over many rows is often slow.
• Sort with "external merge Disk" - the sort didn't fit in work_mem.

A fixing routine

1. Find the slowest queries with the pg_stat_statements extension.
2. Run EXPLAIN (ANALYZE, BUFFERS) and find the node with the most time.
3. Missing index? Add one matching the filter and sort.
4. Estimates way off? Run ANALYZE table_name or increase statistics for that column.
5. Re-run and compare. Paste plans into a visualiser such as explain.depesz.com for long plans.