Detecting N+1 in Rails ActiveRecord #

A Rails index action renders 50 orders with their customer and shipping method. The log shows 101 SELECT lines and the page takes 700 ms, of which 500 ms is spent in the database driver waiting for trivial queries. Adding includes(:customer, :shipping_method) fixes it — but which SQL that produces, and whether it fixes it at all, depends on what else the query does.

The database-side view of this pattern is in detecting N+1 with pg_stat_statements. This page is about ActiveRecord’s loading strategies and what each one sends.

The Condition #

ActiveRecord has three eager-loading directives, and they generate different SQL:

That last rule is the source of most surprises. A query that used includes and produced two clean statements starts producing a join — with duplicated parent columns for every child row — as soon as someone adds where(customers: { region: 'emea' }). Conversely, includes with a string condition referencing the table (rather than a hash) may not be detected, leaving the association unloaded and the N+1 in place.

Rails 6.1+ adds strict_loading, which raises when an unloaded association is accessed, and config.active_record.action_on_strict_loading_violation = :log for production. The mechanics are covered in strict loading guards against lazy queries.

Three directives, three SQL shapes A table of the directives. Preload issues one extra query per association using an IN list. Eager_load issues one query with a LEFT OUTER JOIN. Includes chooses between them depending on whether the query references the association in conditions or ordering. SQL shape conditions on the association preload separate IN query not allowed eager_load LEFT OUTER JOIN allowed includes whichever fits allowed, switches to join includes silently changes shape when a condition is added

Annotated Evidence #

The N+1, visible in the Rails log:

Order Load (2.1ms)     SELECT "orders".* FROM "orders" WHERE "orders"."account_id" = $1 LIMIT $2
Customer Load (0.3ms)  SELECT "customers".* FROM "customers" WHERE "customers"."id" = $1 LIMIT $2
Customer Load (0.3ms)  SELECT "customers".* FROM "customers" WHERE "customers"."id" = $1 LIMIT $2
… ×50
Completed 200 OK in 712ms (Views: 512.4ms | ActiveRecord: 186.2ms)

With preload:

Order Load (2.1ms)     SELECT "orders".* FROM "orders" WHERE "orders"."account_id" = $1 LIMIT $2
Customer Load (0.9ms)  SELECT "customers".* FROM "customers" WHERE "customers"."id" IN ($1, $2, …, $38)
Completed 200 OK in 96ms
-- plan of the batched query
Index Scan using customers_pkey on customers  (actual time=0.03..0.18 rows=38 loops=1)
  Index Cond: (id = ANY ('{…}'::bigint[]))
  Index Searches: 38

With eager_load, one joined statement:

SQL (4.8ms)  SELECT "orders"."id" AS t0_r0, … "customers"."id" AS t1_r0, …
             FROM "orders" LEFT OUTER JOIN "customers" ON "customers"."id" = "orders"."customer_id"
             WHERE "orders"."account_id" = $1

Hash Right Join  (actual time=0.9..4.1 rows=50 loops=1)
  Hash Cond: (customers.id = orders.customer_id)
-- one round trip, but every order row carries all customer columns
Recognising each strategy in the logs Three items are annotated. Repeated Customer Load lines with a single-row condition indicate an N+1. A single Customer Load with an IN list indicates preload. A single SQL line with aliased columns and a LEFT OUTER JOIN indicates eager_load. Customer Load … WHERE id = $1 ×50 lazy loading: one per row Customer Load … WHERE id IN ($1,…) preload: one batched query SELECT … t0_r0, t1_r0 … LEFT OUTER JOIN eager_load: one joined query the alias pattern t0_r0 is the giveaway for eager_load

Step-by-Step Resolution #

  1. Find the loop in the log — repeated Model Load lines with identical shapes during one request. In development, config.active_record.verbose_query_logs = true adds the calling line.

  2. Choose the directive deliberately:

    • no conditions on the association: preload
    • conditions or ordering on the association: eager_load
    • unsure, and the query may grow conditions later: includes, but check the generated SQL in tests.

  3. Guard the code path so regressions fail:

    orders = account.orders.strict_loading.preload(:customer, :shipping_method).limit(50)

  4. Prefer preload for collections. eager_load of a has_many multiplies parent rows by children and transfers the parent’s columns repeatedly; for 50 orders with 40 lines each that is 2,000 rows carrying every order column.

  5. Check the join direction in the plan for eager_load: PostgreSQL may run it as a Hash Right Join, which is correct — see hash right and full joins for outer joins.

  6. Assert the query count in a request spec with enough fixture rows that a loop would exceed it.

  7. Verify from the database that the per-row statement’s call rate dropped, using pg_stat_statements.

