How to Find N+1 Queries Before They Bite

The N+1 query is the performance bug that passes every code review and every demo, then takes your database down the first Monday you get real traffic. It is invisible with ten rows of test data and catastrophic with ten thousand. Nothing in a normal test suite catches it, because the code is correct; it is just quietly executing one query per row.

This tutorial shows you how to detect N+1 queries deliberately, measure what they cost, fix them, and then prevent them from coming back. Examples lean on Django and Rails ORMs plus raw PostgreSQL, but the technique is identical everywhere. For the wider context on query performance, read auditing database queries for scale.

What you’ll need

• An app backed by a relational database, ideally with an ORM
• Access to enable query logging in a dev or staging environment
• A database client that can run EXPLAIN ANALYZE
• About an hour

1. Understand what an N+1 actually is

The name is the definition. You run 1 query to fetch a list of N things, then, without meaning to, 1 more query for each of those N things to fetch something related. Fetch 50 blog posts, then touch post.author.name in a loop, and your ORM lazily fires 50 extra queries. That is 51 round trips to serve one page.

# The trap. One query for posts, then one per post for the author.
posts = Post.objects.all()          # 1 query
for post in posts:
    print(post.author.name)         # N queries, one per post

Each individual query is fast and correct, which is why it survives review. The cost is the count, and the count scales with your data, so the bug gets worse precisely as you succeed.

2. Turn on query logging

You cannot fix what you cannot count, so make the queries visible. Turn on logging at the ORM or database layer.

Django, log every SQL statement to the console:

LOGGING = {
    "version": 1,
    "handlers": {"console": {"class": "logging.StreamHandler"}},
    "loggers": {
        "django.db.backends": {"handlers": ["console"], "level": "DEBUG"},
    },
}

Rails logs queries by default in development; watch log/development.log.

Or log at the database itself, which catches everything regardless of which code path or ORM issued it. In PostgreSQL:

-- Log every statement (dev only; this is very noisy)
ALTER SYSTEM SET log_statement = 'all';
SELECT pg_reload_conf();

3. Find the hot spots

Now load a real endpoint and count. The signature of an N+1 is a run of nearly identical queries differing only by an ID in the WHERE clause:

SELECT * FROM authors WHERE id = 1;
SELECT * FROM authors WHERE id = 2;
SELECT * FROM authors WHERE id = 3;
... 47 more times

For a repeatable count rather than eyeballing the log, wrap the request in a query counter. In Django:

from django.test.utils import CaptureQueriesContext
from django.db import connection

with CaptureQueriesContext(connection) as ctx:
    client.get("/posts/")
print(len(ctx.captured_queries))

Any endpoint whose query count grows when you add rows to the database has an N+1. Load your list pages with 10 rows and then 500 rows; if the query count tracks the row count instead of staying flat, you have found one. Rank the endpoints by query count and start at the top.

4. Confirm the cost with EXPLAIN ANALYZE

Before rewriting anything, measure, so you can prove the fix later. Take one of the repeated queries and run EXPLAIN ANALYZE, which executes the query and reports the real timing and row counts:

EXPLAIN ANALYZE SELECT * FROM authors WHERE id = 42;

Index Scan using authors_pkey on authors  (cost=0.29..8.30 rows=1)
  (actual time=0.021..0.022 rows=1 loops=1)
Planning Time: 0.083 ms
Execution Time: 0.049 ms

One query at 0.05 ms looks harmless. Multiply by the N from step 3, then add per-query round-trip overhead (network latency, connection handling, query parsing), which often dwarfs the execution time itself. Fifty round trips at even 1 ms of overhead each is 50 ms of pure waste per request, and it gets worse under connection-pool contention. This is also the moment to catch a related sin: a repeated query doing a sequential scan (Seq Scan in the plan) instead of an index scan is an N+1 and a missing index at once.

5. Fix it with eager loading or a join

The fix is to fetch the related data up front in one query instead of lazily in N. Every ORM has a dedicated tool for this.

Django, use select_related for foreign keys (SQL join) and prefetch_related for reverse and many-to-many relations (a second batched query):

# One JOIN instead of 51 queries
posts = Post.objects.select_related("author").all()

# For a to-many relation: 2 queries total, not N+1
posts = Post.objects.prefetch_related("comments").all()

Rails, use includes:

# 2 queries total regardless of how many posts
posts = Post.includes(:author).all

In raw SQL, replace the per-row lookups with a single join, or a single batched IN query over the collected IDs:

SELECT posts.*, authors.name
FROM posts
JOIN authors ON authors.id = posts.author_id;

Re-run your query counter from step 3. The count should drop from N+1 to a small constant (1 or 2) and stay flat as you add rows. That flatness is the whole goal.

6. Add a guardrail so it never comes back

N+1s regress constantly, because a future developer adds one innocent attribute access inside a serializer and the lazy loading returns. The defense is a test that asserts a query budget, so the regression fails CI instead of production.

Django:

from django.test import TestCase

class PostListQueryBudget(TestCase):
    def test_list_stays_flat(self):
        # Create enough rows that an N+1 would blow the budget
        make_posts(50)
        with self.assertNumQueries(2):
            self.client.get("/posts/")

Rails teams get the same guarantee from a gem like bullet running in test mode, which raises when it detects an N+1. Either way, the point is identical: encode the expected query count as an assertion so the pattern cannot silently return. This kind of executable guardrail is exactly what separates a codebase that stays fast from one that slowly rots, a theme running through our free code audit checklist.

Get a second pair of eyes (free)

Finding N+1s is a skill, and the ones that survive are hidden behind serializers, GraphQL resolvers, and template loops where the query is three layers from the code you are reading. An experienced reviewer spots the pattern fast. After your own pass, get a free code audit: a Webisoft engineer will manually profile your hot paths and review your data access alongside the rest of the codebase, then send you a prioritized report. Free, human, and specific to your app.