debugsqlpostgresqlMajor
N+1 query problem: detect and fix with EXISTS or JOIN
Viewed 0 times
N+1 queryORM N+1query optimizationJOIN instead of loopeager loadinglazy loading
Problem
An API endpoint that loads 100 orders and then fires one query per order to fetch customer details executes 101 queries instead of 1, causing 10-100x latency.
Solution
Replace N+1 with a single JOIN or an IN-list query:
-- N+1 pattern (bad):
-- SELECT * FROM orders LIMIT 100;
-- For each order: SELECT * FROM customers WHERE id = $1;
-- Fixed with JOIN:
SELECT o.id, o.amount, c.name, c.email
FROM orders o
JOIN customers c ON c.id = o.customer_id
LIMIT 100;
-- Fixed with EXISTS check:
SELECT o.* FROM orders o
WHERE EXISTS (
SELECT 1 FROM customers c
WHERE c.id = o.customer_id AND c.active = true
);
-- Detect in pg_stat_statements:
SELECT query, calls, mean_exec_time
FROM pg_stat_statements
WHERE query LIKE '%customers%'
AND calls > 1000
ORDER BY calls DESC;
-- N+1 pattern (bad):
-- SELECT * FROM orders LIMIT 100;
-- For each order: SELECT * FROM customers WHERE id = $1;
-- Fixed with JOIN:
SELECT o.id, o.amount, c.name, c.email
FROM orders o
JOIN customers c ON c.id = o.customer_id
LIMIT 100;
-- Fixed with EXISTS check:
SELECT o.* FROM orders o
WHERE EXISTS (
SELECT 1 FROM customers c
WHERE c.id = o.customer_id AND c.active = true
);
-- Detect in pg_stat_statements:
SELECT query, calls, mean_exec_time
FROM pg_stat_statements
WHERE query LIKE '%customers%'
AND calls > 1000
ORDER BY calls DESC;
Why
Each database round trip has latency (network + parsing + planning + execution). N+1 multiplies this by the result set size. A JOIN or subquery brings all data in one round trip with a single plan.
Gotchas
- ORMs hide N+1 behind method chains; use query logging (log_min_duration_statement=0) to detect them
- Eager loading in ORMs can produce a Cartesian product for multiple has-many associations; use separate queries per association instead
- pg_stat_statements shows calls count; a query with 10000 calls/minute when the endpoint is called 100 times/minute signals N+1
Code Snippets
Identify high-call-count, individually fast queries (classic N+1 signature)
-- Find N+1 candidates in pg_stat_statements:
SELECT left(query, 80), calls,
round(mean_exec_time::numeric, 3) AS mean_ms
FROM pg_stat_statements
WHERE calls > 10000
AND mean_exec_time < 5
ORDER BY calls DESC
LIMIT 20;Context
ORM-backed REST APIs fetching related records in application loops
Revisions (0)
No revisions yet.