The Wasteful JOIN I Was Afraid to Write? GORM Wrote It for Me.

2026-11-17来源 dev.toEnglish

In my last article I wrote an unnecessary JOIN by hand to see what it cost. That test came from a worry: after a long time of letting GORM write my SQL, I didn't trust myself to write it efficiently.

I ended that article with a promise to check the other half of the worry, which was my assumption that GORM's own SQL is efficient.

So I took the same task and wrote it three ways in GORM. The version I would have reached for at work, GORM's association joins, generated the same wasteful join I had been afraid of writing myself. It also finished first in every run with default settings, which makes it easy to miss.

Limited to one CPU core, it took about twice as long as the alternatives.

The task, written three ways

The data is the same as last time: users, addresses (one per user, with a city) and orders, with 5 million rows in each. The task is also the same: fetch every order placed by a user who lives in Pune.

The GORM models:

type User struct { ID int64 Name string Address Address Orders []Order } type Address struct { ID int64 UserID int64 City string } type Order struct { ID int64 UserID int64 Total float64 CreatedAt time.Time User User } 

1. Association joins. This uses GORM's own relationship handling, and it is the one I would have written by default.

db.Joins("User"). Joins("User.Address"). Where(`"User__Address"."city" = ?`, "Pune"). Find(&orders) 

2. A hand-written join string.

db.Joins("JOIN addresses ON addresses.user_id = orders.user_id"). Where("addresses.city = ?", "Pune"). Find(&orders) 

3. A subquery.

sub := db.Model(&Address{}).Select("user_id").Where("city = ?", "Pune") db.Where("user_id IN (?)", sub).Find(&orders) 

The SQL GORM generated

I printed each statement with db.ToSQL, which builds the SQL without running it. The line breaks below are mine; the SQL is GORM's.

1. Association joins

SELECT "orders"."id", "orders"."user_id", "orders"."total", "orders"."created_at", "User"."id" AS "User__id", "User"."name" AS "User__name", "User__Address"."id" AS "User__Address__id", "User__Address"."user_id" AS "User__Address__user_id", "User__Address"."city" AS "User__Address__city" FROM "orders" LEFT JOIN "users" "User" ON "orders"."user_id" = "User"."id" LEFT JOIN "addresses" "User__Address" ON "User"."id" = "User__Address"."user_id" WHERE "User__Address"."city" = 'Pune' 

2. Hand-written join string

SELECT "orders"."id", "orders"."user_id", "orders"."total", "orders"."created_at" FROM "orders" JOIN addresses ON addresses.user_id = orders.user_id WHERE addresses.city = 'Pune' 

3. Subquery

SELECT * FROM "orders" WHERE user_id IN (SELECT "user_id" FROM "addresses" WHERE city = 'Pune') 

The first statement goes from orders through users to addresses. That is the route I called wasteful in the last article, where I wrote it by hand as Query A. It also selects every column of the user and the address for each order.

The other two never mention users.

Run it yourself

Everything is in the same GitHub repository as the last article. You need Docker and nothing else, because the Go program runs in a container too.

# start PostgreSQL 16 docker compose up -d --wait db # load 5 million users, addresses and orders docker exec pgtest psql -U postgres -v users=5000000 -v orders=5000000 -f /setup.sql # print GORM's SQL for each way, then its plan docker compose run --rm gorm # the same three statements, limited to one CPU core docker exec pgtest psql -U postgres -f /gorm-single-core.sql 

main.go prints the SQL for each of the three ways and runs it with EXPLAIN (ANALYZE, BUFFERS). That executes the query and reports the plan PostgreSQL used, the real timings and how many pages it touched.

All three return the same rows, 999,696 orders in my first set of runs. Your count will differ slightly, because orders are assigned to users at random.

Round 1: default settings

With PostgreSQL's default settings, association joins finished first in all four runs. The median was 1,478 ms, against 1,960 ms for the hand-written join and 1,985 ms for the subquery, so it looked about 25% faster.

Run 1. Association joins 2. Hand-written join 3. Subquery
1 1,573 ms 2,111 ms 2,000 ms
2 1,405 ms 1,850 ms 1,971 ms
3 1,455 ms 2,010 ms 1,891 ms
4 1,501 ms 1,910 ms 2,031 ms
Median 1,478 ms 1,960 ms 1,985 ms

If I had only timed these, I would have kept the association joins. The plan explains why that would have been a mistake:

