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.

"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

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

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 (a bad row estimate is a common cause of a runaway outer side) · Memoize (the cache Postgres adds specifically to this join's inner side) · the full EXPLAIN guide'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. Free, no signup, nothing leaves your browser.