When Lazy Loading Triggers Repeated Index Scans #

The most common performance artifact an ORM leaves in a PostgreSQL query log is not a slow query — it is a fast query run thousands of times. When application code iterates a result set and touches a lazily loaded relation or a deferred column inside the loop, the ORM emits one Index Scan using <table>_pkey per iteration. Each scan is individually trivial. Collectively they dominate request latency, and they hide from every “slowest query” dashboard because no single execution is slow.

This page teaches the signature that identifies the pattern, the crucial difference between a repeated statement and a nested-loop join’s loops=N, and the three fixes ordered from best to last resort.

The Repeated Index Scan Signature #

Suppose a report iterates 5000 orders rows and, inside the loop, reads order.assigned_user.email, where assigned_user is a lazily loaded relation. The ORM issues one lookup per order:

-- Emitted 5000 times, once per loop iteration, each a separate statement
EXPLAIN ANALYZE
SELECT id, email, display_name FROM users WHERE id = 4821;
Index Scan using users_pkey on users
  (cost=0.29..8.30 rows=1 width=68) (actual time=0.011..0.012 rows=1 loops=1)
  Index Cond: (id = 4821)          -- single primary-key equality lookup
Planning Time: 0.060 ms
Execution Time: 0.028 ms           -- fast in isolation; the problem is 5000 of these

Read the annotations carefully. The cost=0.29..8.30 estimate is unitless planner effort, and it is tiny — this is an efficient point lookup. rows=1 and loops=1 confirm the statement did its job once. Nothing about this single plan looks wrong. The pathology only becomes visible when you count how many times the statement ran, which is not in the plan at all.

loops=N Is Not the Same as N Statements #

This is the distinction that trips up most diagnoses. PostgreSQL genuinely can execute one inner node many times — inside a nested-loop join it drives the inner side once per outer row, and reports that with loops=N on the inner node, all within a single statement:

-- ONE statement: a legitimate nested-loop join
Nested Loop  (cost=0.29..1240.00 rows=5000 width=132)
             (actual time=0.03..14.2 rows=5000 loops=1)
  ->  Seq Scan on orders o   (actual rows=5000 loops=1)
  ->  Index Scan using users_pkey on users u
        (cost=0.29..8.30 rows=1 width=68)
        (actual time=0.002..0.002 rows=1 loops=5000)  -- inner driven 5000x, still ONE query
        Index Cond: (u.id = o.assigned_user_id)

Here loops=5000 lives inside one plan. The planner accounted for it, the executor amortized parse and planning once, and there is a single network round trip. That is healthy.

Lazy loading produces the opposite shape: 5000 separate statements, each parsed, planned, and round-tripped independently. There is no single plan with loops=5000 — instead there are 5000 plans that each report loops=1. The database cannot see them as related; only the application knows they came from one loop.

One statement with loops=N versus N separate statements Left: a single nested-loop join plan where one inner Index Scan reports loops equal to 5000 inside one statement and one round trip. Right: an application loop sending 5000 independent SELECT statements, each a separate round trip reporting loops equals 1. Nested-loop join ONE statement, ONE round trip Seq Scan orders (loops=1) Index Scan users_pkey loops=5000 Planner sees the repetition Parse + plan once Lazy-loading loop 5000 statements, 5000 round trips SELECT ... WHERE id=$1 (loops=1) SELECT ... WHERE id=$1 (loops=1) SELECT ... WHERE id=$1 (loops=1) ⋮ ×5000 Planner sees each in isolation Parse + plan 5000 times

Confirming It in pg_stat_statements #

Because the loop produces identical parameterized statements, pg_stat_statements normalizes them into a single row — and that row’s calls column is the smoking gun.

SELECT query, calls, rows, rows / calls AS rows_per_call, mean_exec_time
FROM pg_stat_statements
WHERE query LIKE 'SELECT id, email, display_name FROM users WHERE id = $1%'
ORDER BY calls DESC;
 query                                             | calls | rows | rows_per_call | mean_exec_time
---------------------------------------------------+-------+------+---------------+----------------
 SELECT id, email, display_name FROM users WHERE.. |  5000 | 5000 |             1 |          0.028
--                                                    ^^^^^         ^^^ point lookup answering
--                                              high calls          one row per call = lazy load

The interpretation: a query that returns exactly one row per call, invoked 5000 times per report run, is a per-row lazy load. A genuine nested-loop join would show up as a single call to the outer statement, with the inner index work folded inside that one plan — you would never see the lookup itself accumulating thousands of calls. High calls with rows_per_call near 1 is the ORM fingerprint; loops=N inside one plan is not.

Step-by-Step Resolution Workflow #

Step 1 — Capture the query stream #

Enable pg_stat_statements and run the request once against a reset baseline:

SELECT pg_stat_statements_reset();
-- exercise the endpoint, then:
SELECT query, calls, rows / GREATEST(calls,1) AS rows_per_call
FROM pg_stat_statements
ORDER BY calls DESC
LIMIT 10;

The offending lookup rises to the top by calls.

Step 2 — Confirm the single-row Index Scan #

EXPLAIN ANALYZE
SELECT id, email, display_name FROM users WHERE id = 4821;

