How to find N+1 queries in PostgreSQL
Quick answer: An N+1 query is one query to load a list, plus one extra query per row to load something related — N+1 total. You find it by spotting a query in
pg_stat_statementswith a very highcallscount and lowmean_exec_time(it's cheap but runs constantly), or by counting the queries your ORM fires on a single request. The fix is almost always eager-loading the related data in one query instead of N.
What is an N+1 query?
You load 50 blog posts, then in a loop you access post.author — and your ORM quietly runs a separate SELECT for each author. One query became fifty-one. Each query is fast, so nothing looks broken in isolation; the page is just mysteriously slow, and it gets slower as your data grows.
It's 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 — either the ORM lazy-loads them for you when you touch 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's actually N+1?
Two reliable signals:
1. pg_stat_statements — the high-calls, low-mean_exec_time fingerprint.
SELECT calls, mean_exec_time, query
FROM pg_stat_statements
ORDER BY calls DESC
LIMIT 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 shows up as a tiny query (e.g. SELECT * FROM authors WHERE id = $1) with an enormous calls count relative to everything else. It's not slow — it's frequent. Total time = calls × mean_exec_time, and that product is what's killing you.
A high calls count is a strong hint, not a verdict — plenty of legitimate hot-path queries run constantly. What marks it as an N+1 is the shape: a single-row lookup by primary key whose call count scales with the size of the list rendered on the page. Confirm 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've 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?
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 everywhere: tell the ORM up front what related data you need, so it fetches it in a set, not row-by-row.
Why does AI-generated code produce so many N+1 queries?
Because N+1s are invisible at the layer the AI works in. Your coding agent (Copilot, Cursor, Claude) writes code that is locally correct and idiomatic — for post in posts: post.author.name reads perfectly clean. The extra query per row only exists at the database layer, which the AI never sees.
Three reasons it happens constantly:
- The AI has no view of your database. It doesn't know
postshas 5 million rows, that.authortriggers a separateSELECT, or what your query plans look like. It writes plausible code, not performance-aware code. - It learned from one-at-a-time tutorials. Most example code online loads relations the simple way — a lazy
.authoraccess or a per-row query in a loop (that's easier to teach) — so the model reproduces the pattern, including the N+1 baked into it. - ORMs make N+1 the path of least resistance. Fetching relations row by row — lazy-loaded or looped — 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 — not because the AI is bad, but because it's writing against a database it has never actually seen.
How do I stop them coming back?
N+1s reappear every time someone adds a .author in a template — and now that AI writes a lot of that code, they reappear faster. Catch them two ways: track per-request query counts in CI, and periodically review your top-calls queries in pg_stat_statements.
How DBGorilla helps
DBGorilla connects to your database read-only and gives your AI coding agent (Claude Code, Cursor) the real pg_stat_statements data — so instead of guessing, your agent can point straight at the query with the runaway calls count, explain why it's an N+1, and show the eager-load fix for your ORM. It reads and explains; it doesn't touch production. Get started free →
FAQ
Are N+1 queries only a Postgres problem?
No — it's 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 can't. 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.