When the Centralized Gate Pass System started handling real traffic, a dashboard that felt instant in development began crawling. The page wasn't doing anything obviously expensive — it just listed passes and the gates they belonged to. The culprit was the oldest performance bug in the ORM book: the N+1 query. ## What an N+1 query actually is You fetch a list of N objects in one query, then touch a related object on each one inside a loop. Django dutifully fires one more query per row. A list of 200 passes that each reads `pass.gate.name` becomes 1 + 200 = 201 queries. Each is fast in isolation; together they bury you in round-trips. ``` # The trap: one query for passes, then one per pass for the gate for p in GatePass.objects.all(): print(p.gate.name) # <-- a fresh SELECT every iteration ``` ## Step 1: make the invisible visible You cannot fix what you cannot measure. I install django-debug-toolbar in development and watch the SQL panel — it shows the query count and flags duplicates outright. In tests and CI I assert the count directly: ``` from django.test.utils import CaptureQueriesContext from django.db import connection with CaptureQueriesContext(connection) as ctx: response = client.get("/passes/") assert len(ctx.captured_queries) < 10 ``` That assertion is the real fix — it stops the regression from ever coming back. ## Step 2: select_related vs prefetch_related The two tools solve two different shapes of relationship. `select_related` follows forward ForeignKey and OneToOne links with a SQL JOIN, pulling everything in a single query. Use it for "many passes, each with one gate." `prefetch_related` handles reverse and ManyToMany links by running a second query and stitching the results together in Python. Use it for "one gate with many passes." ``` # 201 queries -> 1 passes = GatePass.objects.select_related("gate") # For the reverse side, fetch passes for all gates in 2 queries total gates = Gate.objects.prefetch_related("passes") ``` ## Step 3: stop fetching what you do not render The list view didn't need every column or every relation. `only()` and `values()` trim the payload; `annotate()` pushes counts and sums into the database instead of looping in Python. ``` GatePass.objects.select_related("gate").only("code", "issued_at", "gate__name") Gate.objects.annotate(active=Count("passes", filter=Q(passes__active=True))) ``` ## The result The dashboard dropped from ~210 queries to 3, and median response time fell from roughly 900ms to under 100ms — without touching the front-end. The lesson I keep relearning: most "slow Django" isn't slow Python or a slow database. It's a fast database asked the same question 200 times.