How to optimize ORM-generated queries
Quick answer: Eager-load related data instead of looping (Django
select_related/prefetch_related, Railsincludes, Prisma/Sequelizeinclude), add indexes on your foreign-key columns (PostgreSQL does not create them for you), select only the columns you need instead ofSELECT *, and paginate with keyset instead of deepOFFSET. Those four changes fix most ORM performance problems.
1. Eager-load instead of looping (kill the N+1)
Section titled “1. Eager-load instead of looping (kill the N+1)”The most common ORM cost is the N+1 query: load a list, then load a related record per row. Fetch the related data before iterating:
- 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 }] }).
Prisma and Sequelize do not lazy-load a relation when you
touch a property as Django and Rails do. Their N+1 comes from querying
inside a loop, with a findUnique / findByPk per item. Query the related rows
in one request 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
Section titled “2. Index your foreign-key columns”PostgreSQL does not automatically index foreign-key columns. It indexes primary keys and unique constraints, but the referencing column is left un-indexed. A join or filter on it may use a sequential scan until you add:
CREATE INDEX CONCURRENTLY ON comments (post_id);(On a live table, always use CONCURRENTLY: a plain CREATE INDEX blocks writes
for the whole build. See
adding an index without downtime.)
Your ORM generated the foreign key. Check whether it also generated the index.
Django’s ForeignKey and Rails’ add_reference add one by default; not every
ORM does.
3. Select only the columns you need
Section titled “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
Section titled “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 are not skipped or repeated, and index both sort
columns:
SELECT * FROM postsWHERE (created_at, id) < (:last_created_at, :last_id)ORDER BY created_at DESC, id DESCLIMIT 20;
CREATE INDEX CONCURRENTLY ON posts (created_at, id);An index on created_at alone cannot seek that cursor. The composite index can (scanned backward for DESC).
Why does AI-generated ORM code hit all four?
Section titled “Why does AI-generated ORM code hit all four?”Your AI coding agent writes the idiomatic ORM call (a loop that reads cleanly, a
findMany, a .filter()) because those calls are common in its training data.
It cannot 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 all depend
on the database and the access pattern, and the model lacks that context.
How do I keep ORM queries fast?
Section titled “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.
What is the difference between select_related and prefetch_related in Django?
select_related follows foreign-key and one-to-one relations with a SQL JOIN in a single query. prefetch_related handles many-to-many and reverse foreign keys by running a second query and joining the results in Python. Use select_related for to-one relations and prefetch_related for to-many.
Do Prisma and Sequelize have N+1 problems?
Yes, but not from lazy-loading a property. Prisma and Sequelize don't transparently load a relation when you access it. Their N+1 comes from querying inside a loop, calling findUnique or findByPk per item. The fix is the same: fetch related rows up front with include.
Does PostgreSQL automatically index foreign key columns?
No. PostgreSQL indexes primary keys and unique constraints automatically, but not the referencing foreign-key columns. Joins and lookups on a foreign key stay slow until you add an index on that column yourself.
Why is deep OFFSET pagination slow?
OFFSET still reads and discards every row it skips, so deep pages get slower. Keyset pagination jumps straight to the next page and stays fast at any depth: WHERE id > :last_seen ORDER BY id LIMIT n, or a composite (created_at, id) cursor with a matching composite index.