N+1 Query Fixes skill for Codex

An endpoint is fixed when its query count is constant with respect to the number of rows it returns, the eager loading fetches only what the response uses, and a test fails if someone reintroduces the loop. “It feels faster” is not the bar; the query count is.

Recognize it

• Logs show one query for the list, then the same query shape repeated per row with a different id.
• pg_stat_statements (or MySQL digest tables) shows a query like SELECT ... FROM authors WHERE id = $1 with a call count far above request count.
• Response time grows linearly with page size; the database is not slow per query.
• In GraphQL, a list field whose resolver loads a relation per parent object.

Workflow

1. Read AGENTS.md, identify the ORM and how to run the app or tests locally.
2. Reproduce with enough rows (at least 20-50 per list) so the pattern is visible.
3. Count queries for one request, before any change (tooling table below).
4. Find the access that triggers lazy loads: usually a serializer, template, or a loop calling a relation or a per-item repository method.
5. Eager load exactly the relations the response uses; for computed values (counts, sums) push the aggregate into SQL instead of loading children.
6. Count again. Target: a small constant (often 2-5 queries) regardless of page size.
7. Add a query-count test or turn on the ORM’s lazy-load guard in dev and test.

Measuring query count

Stack	How
Django	django-debug-toolbar in dev; in tests with self.assertNumQueries(3): or django_assert_num_queries (pytest-django)
Rails	Log sql.active_record notifications; the bullet gem flags N+1 and unused eager loads; strict_loading raises on lazy loads
SQLAlchemy	echo=True, or event.listen(engine, "before_cursor_execute", counter) in tests
Prisma	new PrismaClient({ log: ['query'] }) and count; or a query extension that increments a counter
Laravel	DB::listen(...); Model::preventLazyLoading(! app()->isProduction()) in a service provider
Hibernate / Spring	spring.jpa.properties.hibernate.generate_statistics=true; show_sql in dev
Go (GORM, sqlc, pgx)	Driver or GORM logger at info level; a pgx tracer that counts queries
Any Postgres app	pg_stat_statements calls per digest before and after a load test

Fix patterns by ORM

Django

# FK / one-to-one: JOIN in the same query
orders = Order.objects.select_related("customer", "customer__account")

# reverse FK / many-to-many: one extra query per relation
orders = Order.objects.prefetch_related("items__product")

# filtered or ordered prefetch
from django.db.models import Prefetch
orders = Order.objects.prefetch_related(
    Prefetch("items", queryset=OrderItem.objects.filter(active=True).select_related("product"))
)

# counts and sums: aggregate in SQL instead of len(order.items.all())
from django.db.models import Count, Sum
orders = Order.objects.annotate(item_count=Count("items"), total=Sum("items__price"))

Calling .filter(), .first() or .count() on a prefetched relation inside the loop issues a new query and bypasses the prefetch. Use the prefetched list (order.items.all()) and filter in Python, or use Prefetch(..., to_attr="active_items"). Deeper Django work: $django-orm-performance.

Rails

Order.includes(:customer, items: :product)        # preload or eager_load chosen automatically
Order.preload(:items)                             # always separate queries
Order.eager_load(:items).where(items: { active: true })  # single JOIN query

Enable strict_loading per model or config.active_record.strict_loading_by_default = true in development/test. Counter caches (belongs_to :order, counter_cache: true) fix order.items.size loops. Deeper Rails work: $active-record-performance.

SQLAlchemy 2.x

from sqlalchemy import select
from sqlalchemy.orm import selectinload, joinedload

stmt = (select(Order)
        .options(joinedload(Order.customer), selectinload(Order.items).selectinload(Item.product)))
orders = session.scalars(stmt).unique().all()   # unique() required with joinedload of collections

selectinload for collections, joinedload for many-to-one. Set lazy="raise" on relationships (or .options(raiseload("*"))) so accidental lazy loads fail loudly; with AsyncSession lazy loads already raise. More in $sqlalchemy-2.

Prisma

const orders = await prisma.order.findMany({
  where: { tenantId },
  include: { customer: true, items: { include: { product: true } } },
});

The N+1 in Prisma code is usually a for loop or Promise.all(ids.map(id => prisma.x.findUnique(...))). Replace with one findMany({ where: { id: { in: ids } } }) and a map. Prisma batches findUnique calls made in the same tick, but not findFirst or findMany. Use select to avoid loading wide relations. More in $prisma-orm.

Laravel, Hibernate, GORM

• Laravel: Order::with(['customer', 'items.product'])->get(); withCount('items').
• Hibernate/JPA: JOIN FETCH in JPQL or @EntityGraph(attributePaths = {"items"}); for collections on paginated queries prefer hibernate.default_batch_fetch_size (e.g. 50) or @BatchSize, because fetch-joining collections with pagination paginates in memory.
• GORM: db.Preload("Items.Product").Find(&orders); Joins("Customer") for belongs-to.

GraphQL

Resolvers run per parent object, so naive relation resolvers are N+1 by construction. Use a DataLoader per request:

const authorLoader = new DataLoader(async (ids: readonly string[]) => {
  const rows = await db.author.findMany({ where: { id: { in: [...ids] } } });
  const byId = new Map(rows.map(r => [r.id, r]));
  return ids.map(id => byId.get(id) ?? null);   // same order and length as ids
});

Create loaders per request (in the context factory) so caches do not leak between users. See $graphql-resolvers for the rest of resolver design.

Choosing JOIN vs separate query

Relation	Prefer
Many-to-one (order -> customer)	JOIN (select_related, joinedload, eager_load)
One-to-many / many-to-many	Separate IN query (prefetch_related, selectinload, preload)
Several collections at once	Separate queries; JOINing two collections multiplies rows (cartesian)
Need only a count or sum	Aggregate/annotate in SQL, load nothing
Huge IN lists (10k+ ids)	Paginate the parent query first; some drivers cap parameters (SQLite, SQL Server ~2100)

Gotchas

• Over-eager loading is the opposite bug: includes of five relations on an endpoint that renders one. Load per use case, not per model.
• Serializers and templates trigger lazy loads far from the query. Search the response path (rg "\.items\b" app/serializers) not just the controller.
• Caching around an N+1 hides it until the cache is cold.
• Fixes that load “all children” for a parent with 100k children trade N+1 for one enormous query; paginate or aggregate.
• Background jobs looping over records with per-record queries are N+1 too; batch them (find_each + includes, iterator(chunk_size=...) + prefetch in Django 4.1+).

Query-count test

def test_order_list_query_count(client, django_assert_max_num_queries, orders_factory):
    orders_factory.create_batch(30)
    with django_assert_max_num_queries(5):
        client.get("/api/orders?page_size=30")

Write the test with a page size large enough that a loop would blow the limit.

Verification

• Query count measured before and after for the same request and data
• Count stays constant when page size changes (try 10 and 50)
• Response payload unchanged (snapshot or diff)
• Lazy-load guard or query-count test added
• pg_stat_statements call counts for the repeated digest drop after deploy

Output template

## N+1 fix: <endpoint>
**Before:** <n> queries for <page size> rows; repeated query: `<shape>`
**Trigger:** <file:line where the relation was accessed>
**Fix:** <eager loading / aggregate / batch>, code diff summary
**After:** <n> queries for 10 rows, <n> for 50 rows
**Guard:** <test name or strict loading setting>