Personal Catalogue: Django & PostgreSQL — Chapter 4, Exercise 3 ==================================================== TASK Explain why select_related can't be used for the tags relationship, even though it's used successfully for item_type on the exact same queryset. SOLUTION WHAT select_related ACTUALLY DOES UNDER THE HOOD select_related works by adding a real SQL JOIN to the original query, pulling the related table's own columns directly into the same result set as the main table. This works cleanly for item_type because the relationship is single-valued: each Item row has exactly one ItemType, so joining in that one related row produces exactly one extra set of columns per Item row - the result set stays flat, one row per item, with no duplication. WHY THE SAME APPROACH BREAKS FOR tags tags is a ManyToManyField - each Item can have zero, one, or many related Tag rows. If Django tried to use a JOIN for this the same way it does for item_type, an item with three tags would need three separate result rows just to represent that one item joined against each of its three tags in turn - and an item with zero tags would need special handling (an OUTER JOIN) to still appear at all. The clean, one-row-per-item shape select_related depends on simply doesn't exist once a single JOIN has to represent a one-to-many (or many-to-many) relationship. WHY prefetch_related SOLVES IT DIFFERENTLY INSTEAD Rather than trying to force a many-valued relationship into one flat joined query, prefetch_related runs it as a real, separate second query: it fetches all the Tag rows connected to any of the items on the page, along with which item each tag belongs to, then assembles the correct grouping in Python once both result sets are back. This keeps the main Item query flat and simple, while still avoiding the N+1 problem, just through a genuinely different mechanism (one extra batched query) rather than a JOIN. ANSWER: select_related relies on a SQL JOIN producing exactly one extra set of columns per row, which only works for single-valued relationships like the item_type ForeignKey. tags is a many-to-many relationship, where a single item can have multiple related rows, which a flat JOIN can't represent without duplicating rows - so prefetch_related is used instead, fetching the related tags in one separate batched query and assembling the grouping afterward, rather than trying to force the relationship into the same joined result set. WHY THIS WORKS AS AN ANSWER ---------------------------- It explains the underlying SQL mechanism each optimization actually relies on (a JOIN vs. a separate batched query), and shows why that mechanism specifically breaks down for a many-valued relationship, rather than simply stating the two functions are "for different field types" without explaining why.