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

Why one request sends repeated queries

Lazy relationship loading can run a query when code reads a related property, so the code may not show the database call. The application loads a list of posts and then asks for the comments while it renders each post. The child statement stays the same, and only its post_id parameter changes. The SQLAlchemy documentation describes this pattern as N loaded objects followed by N lazy relationship loads.

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

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.');

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 LEFT JOIN in the bounded replacement returns the requested posts and comments with one database call. LEFT JOIN keeps the post that has no comments. A post with several comments repeats its post columns in the joined rows.

A select-in or prefetch loader is another option with a number of queries that does not grow with the number of posts. It loads the parents and then loads all related rows with one IN query. Django documents this separate prefetch query, and SQLAlchemy documents joined and select-in relationship loading. Large result sets can increase transferred rows, memory, or the size of the IN list, so measure the actual workload.

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.

Keep request traces and query plans separate

This SQLite example counts the queries for one sample request and shows that both versions return the same requested data. An individual statement's plan cannot prove or rule out request-level repetition. PostgreSQL and other production databases have different planners, costs, drivers, and relationship loaders, so verify the same request in its real application environment.