EXPLAIN ANALYZE in PostgreSQL: How to Find Slow Queries

The problem

A query that returns instantly on your laptop against a few thousand seed rows can take seconds in production against a table with tens of millions of rows. Guessing why — "maybe it needs an index" — gets you nowhere reliably. EXPLAIN ANALYZE tells you exactly what the planner decided to do and how long each step actually took, which is the only reliable starting point for fixing a slow query instead of guessing at it.

Why it matters

Backend engineers hit this constantly: a report endpoint that used to take 50ms now takes 4 seconds, or a query that's fine in staging times out in production. Without reading the actual execution plan, the usual response is to add an index and hope, which sometimes helps, sometimes does nothing, and sometimes makes writes slower for no benefit. Reading the plan turns that guess into a diagnosis: you can see whether the planner is scanning the whole table, picking the wrong join strategy, or working off statistics that are stale enough to mislead it.

EXPLAIN vs EXPLAIN ANALYZE

EXPLAIN shows the planner's intended plan and its cost estimates, without running the query. EXPLAIN ANALYZE actually executes the query and reports real timings and row counts alongside the plan.

-- Plan only, does not run the query
EXPLAIN SELECT * FROM orders WHERE customer_id = 482;
 
-- Executes the query and reports actual timing
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 482;

Reading a plan

A plan is a tree, read from the innermost (deepest-indented) node outward. Each node reports:

• cost — cost=start..total, the planner's estimated relative cost, useful for comparing options, not a real time unit.
• rows — estimated rows the node will produce.
• actual time — actual time=start..end, in milliseconds, only present with ANALYZE.
• rows (actual) — how many rows the node actually produced.
• loops — how many times the node executed, relevant inside nested loops.

The single most useful comparison is estimated rows vs. actual rows. A large gap means the planner's statistics don't reflect reality, which can cause it to pick the wrong scan type or join strategy for the rest of the plan.

Scan types

Sequential scan reads every row in the table. It's not inherently bad — for a small table, or a query that needs most of the table's rows anyway, a sequential scan is often cheaper than the overhead of an index lookup.

Seq Scan on orders  (cost=0.00..18334.00 rows=1 width=96) (actual time=0.024..142.311 rows=1 loops=1)
  Filter: (customer_id = 482)
  Rows Removed by Filter: 999999
Planning Time: 0.112 ms
Execution Time: 142.340 ms

The Rows Removed by Filter line is the tell: PostgreSQL read a million rows to find one. That's a strong signal for an index.

Index scan uses a B-tree (or other) index to jump directly to matching rows, then fetches each row from the table:

Index Scan using idx_orders_customer_id on orders  (cost=0.42..8.44 rows=1 width=96) (actual time=0.031..0.034 rows=1 loops=1)
  Index Cond: (customer_id = 482)
Planning Time: 0.098 ms
Execution Time: 0.058 ms

Bitmap index scan shows up when the planner expects enough matching rows that jumping to each one individually (as a plain index scan does) would cost more random I/O than building a bitmap of matching pages first and reading them in physical order:

Bitmap Heap Scan on orders  (cost=4.51..812.30 rows=210 width=96) (actual time=0.412..2.104 rows=198 loops=1)
  Recheck Cond: (status = 'pending')
  ->  Bitmap Index Scan on idx_orders_status  (cost=0.00..4.46 rows=210 width=0) (actual time=0.201..0.201 rows=198 loops=1)
        Index Cond: (status = 'pending')
Planning Time: 0.145 ms
Execution Time: 2.201 ms

Before/after: fixing a sequential scan

Given this query and no relevant index:

SELECT id, status, total_cents, created_at
FROM orders
WHERE customer_id = 482
ORDER BY created_at DESC;

The plan does a full sequential scan followed by an in-memory sort, as shown above — over 140ms on a million-row table. Adding a composite index that matches both the filter and the sort order:

CREATE INDEX CONCURRENTLY idx_orders_customer_created
ON orders (customer_id, created_at DESC);

changes the plan to an index scan that returns rows already in the required order, avoiding both the full scan and the separate sort step:

Index Scan using idx_orders_customer_created on orders  (cost=0.42..12.86 rows=6 width=96) (actual time=0.028..0.041 rows=6 loops=1)
  Index Cond: (customer_id = 482)
Planning Time: 0.104 ms
Execution Time: 0.067 ms

Execution time drops from ~142ms to under 0.1ms — the difference between scanning a million rows and walking a few B-tree pages.

Join strategies

The planner picks between three join strategies depending on table sizes and available indexes:

• Nested loop — for each row in the outer table, scan the inner table (usually via an index). Cheap when the outer side is small.
• Hash join — build an in-memory hash table from the smaller side, then probe it with the larger side. Common when neither side is small enough for a nested loop and there's no useful index for it.
• Merge join — both inputs are sorted on the join key and merged in order. Shows up when both sides are already sorted, often via an index.

If a join you expect to be a fast nested-loop-with-index shows up as a hash join over a full sequential scan, that's usually either a missing index on the join column or a row estimate so far off that the planner ruled the index-based plan out.

Using BUFFERS to see I/O, not just time

EXPLAIN (ANALYZE, BUFFERS) adds shared buffer statistics — how much of the work was served from PostgreSQL's cache versus read from disk:

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE customer_id = 482;

Index Scan using idx_orders_customer_id on orders  (cost=0.42..8.44 rows=1 width=96) (actual time=0.031..0.034 rows=1 loops=1)
  Index Cond: (customer_id = 482)
  Buffers: shared hit=4
Planning Time: 0.098 ms
Execution Time: 0.058 ms

A query that looks fast in a warm cache (shared hit) can still be slow the first time it runs cold (shared read) against disk. On a busy production database with a working set larger than memory, buffer hits/reads often explain more about real-world latency than the cost estimate does.

Production considerations

Keep statistics current. The planner's row estimates come from pg_statistic, refreshed by ANALYZE (autovacuum runs this automatically, but a large bulk load can leave statistics stale until the next run). If estimated and actual rows diverge by an order of magnitude, run ANALYZE orders; manually before concluding the plan itself is wrong.

Find slow queries first with pg_stat_statements. Don't EXPLAIN ANALYZE blind — enable the pg_stat_statements extension and query it for the queries with the highest total or mean execution time, then investigate those with EXPLAIN ANALYZE specifically.

Prefer CREATE INDEX CONCURRENTLY in production. A plain CREATE INDEX takes a lock that blocks writes to the table for the duration of the build; CONCURRENTLY avoids that at the cost of a slower build and a small chance of needing a retry if it's interrupted.

Common mistakes

• Reading only the estimated cost instead of comparing estimated vs. actual rows and time — the cost is a planner heuristic, not a promise.
• Ignoring buffers and assuming a query is fast because it was fast once, when the first run was served from a cold cache and later production traffic won't be.
• Adding an index for a query that returns most of the table anyway — if a query needs 80% of a table's rows, a sequential scan is often genuinely the fastest plan, and an index will sit unused or force the planner into a slower access path.
• Optimizing against a near-empty local database where every plan looks identical regardless of missing indexes, then being surprised when production, with realistic data volume, behaves completely differently.

Summary

EXPLAIN shows what the planner intends to do; EXPLAIN ANALYZE shows what actually happened, in real time, against real data. The habit worth building is reading a plan from the inside out, comparing estimated to actual rows at each node, and checking buffers alongside timing before deciding what to change. Combined with realistic indexing from PostgreSQL Indexes: A Practical Guide for Backend Engineers, this is most of what's needed to diagnose slow queries in a production Postgres database without guessing.