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.

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.

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.

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