Query Performance: select_related & prefetch_related

The N+1 query problem is when fetching a list of N objects with one query, then touching a related object on each one in a loop, makes Django run one extra query per row — 1 + N queries instead of one.

Learn Query Performance: select_related & prefetch_related in our free Django course — a beginner-friendly interactive lesson with worked examples, a…

Part of the free Django course at LearnCodingFast — hands-on lessons with examples you run in your browser, plus practice exercises and a quick quiz.

In this lesson you'll see how N+1 sneaks in, how select_related collapses it into a single JOIN, how prefetch_related turns it into just two queries, and how to trim columns with only()/defer() and actually count the queries you run.

Imagine a page that lists blog posts with each author's name. You write the obvious loop: fetch the posts, then read post.author.name for each one. The list itself is a single query, but post.author was never loaded, so Django quietly fires one extra query per post to fetch the author. With 100 posts that is 1 + 100 = 101 queries — the classic N+1 .

The query log makes the damage obvious — the same author SELECT repeats over and over:

Django gives you two tools to load related data up front. select_related follows to-one relations ( ForeignKey , OneToOneField ) with a SQL JOIN , so the author comes back inside the same row as the post — the N+1 becomes 1 query . prefetch_related handles to-many relations ( ManyToManyField , reverse ForeignKey ) by running a second query for all the related rows and joining them to their parents in Python — N+1 becomes 2 queries .

You can combine and chain them, and even reach across multiple hops with double-underscore syntax:

Once you have the right number of queries, you can shrink how much each one returns. only() loads just the columns you name (plus the primary key); defer() loads everything except the columns you name. Both are useful when a table has heavy TextField or binary columns you do not need on a list page.

To prove your optimisation worked, count the queries. In development read connection.queries ; in tests use assertNumQueries :

Fill in the blank so the optimised version loads the authors with the posts and ends up at a single query, not 1 + N.

❌ Using select_related on a ManyToMany or reverse FK

Django raises FieldError: Invalid field name(s) given in select_related — those relations return many rows, so a single JOIN can't represent them.

✅ Fix: use prefetch_related("tags") for to-many relations and reserve select_related for ForeignKey/OneToOne.

❌ The N+1 is still there after adding select_related

You touched a relation you didn't name, e.g. post.author was loaded but you used post.category.name .

✅ Fix: name every relation you access — select_related("author", "category") — or count queries to find what you missed.

You excluded a column with only() or defer() , then accessed it in a loop, so Django re-fetches it per row.

✅ Fix: include every column you actually use in only(...) , or drop only() for that view.

Given a queryset's optimisations, compute how many queries it runs. Each select_related field folds into the base query (a JOIN), while each prefetch_related field adds one separate query.

Lesson complete — you can find and fix N+1 queries!

You can now recognise the 1 + N pattern, reach for select_related on to-one relations and prefetch_related on to-many relations, trim columns with only() and defer() , and prove the win by counting queries with connection.queries and assertNumQueries .

🚀 Up next: Custom Managers & QuerySets — package these optimised queries into reusable, named methods.

Practice quiz

• Browse all free courses
• Cheat sheets & quizzes
• Coding dictionary