/ecto-queries-performance

name: ecto-queries-performance
description: "Use when a query is slow, N+1 or hard to compose — preload vs join-preload, dynamic/2, subquery/exists, Repo.aggregate, Repo.stream, FOR UPDATE, optimistic_lock, insert_all, Repo.explain, indexes, prepare_query. Not for schemas or scoped contexts (use ecto-essentials)."

Ecto Queries & Performance

> Verified against Elixir 1.20, Phoenix 1.8, Ecto 3.14, ecto_sql 3.14 (PostgreSQL 16) — October 2026

RULES — Follow these with no exceptions

1. **Preload in the context, once for the whole list.** Use `preload:` in the query or one `Repo.preload(list, ...)`. Never call `Repo.preload`, `Repo.get` or a context function per row, whether in `Enum.map`, a HEEx `:for` or a JSON view. 2. **Use separate-query preloads by default.** Join-preload only a `belongs_to`/`has_one` that you also filter or sort on. Joining a `has_many` multiplies rows, and `limit` then counts children instead of parents. 3. **A `limit` in a preload query is global, not per parent.** For "latest N per post", use a `row_number()` window restricted to the parents' ids. 4. **Count and check without loading rows.** Use `Repo.aggregate(q, :count)`, `Repo.exists?/1`, `group_by` + `count()`, or `select_merge` into a virtual field. Never `length(Repo.all(q))`. 5. **Compose with named bindings and `dynamic/2`.** Put `as:` on joins and use `with_named_binding/3` so a join is added only once. For "has any child", use `exists/1` with `parent_as/1` instead of a join plus `distinct`. 6. **Run `Repo.stream` only inside `Repo.transact`.** The function must return `{:ok, _}`, the transaction needs `timeout: :infinity`, and streams don't support preloads. For long jobs, batch by keyset (`where: p.id > ^last_id`), never `OFFSET`. 7. **Never read-modify-write a counter or balance.** Use `update_all(inc: ...)`, a conditional `update_all`, `lock: "FOR UPDATE"` inside `Repo.transact`, or `optimistic_lock/3` with `stale_error_field:`. 8. **`insert_all` and `update_all` bypass changesets.** Nothing is validated and no timestamps are generated. Set `inserted_at`/`updated_at` yourself, and chunk so rows × fields stays under Postgres's 65,535 parameters. 9. **Measure, then index the query you actually run.** Use `Repo.explain(:all, q, analyze: true)` and the `[:my_app, :repo, :query]` telemetry. Put equality columns first in a composite index. A partial or expression index is only used when the query repeats its predicate or expression. 10. **Tenant isolation comes from the scope, passed explicitly.** `prepare_query/3` is only a guard that raises when the tenant option is missing. Never keep the tenant in the process dictionary.

Examples extend the Phoenix 1.8 generated `Blog.Post` with `belongs_to :user` (replacing `field :user_id, :id`) and `has_many :comments`.

---

Spotting N+1

The usual cause is a query hidden inside a loop: a context call in a template, a `Repo.preload` per element, or a JSON view that loads data for each item.

<!-- epg:bad -->

<%!-- One COUNT query per post, on every render --%>
<div :for={{id, post} <- @streams.posts} id={id}>
  {post.title} ({Blog.count_comments(@current_scope, post)} comments)
</div>

<!-- epg:bad -->

# One comments query per post
posts |> Enum.map(&Repo.preload(&1, :comments))

In dev, the repeats are easy to see in the log. The generated `config/dev.exs` sets `stacktrace: true` on the repo, so every query log line ends with `↳ Module.fun/arity, at: file:line`. The same caller and the same SQL repeating with a different `$1` is an N+1. To lock the fix in, assert a query count in a test (see the [query-count helper](reference.md#counting-queries-in-tests)).

def list_posts_with_comment_counts(%Scope{} = scope) do
  counts = from c in Comment, group_by: c.post_id, select: %{post_id: c.post_id, n: count(c.id)}

  from(p in Post,
    where: p.user_id == ^scope.user.id,
    left_join: c in subquery(counts),
    on: c.post_id == p.id,
    # comment_count is `field :comment_count, :integer, virtual: true` on Post
    select_merge: %{comment_count: coalesce(c.n, 0)},
    order_by: [desc: p.inserted_at, desc: p.id]
  )
  |> Repo.all()
end

The template then renders `{post.comment_count}`. If a template or JSON view needs data, the context function that feeds it has to load it.

Preloading: separate queries vs one join

# Separate queries (default): 1 for posts, then 1 per association for the whole
# list. Independent preloads run in parallel outside a transaction.
Post
|> where(user_id: ^scope.user.id)
|> preload([:user, comments: :user])
|> Repo.all()

# Already have the list? One query per association, not per post:
Repo.preload(posts, comments: :user)

# Join-preload: one query. Right for belongs_to/has_one you filter or sort on.
from(p in Post,
  as: :post,
  join: u in assoc(p, :user),
  as: :author,
  where: p.status == :published,
  order_by: [asc: u.email, desc: p.published_at],
  preload: [user: u]
)
|> Repo.all()

| Use | When | |---|---| | `preload: [:comments]` (separate query) | `has_many`/`many_to_many`, paginated parents, nested preloads | | `join` + `preload: [user: u]` | `belongs_to`/`has_one` you also `where`/`order_by` on, so the join is needed anyway | | `preload: [comments: ^query]` | Filter or order the children (`where: is_nil(c.deleted_at)`, `order_by: c.inserted_at`) | | `preload_order: [asc: :inserted_at]` on `has_many` | A default child order, used when the preload query has no `order_by` of its own |

**Join-preloading a `has_many` with `limit`:** `from p in Post, join: c in assoc(p, :comments), preload: [comments: c], limit: 3` returns **one** post with three comments, because the limit applies to the joined rows. Paginate the parents, then preload the children with separate queries.

`Repo.preload/3` skips associations that are already loaded. Pass `force: true` to reload them after a write.

Top N children per parent

A preload query's `limit` applies to all paren