A Django-ORM-Raised Engineer Relearns Raw SQL [Performance Tuning]

This article was automatically translated from theJapanese original by AI. It may contain translation errors.

Introduction

This article is the fourth and final part of my series where I, raised on the Django ORM, relearn raw SQL: Performance Tuning. The series had four parts.

Model Definition: what tables a models.py definition becomes
Model Changes: what ALTER TABLE runs behind makemigrations and migrate
Queries: what queries run behind filter and update
Performance Tuning (this article): the N+1 problem, select_related / prefetch_related, and the internals of aggregation

Through the previous Queries article, I became able to create tables, change them, and read and write individual queries. Last is the speed story. The N+1 problem, famous as the price of the ORM’s convenience — what is actually happening when you look at the SQL? Its countermeasures select_related and prefetch_related — what SQL does each issue to crush N+1? And what SQL syntax do aggregation’s annotate and aggregate, and expression objects like F, Q, Case, and Subquery, turn into? The aim of this installment is to peer at the SQL running behind “slow ORM code” and understand it from the cause side.

I continue to use the same sample models as before (User, Profile, Post, Tag, Comment). For the full definitions and the ER diagram, see the Model Definition article. Locally, I’ve verified with three users (alice, bob, carol), six articles, and tags and comments linked to them.

What I use this time is connection.queries, foreshadowed at the end of the Queries article. The executed SQL is stored one statement at a time, so counting the number tells you directly “how many SQL statements this ORM code threw”¹. The N+1 problem was exactly this problem of count. From here, I’ll prefix each experiment with the “query count.”

Since this is based on my own research, it may contain errors. If you notice anything, I’d greatly appreciate a heads-up.

Observing the N+1 problem in SQL

Suppose you want to display each article’s author name in an article list. Written casually, it becomes this.

>>> for p in Post.objects.all():
...     print(p.author.name)

It looks like an ordinary loop, but counting connection.queries, 7 SQL statements flew for 6 articles.

-- query count: 7
SELECT ... FROM "blog_post"
SELECT ... FROM "blog_user" WHERE "blog_user"."id" = 1 LIMIT 21
SELECT ... FROM "blog_user" WHERE "blog_user"."id" = 1 LIMIT 21
SELECT ... FROM "blog_user" WHERE "blog_user"."id" = 1 LIMIT 21
SELECT ... FROM "blog_user" WHERE "blog_user"."id" = 2 LIMIT 21
SELECT ... FROM "blog_user" WHERE "blog_user"."id" = 2 LIMIT 21
SELECT ... FROM "blog_user" WHERE "blog_user"."id" = 3 LIMIT 21

The first is the SELECT that gets the article list, and the remaining six are the author SELECTs that fly each time you access p.author. At Post.objects.all() it hasn’t touched blog_user, so every time an author is needed, a WHERE id = ? SELECT is issued once per article. The trailing LIMIT 21 is the one from get in the Queries article. Following a single relation is internally also a get, so the same form shows up.

What I want to note is that the SELECT retrieving id=1’s alice repeats three times, exactly as-is. alice has three articles, so it re-fetches alice every time, per article. It doesn’t reuse “the author fetched earlier.” This is the mechanism by which queries grow in proportion to the list count.

Counting the list’s one as “1” and the loop’s growth as “N,” this phenomenon is called the N+1 problem. This time N=6 for 7 statements, but a list of 100 articles flies 101 statements, and 1000 flies 1001 SQL statements. Diagramming the app-DB round-trips makes the shape of the problem clear.

sequenceDiagram
    participant App
    participant DB
    App->>DB: SELECT ... FROM blog_post (list)
    DB-->>App: 6 articles
    loop per article (N times)
        App->>DB: SELECT ... FROM blog_user WHERE id = ?
        DB-->>App: 1 author
    end

