select_related vs prefetch_related Plans #

Both strategies exist to kill the same problem: the N+1 query pattern, where iterating a list of parents issues one query per parent. They solve it in opposite ways. One folds the related table into the original statement as a join; the other issues exactly one additional statement with an IN list and stitches the results together in application memory.

Neither is universally better, and the plans tell you which fits. Django’s names are used here; the SQLAlchemy and ActiveRecord equivalents behave identically.

The Two SQL Shapes #

select_related (SQLAlchemy joinedload, ActiveRecord includes with references) produces one query with a join:

SELECT o.id, o.total_cents, o.created_at,
       c.id, c.name, c.email, c.address_line1, c.address_line2
  FROM orders o
  JOIN customers c ON c.id = o.customer_id
 WHERE o.created_at > '2026-06-01';

prefetch_related (SQLAlchemy selectinload, ActiveRecord preload) produces two queries:

SELECT id, customer_id, total_cents FROM orders WHERE created_at > '2026-06-01';
SELECT id, name, email FROM customers WHERE id IN (4471, 4472, 4488, …);

The decision rule follows from the cardinality:

• To-one relations (each order has one customer): the join adds no rows, so select_related is nearly always right — one round trip, one plan.
• To-many relations (each customer has many orders): the join multiplies the parent columns by the child count, so prefetch_related transfers far less data despite the extra round trip.

Annotated Evidence #

The to-many case where the join is the wrong shape:

-- select_related equivalent: join multiplies the wide customer row
Hash Join  (actual time=204.8..3841.2 rows=4120334 loops=1)
  Hash Cond: (o.customer_id = c.id)
  ->  Seq Scan on orders o  (actual rows=4120334 loops=1)
  ->  Hash  (actual rows=200000 loops=1)  Buffers: shared hit=40004
  Buffers: shared hit=41208 read=178402
Execution Time: 3894.6 ms      -- 4.1M rows, each carrying full customer columns

-- prefetch_related: two statements, each transferring rows once
Seq Scan on orders  (actual rows=4120334 loops=1)             Execution Time: 2841.0 ms
Index Scan using customers_pkey on customers  (actual rows=118422 loops=1)
  Index Cond: (id = ANY ('{4471,4472,…}'::int[]))
  Buffers: shared hit=356102                                   Execution Time: 210.4 ms

Total time is similar, but the bytes crossing the wire are not: the join sends 4.1 million copies of the customer columns, the prefetch sends 118,422. On wide parent rows this is the difference between a fast page render and a timeout.

The to-one case reverses it:

-- prefetch for a to-one relation: an unnecessary round trip
2 queries, 2 planning phases, application-side stitching     Execution Time: 3.2 ms total

-- select_related: one query, one index lookup per order row
Nested Loop  (actual time=0.03..1.10 rows=412 loops=1)
  ->  Index Scan using orders_created_idx on orders  (actual rows=412 loops=1)
  ->  Index Scan using customers_pkey on customers  (actual rows=1 loops=412)
Execution Time: 1.14 ms

Step-by-Step Selection Workflow #

1. Classify the relation. To-one (ForeignKey, belongs_to) favours the join; to-many (reverse FK, has_many, ManyToMany) favours the prefetch.

2. Capture the SQL each strategy generates rather than guessing:

   from django.db import connection
   print(Order.objects.select_related('customer').filter(...).query)
   print(connection.queries[-1]['sql'])

   The equivalent in ActiveRecord and SQLAlchemy is covered under ORM-generated EXPLAIN output.

3. Compare rows transferred, not query count. Two efficient queries beat one query that transfers ten times the data. Query count is a proxy that stops being useful as soon as the join fans out.

4. Check the IN list size on the prefetch query. Past a few thousand values, planning cost rises and the access path degrades:

   EXPLAIN (ANALYZE) SELECT id, name FROM customers WHERE id = ANY('{…}'::int[]);

   Batch the parent page size to keep the list in the range where per-value index lookups still win.

5. Index the foreign key the second query uses. A prefetch keyed on an unindexed column becomes a sequential scan per batch.

6. Re-measure end to end, including serialisation. The whole point of the prefetch is fewer bytes, and that saving lands outside the database.

Before and After #

-- BEFORE: join-based eager load on a to-many relation
Hash Join  rows=4,120,334 · each carrying the full customer column set
Bytes to client ≈ 1.9 GB      Execution Time: 3894.6 ms

-- AFTER: prefetch, two statements
Query 1 rows=4,120,334 (narrow)   Query 2 rows=118,422 (wide, once each)
Bytes to client ≈ 420 MB      Total Execution Time: 3051.4 ms

Execution time improved modestly; transferred volume fell by 78%. On a to-many relation that is the metric that actually decides page latency.

Common Pitfalls #

Using the prefetch for to-one relations. It adds a round trip and application-side stitching for no benefit. Diagnostic signal: a second query returning almost exactly as many rows as the first. Fix: switch to the join form.

Chaining joins across several to-many relations. Row counts multiply, not add. Diagnostic signal: a result set larger than the product of the tables you expected. Fix: prefetch each to-many branch separately.

Unbounded IN lists. A page size of 50,000 parents produces a statement the planner spends real time parsing. Diagnostic signal: rising Planning Time on the second query. Fix: batch the prefetch.

Counting queries as the only metric. “One query is better than two” is how the join-fan-out problem gets introduced. Diagnostic signal: a single query returning millions of near-duplicate rows. Fix: compare transferred rows, as covered in eager loading vs lazy loading plans.

Eager-loading relations the view never touches. Both strategies fetch unconditionally, so a select_related chain added defensively pulls columns and rows nobody renders. Diagnostic signal: a plan joining four tables for a page that displays two fields. Fix: load what the template actually uses, and revisit the call when the template changes.

Prefetching inside a loop. Calling the prefetch per parent reintroduces the N+1 pattern with a heavier statement each time. Diagnostic signal: hundreds of near-identical queries whose IN lists hold a single value. Fix: prefetch once on the outer queryset, before iteration begins.

Ignoring ordering requirements on the prefetch. The second query returns rows in whatever order the plan produces, so any per-parent ordering must be expressed explicitly or applied in memory. Diagnostic signal: child collections rendering in an unstable order between requests. Fix: order the prefetch query on an index that supports it, so the ordering costs nothing extra.

Frequently Asked Questions #

When is a join-based eager load the wrong choice? When the relation is to-many and the parent rows are wide, because the join repeats every parent column once per child row. A second query keyed by an IN list transfers each row exactly once.

Why does a prefetch get slower as page size grows? Because the IN list grows with the parent count. Beyond a few thousand values the planner abandons per-value index lookups and statement parsing itself becomes measurable. Batching keeps the list in the efficient range.

Do these strategies exist outside Django? Yes: SQLAlchemy’s joinedload and selectinload, ActiveRecord’s includes … references and preload. The SQL shapes and the trade-offs are the same.

Related #

• N+1 Query Detection — parent guide: finding the pattern both strategies fix
• Eager Loading vs Lazy Loading Plans — sibling: the plan-level comparison
• ORM Translation Pitfalls — section overview: what ORMs do to your SQL
• ORM-Generated EXPLAIN Output — capturing the SQL each strategy emits