# PostgreSQL Nested Loop: When Is It Actually a Problem?

### **PROBLEM**

A Nested Loop join runs its inner side once per row on the outer side. That's the cheapest possible join when the outer side is small — and a real cost multiplier once it isn't. The operator itself is never the problem; the outer row count and the inner side's per-execution cost are.

### **HOW POSTGRESQL EXPOSES IT**

Run `EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)`. A Nested Loop node has exactly two children — `Plans[0]` is the outer side, `Plans[1]` the inner. The inner child's own `"Actual Loops"` tells you how many times it ran; its `"Actual Total Time"` is already an *average per loop*, not a sum — multiply the two yourself to get real total cost.

```plaintext
"Plans": [
  { "Node Type": "Seq Scan", "Actual Rows": 25000, "Actual Loops": 1 },   // outer
  { "Node Type": "Index Scan", "Actual Rows": 1,
    "Actual Loops": 25000, "Actual Total Time": 0.05 }                    // inner
]
```

### **HEALTHY EXAMPLE**

**NO FINDING**

```plaintext
Outer rows:  200
Inner loops: 200, avg 0.05ms each
Total inner-side work: ~10ms
```

Outer side is nowhere near large enough for the multiplication to matter. A Nested Loop over a small outer side is usually the *fastest* join Postgres has.

### **PROBLEMATIC EXAMPLE**

**FIRES — WARNING**

```plaintext
Outer rows:  25,000
Inner loops: 25,000, avg 0.05ms each
Total inner-side work: ~1,250ms
```

Each inner execution is individually cheap — the cost is purely the sheer number of times it runs.

### **HOW TO INVESTIGATE**

*   Check whether the outer row count itself is expected — is a filter earlier in the plan more selective than it should be?
    
*   Check the inner side's own per-loop time: is it cheap-but-frequent (fix the outer count) or genuinely slow-per-execution (fix the inner side)?
    
*   `EXPLAIN (ANALYZE, BUFFERS)` on the inner side alone, standalone, to see what it costs in isolation.
    

### **WHAT NOT TO ASSUME**

**Operator ≠ problem.** A Nested Loop is not "the slow join type" — it's the correct choice whenever the outer side is small, full stop.

**Materiality matters.** 200 loops at 0.05ms each is noise. 25,000 loops at the same per-loop cost is 1.25 seconds of real work — same operator, same per-loop cost, different verdict, because the count changed.

**No index is recommended without evidence.** This pattern alone doesn't tell you which fix applies — reducing the outer count or speeding up the inner side are both real options, and only the plan's own numbers (which one dominates) point at which.

### **PLANREADER EXAMPLE**

PlanReader's `nested-loop-explosion` rule fires only when the outer side clears 10,000 rows *and* the inner side loops 10,000+ times *and* the cumulative inner-side time (loops × per-loop time) clears 1 second — three independent gates, not one. Below 1 second cumulative, it stays silent even if the loop count alone looks alarming. Above 10 seconds cumulative, it escalates to critical. It also tells you which fix applies: cheap-per-loop points at the outer row count, expensive-per-loop points at the inner side itself — never a blanket "add an index" guess.

Related reading: [Cardinality Estimation Errors](https://ecbe8462-b662-46c1-982a-f0b77053cfb0.frame.claudeusercontent.com/postgresql-cardinality-estimation-errors-explained) (a bad row estimate is a common cause of a runaway outer side) · [Memoize](https://ecbe8462-b662-46c1-982a-f0b77053cfb0.frame.claudeusercontent.com/postgresql-memoize-hits-misses-and-evictions) (the cache Postgres adds specifically to this join's inner side) · [the full EXPLAIN guide](https://kiransabne.dev/how-to-read-a-postgresql-execution-plan-the-complete-guide-with-real-examples)'s own join-strategies section.

### **ANALYZE YOUR PLAN**

See exactly which side of your own Nested Loop is driving the cost — paste your EXPLAIN into [**PlanReader**](https://planreader.dev/?utm_source=kiransabne.dev&utm_medium=blog&utm_campaign=nested-loop&utm_content=analyze-cta). Free, no signup, nothing leaves your browser.