Lazy-Loading Plan Artifacts in ORM-Generated SQL #

Lazy loading is the ORM policy of deferring a database fetch until the moment an attribute is accessed. The N+1 query storm is its most famous consequence, but lazy loading leaves several other fingerprints in PostgreSQL execution plans — and some of them mimic N+1 closely enough to send you fixing the wrong thing. This page catalogs the plan-level artifacts of lazy loading at the column level, where deferred and TOAST-backed columns generate repeated single-row scans that a relationship-focused N+1 audit will miss.

Deferred Columns and the Second Index Scan #

Every major ORM lets you exclude columns from the initial fetch: Django’s only() and defer(), SQLAlchemy’s deferred() column property, and Rails’ select. The intent is to skip loading a wide column you do not need. The hazard is what happens when you do touch it afterward.

# Django: fetch orders WITHOUT the large fulfillment_notes column
orders = Order.objects.only("id", "status", "total").filter(status="shipped")
for order in orders:
    audit(order.fulfillment_notes)   # ← each access re-fetches THIS row by primary key

The initial query is narrow and fast. But order.fulfillment_notes was never loaded, so each access forces a second statement — a fresh SELECT for just that column, keyed on the row’s primary key:

EXPLAIN (ANALYZE, BUFFERS)
SELECT fulfillment_notes FROM orders WHERE id = 91824;
Index Scan using orders_pkey on orders  (cost=0.42..8.44 rows=1 width=248)
                                        (actual time=0.017..0.018 rows=1 loops=1)
  Index Cond: (id = 91824)          -- ← SAME table, PRIMARY KEY = deferred-column re-fetch
  Buffers: shared hit=4
Planning Time: 0.049 ms
Execution Time: 0.032 ms

Annotations:

One deferred-column access per row of an N-row result is, physically, another N+1 — but a self-N+1 against the base table rather than a fan-out to a child table.

Telling a Lazy-Load Artifact from a Genuine N+1 #

Both patterns look identical in pg_stat_statements: a normalized single-row Index Scan with huge calls and tiny mean_exec_time. You cannot tell them apart by volume. You tell them apart by reading the Index Cond and the target table.

Deferred-column re-fetch versus relationship N+1 A decision split: repeated single-row Index Scans branch by their Index Cond. Same table plus primary key means a deferred-column re-fetch. Different child table plus foreign key means a relationship N+1. Repeated single-row Index Scan huge calls, tiny mean — read the Index Cond same table, PK child table, FK Deferred-column re-fetch Index Cond: id = $1 on orders (already scanned) cause: only() / defer() / deferred() excluded a column fix: load the column eagerly Relationship N+1 Index Cond: customer_id = $1 on customers (child table) cause: lazy relationship access order.customer per row fix: select_related / prefetch Same volume, different Index Cond → different fix

The rule is mechanical: same table + primary-key Index Cond is a deferred-column re-fetch, cured by loading the column; child table + foreign-key Index Cond is a relationship N+1, cured by select_related/prefetch_related as covered in N+1 query detection. Confusing the two leads to adding an eager relationship load that does nothing, because the repeated scan was never about a relationship.

Lazy Relationship Access as a Column Problem in Disguise #

A subtler artifact arises when a deferred foreign key triggers an implicit lazy join. If the FK column itself is deferred and later accessed, the ORM first re-fetches the FK value (a same-table primary-key scan), then resolves the relationship (a child-table foreign-key scan). One logical access becomes two round-trips of different shapes. In the plan you will see interleaved orders_pkey and customers_pkey scans for the same logical row — a compound artifact that is neither a pure deferred-column nor a pure relationship N+1, but both stacked.

TOAST Fetch Amplification #

Column-level lazy loading interacts badly with PostgreSQL’s TOAST mechanism. Values in TEXT, JSONB, bytea, and other variable-length types that exceed roughly 2 kB are compressed and stored out-of-line in an associated TOAST table. A lazily fetched large column therefore costs more than a single heap page read:

EXPLAIN (ANALYZE, BUFFERS)
SELECT payload FROM events WHERE id = 55012;   -- payload is a large JSONB column
Index Scan using events_pkey on events  (cost=0.43..8.45 rows=1 width=18)
                                        (actual time=0.031..0.052 rows=1 loops=1)
  Index Cond: (id = 55012)
  Buffers: shared hit=9        -- ← 1 heap page + several TOAST chunk pages
Execution Time: 0.071 ms

Annotations:

For large-column workloads, whether to store the column inline, split it to a side table, or cover it with a covering index is a real design decision — a covering index cannot include an out-of-line TOAST value, so the usual “add the column to the index” fix does not apply here.

The second visit to a row you already had The first query fetches a narrow column set; touching a deferred attribute issues a second primary-key lookup for the same row, one per row touched. query 1 — deferred load SELECT id, name FROM orders code touches order.payload a deferred attribute query 2 — SELECT payload WHERE id = $1 once per row touched an Index Scan on the primary key, looking perfectly healthy on its own

Numbered Diagnostic Workflow #

Step 1 — Log every statement the request emits.

# Django
from django.db import connection
# ... run the request ...
for q in connection.queries:
    print(q["time"], q["sql"][:90])

Look for a burst of near-identical single-row SELECTs that differ only in a key parameter.

Step 2 — Read the Index Cond of the repeated statement.

EXPLAIN (ANALYZE, BUFFERS)
SELECT fulfillment_notes FROM orders WHERE id = 91824;

Note two things: the table (orders) and the condition column (id, the primary key).

Step 3 — Classify.

Same table + primary key → deferred-column re-fetch. Different child table + foreign key → relationship N+1. A mix of both → deferred FK triggering an implicit lazy join.

Step 4 — Decide eager vs deferred.

