Reading Postgres EXPLAIN ANALYZE Output: A Practical Deep Dive

Slow Postgres queries produce a kind of dread that is hard to shake — especially when you cannot tell whether the database is doing something obviously wrong or working exactly as designed. EXPLAIN ANALYZE is the tool that answers that question. Most engineers have seen it but few read it systematically. This guide walks through the output structure and the specific signals that matter.

Running EXPLAIN ANALYZE Correctly

The basic form:

EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42;

For deeper diagnostic information, add BUFFERS:

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

BUFFERS shows how many pages were read from disk (read) versus served from the shared buffer cache (hit). A high read count on a hot query is a sign that the working set does not fit in memory.

Important: EXPLAIN ANALYZE actually executes the query. For UPDATE or DELETE statements, wrap in a transaction and roll it back:

BEGIN;
EXPLAIN ANALYZE DELETE FROM events WHERE created_at < now() - interval '1 year';
ROLLBACK;

Anatomy of an EXPLAIN ANALYZE Output

A typical output looks like this:

Seq Scan on orders  (cost=0.00..4821.00 rows=1 width=120)
                    (actual time=0.042..48.210 rows=1 loops=1)
  Filter: (customer_id = 42)
  Rows Removed by Filter: 120000
Planning Time: 0.4 ms
Execution Time: 48.3 ms

The Cost Estimate

cost=0.00..4821.00 — the planner's estimate of relative work units. The first number is the startup cost (work before the first row is returned). The second is the total cost. These are not milliseconds; they are abstract units the planner uses to compare plan alternatives.

High total cost relative to other nodes in the plan points to where the query is spending most of its effort.

Estimated vs Actual Rows

rows=1 (estimate) vs actual ... rows=1 (actual). When these diverge badly — estimate of 1 row, actual of 50,000 rows — the planner is working with stale statistics and may have chosen a suboptimal plan. Run ANALYZE on the table to refresh statistics, or tune default_statistics_target for columns with unusual distributions.

Loops

loops=1 means the node ran once. If a node is inside a nested loop join, it might run thousands of times. The actual time shown is per-loop. Multiply by loops to get total time.

Index Scan on order_items  (cost=0.43..8.45 rows=1 width=80)
                           (actual time=0.012..0.014 rows=3 loops=1200)

Here the index scan ran 1,200 times — once per outer row. Total actual time is roughly 0.014 * 1200 = 16.8ms. A nested loop with a large outer set is a common performance problem.

Key Node Types and What They Signal

Seq Scan (Sequential Scan)

Reads every row in the table. Not always bad — if the query needs most of the table, a sequential scan is faster than an index scan. But a Seq Scan with Rows Removed by Filter: 120000 on a table where only 1 row matches is a clear sign a useful index is missing.

Index Scan

Uses an index to locate rows, then fetches heap pages for the full row data. Fast for low-selectivity queries (few matching rows). Can become slow at high selectivity because of random heap page access — this is called a heap fetch bottleneck.

Index Only Scan

Postgres answered the query entirely from the index without touching the heap. This is what covering indexes (INCLUDE) enable. Fast, low I/O.

Bitmap Index Scan + Bitmap Heap Scan

Used when multiple rows match and random access would be inefficient. Postgres first builds a bitmap of matching pages, then reads them in sequential order. More efficient than repeated random heap access for medium-selectivity queries.

Hash Join

Builds a hash table from the smaller of two relations, then probes it for matches. Efficient for large joins when neither side has a usable index. Watch for hash joins spilling to disk — Batches: N where N is greater than 1.

Nested Loop

For each row in the outer relation, scan the inner relation. Fast when the inner scan uses an index and the outer set is small. Catastrophic when the outer set is large and each inner scan is a sequential scan.

Sort

Explicit sort step. If you see a Sort node that could be avoided by an index that delivers rows in the required order, add the index. Look at Sort Method: external merge Disk — this means the sort ran out of work_mem and spilled to disk.

The Most Important Signals to Look For

Plan rows vs actual rows mismatch over 10x: Run ANALYZE on the table. If the problem persists, increase ALTER TABLE ... ALTER COLUMN ... SET STATISTICS N for the affected column.

Sequential scan with high Rows Removed by Filter: A missing or unused index. Check whether an index exists for the filter column and run EXPLAIN to see if the planner is ignoring it.

Sort Method external merge Disk: Increase work_mem for the session (SET work_mem = '64MB';) and re-run. If the sort fits in memory, the query becomes faster. Be careful raising work_mem globally — each sort operation per connection can use this much memory.

Hash Batches greater than 1: The hash join spilled to disk. Increase work_mem.

High actual time on Index Scan with many heap fetches: Consider a covering index using INCLUDE to eliminate heap fetches.

Nested Loop where outer rows are large: Look for a missing join condition or a join that should use a Hash Join. Sometimes the planner makes the wrong choice because row count estimates are off.

A Practical Diagnosis Workflow

1. Run EXPLAIN (ANALYZE, BUFFERS).
2. Find the node with the highest actual time.
3. Check whether estimated rows match actual rows. If not, run ANALYZE.
4. Identify whether the slow node is a sequential scan that should be an index scan, or a sort that should be avoided.
5. Make one change (add an index, rewrite a join, increase work_mem), re-run, compare.

Change one thing at a time. Query plan changes can be non-obvious — a new index can change the plan in unexpected ways elsewhere.

If you have a Postgres performance problem that you have been unable to diagnose from EXPLAIN output alone, the Clixo engineering team can take a look. Start a build and share your schema and queries.