Here's ToolShed's browse page, more or less exactly as Priya first wrote it:
def tool_list(request):
tools = Tool.objects.all()
return render(request, "catalog/tool_list.html", {"tools": tools})
{% for tool in tools %}
<li>{{ tool.name }} — listed by {{ tool.owner.username }}</li>
{% endfor %}
Six tools on the page, in local testing, and this is instant — you'd never notice anything was wrong. Verified against an equivalent setup: fetching eight rows and touching .owner on each one fires nine queries total, not one — one to get the tools, then a separate round trip for every single tool's owner, because each tool.owner access is its own lazy lookup that nobody told to happen ahead of time. That's the shape of the N+1 problem: one query for the batch, plus N more, one per row, for whatever related object each row touches. It scales with the data, which is exactly why six tools hides it completely and six hundred does not — the page that was instant in development is the same page that quietly turns into a three-second load once ToolShed actually has real listings on it, with nothing crashing loudly enough along the way to announce it.
select_related is the fix for exactly this shape — a ForeignKey or OneToOneField fetched via one SQL JOIN instead of N separate lookups:
def tool_list(request):
tools = Tool.objects.select_related("owner").all()
return render(request, "catalog/tool_list.html", {"tools": tools})
Same template, zero changes to it — tool.owner.username now reads from data that was already pulled in alongside tool itself. Verified: the exact same eight-row page, same loop, drops from nine queries to one. You can hand select_related more than one relationship at once, too, which matters the moment a page touches more than one FK — ToolShed's "your active loans" page needs both the tool and the borrower on every row:
Loan.objects.select_related("tool", "borrower").filter(status="approved")
A join stops being a good option once the relationship is "many" instead of "one," though — a tool can carry several tags, and joining tools to tags would turn every single-tag tool row into several duplicate copies, one per tag it has. prefetch_related sidesteps the duplication entirely by running a completely separate lookup for the related side, then stitching the two result sets back together in Python:
def tool_detail(request, pk):
tool = get_object_or_404(Tool.objects.prefetch_related("tags"), pk=pk)
...
Verified: rendering each tool's full tag list across the same eight rows drops from three queries down to two — one for the tools, one single query that grabs every tag for every tool at once, rather than one tag lookup per tool.
select_related | prefetch_related | |
|---|---|---|
| Works on | ForeignKey, OneToOneField | ManyToManyField, reverse FK, and everything select_related also handles |
| How it fetches | One JOIN, one query total | One query for the rows you asked for, then one further query per relation you told it to prefetch |
| What gets attached | Joined columns, folded into the same row | A cached result set attached to each object |
There's no way to eyeball this bug from reading a single line — tool.owner.username looks completely ordinary, and it is completely ordinary, right up until it's sitting inside a loop over rows that didn't fetch owner ahead of time. django-debug-toolbar, or just logging connection.queries inside a test, is the honest way to actually catch it before a neighbor does.