However fast a single SQL statement is, the round-trip itself has a fixed cost (network latency and query parsing). The N+1 problem bites not because each query is slow, but because it round-trips light queries many times. In fact, the total time locally was a few milliseconds, and the six SELECTs themselves were instant. Yet in a production environment where the DB is on a separate server and each round-trip takes a few milliseconds, this count turns directly into latency.

The same thing happens when counting. Trying to display each article’s comment count by calling p.comments.count() in a loop was again 7 statements.

-- query count: 7
SELECT ... FROM "blog_post"
SELECT COUNT(*) AS "__count" FROM "blog_comment" WHERE "blog_comment"."post_id" = 1
SELECT COUNT(*) AS "__count" FROM "blog_comment" WHERE "blog_comment"."post_id" = 2
SELECT COUNT(*) AS "__count" FROM "blog_comment" WHERE "blog_comment"."post_id" = 3
...

Here a COUNT flies once per article. Whether fetching the related objects themselves or counting the related records, touching a relation inside a loop yields the same N+1. So how do you combine them into one? From here are the countermeasures. Django provides two: select_related, which crushes it with a JOIN, and prefetch_related, which crushes it with a separate query. Let me look at them in order.

select_related: combine into one with a JOIN

To the earlier loop, add one select_related("author").

>>> for p in Post.objects.select_related("author"):
...     print(p.author.name)

-- query count: 1
SELECT "blog_post"."id", ..., "blog_post"."updated_at",
       "blog_user"."id", "blog_user"."name", "blog_user"."email"
FROM "blog_post" INNER JOIN "blog_user" ON ("blog_post"."author_id" = "blog_user"."id")

7 statements became 1. What came out is a JOIN. I saw JOIN itself in the Queries article as “a filter crossing a relation,” but the role differs from then. That JOIN was a path for narrowing by a blog_user-side column. What’s essential in this JOIN is the SELECT clause. Look closely: following all of blog_post’s columns, blog_user’s id, name, and email are also lined up. It sticks an article and its author into a single row and fetches them together in one shot.

Let me look at the result shape in a table to see how the JOIN turns two tables into one row. ON ("blog_post"."author_id" = "blog_user"."id") specifies “attach the user row matching the article’s author_id side-by-side,” and the result is this.

blog_post.id	blog_post.title	blog_post.author_id	blog_user.id	blog_user.name
1	Intro to Django’s ORM	1	1	alice
2	Relearning SQL	1	1	alice
4	New Features in Python 3.14	2	2	bob

For each article row, a row forms with its author’s columns snugly attached to the side. From this single SELECT result, Django assembles the Post instance along with the author instance and caches it. So even when you touch p.author in the loop, it no longer goes to the DB. The entire N of N+1 vanished — that’s the logic.

INNER JOIN and LEFT OUTER JOIN

The SQL above was an INNER JOIN. INNER JOIN keeps only “rows where a match was found on both sides.” Since an article’s author is required (NOT NULL), there’s no article without an author, so INNER misses nothing.

On the other hand, for a relation where the other side might not exist, the JOIN type changes. Let me try with the user-profile relationship. Only carol has no profile.

>>> for u in User.objects.select_related("profile"):
...     ...

-- query count: 1
SELECT "blog_user"."id", "blog_user"."name", "blog_user"."email",
       "blog_profile"."id", "blog_profile"."user_id", "blog_profile"."bio", "blog_profile"."links"
FROM "blog_user" LEFT OUTER JOIN "blog_profile" ON ("blog_user"."id" = "blog_profile"."user_id")

It became a LEFT OUTER JOIN. This is also the part that showed its face in the __isnull section of the Queries article: it keeps all rows on the left side (blog_user) and joins carol’s row, which has no match, with the profile-side columns filled with NULL. If I used an INNER JOIN here, carol, who has no profile, would vanish at the JOIN, and one person would drop out of the list. Django looks at whether the joined side could be NULL (i.e., the relation’s nullability) and chooses between INNER and LEFT OUTER.

select_related only works up to FK and OneToOne

