N + 1 query
By default django makes
N + 1 SQL queries when dealing with related objects.
For example, let's say you have
post model with
author foreign key to
user model. Iterating over 3 posts and printing user makes 3 + 1 SQL queries. 1 query to select posts. 3 queries to select related author fields.
select_related [from N + 1 to 1 query using "JOIN" clause]
Select related reduces number of SQL queries to one. It doesn't work with
reverse relations. Django allows only to use it with
ForeignKey relations. Trying to use other relations throws an exception. Thus it does joining in SQL.
- Might cause a lot of SQL joins affecting performance;
prefetch_related [from N + 1 to 2 queries using "IN" operator]
prefetch_related instead of using SQL
JOIN clause uses
IN operator and makes 2 queries whereas
select_related makes 1. The first query fetches all posts and saves id inside a python variable. Then it passes the list of ids inside the second SQL query. Thus it "does joining in python".
- Huge datasets might rise memory consumption;