If the request reliably accesses the column, stop deferring it — remove it from only() or add it to the initial fetch:

# Load the previously deferred column up front
Order.objects.filter(status="shipped")            # loads all columns, one pass
# or explicitly include just what you need plus the formerly deferred column
Order.objects.only("id", "status", "total", "fulfillment_notes").filter(status="shipped")

If the column is genuinely rare to access and large (a TOAST-backed JSONB), keeping it deferred and paying the occasional re-fetch may be correct — the choice hinges on access frequency, not a blanket rule.

Step 5 — Verify.

SELECT pg_stat_statements_reset();
-- re-run the request, then:
SELECT calls, left(query, 60) FROM pg_stat_statements ORDER BY calls DESC LIMIT 5;

The repeated re-fetch statement should be gone (loaded eagerly) or its calls reduced to the rare paths that truly need it. Because the initial fetch is now wider, confirm the base scan did not regress — a wider row may tip an index scan into a sequential scan if the extra width changes the planner’s cost math.

Wide columns live somewhere else The main heap tuple holds a pointer for an oversized column; reading it follows the pointer into the TOAST table, which is extra reads the plan attributes to the same node. heap tuple narrow columns + a TOAST pointer TOAST table chunks reassembled on read — extra buffer reads selecting a wide column you never display pays this on every row

Common Pitfalls #

1. only()/defer() producing worse plans. Diagnostic: after adding only(), pg_stat_statements shows a new high-calls primary-key re-fetch on the base table. Fix: you deferred a column the request accesses. Include it in the initial fetch; deferral only pays off for columns never touched on that path.

2. JSONB lazy loads amplifying I/O. Diagnostic: a re-fetch statement has a small mean_exec_time but a high per-call buffer count. Fix: the column is TOAST-backed. Either load it eagerly to amortize the reassembly across one scan, or move rarely-needed large JSONB to a side table fetched only on demand.

3. Serializer field access re-triggering loads. Diagnostic: the queryset used only(), yet the storm appears during response serialization. Fix: a serializer field referencing a deferred column re-triggers the load at render time. Align the serializer’s field set with the queryset’s only() list.

4. Deferred FK causing implicit lazy joins. Diagnostic: interleaved primary-key and foreign-key scans for the same logical row. Fix: the deferred foreign-key column is being accessed, forcing a re-fetch of the FK followed by the relationship lookup. Load the FK column (and, if the relationship is used, select_related it) up front.

5. Assuming deferral reduces total work. Diagnostic: total query count rose after a defer() change. Fix: deferral trades one wide read for one narrow read plus N re-fetches. It reduces work only when the deferred column is never accessed on that path.

A lazy-load artifact is defined by what the plan does not show. Each individual statement is a clean, well-indexed lookup that no reviewer would flag, and the plan for it is genuinely optimal. The defect exists only in the aggregate — in the fact that the same optimal statement runs several thousand times to answer one request — which is why the entire diagnostic technique is about counting rather than about reading node types.

That makes the aggregate views the primary tool. pg_stat_statements collapses the repetition into a single row with a large calls value and a small mean_exec_time, and sorting by total_exec_time moves it to the top where a slow-query log never would. The application-side counterpart is a per-request query counter, which turns the problem into an assertion a test can make: this endpoint must issue at most N statements.

The other half of the discipline is knowing which access patterns defer. Column deferral, relationship traversal, and oversized values stored out of line all produce the same shape — a second visit to a row you already had — but they are fixed differently. Deferred columns are fixed by widening the projection, relationships by eager loading, and out-of-line values by not selecting them at all. Diagnosing which one you have is a matter of reading the second statement’s target list, not its plan.

Chasing the plan instead of the projection. Every statement in a lazy-load artifact is a well-planned primary-key lookup, so plan-level tuning has nothing to work with. The lever is the column list the framework asked for, and it lives in application code rather than in the database. Diagnostic signal: a perfect plan repeated thousands of times. Fix: widen the initial projection so the second visit never happens.

Testing with a warm object cache. A framework that caches loaded objects within a request or a session will serve the second access from memory during development and re-issue it in production, where each request starts empty. Diagnostic signal: a query count that differs between a script and a live request for the same code path. Fix: measure query counts per request against a fresh session, which is what production always has.

Frequently Asked Questions #

How does a deferred column trigger a second Index Scan? #

When only() or defer() excludes a column from the initial SELECT, the ORM builds objects without that attribute loaded. Accessing the attribute later forces the ORM to issue a fresh SELECT for just that column, keyed on the row’s primary key. That fresh statement is a second Index Scan on the same table using the primary-key index — one per row whose deferred column you touch.

How do I distinguish a lazy-load re-fetch from a genuine N+1? #

Read the Index Cond and the table name. A deferred-column re-fetch scans the same table you already scanned, keyed on its primary key (Index Cond: id = $1). A genuine N+1 scans a different, child table keyed on a foreign key (Index Cond: customer_id = $1). Same table plus primary key means lazy-load artifact; child table plus foreign key means relationship N+1.

Why does lazily loading a JSONB column read so many buffers? #

Large TEXT and JSONB values are stored out-of-line in a TOAST table. When you lazily fetch such a column, the row’s Index Scan is followed by additional reads against the TOAST relation to reassemble the value from its chunks. The per-row buffer count therefore includes both the base heap page and several TOAST chunk pages, multiplying I/O well beyond a plain column re-fetch.

Does only() always make queries faster? #

No. only() narrows the initial SELECT, which helps if you never touch the excluded columns. But if the request later accesses a deferred column, only() converts one wide fetch into one narrow fetch plus N single-row re-fetches, which is strictly worse. Defer a column only when you are certain the code path will not read it.

Up: ORM Translation Pitfalls