In the examples so far, select_related’s target was always a ForeignKey or OneToOne. This is a constraint. select_related’s strategy is “join side-by-side into one row,” so it can’t be used for a relation where the join would increase the number of article rows.

Recall the “JOIN increases rows” phenomenon from the Queries article. When one user has two published articles, joining the user and their articles increased the user’s rows to two. Joining a ManyToMany or a reverse relation (the “many” side of one-to-many) side-by-side causes exactly this, padding out the original table’s rows. So for that direction, you need a different strategy that doesn’t increase rows. That’s prefetch_related.

prefetch_related: throw a second query with an IN clause

Let me write “display each article’s comment list,” a form prone to N+1, with prefetch_related.

>>> for p in Post.objects.prefetch_related("comments"):
...     list(p.comments.all())

-- query count: 2
SELECT ... FROM "blog_post"
SELECT ... FROM "blog_comment" WHERE "blog_comment"."post_id" IN (1, 2, 3, 4, 5, 6)

Unlike select_related, the SQL is two statements, not one. But rather than N+1’s seven, it stops at exactly two. The first gets all the articles, collects the article ids that appeared (1–6), and the second queries them together with WHERE post_id IN (...). Django distributes the fetched comments per article on the Python side and caches them. Rather than making one table with a JOIN, the strategy is to fetch the relation all at once in a separate query and join them on the app side.

Why not a JOIN is as in the previous section. Joining articles and comments makes an article with three comments become three rows, and a large column like the body (content) is transferred three times, duplicated. With many articles and many comments, this padding bites. If you split it out with an IN clause, articles and comments are each carried only once.

ManyToMany is the same form. Trying it with tags, the second statement went through the junction table.

-- query count: 2
SELECT ... FROM "blog_post"
SELECT ("blog_post_tags"."post_id") AS "_prefetch_related_val_post_id", "blog_tag"."id", "blog_tag"."name"
FROM "blog_tag" INNER JOIN "blog_post_tags" ON ("blog_tag"."id" = "blog_post_tags"."tag_id")
WHERE "blog_post_tags"."post_id" IN (1, 2, 3, 4, 5, 6)

A JOIN is used inside the second statement, but that’s for joining the tag body and the junction table; the article side is still split out with an IN clause. The junction table blog_post_tags I saw in the Model Definition article works as a waypoint here too. The leading _prefetch_related_val_post_id is a column that carries post_id back so Django can decide which article to distribute each fetched tag to.

Let me contrast the two countermeasures in SQL form.

	select_related	prefetch_related
SQL count	1 (JOIN)	2 (body + IN)
Where it joins	DB (JOIN)	Python side
Usable relations	ForeignKey / OneToOne	ManyToMany / reverse relation (one-to-many) too
Unsuited situation	relations that increase rows	when one-to-one suffices (2 statements where 1 would do)

For choosing, “if the target settles to one record, select_related (1 statement with JOIN); if the target could be multiple, prefetch_related (2 statements with IN)” will hardly steer you wrong. Both are tools to collapse the query count from N to a constant.

Understanding aggregation via GROUP BY

After N+1 comes aggregation. Aggregations like count, sum, and average are expressed in SQL with aggregate functions like COUNT and SUM, and the GROUP BY syntax. Let me see which of these Django’s aggregate and annotate correspond to.

aggregate and Count, Sum, Avg, Min, Max

First, aggregate. Let me get the total article count and average view count at once.

>>> Post.objects.aggregate(Count("id"), Avg("view_count"))
{'id__count': 6, 'view_count__avg': 128.33...}

-- query count: 1
SELECT COUNT("blog_post"."id") AS "id__count", AVG("blog_post"."view_count") AS "view_count__avg"
FROM "blog_post"

Count and Avg became SQL’s aggregate functions COUNT and AVG as-is. An aggregate function is a function that takes many rows and folds them into a single value. COUNT is the row count, SUM the sum, AVG the average, and MIN and MAX the minimum and maximum. This SELECT has no WHERE or GROUP BY, so the aggregate functions apply to the single cluster that is the whole table, and the result returns just one row (one dict). If you want to aggregate after narrowing, just put the usual filter before it.

