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