Skip to content
Tutorials Center
My learning

Topics

Lesson 22 of 77 ยท Models and the ORM

ORM Performance: N+1, select_related, prefetch_related

Find and fix slow Django queries - count queries, remove the N+1 problem with select_related and prefetch_related, trim columns, stream rows and add indexes.

The Django ORM makes it easy to write code that works and hard to notice that it is slow. A page that shows twenty books with their authors reads naturally in a template, yet it may quietly run twenty-one database queries. With ten rows nobody notices. With ten thousand rows and many users, the database becomes the bottleneck of the whole site.

This lesson gives you a method: measure the number of queries, fix the usual suspects, and verify that the count went down. You will meet the N+1 problem, select_related() and prefetch_related(), the Prefetch object, column trimming with only() and defer(), streaming with iterator(), and indexes together with explain(). The models come from Relationships; the cost of related lookups is the reason that lesson ends with a pointer here.

All examples run Django in your browser against an in-memory SQLite database, so the number of queries is what we measure. Timings are left out on purpose: a tiny local database hides the real cost, which is the round trip to a database server in production. Fewer queries is nearly always faster.

Measure first

Never guess where the time goes. Django records every SQL statement when you wrap code in CaptureQueriesContext, which works whether or not DEBUG is on. len(context) is the number of queries, and context.captured_queries is a list of dictionaries with the SQL text. In development you can also read django.db.connection.queries when DEBUG = True, and in tests assertNumQueries pins the count so a regression fails loudly (see Testing Django Apps). The django-debug-toolbar package shows the same data in your browser for every request.

The N+1 problem

A ForeignKey attribute is loaded lazily: the first access to book.author runs a query for that one author. Inside a loop that means one query for the list plus one query per row, which is where the name "N+1" comes from.

sequenceDiagram
    participant V as View code
    participant DB as Database
    V->>DB: SELECT books (1 query)
    DB-->>V: 12 rows
    loop for each of the 12 books
        V->>DB: SELECT author WHERE id = ...
        DB-->>V: 1 row
    end
    Note over V,DB: 13 queries for one page

The naive loop pays one extra round trip per row.

Django offers two cures, and the difference between them is the most important idea in this lesson:

  • select_related() follows single-valued relations (ForeignKey and OneToOneField, forward or reverse) with a SQL JOIN, so everything arrives in one query.
  • prefetch_related() handles multi-valued relations (reverse foreign keys and ManyToManyField). It runs one extra query per relation, then stitches the results together in Python, so the total is a small constant number of queries no matter how many rows there are.
flowchart TD
    A["Which relation do you read?"] --> B{"Single object per row?<br/>(ForeignKey, OneToOne)"}
    B -->|yes| C["select_related('author')<br/>one JOIN query"]
    B -->|"no: many objects"| D["prefetch_related('books')<br/>one extra query per relation"]

Join for the "one" side, prefetch for the "many" side.

Python Counting queries: N+1, select_related, prefetch_related

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The numbers tell the story. The naive loop ran 13 queries for 12 books, and select_related("author") needed 1. For the reverse relation, counting each author's books ran 5 queries (the list plus one per author), while prefetch_related("books") needed 2 however many authors there are. Loading books with their author and their tags took just 2 queries in total. After a prefetch_related(), call .all() and iterate or take len() of it, and the prefetched rows are used. Filtering the prefetched manager again (author.books.filter(...)) ignores that cache and runs a new query.

Prefetch objects

Sometimes you want only some of the related rows, in a particular order, or under a different attribute name. Pass a Prefetch("books", queryset=..., to_attr="recent") object. The custom queryset can itself use select_related() or prefetch_related(), and to_attr stores the result in a plain list, which avoids mixing the filtered and unfiltered rows. You can also chain through relations with double underscores: prefetch_related("books__tags") loads the books and then their tags, in three queries in total.

Load less: only(), defer(), values()

