# 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 do not 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](https://www.dbgorilla.com/learn/postgres/how-to-find-n-plus-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:

```sql
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](https://www.dbgorilla.com/learn/postgres/how-to-add-a-postgres-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:

```sql
-- 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:

```sql
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?

Your AI coding agent (Claude Code, Cursor) writes the idiomatic ORM call, a
loop that reads cleanly, a `findMany`, a `.filter()`, because that is what the
training data is full of.  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 are all
decisions that depend on the database and the access pattern, exactly what the
model does not 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 →](https://app.dbgorilla.com/signup)

## 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 do not
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.

## Related

- [How to find N+1 queries in PostgreSQL](https://www.dbgorilla.com/learn/postgres/how-to-find-n-plus-1-queries/)
- [Why AI-generated SQL is slow](https://www.dbgorilla.com/learn/postgres/why-ai-generated-sql-is-slow/)
- [How to identify slow queries in PostgreSQL](https://www.dbgorilla.com/learn/postgres/how-to-identify-slow-queries-in-postgresql/)
- Product: [DBGorilla docs](https://www.dbgorilla.com/docs/)