Debugging query performance issues when using the Django ORM
Although using the Django ORM provides a convenient and simple interface for query generation, performance issues can easily appear. Common cases are in the form of N+1 queries when querying related objects or inefficient query generation.
Here are some methods that I often use to debug performance issues relating to query execution in Django.
1. Logging all executed queries
This is a simple approach but is a good starting point to see what queries are being executed by your code. To log the queries, open your Django settings file and add the following logger at DEBUG level. With this setting on, all queries will be logged to the console.
I find it useful to keep this logger in your development settings file and leave the log level above DEBUG by default e.g. INFO so it is easy to drop it down to DEBUG when looking into a performance issue.
'loggers': {
'django.db': {
'level': 'DEBUG',
'handlers': ['console'],
}
}2. Using test assertions to avoid N+1 queries
It is very easy to leave out a select_related or a prefetch_related when querying Django models with related objects, especially when using libraries like Django REST Framework. As a best practice, it is a good idea to write tests for areas of the code which are prone to the introduction of N+1 queries, for example REST endpoints which return multiple objects with related fields.
Django’s TestCase class conveniently provides an assertion method called assertNumQueries which can be used to ensure that no unexpected queries are executed.
def test_endpoint_executes_a_single_query(self):
with self.assertNumQueries(1):
self.client.get('/api/foo')If more than a single query is executed by the above endpoint, the test will fail and the executed queries will be displayed in the error output.
If you would like more flexibility, for example to assert that the number of queries is between 1 and 10, or less than 5, Luke Plant wrote a blog post on how to achieve this with assertNumQueries.
3. Utilizing Django applications for debugging
There are a number of useful open source tools you can use in your Django projects to help with debugging.
Django Debug Toolbar is a well known project which displays the database queries each page of your Django application is executing, along with a lot of other useful information. If you are using a Single Page Application (SPA) with a Django back-end instead of a traditional Django website, you will need to also install Django Debug Panel. The Django Debug Panel will then appear under your Chrome developer tools.
Django Query Inspect is an excellent plugin that I have found to be invaluable when developing Django applications. This plugin logs information about each SQL query with the total SQL and overall request time, as well as any duplicated queries (including the traceback of where it was generated). The library also returns metadata inside each request’s response headers, so you can easily see the number of queries each AJAX request from your application is executing alongside other relevant information by inspecting requests in the network tab of your browser’s developer tools.
Django Query Debug is another useful plugin which can be used to detect where duplicate or slow queries are being executed. Although not as popular as the other two Django applications listed above, I have found the analyze_block context manager and PatchDjangoDescriptors classes to be useful to detect where queries are being generated in my applications, and where I may be missing a select_related or prefetch_related.
4. Analyzing queries with EXPLAIN
Explain Plans show you how the databases query optimizer will execute a query. This can be very useful for debugging why a query may be running slowly, as it may for example show that the query is not hitting any indexes on the database table.
Previously if you wanted to retrieve the explain plan for a query that would be generated by the ORM, you would need to obtain the query by writing the following code:
# Retrieve the generated database query
query = str(Blog.objects.all().query)# Modify query string with explain syntax
query_with_explain = f'EXPLAIN {query}'# Execute explain query to retrieve explain plan
As of Django 2.1, Django’s QuerySet API provides a convenient API to retrieve the explain plan for you:
print(Blog.objects.filter(title='My Blog').explain(verbose=True))
Seq Scan on public.blog (cost=0.00..35.50 rows=10 width=12) (actual time=0.004..0.004 rows=10 loops=1)
Output: id, title
Filter: (blog.title = 'My Blog'::bpchar)
Planning time: 0.064 ms
Execution time: 0.058 ms5. Look into whether database-specific extensions may benefit your application
Django by default provides a solid API for use across many database platforms. However, it supports some database platforms like PostgreSQL better than others. There are some Django applications, such as Django-MySQL which support database-specific features to ensure that you can get the best performance out of your database. Based on the information you find in your explain plans you may want to take a look at these extensions and see if they offer an API which may help you, for example using query hints.
I hope this article has given you some useful tips for debugging any performance issues you may encounter in your Django projects. If you have any further tips, please let me know in the comments section below!