Every column you do not need still travels from the database. That matters for large text or JSON columns, such as a long summary. Three tools help:

  • only("title", "year") loads just those fields (plus the primary key). defer("summary") loads everything except the named ones.
  • A skipped field is not lost. Touching it triggers one extra query for that instance, which brings back the N+1 problem if you do it in a loop. Use get_deferred_fields() to check.
  • values() and values_list() return dictionaries or tuples instead of model objects, which is the cheapest way to read plain data for an export or a chart.

Use the right call for the question, too. exists() stops at the first match, count() asks the database for a number, and len(queryset) loads every row to count them in Python. A queryset caches its rows after the first evaluation, so iterating it twice costs one query, not two.

For very large result sets, iterator(chunk_size=...) streams rows without keeping them all in memory. The trade-off is that nothing is cached, and combining it with prefetch_related() needs chunk_size to be set on recent Django versions.

Python only(), defer(), exists() and iterator()

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Indexes and explain()

Fewer queries is only half of the story. A single query can be slow when the database has to read every row to find a match, which is called a full table scan. An index is a sorted lookup structure on one or more columns that lets the database jump straight to the rows you want, much like the index of a book.

Django creates indexes for you in several cases: primary keys, unique=True fields and foreign keys. You add more with db_index=True on a field, or with Meta.indexes for several columns or named indexes. Put an index on columns that you filter, order or join by often. The cost is slower writes and more disk space, so do not index everything. A composite index such as ["author", "-year"] serves queries that filter by author and sort by year, and it can also serve queries that filter by the first column alone.

queryset.explain() asks the database how it plans to run a query. The text differs per database: PostgreSQL prints a tree with costs, SQLite prints lines such as SEARCH ... USING INDEX. The next example only checks whether the plan scans the table or searches an index.

Python Indexes change a scan into a search

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Adding or changing an index is a schema change, so it goes through a migration. On a big production table, create indexes during quiet hours or with your database's concurrent option.

A practical checklist

  1. Count the queries of a slow page (debug toolbar or CaptureQueriesContext).
  2. If the count grows with the number of rows, look for a related attribute used in a loop or template, and add select_related() or prefetch_related().
  3. If a single query is slow, run explain() and look for scans on large tables, then add an index that matches the WHERE and ORDER BY.
  4. If you load big objects only to read two fields, use only() or values().
  5. Push counting and summing into the database with aggregation instead of looping in Python.
  6. Re-measure. Lock the gain in with an assertNumQueries test.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. A template loops over 50 comments and prints comment.author.name. How many queries can this run by default, and what is the fix?

    One query loads the comments and each author access runs another one. author is a ForeignKey, so select_related('author') joins it into the first query. prefetch_related would also work (two queries) but select_related is the natural fit for a single-valued relation.
  2. 2. Which relation needs prefetch_related rather than select_related?

    select_related uses a JOIN and only works for relations that give one object per row. A many-to-many (or a reverse foreign key) gives many objects, so Django fetches them with a separate query and joins them in Python.
  3. 3. What happens when you access a field that was skipped with defer('summary')?

    Deferred fields are loaded on first access, one object at a time. Inside a loop this can recreate an N+1 problem.
  4. 4. Which statement about indexes is true?

    Indexes trade write speed and disk space for faster reads on the indexed columns. Django already indexes primary keys, unique fields and foreign keys. Indexes do not change the query count, only how fast each query runs.

Summary

  • Measure with CaptureQueriesContext, connection.queries in development, the debug toolbar, or assertNumQueries in tests.
  • N+1 means one query for the list plus one per row for a lazily loaded relation.
  • select_related() joins single-valued relations in one query; prefetch_related() adds one query per multi-valued relation.
  • Prefetch(..., queryset=..., to_attr=...) customizes the extra query.
  • only(), defer() and values() load fewer columns; exists(), count() and iterator() avoid loading rows you do not need.
  • Add indexes (db_index, Meta.indexes) for columns you filter or sort by, and check the plan with explain().

Next, learn how to package queries into reusable building blocks in Custom Managers, QuerySets and Model Methods.