Skip to content

What the N+1 query problem is and how to fix it

The N+1 query problem happens when one query loads a set of rows and the application runs another query for each row. Loading three posts and their comments can require four queries, one for the posts and one for each post's comments.

  • 5 minute read
  • Examples run in SQLite

Why one request sends repeated queries

Lazy loading fetches related data when application code first asks for it. Reading a post's comments can therefore run a query even if the database call is not visible in that code. When an application repeats the step for every post, it sends the same comment query with a different post_id value. SQLAlchemy documents the repeated relationship queries as the N+1 pattern.

The problem is in the sequence of statements that one request or job sends. A quick child lookup can still be expensive when the application repeats it hundreds of times.

Trace one request across its SQL statements

The request loads three posts and then the comments for each post. Two posts have comments, and one has none.

Schema and sample SQL
Schema and sample data
CREATE TABLE posts (
  id INTEGER PRIMARY KEY,
  title TEXT NOT NULL
);
CREATE TABLE comments (
  id INTEGER PRIMARY KEY,
  post_id INTEGER NOT NULL REFERENCES posts(id),
  body TEXT NOT NULL
);
INSERT INTO posts (id, title) VALUES
  (1, 'Measuring database requests'),
  (2, 'Reading query plans'),
  (3, 'A post without comments');
INSERT INTO comments (id, post_id, body) VALUES
  (10, 1, 'Count the whole request.'),
  (11, 1, 'Keep the result equivalent.'),
  (12, 2, 'A plan covers one statement.');
N+1 request trace
  1. SELECT id, title FROM posts ORDER BY id;
  2. SELECT id, body FROM comments WHERE post_id = 1 ORDER BY id;
  3. SELECT id, body FROM comments WHERE post_id = 2 ORDER BY id;
  4. SELECT id, body FROM comments WHERE post_id = 3 ORDER BY id;

4 queries (1 + 3)

Replacement with one query
SELECT
  posts.id,
  posts.title,
  comments.id AS comment_id,
  comments.body AS comment_body
FROM posts
LEFT JOIN comments ON comments.post_id = posts.id
ORDER BY posts.id, comments.id;

Detect repetition at the request boundary

Count the database calls for the whole request, job, or resolver. SQL logs and application traces show the repeated statement and its changing parameters. A statement's execution plan describes how that one statement reads its data. It does not show how many times the application sent the statement.

  • Set an expected query count in an integration test for a typical request.
  • Group SQL statements that differ only in their parameter or literal values, and find the code that repeats them.
  • Test empty results and parents with no related rows, so a fix does not quietly drop data.
  • Measure with realistic row counts, because three rows of sample data show the pattern but not its production cost.

Replace the loop with a bounded number of queries

The replacement uses LEFT JOIN to fetch the posts and comments in one database call. The join keeps the post with no comments. A post with several comments appears in several result rows, with its post columns repeated.

You can also load the posts first, then fetch their comments together using an IN query. SQLAlchemy calls this select-in loading, and Django provides prefetch loading. Some loaders split large requests into batches, so inspect their query counts with the data size your application uses. A join or batch can also increase transferred data and memory use.

An index on the child foreign key can make each lookup cheaper, but it does not remove the application loop. After request-level tracing shows which statements repeat, use the free visualizer to inspect one SQLite statement.

Key points

  • Count every database statement that the typical request sends.
  • Compare the requested parent and related-row data before and after the change.
  • Include an empty parent result and a parent with no related rows.
  • Confirm that related rows load together instead of through one query per parent.
  • Check row duplication, transferred data, memory use, and realistic timing.
  • Check whether writes or other important queries pay for any new index.

Keep request traces and query plans separate

The SQLite example counts the queries for one request and shows that both versions return the requested data. SQLite runs in the application's process, so its queries do not make network round trips to a separate database server. More queries alone do not prove that a SQLite request is slow. A statement's plan cannot show how often the application repeats it. Verify query counts, results, and costs in the application and database you use.

Practice this in Module 4: Joins

Twelve exercises on joins that return the right rows. One asks you to include customers without orders, as the LEFT JOIN here keeps posts without comments.

See Module 4