Django select_related vs prefetch_related - animated cheatsheet

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 M2M and reverse relations. Django allows only to use it with OneToOne  and ForeignKey relations. Trying to use other relations throws an exception. Thus it does joining in SQL.

Possible issues:

• 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".

Possible issues:

• Huge datasets might rise memory consumption;