Eager Loading vs Lazy Loading: Plan Impact #

Every ORM offers two ways to pull related rows: eagerly, by folding the relationship into a JOIN so one statement returns everything, or lazily, by issuing a fresh query the moment your code touches the attribute. The choice is usually framed as “one query good, many queries bad” — but that framing hides a trap. Eager loading changes the shape of the execution plan, and for certain relationship graphs the single-query plan reads dramatically more rows than the many-query alternative. This page shows how to read that difference in a PostgreSQL plan and decide by evidence rather than reflex.

Two loading strategies, two plan shapes #

Lazy loading (Django’s default relationship access, SQLAlchemy’s lazy='select') produces many small, round-trip-bound plans. Each is a trivial primary-key lookup, but you pay network latency once per parent row — the classic N+1 pattern covered in detecting N+1 queries in the Django ORM.

Eager loading (select_related, prefetch_related, joinedload, selectinload) trades round trips for a wider single plan. There are two distinct eager strategies, and they generate completely different plans:

• JOIN-based eager (select_related, joinedload) folds the relation into the driving query as an INNER or LEFT join. One statement, one plan.
• IN-based eager (prefetch_related, selectinload) issues a second query of the form WHERE parent_id IN (...) using the primary keys gathered from the first. Two statements, two independent plans, no join between them.

The distinction matters most when the relationship is to-many.

When eager loading is worse: the join explosion #

A single to-many JOIN is fine — 1,000 orders joined to their 4,000 line items produces 4,000 rows, which is the true size of the result. The problem appears when you eagerly join two or more to-many relations in the same statement. Because SQL joins produce a cartesian product per parent, joining orders to both line_items and shipments yields roughly N_line_items × N_shipments rows per order.

Consider 1,000 orders, each with ~4 line items and ~3 shipments. The natural data is 4,000 line-item rows and 3,000 shipment rows. But the stacked join emits ~12 rows per order — 12,000 rows — and every order’s wide column set is duplicated across all 12.

-- Eager JOIN-based load of TWO to-many relations in one statement
EXPLAIN (ANALYZE, BUFFERS)
SELECT o.order_id, o.placed_at, o.billing_address,
       li.line_item_id, li.sku, li.quantity,
       sh.shipment_id, sh.carrier, sh.tracking_code
FROM orders o
JOIN line_items li ON li.order_id = o.order_id
JOIN shipments  sh ON sh.order_id = o.order_id
WHERE o.placed_at >= DATE '2026-06-01';

Hash Join  (cost=210.00..2140.00 rows=12000 width=180)
           (actual time=1.9..44.6 rows=12000 loops=1)   -- ← 12000 rows from 1000 orders
  Hash Cond: (sh.order_id = o.order_id)
  Buffers: shared hit=980 read=610
  ->  Hash Join  (cost=120.00..900.00 rows=4000 width=120)
                 (actual time=1.1..12.4 rows=4000 loops=1)  -- ← first to-many: 4000 (OK)
        Hash Cond: (li.order_id = o.order_id)
        ->  Seq Scan on line_items li ...
        ->  Hash  (rows=1000 width=96)
              ->  Seq Scan on orders o
                    Filter: (placed_at >= '2026-06-01')     -- ← 1000 driving rows
  ->  Hash  (cost=60.00..60.00 rows=3000 width=64)
        ->  Seq Scan on shipments sh ...                    -- ← second to-many multiplies 4000 → 12000

Annotation: the inner Hash Join correctly emits 4,000 rows (one per line item). The outer Hash Join then multiplies that by the shipment fan-out, producing 12,000 rows — three times the largest real child table and twelve times the driving table. Each of those 12,000 rows carries the full 180-byte width, so orders.billing_address and every wide column is shipped twelve times. The width=180 combined with rows=12000 is the cost the cost estimation models are pricing, and it is almost entirely duplication.

The IN-based alternative #

Splitting each to-many branch into its own IN (...) query removes the cartesian product entirely. This is what prefetch_related and selectinload emit under the hood.

-- Query 1: the parents
EXPLAIN ANALYZE
SELECT order_id, placed_at, billing_address
FROM orders WHERE placed_at >= DATE '2026-06-01';
--  Seq Scan on orders (actual rows=1000)   -- ← 1000 rows, no duplication

-- Query 2: line items for exactly those parents
EXPLAIN ANALYZE
SELECT line_item_id, order_id, sku, quantity
FROM line_items WHERE order_id IN ( /* 1000 ids */ );
--  Index Scan using line_items_order_id_idx (actual rows=4000)  -- ← natural 4000

-- Query 3: shipments for exactly those parents
EXPLAIN ANALYZE
SELECT shipment_id, order_id, carrier, tracking_code
FROM shipments WHERE order_id IN ( /* 1000 ids */ );
--  Index Scan using shipments_order_id_idx (actual rows=3000)   -- ← natural 3000

Three round trips return 1,000 + 4,000 + 3,000 = 8,000 rows total, each at its natural width, versus one round trip returning 12,000 fat rows. The ORM stitches the children back onto the parents in application memory. Fewer rows read, no duplicated wide columns, and the plans are simple index scans.

