I Wrote a Wasteful JOIN on Purpose. It Ran Faster, Until It Didn't.

On my projects, GORM wrote most of the SQL. We used it for almost everything and dropped down to a SQL library only for the complex queries. Over time GORM became the comfortable default, and the idea of writing raw SQL with sqlc and pgx made me uneasy.

The worry was specific. GORM has its own way of building queries, and I trusted its defaults to produce something reasonably efficient. When I write the SQL myself, nothing stops me from taking a strange route: joining through a table I don't need, because the result comes out the same. The rows are right, so nothing looks wrong, and the wasted work stays hidden.

sqlc will tell me if a query is invalid or a type doesn't match. It won't tell me the query is doing pointless work.

So I wrote a wasteful join on purpose and measured it. On small data it made no real difference. On 5 million rows it finished ahead of the clean query. Then I changed one setting, and it took nearly twice as long.

The test

The test uses three tables: users, addresses (one per user, with a city) and orders (many per user).

CREATE TABLE users ( id bigserial PRIMARY KEY, name text NOT NULL ); CREATE TABLE addresses ( id bigserial PRIMARY KEY, user_id bigint NOT NULL UNIQUE REFERENCES users(id), city text NOT NULL ); CREATE TABLE orders ( id bigserial PRIMARY KEY, user_id bigint NOT NULL REFERENCES users(id), total numeric(10,2) NOT NULL, created_at timestamptz NOT NULL DEFAULT now() ); CREATE INDEX orders_user_id_idx ON orders (user_id); CREATE INDEX addresses_city_idx ON addresses (city); 

The task: fetch every order placed by a user who lives in Pune.

Query A takes the route I was afraid of. It goes through users, although the result needs nothing from that table.

SELECT o.id, o.user_id, o.total FROM orders o JOIN users u ON u.id = o.user_id JOIN addresses a ON a.user_id = u.id WHERE a.city = 'Pune'; 

Query B goes straight from orders to addresses.

SELECT o.id, o.user_id, o.total FROM orders o JOIN addresses a ON a.user_id = o.user_id WHERE a.city = 'Pune'; 

Both return exactly the same rows. Every order has a user_id that points at a real user, so joining users adds nothing and removes nothing.

I ran each query with EXPLAIN ANALYZE, which executes the query and prints the plan PostgreSQL chose along with the real timings.

Run it yourself

Everything runs in one PostgreSQL 16 container. The two files, setup.sql and queries.sql, are in this GitHub repository.

docker run --name pgtest -e POSTGRES_PASSWORD=pass -d postgres:16 # wait about 10 seconds for the server to start docker cp setup.sql pgtest:/setup.sql docker cp queries.sql pgtest:/queries.sql docker exec pgtest psql -U postgres -f /setup.sql docker exec pgtest psql -U postgres -f /queries.sql 

setup.sql creates the tables and fills them with generate_series. I used two sizes.

Addresses are spread evenly over five cities, so Pune holds one fifth of the users.

The small size is the default. For the large one, the same file takes the row counts on the command line:

docker exec pgtest psql -U postgres -v users=5000000 -v orders=5000000 -f /setup.sql 

Round 1: small data

On the small dataset the two queries were level. Across five runs, Query A had a median of 59.6 ms and Query B 65.2 ms.

The plan for Query A shows the extra work clearly:

-> Seq Scan on users u (cost=0.00..819.00 rows=50000 width=8) (actual time=0.003..2.741 rows=50000 loops=1) 

PostgreSQL read all 50,000 users even though the result uses none of their columns. It did not drop the join for me. That detour cost about 7 ms: the address-and-user stage finished at roughly 10 ms in Query A, against 2.5 ms for the equivalent stage in Query B.

So why wasn't Query A slower overall? The two plans also fetched orders differently. Query A looked up each user's orders through the index, 10,000 small lookups. Query B read all 500,000 orders and kept the matches. On my machine the index lookups saved more time than the detour cost.

That part is not a rule. The same files on another machine gave the opposite order, with Query B ahead. At this size the difference is too small to matter, and the run time says nothing about the wasted join.

Round 2: 5 million rows

On the large dataset, with PostgreSQL's default settings, the query with the unnecessary join finished first. Query A took 1,379 ms and Query B took 1,829 ms, so the wasteful query was about 25% faster on the clock.

The reason is in the first lines of Query A's plan:

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

PostgreSQL ran Query A as a parallel query: the main process plus two workers, three CPU cores in total. Query B's plan had no Gather node, so it ran on one core.

Query A was not doing less work. It was doing more work, spread over three cores, and it still read all 5 million users.

Round 3: a single CPU core for each query

With both queries limited to a single core, the unnecessary join made the query take 1.9 times as long: 3,185 ms for Query A against 1,681 ms for Query B.

I switched parallel workers off for the session and ran the same file again:

docker exec pgtest psql -U postgres -c "SET max_parallel_workers_per_gather = 0;" -f /queries.sql 

The plan shows where the time went. In Query A, reading 5 million users and joining them to the Pune addresses was not finished until about 1,600 ms. Query B had its Pune addresses ready at about 276 ms. The detour through users cost roughly 1.3 seconds.

A busy production server may not have spare cores to hand out, so I treat this as the more realistic number.

All three results side by side

The same unnecessary join looked harmless, then helpful, then expensive. The only things that changed were the data size and how many cores the query was given.

In all three cases the plan showed the extra scan of users. The run time never did.

What I do differently now

Before running any of this, I expected the direct query to win every time. It seemed like common sense: fewer tables, less work. Common sense was right in the end, but the clock hid it twice.

Two habits came out of this.

• Decide what the query needs before writing it. I start from the table that holds the rows I want and add only the tables the result or the filter really needs. PostgreSQL chooses the order it reads tables in, but in this test it never removed the table I had included by mistake.
• Read the plan, not the run time. For any query with a join I now run EXPLAIN ANALYZE and look for two things: a scan of a table the result doesn't use, and a Gather node that could be making a slow plan look fast. For anyone moving from GORM to sqlc, this is the gap to know about. sqlc checks that the SQL is valid and that the types match. Whether the query is efficient is still my job, and EXPLAIN ANALYZE is the tool for it.

Limits of this test

• It ran on one machine, with PostgreSQL 16 in Docker Desktop and the default configuration.
• The large-data timings are single runs. The small-data figures are medians of five runs.
• The large dataset has about one order per user and the small one about ten, so the two differ in shape as well as size.
• It covers one query shape. A different join, filter or index could behave differently.
• I have not yet compared these queries with the SQL that GORM generates for the same task. That is the next test.