How to find N+1 queries in PostgreSQL
Quick answer: An N+1 query loads a list with one query, then runs one extra query for each row to load related data. That makes N+1 queries. You can find it by looking for a query in
pg_stat_statementswith a very highcallscount and lowmean_exec_time, meaning it is cheap but runs constantly, or by counting the queries your ORM sends for a single request. The usual fix is to eager-load the related data in one query instead of N.
What is an N+1 query?
Section titled “What is an N+1 query?”You load 50 blog posts, then access post.author in a loop, and your ORM runs a separate SELECT for each author. One query becomes fifty-one. Each query is fast, so nothing looks broken in isolation. The page is slow, and it gets slower as your data grows.
It is the single most common performance bug in ORM-backed apps (Django, Rails, Prisma, Sequelize, Hibernate) because fetching related rows one at a time is the path of least resistance: the ORM either lazy-loads them when you access the relation (Django, Rails, Hibernate), or you fetch them in a loop yourself (Prisma, Sequelize, which only load relations you explicitly ask for).
How do I confirm it is actually N+1?
Section titled “How do I confirm it is actually N+1?”Two reliable signals:
1. pg_stat_statements, the high-calls, low-mean_exec_time pattern.
SELECT calls, mean_exec_time, queryFROM pg_stat_statementsORDER BY calls DESCLIMIT 20;(On PostgreSQL 12 and earlier, the column is mean_time. It was renamed to mean_exec_time in PostgreSQL 13.)
An N+1 offender appears as a small query, such as SELECT * FROM authors WHERE id = $1, with an enormous calls count relative to other queries. Each call is fast. The problem is how often it runs. Total time = calls × mean_exec_time, and that product accounts for the cost.
A high calls count is a strong hint rather than proof, because many legitimate hot-path queries run constantly. The query shape is what identifies an N+1: a single-row lookup by primary key whose call count scales with the size of the list rendered on the page. Confirm it with the query count per request below.
2. Query count per request. Turn on your ORM’s SQL logging and load one page. If a list of N items fires N+1 queries, you have found it.
| Signal | N+1 looks like |
|---|---|
pg_stat_statements | one small query, calls orders of magnitude above the rest |
| ORM request log | query count scales with row count on the page |
| Latency | fine with 10 rows, awful with 10,000 |
How do I fix an N+1 query?
Section titled “How do I fix an N+1 query?”Load the related data in one query instead of N:
- Django:
select_related()(FK, JOIN) /prefetch_related()(M2M, second query). - Rails:
includes(:author). - Prisma:
include: { author: true }. - Sequelize/Hibernate: eager
include/JOIN FETCH.
The pattern is the same across these ORMs: tell the ORM up front which related data you need, so it fetches the data in a set instead of row by row.
Why does AI-generated code produce so many N+1 queries?
Section titled “Why does AI-generated code produce so many N+1 queries?”N+1s are invisible at the layer where the AI operates. Your coding agent writes code that is locally correct and idiomatic. for post in posts: post.author.name reads cleanly. The extra query per row exists at the database layer, which the AI cannot see.
The pattern recurs for several reasons:
- The AI has no view of your database. It does not know
postshas 5 million rows, that.authortriggers a separateSELECT, or what your query plans look like. It writes code that looks plausible but is not performance-aware. - It learned from one-at-a-time tutorials. Most example code online loads relations the simple way, through a lazy
.authoraccess or a per-row query in a loop, because that is easier to teach. The model reproduces the pattern, including the N+1. - ORMs make N+1 the path of least resistance. Fetching relations row by row, through lazy loading or a loop, is what you get unless you explicitly eager-load (
select_related,includes,include), which the AI rarely adds unless you prompt for it.
The more code your team generates with AI, the more N+1s you ship, because the AI writes against a database it has never seen.
How do I stop them coming back?
Section titled “How do I stop them coming back?”N+1s reappear whenever someone adds a .author in a template. Since AI now writes much of that code, they may appear more often. Catch them by tracking per-request query counts in CI and periodically reviewing your top-calls queries in pg_stat_statements.
Are N+1 queries only a Postgres problem?
No, it is an ORM/access-pattern problem. It happens on any database. Postgres just gives you pg_stat_statements to catch it cleanly.
Is one N+1 query always bad?
Not necessarily. N+1 over 5 rows is invisible. The danger is that N grows with your data, so it degrades silently over time.
Does EXPLAIN ANALYZE find N+1 queries?
No. EXPLAIN analyzes a single query; N+1 is about many queries. Use pg_stat_statements (frequency) or ORM query counts instead.
Why does AI-generated code have N+1 queries?
Because the AI writes against code it can see, not the database it cannot. post.author.name looks clean in the source; the per-row query only exists at the database layer the model never sees. It also learned from lazy-loading tutorial code, so it reproduces the pattern by default.