Skip to main content

How to optimize ORM-generated queries

Quick answer: Eager-load related data instead of looping (Django select_related/prefetch_related, Rails includes, Prisma/Sequelize include), add indexes on your foreign-key columns (PostgreSQL does not create them for you), select only the columns you need instead of SELECT *, and paginate with keyset instead of deep OFFSET. Those four changes fix most ORM performance problems.

1. Eager-load instead of looping (kill the N+1)​

The number-one ORM cost is the N+1 query: load a list, then load a related record per row. Fetch the related data up front:

  • Django: select_related('author') (foreign key / one-to-one — a JOIN in one query); prefetch_related('tags') (many-to-many / reverse FK — a second query joined in Python).
  • Rails (ActiveRecord): Post.includes(:author).
  • Prisma: prisma.post.findMany({ include: { author: true } }).
  • Sequelize: Post.findAll({ include: [{ model: Author }] }).

One honest nuance: Prisma and Sequelize don't lazy-load a relation when you touch a property the way Django and Rails do. Their N+1 comes from querying inside a loop — a findUnique / findByPk per item. The fix is the same: pull the related rows in one query with include (or a single where … in).

See how to find N+1 queries for how to detect them.

2. Index your foreign-key columns​

A trap people learn the hard way: PostgreSQL does not automatically index foreign-key columns. It indexes primary keys and unique constraints, but the referencing column is left un-indexed. Every join or filter on it is a sequential scan until you add:

CREATE INDEX CONCURRENTLY ON comments (post_id);

(On a live table, always CONCURRENTLY — a plain CREATE INDEX blocks writes for the whole build. See adding an index without downtime.)

Your ORM generated the foreign key; it did not generate the index for the column that points at it.

3. Select only the columns you need​

SELECT * moves columns the app never reads and defeats index-only scans. Ask for what you use:

  • Django: .only('id', 'title') or .values('id', 'title').
  • Rails: .select(:id, :title).
  • Prisma: select: { id: true, title: true }.
  • Sequelize: attributes: ['id', 'title'].

4. Paginate with keyset, not deep OFFSET​

LIMIT n OFFSET m still reads and throws away all m skipped rows, so deep pages get progressively slower. Keyset (cursor) pagination seeks the index instead:

-- slow at depth:
SELECT * FROM posts ORDER BY id LIMIT 20 OFFSET 200000;

-- fast at any depth (unique cursor):
SELECT * FROM posts WHERE id > :last_seen_id ORDER BY id LIMIT 20;

If you page by a non-unique column like created_at, add a unique tiebreaker so rows sharing a timestamp aren't skipped or repeated — and index both sort columns:

SELECT * FROM posts
WHERE (created_at, id) < (:last_created_at, :last_id)
ORDER BY created_at DESC, id DESC
LIMIT 20;

CREATE INDEX CONCURRENTLY ON posts (created_at, id);

An index on created_at alone can't seek that cursor. The composite index can (scanned backward for DESC).

Why does AI-generated ORM code hit all four?​

Your AI coding agent (Claude Code, Cursor) writes the idiomatic ORM call — a loop that reads cleanly, a findMany, a .filter() — because that's what the training data is full of. It can't see that comments.post_id has no index, that the table has 50 million rows, or that the endpoint is paginated to page 8,000. Eager loading, FK indexes, column selection, and keyset pagination are all decisions that depend on the database and the access pattern — exactly what the model doesn't have in front of it.

How do I keep ORM queries fast?​

  • Default to eager loading for any relation you render in a list.
  • Add an index for every foreign key you filter or join on.
  • Select explicit columns on hot paths.
  • Use keyset pagination anywhere users can page deep.

How DBGorilla helps​

DBGorilla connects read-only and gives your AI coding agent (Claude Code, Cursor) the database context an ORM hides — which foreign keys lack indexes, the real row counts, and the pg_stat_statements load behind an endpoint — so it can turn a loop into an eager-load, flag the missing FK index, and explain the fix in your ORM's terms. It surfaces and explains through your agent; it does not touch production. Get started free →

FAQ​

What is the difference between select_related and prefetch_related in Django? select_related follows to-one relations (FK, one-to-one) with a JOIN in one query; prefetch_related handles to-many relations with a second query joined in Python.

Do Prisma and Sequelize have N+1 problems? Yes — but from querying inside a loop, not from lazy property access. They don't transparently load a relation on access; use include to fetch related rows up front.

Does PostgreSQL automatically index foreign key columns? No. It indexes primary keys and unique constraints, not the referencing FK column. Add that index yourself or joins on it stay slow.

Why is deep OFFSET pagination slow? OFFSET reads and discards every skipped row. Keyset pagination (WHERE id > :last ORDER BY id LIMIT n, or a composite (created_at, id) cursor with a matching composite index) seeks the index and stays fast at any depth.