How would you track down and fix an N+1 query problem in a Django app?

Confirm it first by counting queries, using Django Debug Toolbar locally or assertNumQueries in a test. The pattern is a loop touching a related object, which triggers a query per row because querysets are lazy. Fix forward relations with select_related, which does a join, and reverse or many to many relations with prefetch_related, which runs one extra query.

Measurement comes first, because guessing at ORM performance is a waste of an afternoon. Locally I turn on the debug toolbar or log queries at debug level, and if it is already in production I look at the trace, where an endpoint firing four hundred nearly identical selects is unmistakable. The cause is that a queryset does not hit the database until you iterate it, and accessing a foreign key on each row lazily fetches that row relation. For a forward foreign key I add select_related, which turns it into a join and one query. For reverse relations or many to many I use prefetch_related, which issues a second query and stitches the results together in Python. Once fixed, I write a test wrapping the view in assertNumQueries with the expected number, because otherwise the next person adds a template field and it regresses silently. On an internal dashboard we took one page from about three hundred queries to four, and load time went from roughly two seconds to under two hundred milliseconds.