id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
21760	prefetch_related uses an inefficient query to get the related fk data	valtron2000@…	nobody	"Consider the following models and query:

{{{
#!python

class Author(models.Model):
	name = models.CharField(max_length = 20)

class Book(models.Model):
	name = models.CharField(max_length = 20)
	author = models.ForeignKey(Author, related_name = 'books')

Book.objects.all().prefetch_related('author')
}}}

The query I'd expect to be issued by the `prefetch_related` is:

{{{
#!sql

SELECT * FROM author
WHERE id IN (... the author_ids of the books from the main query ...)
}}}

However, the query that actually get executed are:

{{{
#!sql

SELECT a.* FROM author a INNER JOIN book b ON (a.id = b.author_id)
WHERE b.id IN (...  the ids of the books from the main query ...)
}}}

There are two problems with this approach:

1. It does a join, which wouldn't be necessary if `author_id`s were used instead of `book.id`s
2. When one uses `prefetch_related` in such a case (that is, to prefetch a single fk per object rather than a many to many) instead of `select_related`, it's usually because there are many primary objects (books) and few unique related objects (authors); `select_related`, despite getting all the data in one query, could end up returning much more data than is needed because the related objects are duplicated many times over, and will be slower overall. Thus, the approach currently used by `prefetch_related` ends up using the set of ids that will almost always be larger (the book ids, rather than the authors).

The reverse case (`Author.objects.all().prefetch_related('books')`) does the efficient, joinless query I'd expect -- namely:

{{{
#!sql

SELECT * FROM book
WHERE author_id IN (... the ids of the authors from the main query ...)
}}}

I've attached a sample project with a test."	Bug	closed	Database layer (models, ORM)	1.6	Release blocker	fixed	prefetch_related ForeignKey	loic@… marti@…	Accepted	1	0	0	0	0	0