All posts

What is the N+1 query problem? Find it in SQL logs and fix it with JOINs or batching

October 25, 2026 · 10 min read

A slow list API can hide behind individually fast queries. Follow a posts endpoint to detect N+1 queries, choose between JOINs and batched reads, apply Rails or Django eager loading, and check pagination, indexes, and performance afterward.

What is the N+1 query problem? How 50 posts become 51 queries

An endpoint displays 50 post titles and their authors. It fetches the posts, then reads each author inside a loop. If the ORM uses lazy loading, accessing that relationship triggers another SQL query.

posts = fetchLatestPosts(limit=50)  // Fetch the list once
for post in posts:
    author = fetchAuthor(post.authorId)  // Fetch once per row
    render(post.title, author.name)

If each access hits the database, that is 1 + 50 = 51 queries. An ORM cache can reduce the count when authors repeat, so 51 is conditional. The signal to investigate is related queries growing with the list size. A loop of handwritten SQL can cause the same problem.

Detection: count SQL queries per request before looking for one slow query

Sequential round trips add up even when each query is fast. If each takes 2 ms to complete, 51 take 102 ms and two take 4 ms. This calculation assumes equal durations and sequential execution; it does not predict a production speedup.

1. Associate queries with a request ID or trace. Count calls, sum durations, and look for repeated SELECT statements with the same shape but different parameter values.
2. Compare 10 and 50 results through the same code path, including serialization and templates. With one related query per item, the count grows from 11 to 51.
3. Separate fixed queries such as authentication and COUNT from relationship queries that grow with the list. Redact tokens and personal data from logs.

Fix 1: JOIN a single-valued relationship such as an author

If a post has at most one author, joining to the author's primary key does not multiply the post's rows. LEFT JOIN preserves posts without a matching author. Select only the columns the response needs to limit transfer volume.

SELECT p.id, p.title, a.name AS author_name
FROM posts AS p
LEFT JOIN authors AS a ON a.id = p.author_id
WHERE p.tenant_id = $1
ORDER BY p.created_at DESC, p.id DESC
LIMIT 50;

Scope the posts to the authorized tenant. This example assumes author_id refers to an author the reader may access. In a multitenant implementation, apply any required authorization conditions to related data too.

Fix 2: batch collections such as comments with an IN query

For posts with multiple comments, fetch the parent page first and load comments for those post IDs together. Group them by post_id in the application. The relationship takes one query for N posts; assign an empty collection to posts without comments.

-- Query 1: select the parent page
SELECT id, title
FROM posts
WHERE tenant_id = $1
ORDER BY created_at DESC, id DESC
LIMIT 50;

-- Query 2: comments belonging to posts on that page
-- $2 onward are returned IDs; create the required number of placeholders
SELECT id, post_id, body
FROM comments
WHERE tenant_id = $1 AND post_id IN ($2, $3, $4)
ORDER BY post_id, id;

The example shows three IDs. Skip the related query for an empty parent page, and split large batches to respect parameter limits. Two queries can still be expensive if they return hundreds of thousands of comments. Aggregate when only a count is needed; bound child collections when displaying their contents.

Is one JOIN always better? Watch collections multiply rows

Joining both four comments and three tags produces 4 × 3 = 12 rows for one post. If all 50 posts have those counts, the result has 600 rows, repeating parent columns. Deduplicating parents with DISTINCT still leaves the question of how to retrieve the required child collections.

A LIMIT 50 on a posts-to-comments JOIN counts 50 joined rows, not 50 posts. A few heavily commented posts could fill the page. Page the parents first, or establish the parent page in a subquery before joining related data.

Rails and Django: eager load the relationships the response needs

Eager loading fetches relationships in advance, using JOINs or separate queries. Inspect generated SQL instead of inferring query count from a method name.

In Django, prefetching comments may not help if the loop then calls comments.filter(...), which can issue fresh queries. Match the prefetch to the conditions the response uses. Rails strict_loading can detect unexpected lazy loading.

Still slow after fixing N+1? Check pagination and composite indexes

After reducing round trips, the parent query may still skip many rows. For deep OFFSETs, consider cursor pagination using the last displayed row. Here, created_at is NOT NULL and immutable, and id is unique.

SELECT id, title, author_id, created_at
FROM posts
WHERE tenant_id = $1
  AND (created_at, id) < ($2, $3)
ORDER BY created_at DESC, id DESC
LIMIT 50;

-- Candidate index for this access pattern
CREATE INDEX posts_tenant_created_id_idx
  ON posts (tenant_id, created_at DESC, id DESC);

Bind the previous page's final timestamp and ID to $2 and $3. The ID breaks timestamp ties so posts are not skipped. The index starts with the equality condition tenant_id, followed by the continuation columns. Validate this candidate with the plan and its write overhead. A cursor does not make all pages share one snapshot.

Verification: measure query count, returned rows, and API p95

Ten small test records can hide the cost of production relationships. Compare parent counts, skewed comment counts, cache states, and concurrency under equivalent conditions.

• Increase parents from 10 to 50 and verify related queries do not grow proportionally. Include distinct authors to expose problems that repeated-author caching could hide.
• Track query count and total duration alongside returned rows, transferred bytes, and application memory. Check that a large JOIN or excessive prefetching has not made things worse.
• Compare endpoint p95 and p99 under the same load, including connection waits, result assembly, and serialization.
• Inspect remaining expensive SELECTs with EXPLAIN (ANALYZE, BUFFERS). ANALYZE actually executes the query, so account for load in a test environment. Keep API measurements too: the plan measurement excludes transmission to the client.

FAQ: can indexes or parallel execution solve N+1 on their own?

• Will adding an index fix it? Individual queries may become faster, but their count remains. Fix the fetch pattern, then inspect plans for the remaining queries.
• Can I fetch every row's relationship in parallel? This may shorten elapsed time but leaves the SQL count unchanged. It can increase connection contention under load; consider batching first.
• Is caching enough? Hits reduce work, but the original queries remain on cold starts, expiration, and invalidation. Choose a fetch pattern that also performs acceptably with a cold cache.

In an interview: how would you investigate a slow list API?

Explain the sequence: measure query counts and time per request, then increase list size to check growth. If it is N+1, consider JOINs for single-valued relationships and batched reads after parent pagination for collections. Compare returned rows as well as query count, then inspect plans for remaining expensive queries. This connects observation, intervention, and verification.

Primary sources for checking your implementation

• Ruby on Rails Guides — Eager Loading Associations
• Django 5.2 — select_related / prefetch_related
• PostgreSQL 17 — LIMIT and OFFSET
• PostgreSQL 17 — Multicolumn Indexes
• PostgreSQL 17 — Row Constructor Comparison
• PostgreSQL 17 — Using EXPLAIN
• GitLab Docs — QueryRecorder