>>> Post.objects.filter(status="published").aggregate(Sum("view_count"), Min("view_count"), Max("view_count"))

SELECT SUM("blog_post"."view_count") AS "view_count__sum", MIN(...) AS "view_count__min", MAX(...) AS "view_count__max"
FROM "blog_post" WHERE "blog_post"."status" = 'published'

The WHERE clause filter assembles is the same parts I saw in the Queries article, plugged directly into the aggregation SELECT. “Total, min, and max targeting only published articles” was obtained in a single SQL statement.

By the way, for just the count there’s the familiar count().

>>> Post.objects.count()

SELECT COUNT(*) AS "__count" FROM "blog_post"

Almost the same as aggregate(Count("id")), it becomes COUNT(*). If you just want to know the count, this is the straightforward choice.

annotate and GROUP BY

Whereas aggregate folds the whole table into a single value, annotate is an operation that “adds an aggregation column per row.” Let me get each user’s post count.

>>> User.objects.annotate(post_count=Count("posts"))

-- query count: 1
SELECT "blog_user"."id", "blog_user"."name", "blog_user"."email", COUNT("blog_post"."id") AS "post_count"
FROM "blog_user" LEFT OUTER JOIN "blog_post" ON ("blog_user"."id" = "blog_post"."author_id")
GROUP BY "blog_user"."id"

What newly appeared is GROUP BY. GROUP BY collects rows with the same value in the specified column into one group and applies aggregate functions per group. Here it’s GROUP BY "blog_user"."id", so it groups articles by user ID and counts COUNT within each group. Let me follow the behavior in a table. First, joining users and articles gives this.

blog_user.id	blog_post.id
1 (alice)	1
1 (alice)	2
1 (alice)	3
2 (bob)	4
2 (bob)	5
3 (carol)	6

Grouping this by user.id gives groups of alice=3 rows, bob=2 rows, carol=1 row, and the value from COUNTing each group’s rows is post_count. The aggregate function applies to this group unit rather than the whole table — that was the difference from aggregate. The JOIN is LEFT OUTER so that a user with 0 articles remains as COUNT=0 (with INNER, users with 0 articles vanish).

One more: let me collect the foreshadowing laid by values in the Queries article. Combining values and annotate changes the target of GROUP BY. Let me count the number of articles per status.

>>> Post.objects.values("status").annotate(n=Count("id"))

SELECT "blog_post"."status" AS "status", COUNT("blog_post"."id") AS "n"
FROM "blog_post" GROUP BY 1

This time it’s GROUP BY 1, grouping by the first column of the SELECT (status). The values(“status”) placed before annotate directly decides the GROUP BY unit. This is a gotcha: what you list in values changes the granularity of the group. Whereas the earlier user example was GROUP BY user.id, inserting values groups by that column. If you map “per-row aggregation is annotate, and its aggregation unit is GROUP BY (specifiable with values),” you can count at the intended granularity.

Using exists() and count() properly

Related to aggregation, let me also look at the difference between exists() and count(), which you often waver over in practice, in SQL. When you want to know “whether there’s even one published article,” you can write it both ways.

>>> Post.objects.filter(status="published").exists()

SELECT 1 AS "a" FROM "blog_post" WHERE "blog_post"."status" = 'published' LIMIT 1

>>> Post.objects.filter(status="published").count()

SELECT COUNT(*) AS "__count" FROM "blog_post" WHERE "blog_post"."status" = 'published'

exists() is SELECT 1 ... LIMIT 1, meaning “abort as soon as one matching row is found.” For an existence check, finding one is enough, so the DB can stop working the moment it finds the first record. count(), by contrast, is COUNT(*), counting all matching rows. “Counting everything when you only want to know whether it exists” is visible as SQL workload as waste.