Gather Workers Planned: 2 Workers Launched: 2 -> Parallel Hash Join -> Parallel Seq Scan on orders -> Parallel Hash -> Parallel Hash Join -> Parallel Seq Scan on users "User" (... rows=1666667 loops=3) 

PostgreSQL ran the association-join query on three CPU cores: the main process plus two workers. The other two queries each ran on one core. It is the same effect I found in the last article, and this time GORM wrote the query.

Round 2: a single CPU core for each query

With every query limited to one core, association joins took about twice as long as the other two: between 1.9 and 2.4 times, for the same rows. I ran this round twice, the second time on a fresh load of the data.

1. Association joins 2. Hand-written join 3. Subquery
Default settings, median of 4 1,478 ms 1,960 ms 1,985 ms
Single CPU core, run 1 4,271 ms 1,817 ms 1,866 ms
Single CPU core, run 2 3,740 ms 1,921 ms 1,906 ms

The plan shows where the time went:

-> Hash Join (... actual time=478.503..2175.753 rows=1000000 loops=1) Hash Cond: ("User".id = "User__Address".user_id) -> Seq Scan on users "User" (... rows=5000000 loops=1) 

PostgreSQL read all 5 million users and joined them to the Pune addresses before it matched a single order. That stage was not ready until 2.0 to 2.3 seconds in. In the hand-written join, the Pune addresses were ready at about 0.3 seconds.

A busy production server may not have spare cores to hand out, so I treat the single-core figure as the more realistic one.

What the plans show, whatever the clock says

The run times moved around between rounds. The amount of work did not. BUFFERS reports how many pages each query touched, and those figures were almost identical in every run.

1. Association joins 2. Hand-written join 3. Subquery
Reads all of users Yes, 5 million rows No No
Table pages read About 102,400 70,504 70,504
Temp file pages read 63,000 to 68,500 30,760 30,760
Row width in the plan 73 bytes 30 bytes 30 bytes

Three things stand out.

  • Association joins read 45% more table pages and about twice the temp file data. The extra pages are the users table, which the result did not need for filtering.
  • Each row is about 2.4 times as wide. GORM selects the user and address columns for every order so it can fill in order.User and order.User.Address. EXPLAIN does not count the cost of sending those rows to the application or building the structs, so the real gap is larger than these timings show.
  • The hand-written join and the subquery are the same query to PostgreSQL. Their plans match line for line. PostgreSQL turned IN (SELECT ...) into the same hash join.

One more detail is worth knowing. GORM writes LEFT JOIN, but the plan shows plain hash joins. The WHERE clause filters on the address, which discards any order without a matching address, so PostgreSQL treats both joins as inner joins.

When association joins are the right choice

Association joins are not wrong. They do more because they return more: every order comes back with its user and that user's address already filled in.

If an endpoint needs to show the customer's name and city next to each order, that extra work buys something, and one joined query is a reasonable way to get it.

The problem is using them only to filter. I wanted orders, I needed the city just to decide which orders, and the query paid for 5 million user rows and extra columns on every order that nothing used.

What I do differently now

In the last article I was afraid that raw SQL would let me write a wasteful join without noticing. The same join was already in my GORM code, written by the feature I trusted most.

Three habits came out of this.

  • Filter with a subquery or a hand-written join. If I only need a related table to decide which rows to return, I no longer use association joins for it.
  • Load related data on purpose. If I need a couple of fields from a related table, I select those fields, instead of letting GORM load whole nested structs by default.
  • Read the SQL before shipping. For list and reporting endpoints I print the statement with ToSQL or Debug() and run it through EXPLAIN (ANALYZE, BUFFERS). The page counts tell me more than the run time does.

Limits of this test

  • It ran on one machine, with PostgreSQL 16 in Docker Desktop and the default configuration.
  • The single-core figures are from two runs, the second on a fresh load of the data. The default-settings figures are medians of four runs.
  • The timings come from EXPLAIN ANALYZE, so they cover the database only. They leave out sending rows to the application and GORM building the structs.
  • It covers one query shape on one dataset, with about one order per user. Other data could behave differently.
  • I tested three ways of writing the query. GORM has others, such as Preload and InnerJoins, that I have not measured.
  • The GORM version is whatever go mod tidy fetched on the day. The exact version is in go.mod in the repository.

原文:https://www.a8q9.cn/en/article/the-wasteful-join-i-was-afraid-to-write-gorm-wrote-it-for-me-57f86a.html

相关文章