Confirm it is Index Scan using users_pkey, rows=1, loops=1. This proves the individual query is optimal and the problem is volume, not the plan.

Step 3 — Classify: loop vs join #

Ask one question: does the lookup appear as its own high-calls row in pg_stat_statements? If yes, it is an application loop. If instead you see a single call to a larger join query with loops=N in its plan, PostgreSQL is already batching the work and there is nothing to fix here.

Step 4 — Eager-load with a JOIN #

The best fix collapses the 5000 lookups into the parent query. In SQL terms, replace “select orders, then look up each user” with a single join (the ORM equivalent is select_related/joinedload):

EXPLAIN ANALYZE
SELECT o.id, o.total_amount, u.email, u.display_name
FROM orders o
JOIN users u ON u.id = o.assigned_user_id
WHERE o.created_at >= DATE '2026-07-01';
Hash Join  (cost=180.00..920.00 rows=5000 width=132)
           (actual time=1.2..9.4 rows=5000 loops=1)   -- ONE statement, one round trip
  Hash Cond: (o.assigned_user_id = u.id)
  ->  Seq Scan on orders o   (actual rows=5000 loops=1)
  ->  Hash  (actual rows=5000 loops=1)
        ->  Seq Scan on users u  (actual rows=5000 loops=1)

One statement now delivers every user row alongside its order. See Covering Index Design for when a join can be served entirely from an index.

Step 5 — Covering index for unavoidable per-row lookups #

If the access pattern genuinely cannot be batched (for example a cache-miss path that fetches one user at a time), make each lookup an Index Only Scan so it never touches the heap:

CREATE INDEX CONCURRENTLY idx_users_id_covering
  ON users (id) INCLUDE (email, display_name);
EXPLAIN ANALYZE
SELECT id, email, display_name FROM users WHERE id = 4821;
Index Only Scan using idx_users_id_covering on users
  (cost=0.29..4.31 rows=1 width=68) (actual time=0.006..0.006 rows=1 loops=1)
  Index Cond: (id = 4821)
  Heap Fetches: 0          -- ← all columns served from the index, no heap visit

Heap Fetches: 0 is the goal: the lookup halves its cost by skipping the heap. This does not reduce the number of statements, so treat it as a mitigation only when Step 4 is impossible.

Step 6 — Validate #

Re-reset pg_stat_statements, rerun the request, and confirm the point-lookup row’s calls dropped from 5000 to 0 (join) or that its per-call cost fell (covering index). Total request latency should fall by the round-trip savings.

Before/After Plan Comparison #

-- BEFORE: 5000 separate statements, each a single-row Index Scan
5000 × [ Index Scan using users_pkey on users (rows=1) (loops=1) ]
       pg_stat_statements: calls=5000, rows_per_call=1

-- AFTER: one statement joins every user in a single pass
Hash Join (actual rows=5000 loops=1)
  Hash Cond: (o.assigned_user_id = u.id)
       pg_stat_statements: calls=1, rows=5000

The transformation is 5000 round trips into one. The index work is comparable; the eliminated cost is per-statement overhead.

Common Pitfalls #

A deferred column accessed in a hot loop. Selecting a narrow projection to “save bandwidth” and then reading a dropped column inside the loop re-fetches each row individually. If the loop needs the column, include it in the original projection instead of deferring it into 5000 follow-up lookups.

Reading .pk versus the full related object. Accessing only the foreign-key value (order.assigned_user_id) issues no query — the value is already on the parent row. Accessing the related object (order.assigned_user.email) triggers the lazy load. Audit loops for which attributes actually force a fetch; often only one attribute is the culprit.

Assuming the ORM identity map spans requests. The per-request cache deduplicates repeat lookups within one request, but it is discarded at request end. The same 5000 lookups recur on every request, so a warm cache in development can mask a cold-cache stampede in production.

Mistaking a healthy nested loop for the problem. A plan showing loops=5000 on an inner Index Scan inside one statement is PostgreSQL doing the batching for you. Do not “fix” it by adding round trips — verify whether the work is inside one plan before rewriting anything.

Frequently Asked Questions #

Is a plan with loops=5000 the same problem as 5000 separate index-scan statements? No. A plan with loops=5000 on an inner node is a single SQL statement executing a nested-loop join — PostgreSQL drives the inner side once per outer row inside one plan, with one parse and one round trip. Lazy loading in an application loop instead sends 5000 independent SELECT statements over the wire. The first is one query; the second is 5000 queries that merely look identical.

How do I find lazy-loading loops in pg_stat_statements? Look for a normalized query such as SELECT ... FROM users WHERE id = $1 with a very high calls value and a rows/calls ratio near 1. A single point-lookup answering thousands of calls per request cycle is the fingerprint, because a genuine nested loop appears as one call to the outer statement, not thousands of calls to the lookup.

If each lookup is a fast Index Scan, why is lazy loading still slow? Each Index Scan is cheap in isolation, but the cost is paid per statement in network round trips, planning, and protocol overhead. Five thousand round trips dominate total latency even when each execution is a fraction of a millisecond. Collapsing them into a single JOIN or a covering Index Only Scan removes the per-statement overhead, not the index work itself.

Related