For the same reason, the way of writing if queryset: needs care too. Boolean-testing a QuerySet makes Django SELECT all records into a list and then check whether it’s empty.

-- SQL that runs for if qs: (all columns, all records)
SELECT "blog_post"."id", ..., "blog_post"."updated_at" FROM "blog_post" WHERE "blog_post"."status" = 'published'

Even though you only want to know existence, it carries all columns of all articles. Writing if queryset.exists(): finishes with the LIMIT 1 above. Deciding “for ‘is there?’, use exists()” was the safe choice.

Reading the execution plan with EXPLAIN

So far it’s been about “how many SQL statements fly.” Last, let me peer at how a single SQL statement is processed inside the DB. What I use is EXPLAIN. Putting EXPLAIN at the head of an SQL statement tells you how the DB intends to execute that query (the execution plan). In Django you can see it with a QuerySet’s explain().

In the Model Changes article, I put an index on published_at (db_index=True). Let me confirm here whether it’s actually used. However, for a small table like the six rows at hand, the DB judges “reading everything is faster” and doesn’t use the index. So, with about 10,000 rows inserted with scattered published_at values (rolled back after the experiment), let me narrow with a condition that hits only a few.

>>> print(Post.objects.filter(published_at__gte=recent).explain())

Index Scan using blog_post_published_at_9524a659 on blog_post  (cost=0.29..9.37 rows=31 width=74)
  Index Cond: (published_at >= '2026-07-08 02:20:55+00'::timestamp with time zone)

Index Scan came out. The name of the used index, blog_post_published_at_9524a659, is the one Django created when I migrated in the Model Changes article. The index created with CREATE INDEX back then is now working to speed up the query — that’s the payoff of the foreshadowing. The cost=0.29..9.37 at the head of the line is a rough estimate of the cost the DB estimated; smaller means expected to be lighter.

For comparison, let me narrow about the same number of rows on view_count, which has no index.

>>> print(Post.objects.filter(view_count__lt=30).explain())

Seq Scan on blog_post  (cost=0.00..268.07 rows=32 width=74)
  Filter: (view_count < 30)

This time it’s a Seq Scan. Seq Scan is a brute-force reading method that “reads all rows from the head of the table and picks only rows matching the condition.” Comparing the numbers to the right of cost, Index Scan was 9.37 versus Seq Scan’s 268.07. Narrowing on a column with an index follows the index and reads only the target rows, while narrowing on a column without one reads all rows and sifts them out — this difference shows up directly in the cost. If you can tell these two apart, you can read from the execution plan the situation “the index isn’t working on a slow query.”

Further, adding explain(analyze=True) shows not just the estimate but actual measured values by actually executing it.

Index Scan using blog_post_published_at_9524a659 on blog_post  (cost=0.29..9.37 rows=31 width=74) (actual time=0.003..0.007 rows=32 loops=1)
  Index Cond: (published_at >= '...')
Planning Time: 0.022 ms
Execution Time: 0.011 ms

actual time and Execution Time are the time actually taken. When in production “only this query is somehow slow,” first putting it through EXPLAIN ANALYZE and checking whether it became a Seq Scan and whether it finished with the expected number of rows is the entry point of the investigation. Knowing you can do the same thing from the ORM’s explain() is convenient.

Objects that assemble expressions

From here are tools for assembling more elaborate SQL. Conditions and updates you couldn’t write with filter and exclude alone can be expressed with objects like F, Q, Case, and Subquery. Let me check what SQL syntax each becomes.

F: comparing and updating columns against each other

Usually, to increase a view count by 1 you’d want to write “fetch, +1, save.” But that invites a conflict (race condition) where if another process touches the same row between fetch and save, one update is lost. Using F, you can complete this calculation inside the DB.

>>> Post.objects.filter(id=1).update(view_count=F("view_count") + 1)

-- query count: 1
UPDATE "blog_post" SET "view_count" = ("blog_post"."view_count" + 1) WHERE "blog_post"."id" = 1

