Finding and fixing N+1 queries in Django apps

April 30 Bullet_white By Derek Bullet_white Comments Comments

The Django ORM makes it easy to fetch data, but there's a downside: it's easy to write inefficient queries as the number of records in your database grows.

One area where the ease of writing queries can bite is you is with N+1 queries. Expensive N+1 queries go undiscovered in small development databases. Finding expensive N+1 queries is an area where Scout is particularly helpful.

Lets say you deploy a new Django view that renders a list of invoices and their associated retail store:

With a bare-bones development database, this performs just fine. However, when you deploy to production, performance will suffer significantly if a customer has many invoices.

When you add Scout to your Django app, Scout automatically identifies N+1s and calls these out on your app performance dashboard:

n plus one

These insights are filtered to just N+1s consuming a significant amount of time, reducing noise. Also, since this is the first time an N+1 was detected for this view, the insight has a "NEW" indicator.

Clicking on an insight reveals a transaction trace for the request. Scout identifies the N+1 in the trace and provides a backtrace to the source of the query:

trace

That's a lot of queries - 1000 separate calls to be exact!

In this case, the fix is straight-forward. Use select_related:

After deploying the fix, Scout continues to watch your new endpoint to see if an expensive N+1 re-emerges. When the N+1 hasn't appeared over a 24 hour period, the insight will disappear.

TL;DR

Knowing which N+1s are causing production problems is more efficient than optimizing every Django SQL query locally. When you add scout_apm to your Django app, you're able to see just the expensive queries and backtraces to their source, avoiding time-consuming pre-optimization work.

Django monitoring is free while in our Tech Preview program. Sign up for Scout.

Get notified of new posts.

Once a month, we'll deliver a finely-curated selection of optimization tips to your inbox.

Comments

comments powered by Disqus