Reading EXPLAIN ANALYZE — A Practical Guide to Query Plans

Your query is slow. You add an index. It's still slow. You add another index. Now it's slower. What's happening?

EXPLAIN ANALYZE is the answer. It shows exactly what the database does: which indexes it uses (or ignores), which join algorithm it picks, how many rows it actually reads, and where time is spent. If you're debugging database performance without EXPLAIN, you're guessing.

The Basics

EXPLAIN ANALYZE SELECT * FROM users WHERE email = '[email protected]';

Index Scan using users_email_idx on users  (cost=0.29..8.31 rows=1 width=72)
  Index Cond: (email = '[email protected]'::text)
  Planning Time: 0.087 ms
  Execution Time: 0.042 ms

Reading this:

• Index Scan — using the B-tree index on email. Good.
• cost=0.29..8.31 — estimated startup cost (0.29) and total cost (8.31) in arbitrary units.
• rows=1 — estimated 1 row matches. With ANALYZE, you also see actual rows.
• width=72 — estimated average row size in bytes.
• Execution Time: 0.042 ms — 42 microseconds. Fast.

Now the same query without an index:

Seq Scan on users  (cost=0.00..1234.00 rows=1 width=72)
  Filter: (email = '[email protected]'::text)
  Rows Removed by Filter: 49999
  Planning Time: 0.065 ms
  Execution Time: 12.345 ms

Seq Scan — sequential scan. Reading every page in the table. 50,000 rows scanned to find 1 match. 12ms instead of 0.042ms — 300x slower.

The Scan Types

Scan	What it does	When used	Speed
Seq Scan	Read every page	No useful index, or most rows match	O(n)
Index Scan	Traverse B-tree, fetch each row	Few rows match, index is selective	O(log n + k)
Index Only Scan	Read only the index, skip table	All columns are in the index	Fastest
Bitmap Index Scan	Build bitmap of pages, then fetch	Moderate selectivity	Between seq and index

Seq Scan on a large table is the #1 cause of slow queries. The fix is almost always: add an index on the column in the WHERE clause.

But not always. If the query returns 80% of the table, a Seq Scan is actually faster than an Index Scan (sequential reads are faster than thousands of random index lookups). The query planner makes this decision based on statistics.

Reading Join Plans

EXPLAIN ANALYZE
SELECT o.id, o.total, u.name
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.status = 'shipped'
  AND o.created_at > '2026-01-01';

Hash Join  (cost=12.50..456.78 rows=150 actual rows=142 loops=1)
  Hash Cond: (o.user_id = u.id)
  ->  Bitmap Heap Scan on orders o  (cost=8.50..400.00 rows=150 actual rows=142)
        Recheck Cond: (status = 'shipped')
        Filter: (created_at > '2026-01-01')
        Rows Removed by Filter: 28
        ->  Bitmap Index Scan on orders_status_idx  (cost=0.00..8.46 rows=178)
              Index Cond: (status = 'shipped')
  ->  Hash  (cost=3.00..3.00 rows=200 actual rows=200)
        ->  Seq Scan on users u  (cost=0.00..3.00 rows=200 actual rows=200)
Planning Time: 0.234 ms
Execution Time: 1.567 ms

Read bottom-up:

1. Seq Scan on users — scan all 200 users. Small table, no index needed.
2. Hash — build a hash map from the 200 users.
3. Bitmap Index Scan on orders_status_idx — use the status index to find shipped orders (178 candidates).
4. Bitmap Heap Scan — fetch those 178 rows and filter by date (142 remain, 28 removed).
5. Hash Join — match each order to its user via the hash map.

Total: 1.567 ms. Fast because the index narrowed 100,000 orders down to 178, and the users table is tiny.

The Red Flags

"Rows Removed by Filter" is large — the index isn't selective enough. The database fetches many rows from the index but then discards most of them. Consider a more specific index or a composite index.

"actual rows" >> "rows" (estimated) — stale statistics. The planner thinks the table has 1,000 rows but it actually has 1,000,000. Run ANALYZE tablename; to update statistics.

"Sort Method: external merge Disk" — the sort exceeded work_mem and spilled to disk. Dramatically slower. Increase work_mem or add an index that provides the desired sort order.

Nested Loop with large outer table — O(n × m) without an inner index. Often a missing index on the join column. Add it and the nested loop becomes O(n × log m) or the planner switches to a Hash Join.

Multiple Seq Scans on the same large table — the planner is scanning the table once per subquery or CTE. Consider materializing or restructuring the query.

Composite Index Example

-- Slow: index on status doesn't help with the date filter
EXPLAIN ANALYZE SELECT * FROM orders
WHERE status = 'shipped' AND created_at > '2026-01-01'
ORDER BY created_at;

Sort  (cost=500..505)
  ->  Bitmap Heap Scan  (cost=8..450 rows=178)
        -- index only helps with status, date filter is post-scan

-- Add composite index
CREATE INDEX ON orders(status, created_at);

-- Now:
EXPLAIN ANALYZE SELECT * FROM orders
WHERE status = 'shipped' AND created_at > '2026-01-01'
ORDER BY created_at;

Index Scan using orders_status_created_at_idx  (cost=0.29..25.00 rows=142)
  Index Cond: (status = 'shipped' AND created_at > '2026-01-01')
  -- No sort needed: index is already ordered by created_at within status

The composite index handles both the filter AND the sort. One index scan, no separate sort step. The leftmost prefix rule means the order of columns matters: (status, created_at) works for WHERE status = X AND created_at > Y, but (created_at, status) wouldn't help filter on status first.

The Checklist

When a query is slow:

1. Run EXPLAIN ANALYZE — see the actual plan, not just the estimated one.
2. Look for Seq Scan on large tables — add an index.
3. Check "Rows Removed by Filter" — if it's large, the index isn't selective enough.
4. Check estimated vs actual rows — large discrepancy means stale stats. Run ANALYZE.
5. Check for disk sorts — increase work_mem or add a sorted index.
6. Check join algorithms — Nested Loop on large tables without an inner index is expensive.
7. Consider a composite index — if WHERE uses multiple columns, a single composite index is better than separate indexes.

EXPLAIN ANALYZE is the most important debugging tool for database performance. Every developer who writes SQL should know how to read its output.