F("view_count") became the column reference "blog_post"."view_count" inside the SQL. Without bringing the value to the Python side, it has the DB compute SET view_count = view_count + 1. Rather than reading the current value and then writing, the DB reads and writes within a single UPDATE, so the conflict from earlier doesn’t occur. For updates like inventory, balances, or counters that “increment based on the current value,” this style is safe.

F is usable not just for updates but also for comparing columns against each other. Let me find “articles updated after being published” by comparing updated_at and published_at.

>>> Post.objects.filter(updated_at__gt=F("published_at"))

SELECT ... FROM "blog_post" WHERE "blog_post"."updated_at" > ("blog_post"."published_at")

The right side of WHERE is another column, not a constant. A normal filter compares a column with a constant, like view_count__gt=100, but passing F lets you write a WHERE comparing two columns against each other.

Q: OR conditions and complex WHERE

As I saw in the Queries article, listing conditions in filter joins them with AND. What you use when you want to write OR is Q.

>>> Post.objects.filter(Q(status="published") | Q(view_count__gte=200))

SELECT ... FROM "blog_post" WHERE ("blog_post"."status" = 'published' OR "blog_post"."view_count" >= 200)

| became SQL’s OR. Whereas a filter without Q could only write AND, joining Q with | makes OR appear in WHERE for the first time. Similarly, ~Q(...) is NOT.

>>> Post.objects.filter(~Q(status="draft"))

SELECT ... FROM "blog_post" WHERE NOT ("blog_post"."status" = 'draft')

This is the same WHERE form as exclude in the Queries article; in fact exclude(status="draft") and filter(~Q(status="draft")) become the same SQL. Q and ordinary keyword arguments can be mixed, and then the Q side is joined with OR and the keyword side with AND.

>>> Post.objects.filter(Q(view_count__lt=50) | Q(view_count__gte=300), status="published")

SELECT ... FROM "blog_post"
WHERE (("blog_post"."view_count" < 50 OR "blog_post"."view_count" >= 300) AND "blog_post"."status" = 'published')

A parenthesized WHERE of (A OR B) AND C was assembled. The condition is “view count is extreme (under 50 or 300 or more) and published.” Q was a tool for assembling an intricate WHERE, along with its parenthesized structure, that AND alone can’t express.

Case, When: SQL’s CASE expression

What you use when you want to switch the display based on a row’s value is Case and When. Let me put a popularity label on articles by view count.

>>> Post.objects.annotate(
...     popularity=Case(
...         When(view_count__gte=200, then=Value("Popular")),
...         When(view_count__gte=100, then=Value("Average")),
...         default=Value("Low"),
...     )
... )

SELECT "blog_post"."title", "blog_post"."view_count",
       CASE WHEN "blog_post"."view_count" >= 200 THEN 'Popular'
            WHEN "blog_post"."view_count" >= 100 THEN 'Average'
            ELSE 'Low' END AS "popularity"
FROM "blog_post"

Case/When became SQL’s CASE expression. CASE WHEN condition THEN value ... ELSE value END is SQL’s conditional branch that looks at conditions top-down per row and returns the first matching value. It’s the CASE that appeared briefly with bulk_update in the Queries article, this time assembled by myself. Since it’s evaluated top-down, the order of writing the >= 200 branch before >= 100 matters.

This conditional branch is powerful combined with aggregation. Let me write a conditional aggregation of “count only each user’s published articles.”

>>> User.objects.annotate(published_count=Count("posts", filter=Q(posts__status="published")))

SELECT "blog_user"."id", "blog_user"."name", "blog_user"."email",
       COUNT("blog_post"."id") FILTER (WHERE "blog_post"."status" = 'published') AS "published_count"
FROM "blog_user" LEFT OUTER JOIN "blog_post" ON ("blog_user"."id" = "blog_post"."author_id")
GROUP BY "blog_user"."id"