One index action, four approaches Lazy loading takes 712 milliseconds with 101 queries. Preload takes 96 milliseconds with two queries. Eager_load takes 104 milliseconds with one query but transfers more data. Eager_load of a has_many association takes 288 milliseconds because parent columns repeat for every child row. lazy (N+1) 712 ms, 101 queries preload 96 ms, 2 queries eager_load (belongs_to) 104 ms, 1 query eager_load (has_many) 288 ms, duplicated rows for collections, preload avoids the row multiplication

Before and After #

-- BEFORE: lazy association access in the view
Customer Load … WHERE id = $1   ×50        Completed 200 OK in 712ms

-- AFTER: preload(:customer, :shipping_method) + strict_loading
Customer Load … WHERE id IN ($1,…,$38)     Completed 200 OK in 96ms

Counter caches and other query removals #

Some N+1 patterns disappear entirely rather than being batched. A view that renders order.lines.count per order issues a COUNT query per order; a counter cache column maintained by Rails turns that into a column read with no query at all. Similarly, exists? checks per row can often be replaced by a single query that loads the set of ids that have children, or by a LEFT JOIN producing a boolean.

The trade-off is write-side work and the risk of drift: counter caches are updated by callbacks, so bulk operations that bypass callbacks — update_all, delete_all, direct SQL — leave them stale. Where accuracy matters, a periodic reconciliation job comparing the cache against a COUNT is worth the few minutes it takes to write.

For read-heavy endpoints that aggregate over children, neither batching nor counter caches beats computing the aggregate in the same query: orders.select('orders.*, (SELECT count(*) …) AS line_count') is a correlated subquery — the per-row cost described in correlated SubPlans in SELECT lists — while a LEFT JOIN … GROUP BY computes all counts in one pass.

Making the check part of the test suite #

Rails request specs can assert on query counts directly by subscribing to the sql.active_record notification and counting statements, ignoring schema and transaction noise. Wrapping the assertion in a helper — assert_queries(4) { get orders_path } — makes it cheap enough to add to every index action’s spec. The important detail is fixture size: with one order in the database, a per-object loop issues one extra query and the assertion still passes, so the fixture must contain enough rows that a loop is unmistakable.

Combined with strict_loading in the test environment, the two checks cover different failures: strict loading catches the access, the query count catches batching that went wrong — for example an includes that silently produced a join with far more rows than expected.

Common Pitfalls #

includes silently switching to a join. Adding a condition changes the SQL shape. Diagnostic signal: aliased t0_r0 columns appearing in logs. Fix: choose preload or eager_load explicitly.

String conditions with includes. Rails cannot always detect the reference and may not load the association. Diagnostic signal: a StatementInvalid or an unexpected N+1. Fix: hash conditions, or eager_load.

eager_load on large collections. Parent columns repeat per child row. Diagnostic signal: transferred bytes far above the logical result. Fix: preload.

Fixing only the view. The same association may be touched by a serializer too. Diagnostic signal: query count still above the assertion. Fix: strict_loading to surface every access.

Frequently Asked Questions #

What is the difference between includes, preload and eager_load? #

Preload always issues a separate batched query per association. Eager_load always uses a LEFT OUTER JOIN in one query. Includes picks between them depending on whether the query references the association in conditions or ordering.

How do I detect N+1 queries in Rails? #

Watch for repeated identical Model Load lines in the development log, enable strict_loading so unplanned association access raises, and confirm from the database with pg_stat_statements call counts.

Why did eager_load make my page slower? #

For has_many associations it multiplies parent rows by child rows and repeats every parent column in each one, so the result set can be far larger than with preload’s two separate queries.

Up: N+1 Query Detection