# Reading Postgres EXPLAIN ANALYZE Output: A Practical Deep Dive

> Learn to read Postgres EXPLAIN ANALYZE output confidently — understand node types, cost estimates, actual rows, and the signals that point to real query problems.

- **Published:** 2025-12-07
- **Author:** Clixo
- **Reading time:** 6 min read
- **Tags:** postgres, query-performance, explain-analyze, database
- **Canonical URL:** https://clixo.sh/blog/reading-postgres-explain-analyze-output

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:

```sql
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42;
```

For deeper diagnostic information, add `BUFFERS`:

```sql
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:

```sql
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.

```mermaid
flowchart TD
  A["Run EXPLAIN (ANALYZE, BUFFERS)"] --> B[Find node with highest actual time]
  B --> C{Estimated rows vs actual rows: large mismatch?}
  C -->|Yes| D[Run ANALYZE to refresh table statistics]
  C -->|No| E{Sequential scan with many rows removed?}
  D --> E
  E -->|Yes| F[Add index on filter column]
  E -->|No| G{"Sort spilling to disk or hash batches > 1?"}
  F --> G
  G -->|Yes| H[Increase work_mem for the session]
  G -->|No| I[Make one change, re-run, compare plans]
```

---

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](https://clixo.sh/#contact) and share your schema and queries.

---

Clixo · 1141 W Bryn Mawr Ave, Itasca, IL 60143, US · [hello@clixo.sh](mailto:hello@clixo.sh)
[Start a build](https://clixo.sh/#contact) · [All services](https://clixo.sh/services) · [Agent guide (llms.txt)](https://clixo.sh/llms.txt)