Passing filter= to Count became the syntax COUNT(...) FILTER (WHERE ...). This is the SQL-standard way of writing “count only rows matching the condition within a group.” It groups all articles per user with annotate’s GROUP BY while COUNTing only the published ones among them. You can produce aggregations like “how many per status” per user all at once.

Subquery and OuterRef: correlated subqueries

Last is the subject of wanting to attach “each user’s latest article title” to a list. Since we select one article per user, this can’t be expressed with the aggregate functions so far. What you use is Subquery and OuterRef.

>>> latest = Post.objects.filter(author=OuterRef("pk")).order_by("-published_at")
>>> User.objects.annotate(latest_title=Subquery(latest.values("title")[:1]))

SELECT "blog_user"."name",
       (SELECT U0."title" FROM "blog_post" U0
        WHERE U0."author_id" = ("blog_user"."id")
        ORDER BY U0."published_at" DESC LIMIT 1) AS "latest_title"
FROM "blog_user"

Inside the SELECT clause, a separate SELECT enclosed in parentheses appeared. This is a subquery (a query within a query). The point is the WHERE U0."author_id" = ("blog_user"."id") part, where the inner SELECT references the outer blog_user’s column. OuterRef("pk") became this reference to “the outer row’s id.”

For each outer row, the inner SELECT runs using that row’s value (the user id); such a subquery is called a correlated subquery. When building alice’s row, the inner one fetches one latest article with author_id = 1, and for bob’s row it fetches with author_id = 2. The inner query is linked per row. .values("title")[:1] corresponds to the inner SELECT title ... LIMIT 1.

You can do something similar with annotate’s GROUP BY, but correlated subqueries suit situations like “select one record per group and get another column of that row (here title)” that are hard to express with a simple aggregate function. Keeping “you can embed a SELECT inside a SELECT” in your toolbox lets you assemble a complex list in a single SQL statement.

Wrap-up

The final installment lined up the ORM and SQL from the perspective of speed.

Handling the N+1 problem, when boiled down, was a choice of two. Combine into one with a JOIN (select_related), or fetch together with an IN clause and split into a separate query (prefetch_related). Which to choose is decided by whether the target you follow settles to one record or could be multiple. The goal was to collapse queries that had ballooned to N into a constant number.

Aggregation and its surroundings mapped cleanly to SQL syntax. Let me collect this time’s correspondences into one sheet.

ORM side	SQL side
select_related	JOIN (combine into one)
prefetch_related	body SELECT + WHERE … IN (2 statements)
aggregate(Count, Sum, Avg, Min, Max)	aggregate functions (no GROUP BY, returns 1 row)
annotate	aggregation column + GROUP BY
values().annotate()	specify the GROUP BY unit with values
exists() / count()	SELECT 1 … LIMIT 1 / COUNT(*)
explain()	EXPLAIN (Index Scan / Seq Scan)
F	column reference (calculating and comparing columns)
Q(`\|` / `~`)	WHERE’s OR / NOT
Case, When	CASE WHEN … END
Subquery, OuterRef	correlated subquery

Finally, let me look back on the whole series. What I did across four articles was the same work of turning over one page of ORM code to see the SQL. CREATE TABLE for model definitions, ALTER TABLE for migrations, SELECT for filter, and the SQL running behind N+1. In every case, turning it over, SQL was waiting. What I learned there is that the ORM isn’t a naive translator but embeds efforts toward the safe side and the efficient side throughout. JOIN increases rows. OFFSET reads the rows it skips. N+1 bites by the number of round-trips. Such SQL-side properties didn’t disappear even while using the ORM.

At the start, in the Model Definition article, I wrote “I know SELECT, WHERE, and JOIN but can’t accurately explain them.” Through four articles, I think I’ve become able to read each of them mapped to ORM code. The way of seeing SQL I gained here should work outside Django too. Reading Prisma or TypeORM docs also ends up being about tracing “what SELECT or JOIN this method becomes.” Being able to see the SQL beyond the ORM was the biggest gain.