Repository: django/django
Issue category: Performance Issue

Elide ordering of prefetch querysets for single valued relationships
Description
		
(last modified by Laurent Lyaudet)		
While the ordering of multi-valued relationships must be preserved when prefetching relationships is it unnecessary when using prefetch_related against single valued relationships.
For example, given the following models
class Author(models.Model):
	name = models.CharField(max_length=200)
	class Meta:
		ordering = ["name"]
class Book(models.Model):
	title = models.CharField(max_length=200)
	author = models.ForeignKey(Author, related_name="books", on_delete=models.CASCADE)
	class Meta:
		ordering = ["title"]
The ordering of an author's books in Author.objects.prefetch_related("books") has a significance as multiple books might be associated with each authors.
It's not the case for a book's author in Book.objects.prefetch_related("author") through as the relationship can only contain a single author and there is a single way to order the members of a singleton.
In other words sorted([element], key=sort_func) will result in [element] for any sort_func.
This property holds true for all the single valued relationships that the ORM supports (backward and forward 1:1 and forward 1:M) which allows the prefetching to elide any predefined ordering safely to avoid an unnecessary and possibly expensive ordering defined for the related model queryset.
Currently the prefetch of authors will use the order by and add useless load on the DB server.
It would be useful to remove this order by.
#ClimateChangeBrake


Functions to edit:
django/db/models/fields/related_descriptors.py:ForwardManyToOneDescriptor.get_prefetch_querysets
django/db/models/fields/related_descriptors.py:ReverseOneToOneDescriptor.get_prefetch_querysets

Patch:
diff --git a/django/db/models/fields/related_descriptors.py b/django/db/models/fields/related_descriptors.py
index a2f00eb172c4..c7848ee63a3e 100644
--- a/django/db/models/fields/related_descriptors.py
+++ b/django/db/models/fields/related_descriptors.py
@@ -195,6 +195,9 @@ def get_prefetch_querysets(self, instances, querysets=None):
         else:
             query = {"%s__in" % self.field.related_query_name(): instances}
         queryset = queryset.filter(**query)
+        # There can be only one object prefetched for each instance so clear
+        # ordering if the query allows it without side effects.
+        queryset.query.clear_ordering()
 
         # Since we're going to assign directly in the cache,
         # we must manage the reverse relation cache manually.
@@ -469,6 +472,9 @@ def get_prefetch_querysets(self, instances, querysets=None):
         instances_dict = {instance_attr(inst): inst for inst in instances}
         query = {"%s__in" % self.related.field.name: instances}
         queryset = queryset.filter(**query)
+        # There can be only one object prefetched for each instance so clear
+        # ordering if the query allows it without side effects.
+        queryset.query.clear_ordering()
 
         # Since we're going to assign directly in the cache,
         # we must manage the reverse relation cache manually.

Test patch: diff --git a/tests/prefetch_related/test_prefetch_related_objects.py b/tests/prefetch_related/test_prefetch_related_objects.py
index ca1f904c5205..eea9a7fff78c 100644
--- a/tests/prefetch_related/test_prefetch_related_objects.py
+++ b/tests/prefetch_related/test_prefetch_related_objects.py
@@ -1,7 +1,7 @@
 from django.db.models import Prefetch, prefetch_related_objects
 from django.test import TestCase
 
-from .models import Author, Book, Reader
+from .models import Author, Book, House, Reader, Room
 
 
 class PrefetchRelatedObjectsTests(TestCase):
@@ -33,6 +33,17 @@ def setUpTestData(cls):
         cls.reader1.books_read.add(cls.book1, cls.book4)
         cls.reader2.books_read.add(cls.book2, cls.book4)
 
+        cls.house1 = House.objects.create(name="b1", address="1")
+        cls.house2 = House.objects.create(name="b2", address="2")
+
+        cls.room1 = Room.objects.create(name="a1", house=cls.house1)
+        cls.room2 = Room.objects.create(name="a2", house=cls.house2)
+
+        cls.house1.main_room = cls.room1
+        cls.house1.save()
+        cls.house2.main_room = cls.room2
+        cls.house2.save()
+
     def test_unknown(self):
         book1 = Book.objects.get(id=self.book1.id)
         with self.assertRaises(AttributeError):
@@ -58,20 +69,75 @@ def test_m2m_reverse(self):
 
     def test_foreignkey_forward(self):
         authors = list(Author.objects.all())
-        with self.assertNumQueries(1):
+        with self.assertNumQueries(1) as ctx:
             prefetch_related_objects(authors, "first_book")
+        self.assertNotIn("ORDER BY", ctx.captured_queries[0]["sql"])
 
         with self.assertNumQueries(0):
             [author.first_book for author in authors]
 
+        authors = list(Author.objects.all())
+        with self.assertNumQueries(1) as ctx:
+            prefetch_related_objects(
+                authors,
+                Prefetch("first_book", queryset=Book.objects.order_by("-title")),
+            )
+        self.assertNotIn("ORDER BY", ctx.captured_queries[0]["sql"])
+
     def test_foreignkey_reverse(self):
         books = list(Book.objects.all())
-        with self.assertNumQueries(1):
+        with self.assertNumQueries(1) as ctx:
             prefetch_related_objects(books, "first_time_authors")
+        self.assertIn("ORDER BY", ctx.captured_queries[0]["sql"])
 
         with self.assertNumQueries(0):
             [list(book.first_time_authors.all()) for book in books]
 
+        books = list(Book.objects.all())
+        with self.assertNumQueries(1) as ctx:
+            prefetch_related_objects(
+                books,
+                Prefetch(
+                    "first_time_authors",
+                    queryset=Author.objects.order_by("-name"),
+                ),
+            )
+        self.assertIn("ORDER BY", ctx.captured_queries[0]["sql"])
+
+    def test_one_to_one_forward(self):
+        houses = list(House.objects.all())
+        with self.assertNumQueries(1) as ctx:
+            prefetch_related_objects(houses, "main_room")
+        self.assertNotIn("ORDER BY", ctx.captured_queries[0]["sql"])
+
+        with self.assertNumQueries(0):
+            [house.main_room for house in houses]
+
+        houses = list(House.objects.all())
+        with self.assertNumQueries(1) as ctx:
+            prefetch_related_objects(
+                houses,
+                Prefetch("main_room", queryset=Room.objects.order_by("-name")),
+            )
+        self.assertNotIn("ORDER BY", ctx.captured_queries[0]["sql"])
+
+    def test_one_to_one_reverse(self):
+        rooms = list(Room.objects.all())
+        with self.assertNumQueries(1) as ctx:
+            prefetch_related_objects(rooms, "main_room_of")
+        self.assertNotIn("ORDER BY", ctx.captured_queries[0]["sql"])
+
+        with self.assertNumQueries(0):
+            [room.main_room_of for room in rooms]
+
+        rooms = list(Room.objects.all())
+        with self.assertNumQueries(1) as ctx:
+            prefetch_related_objects(
+                rooms,
+                Prefetch("main_room_of", queryset=House.objects.order_by("-name")),
+            )
+        self.assertNotIn("ORDER BY", ctx.captured_queries[0]["sql"])
+
     def test_m2m_then_m2m(self):
         """A m2m can be followed through another m2m."""
         authors = list(Author.objects.all())