Decision guidance #

Pick the strategy from the relationship type, not from a blanket rule.

The rule of thumb: keep to-one relationships eager and joined; split every to-many relationship into its own IN query.

Step-by-step: choosing by plan shape #

Step 1 — Capture the eager plan #

Run EXPLAIN (ANALYZE, BUFFERS) on the JOIN-based eager query and record the actual rows leaving the topmost join node, plus Buffers read.

Step 2 — Compare against the driving row count #

Divide the top-join row count by the driving table’s row count. A ratio near 1 is healthy; a ratio equal to the product of your child cardinalities confirms row multiplication.

Step 3 — Count the to-many branches #

Inspect the join tree. Every join to a child table that can have multiple rows per parent is a to-many branch. Two or more stacked to-many branches is the explosion signature.

Step 4 — Split each to-many branch into an IN query #

Rewrite so the parents are fetched once and each to-many child is fetched with WHERE parent_id IN (...). In an ORM this is prefetch_related (Django) or selectinload (SQLAlchemy).

Step 5 — Confirm natural row counts #

Each split query’s plan should show its natural child count and no de-duplicating Sort or HashAggregate above it. If a DISTINCT is still present, a to-many join is still hiding in the query.

Step 6 — Re-measure #

Sum rows read and round trips across the split queries and compare to the single eager plan. Keep to-one relations joined; ship the to-many splits.

Before / after #

-- BEFORE: two stacked to-many joins, cartesian product
Hash Join  (actual time=1.9..44.6 rows=12000)   width=180   -- 12x the driving table
  ->  Hash Join (actual rows=4000)
  ->  Hash on shipments (rows=3000)
Total rows shipped: 12000 wide rows, 1 round trip

-- AFTER: parents + two IN queries
Seq Scan on orders          (actual rows=1000)
Index Scan on line_items    (actual rows=4000)  Index Cond: order_id = ANY(...)
Index Scan on shipments     (actual rows=3000)  Index Cond: order_id = ANY(...)
Total rows shipped: 8000 natural rows, 3 round trips

The after-state reads two-thirds the rows, ships no duplicated wide columns, and every node is a plain index scan. Three round trips beat one bloated join because the row volume — not the statement count — dominated.

Common pitfalls #

select_related across a nullable FK forces a LEFT JOIN. When the foreign key column allows NULL, the ORM must keep parents that have no related row, so it emits a LEFT OUTER JOIN. The plan widens, produces NULL-extended rows, and the planner cannot push filters through the outer side as freely. If the relationship is truly mandatory, declare the FK NOT NULL so an INNER JOIN is available.

Over-fetching wide columns inside a to-many join. Every wide parent column (billing_address, JSON blobs, text bodies) is duplicated onto every joined child row. A single TEXT column on the parent, joined to a 12× fan-out, is transferred twelve times. Select only the columns you use, or split the to-many branch so the parent columns are fetched once.

Bolting DISTINCT on to hide multiplication. Adding DISTINCT (or the ORM’s .distinct()) to collapse duplicated rows does not undo the work — it adds a Sort or HashAggregate node above the join that must process every duplicated row first. You pay the cartesian-product cost and the de-duplication cost. Treat a DISTINCT that only exists to fix row counts as a signal to split the query.

Assuming more statements is always slower. IN-based loading issues more statements, which looks worse in a naive query counter, yet reads far fewer rows. Measure rows and buffers, not statement count.

Frequently Asked Questions #

Is eager loading always faster than lazy loading? No. Eager loading collapses many round trips into one JOIN plan, which wins when you follow a to-one relationship or a single to-many relationship. But joining two or more to-many relationships in the same statement multiplies parent rows by the product of the child cardinalities, so the plan reads and transfers far more rows than exist. In that case separate IN (...) queries (selectinload / prefetch_related) are faster despite issuing more statements.

How do I see row multiplication in an execution plan? Run EXPLAIN ANALYZE and compare the actual rows emitted by the top join against the row count of the driving table. If 1,000 parent rows leave the top Nested Loop or Hash Join as 10,000+ rows, each parent is being duplicated once per matching child on every to-many branch. A DISTINCT or GROUP BY appearing above the join to de-duplicate is a second tell-tale sign.

Why does select_related add a LEFT JOIN instead of an INNER JOIN? When the foreign key is nullable, the ORM must preserve parent rows that have no related row, so it emits a LEFT OUTER JOIN. This widens the plan and produces NULL-extended rows the optimizer cannot filter early. Making the FK NOT NULL, or switching to a separate IN query, lets the planner use a cheaper INNER JOIN or avoid the join entirely.

Related

• N+1 Query Detection — parent cluster: how ORMs turn relationship access into query patterns and how to catch the bad ones
• ORM Translation Pitfalls — grandparent pillar: the full map of how object mappers translate into SQL and execution plans
• Detecting N+1 Queries in the Django ORM — the sibling technique for spotting the lazy-loading round-trip storm this page’s eager strategies fix
• Cost Estimation Models — how the planner prices the row count and width that row multiplication inflates