n-plus-one-query-detection
verified90302084-5a3d-40b6-a37f-a5eb8dd0ae7c
Detect and eliminate N+1 query patterns in ORM code — find loops that issue one query per row and batch them. Use when list endpoints are slow under load.
Metadata
Skill file
# N+1 Query Detection
Use when a list endpoint is slow and you suspect the ORM is issuing one query per row instead of a few batched queries. This is the single most common ORM performance bug.
## Spotting the Pattern
The N+1 pattern: **1 query to fetch N rows, then N more queries (one per row) to fetch a related object.**
```python
# ❌ N+1 — SQLAlchemy: lazy loads each order's customer
orders = session.query(Order).limit(100).all()
for order in orders:
print(order.customer.name) # 1 query per iteration → 101 queries total
```
```python
# ❌ N+1 — Django: lazy loads each order's customer
orders = Order.objects.all()[:100]
for order in orders:
print(order.customer.name) # 1 query per iteration → 101 queries total
```
### Enable Query Logging to See It
```python
# SQLAlchemy — print every query
import logging
logging.basicConfig()
logging.getLogger("sqlalchemy.engine").setLevel(logging.INFO)
# Django — count queries in a view
from django.db import connection, reset_queries
reset_queries()
# ... run the view code ...
print(f"Query count: {len(connection.queries)}")
for q in connection.queries:
print(q["sql"])
```
## The Fix: Eager Loading
### SQLAlchemy
```python
from sqlalchemy.orm import selectinload, joinedload
# ✅ selectinload — 2 queries total (fetch orders, then fetch all customers IN one query)
orders = (
session.query(Order)
.options(selectinload(Order.customer))
.limit(100)
.all()
)
# ✅ joinedload — 1 query with a JOIN (watch for cartesian products with collections)
orders = (
session.query(Order)
.options(joinedload(Order.customer))
.limit(100)
.all()
)
```
**Decision**: `selectinload` for collections (many-to-many, one-to-many) and `joinedload` for single relations (many-to-one). `selectinload` is safer for collections because `joinedload` with multiple collections produces a cartesian product.
### Django
```python
# ✅ select_related — SQL JOIN, for ForeignKey/OneToOne
orders = Order.objects.select_related("customer").all()[:100]
# ✅ prefetch_related — separate batched query, for ManyToMany/reverse FK
orders = Order.objects.prefetch_related("items").all()[:100]
# Both together for nested relations
orders = (
Order.objects
.select_related("customer")
.prefetch_related("items__product")
.all()[:100]
)
```
## Batching Into a Single Query (no ORM relationship)
When eager loading isn't available, batch the lookup yourself:
```python
# ❌ N+1 — query inside loop
for user_id in user_ids:
user = db.get_user(user_id)
process(user)
# ✅ Single IN query, then a dict lookup
users = db.get_users_by_ids(user_ids) # SELECT * FROM users WHERE id IN (...)
user_map = {u.id: u for u in users}
for user_id in user_ids:
process(user_map[user_id])
```
## Verifying Query Count Before/After
```python
# SQLAlchemy — count queries
from sqlalchemy import event
query_count = 0
@event.listens_for(Engine, "before_cursor_execute")
def count_queries(*args):
global query_count
query_count += 1
```
```python
# Django — assertNumQueries in tests
from django.test import TestCase
class OrderViewTest(TestCase):
def test_no_n_plus_one(self):
with self.assertNumQueries(2): # exactly 2 queries, not 101
response = self.client.get("/api/orders/")
self.assertEqual(response.status_code, 200)
```
```python
# pytest + Django — assert on query count
def test_order_list_queries(django_assert_num_queries):
with django_assert_num_queries(2):
list_orders() # should take 2 queries, not N+1
```
## Guardrails
- **Never** eager-load *everything* — fetching unused relations wastes memory and can be slower than the N+1 it replaces.
- **Never** fix an N+1 with a giant `joinedload` across multiple collections — you'll get a cartesian product that's *worse*.
- **Always** measure query count before and after — prove it went from N+1 to ~2, don't assume.
- **Always** add a regression test that asserts the query count, so the N+1 can't sneak back in.
## Pitfalls
- **Eager-loading everything (over-fetching)**: Adding `select_related`/`joinedload` for every relation loads data the view never uses. Eager-load only what the view actually reads.
- **Fixing N+1 into a giant join that's worse**: `joinedload` on two `one-to-many` collections produces `rows = A × B` cartesian product. Use `selectinload` for collections.
- **Not measuring — "I think it's fixed"**: The whole point is the query count. Measure it (logging, `assertNumQueries`, `django_assert_num_queries`).
- **Missing the N+1 that hides in a template/helper**: The loop may be in a Jinja template or a serializer, not the obvious view code. Enable query logging and trace *every* query.
- **Fixing the query but not the pattern**: One N+1 fixed, but the team keeps writing new ones. Add a linter (e.g., `nplusone` for SQLAlchemy, `django-querycount`) and a test.
## Verify / Checklist
- [ ] Query logging enabled and the N+1 pattern observed (1 + N queries)
- [ ] Root cause identified: which relationship is being lazy-loaded in a loop
- [ ] Fix applied: `selectinload`/`joinedload` (SQLAlchemy) or `select_related`/`prefetch_related` (Django)
- [ ] Query count measured before (e.g., 101) and after (e.g., 2)
- [ ] Only the relations the view actually uses are eager-loaded (no over-fetching)
- [ ] Regression test asserts the query count (`assertNumQueries` / `django_assert_num_queries`)
- [ ] No cartesian product introduced (collections use `selectinload`, not `joinedload`)Attached files
No attached files.