October 4, 2026•5 min read

The answer: Two Fixes in Two Different Places

Two queries, correct code, still one second. The answer lives in two different places, and only one of them is in the code.

Read in Indonesian

Lab repo

The lab lives in folder 0001.n-plus-one

The deliberately broken code, the dataset, and the target live in a public repo. Everything runs in Docker. make check

This is the answer article. If you have not done the lab yet, close this page and start with Two Queries Left, Still One Second. The lab is in inva-lab — make up, make check, and the target is <= 3 queries with p95 <= 30 ms.

Everything below is the answer.

Key takeaways

  • Fewer queries is not the same as faster: 2 queries can be slower than 41.
  • Counting with len() means moving every row into application memory. Ask for the number instead.
  • After the code is right, the second layer is in the schema: a foreign key with no index makes the database scan the whole table and throw almost all of it away.
  • An index speeds up how the database searches. It does not reduce how much data has to cross.

The full numbers

Six variants of the same endpoint, on the same dataset (400 posts, 750,000 comments, 20 posts per page). All measured on the same machine, and every variant returns an identical response body — only the performance changed.

version queries p50 p95
as-is 41 950 ms 1,152 ms
author eager loaded 21 915 ms 981 ms
trap: comments eager loaded, counted with len() 2 983 ms 1,026 ms
trap + index 2 949 ms 1,002 ms
comments counted as one aggregate 2 60 ms 62 ms
aggregate + index 2 7 ms 8 ms

The first three rows were in the previous article. The last three are the answer, and look again at the fourth row — that is the part that is easiest to skip.

Fix one: count, do not move

The mistake was not using eager loading. Use eager loading forever, for data you actually intend to display.

The mistake was using len() to count. For len() to work, every row has to be in application memory first. You never needed the rows — you needed one number per post.

So do not fetch the rows. Ask for the number:

PYTHON
post_ids = [post.id for post in posts]

counts = dict(
    session.execute(
        select(Comment.post_id, func.count())
        .where(Comment.post_id.in_(post_ids))
        .group_by(Comment.post_id)
    ).all()
)

for post in posts:
    post.comment_count = counts.get(post.id, 0)

Still 2 queries. Latency drops from 1,026 ms to 62 ms.

Note the shape, too: latestArticles on the home page, readNextPosts on an article page, the list on a category page — all the same pattern. If you have ever shown "the last 5 comments" by loading all comments and slicing in code, that is the same mistake.

Why it is still 62 ms

At this point the query is correct and the result is correct. What is not correct is the schema, and that is invisible no matter how long you read the code.

Here is what is actually happening. The average post has ~1,875 comments, and a page shows 20 posts. So the aggregate query above has to examine 37,500 rows. That number is fine for a database.

What is not fine is how it looks for them. This is EXPLAIN ANALYZE on that query, run directly in the database, outside the application:

SQL
EXPLAIN ANALYZE
SELECT post_id, count(*)
FROM comments
WHERE post_id IN (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20)
GROUP BY post_id;
TEXT
 Finalize GroupAggregate  (actual time=64.299..65.905 rows=20 loops=1)
   ->  Gather Merge  (actual time=64.292..65.886 rows=60 loops=1)
         Workers Launched: 2
         ->  Partial HashAggregate  (actual time=57.802..57.807 rows=20 loops=3)
               ->  Parallel Seq Scan on comments  (actual time=0.046..54.035 rows=12500 loops=3)
                     Filter: (post_id = ANY ('{1,2,3,...}'::integer[]))
                     Rows Removed by Filter: 237500
 Execution Time: 66.049 ms

The result is 20 numbers. Rows read and then discarded: 237,500 × 3 workers = 712,500.

Nearly the whole table is scanned and 95% of it is thrown away, just to learn how many comments 20 posts have. Make the table 10× bigger and that number grows 10× — and make check on your laptop will not show it, because the dataset does not grow.

One more thing worth seeing: the exact same shape appears in the one-query-per-post version (50.9 ms, 748,125 rows discarded) and in the original 41-query version. Every variant is slow, from the same single cause. The query counts differ; the cause does not.

Fix two: an index, and it is outside the code

SQL
CREATE INDEX CONCURRENTLY idx_comments_post_id ON comments (post_id);

The plan becomes this:

TEXT
 HashAggregate  (actual time=12.297..12.302 rows=20 loops=1)
   ->  Bitmap Heap Scan on comments  (actual time=1.685..7.374 rows=37500 loops=1)
         ->  Bitmap Index Scan on idx_comments_post_id  (rows=37500 loops=1)
               Index Cond: (post_id = ANY ('{1,2,3,...}'::integer[]))
 Execution Time: 12.427 ms

Rows read: 37,500 — exactly what is needed, nothing discarded. Seq Scan is gone, replaced by Bitmap Index Scan. At the endpoint level, the result is 8 ms.

Why CONCURRENTLY? In this lab the table is yours and nothing writes to it, so a plain CREATE INDEX is safe. In production it is not: comments keeps taking writes, and a plain CREATE INDEX holds a lock that blocks them for the duration. CONCURRENTLY does not block — the price is two table scans and more wall time. If it fails, what is left behind is an index in invalid state that you have to drop by hand before trying again.

Two different places, and only one of them is in the code

This is the part worth remembering.

You can read repositories.py until you know it by heart and you will never find the second fix, because what is wrong is not how you fetch data — what is wrong is that the data has no shortcut for that kind of question.

And look at the fourth row of the table once more: the trap from the previous article is still 1,002 ms even with the index in place. An index speeds up how the database searches. If the problem is how much data has to cross, an index is not the answer.

Counting does not need the data. It needs the number.

If you stopped before the index

If you solved fix one and stopped because you were already 94% faster, you were not wrong — you were just not finished. In real life that decision is often the right one: 62 ms may be plenty, and an index has a price (slower writes, more disk).

What turns that into a decision is knowing a second layer exists and consciously choosing not to do it. Instead of not knowing.

The lab, if you want to prove it yourself

👉 inva-lab

BASH
git clone https://github.com/izzudd/inva-lab.git
cd inva-lab/0001.n-plus-one
make up
make check   # target: <= 3 queries, p95 <= 30 ms

The solusi branch holds both fixes plus the measurement notes. If you want to prove the cost grows with row count, multiply db/init/02_seed.sql by 3, run make reset, then make bench again.

A question for the next lab: if comments already holds 200 million rows and a per-post count(*) is part of a page millions of people see, an index stops being enough. What do you change? (Three answers get used in production, and each one has a drawback.)

Get better at shipping code.

Join the other developers getting tech updates, tips, and relaxed tutorials in their inbox every week.

Stay in the loop

Tech updates, coding tips, and relaxed insights straight to your inbox every week.

No spam. Unsubscribe anytime.

Read next

See all
Struktur DataWhat a Linked List Actually Is

A linked list is a linear chain of nodes, each holding data and a pointer to the next node. Here is what that chain looks like, what it costs next to an array, and the same minimal implementation in Java, Python, and JavaScript.

For developers who learn by struggling

© 2026 Inva.dev.