What's the difference between select_related and prefetch_related in Django ORM?

django

orm

database-queries

performance-optimization

byNikita Barsukov·Jan 23, 2025

When dealing with Django ORM, we have two tools at our disposal to speed up database operations significantly, select_related and prefetch_related.

select_related comes to the rescue when we need to deal with one-to-one and many-to-one relationships. It uses single SQL JOINS to retrieve related single instances, hence reducing the total number of queries:

book = Book.objects.select_related('author').get(id=1)
author = book.author  # No extra query, George Martin would be proud!

prefetch_related, on the other hand, is best suited for many-to-many and one-to-many relationships. It executes a separate query for each related set, ensuring all data is aggregated effectively:

book = Book.objects.prefetch_related('chapters').get(id=1)
chapters = book.chapters.all()  # No more "Wait, how many chapters did I fetch again?" moments!

Understanding use cases: `select_related` vs. `prefetch_related`

Knowing when to use one or the other can be as vital as understanding how to use them. Let's delve into specific use cases:

When to `select_related`

To-one relationships: Favor select_related when dealing with ForeignKey or OneToOneField.
Nested access: If you're digging through a series of related objects, select_related can shine, especially if these nested relations are not numerous.
In search of fewer queries? The true strength of select_related is reducing the total number of queries by adding more columns to the initial queryset.

When to `prefetch_related`

To-many relationships: prefetch_related is your friend for ManyToManyField or reverse ForeignKey relationships.
A large family: If you're working with many instances of related objects, consider gathering them all under prefetch_related umbrella. It minimizes the quantity of queries during bulk access.
Python-powered joins: prefetch_related allows Python to handle "joining" related objects, potentially saving your database from carrying out complex joins.

Examining under the hood: `select_related` vs. `prefetch_related`

Both of these methods work differently because of the place where the "joining" of tables or objects occurs.

`select_related`: SQL does the work

select_related uses a single SQL join to combine related tables in your initial queryset. While it results in a single complex query, your total database hits decrease. However, with power comes responsibilities: careful with bloated queries when dealing with deeply nested or large datasets.

`prefetch_related`: Python takes the wheel

prefetch_related, in contrast, performs a separate database query for every related object. Python then takes up the mantle, joining the separate results into a coherent whole. Reduced database strain is the win, but remember, Python is now earning its keep, increasing total queries.

Tips for success: Optimization rules

Optimal usage is a summary of wisely chosen strategies and avoiding common pitfalls:

Order of queries

Rearranging select_related and prefetch_related doesn't alter their functionality, but can make a difference in efficiency and code comprehensibility.

Tune your SQL

Use Django's QuerySet.explain() function to understand the SQL execution plan. It's like getting an x-ray of your SQL queries to better diagnose your performance pains.

Avoid overloading

While select_related can help to reduce the number of queries, using it carelessly leads to over-fetching, a common ORM pitfall. prefetch_related is a way to avoid such issue but asks more of your system memory to handle the results.

Most common caveats and their solutions

Knowing how to evade potential mishaps is just as important:

Unintended massive joins

select_related can bloat your SQL queries by introducing unintended large joins. Keeping your queries tight and purposeful prevents this bloating.

Overhead with small datasets

Overusing prefetch_related with small datasets can add unnecessary overhead. When dealing with small sets, select_related is generally a better choice.

The N+1 query issue

The infamous N+1 query issue is when you make one database query for the parent model and then an extra query for each related model instance. prefetch_related shines here, delivering all the related instances in one-to-many and many-to-many relations with a single extra query.