Home / Databases / PostgreSQL

DatabasesHow-toIntermediate

Reading PostgreSQL EXPLAIN ANALYZE: find the slow step in five minutes

Run EXPLAIN with the right options, spot misestimates, sequential scans, sorts spilling to disk and nested loops gone wrong, and know which fix each one needs.

Deepak Bhardwaj2 min read

Applies toPostgreSQL 12–17 (self-managed and managed services)

Run it the right way

EXPLAIN (ANALYZE, BUFFERS, VERBOSE, SETTINGS)
SELECT o.id, c.name FROM orders o JOIN customers c ON c.id = o.customer_id
WHERE o.created_at >= now() - interval '7 days' AND o.status = 'PENDING';

Warning

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

Find the slow query first with pg_stat_statements:

SELECT calls, round(mean_exec_time) AS mean_ms, round(total_exec_time) AS total_ms, left(query, 120)
FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10;

How to read a plan

  • Read from the innermost (most indented) nodes upward. Children feed parents.
  • Each node shows actual time=start..end rows=N loops=L. Time is per loop, so multiply by loops.
  • Find the node where time jumps. That's your problem.

The five common problems

1. Estimated vs actual rows are far apart

Index Scan ... (cost=... rows=12 ...) (actual time=... rows=480000 loops=1)

The planner chose the plan for 12 rows; it got 480,000. Fix: ANALYZE orders;. For correlated columns, extended statistics:

CREATE STATISTICS orders_status_date (dependencies, ndistinct) ON status, created_at FROM orders;
ANALYZE orders;

2. Seq Scan with Rows Removed by Filter huge

The query reads the whole table to keep a few rows. Add an index that matches the filter:

CREATE INDEX CONCURRENTLY idx_orders_pending ON orders (created_at) WHERE status = 'PENDING';

3. Sort spilling to disk

Sort Method: external merge  Disk: 812345kB

Raise work_mem for that session or query (SET work_mem = '256MB';), or add an index that delivers rows already ordered. Don't raise it globally by much: it's per sort node, per connection.

4. Nested Loop with many loops

Nested Loop (actual ... loops=1)
  -> Index Scan on order_items (actual rows=3 loops=250000)

Fine for small outer sets, terrible here. Usually a misestimate (see 1). Better statistics let the planner pick a Hash Join.

5. Lots of Buffers: shared read

High read (from disk) vs hit (from cache) → cold cache or an undersized shared_buffers. Run it twice; if the second run is fast, it's caching.

Functions hide indexes

WHERE lower(email) = 'a@b.com' or WHERE created_at::date = '2026-10-22' can't use a plain index on the column. Use an expression index, or rewrite as a range (created_at >= '2026-10-22' AND created_at < '2026-10-23').

Tip

Turn on auto_explain with log_min_duration = '2s' and log_analyze = on (watch the overhead) in production. Then the plan of the slow query is already in the log when the customer calls.

Related fixes