Query Slower With Prefetch Related Than Without?

This is the pseudocode of my models Offer match = FK(Match) Odds offer = FK(Offer, related_name='odds') test1 = '[o.odds.all() for o in Offer.objects.filter(match__id=123

Solution 1:

Since you are using an FK you could use a select_related rather than prefetch_related and see how this compares:

select_related works by creating an SQL join and including the fields of the related object in the SELECT statement. For this reason, select_related gets the related objects in the same database query. However, to avoid the much larger result set that would result from joining across a ‘many’ relationship, select_related is limited to single-valued relationships - foreign key and one-to-one.

prefetch_related, on the other hand, does a separate lookup for each relationship, and does the ‘joining’ in Python. This allows it to prefetch many-to-many and many-to-one objects, which cannot be done using select_related, in addition to the foreign key and one-to-one relationships that are supported by select_related. It also supports prefetching of GenericRelation and GenericForeignKey.

Solution 2:

If you examine the queries Django executes, you'll see that prefetch_related() leads to an additional query with a WHERE IN (ids) clause. Those values are then mapped back to the original object in Python, preventing additional database load.

Calling prefetch_related() on unrestrained all() QuerySets against your entire database can be very costly, especially if your tables are large. However, if you are working with smaller sets of data thanks to a meaningful filter(key=value) addition to your original QuerySet, prefetch_related() will reduce N trips to the database down to 1 trip to the database. This can lead to a drastic performance increase.

And as a side note -- don't run prefetch_related("odds") unless you need that data. Your test isn't making use of the extra information, but it's still doing the extra work to fetch it. There's no free lunch :)