Skip to content

How to optimize ORM-generated queries

Last updated

View as Markdown

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)

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.

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.

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'].

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 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 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.

  • 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.