Reading ActiveRecord EXPLAIN Output in Rails #

ActiveRecord is generous with abstraction and quiet about consequences. Four different method chains — includes, includes(...).references(...), joins, and eager_load — read almost identically in Ruby but emit three structurally different PostgreSQL plans. Reading the plan back to the chain that produced it is the core skill for tuning a Rails application, because the ORM will happily switch strategies underneath you without a warning. This page is part of the ORM-Generated EXPLAIN Output cluster and focuses specifically on the Rails dialect of that problem.

Getting the Plan Out of Rails #

Every ActiveRecord::Relation responds to .explain, which sends the composed SQL to PostgreSQL with EXPLAIN and prints the planner’s estimate:

Order.where(status: "pending").explain

That gives you cost estimates and estimated rows, but no runtime truth. On Rails 7 and later, .explain(:analyze) actually executes the query and returns EXPLAIN (ANALYZE) output — real timings and real row counts:

Order.where(status: "pending").includes(:customer).references(:customers).explain(:analyze)

For production, you rarely call .explain by hand. Instead set a threshold so any slow query auto-logs its plan:

# config/environments/production.rb
config.active_record.auto_explain_threshold_in_seconds = 0.5

Any query whose execution exceeds half a second logs its plan alongside the SQL, which is the fastest way to catch an N+1 or an accidental join explosion in the wild. Detection technique is covered in depth in Detecting N+1 Queries in the Django ORM; the plan-reading mechanics translate directly to Rails.

The Four Loading Verbs and Their Plans #

The single most important fact about ActiveRecord query plans is that the loading verb, not the models, decides the SQL shape. Here is the mapping, using an orders table with a belongs-to customers.

includes — preload strategy, two separate queries. By default includes runs the parent query, collects the foreign keys, and issues a second query using WHERE ... IN (...):

Order.where(status: "pending").includes(:customer).to_a
-- Query 1: the parents
Seq Scan on orders  (cost=0.00..1850.00 rows=4200 width=48)
  Filter: (status = 'pending')          -- ← parent rows fetched first

-- Query 2: the associated customers, one round trip, NOT N queries
Index Scan using customers_pkey on customers
  (cost=0.42..320.00 rows=4200 width=40)
  Index Cond: (id = ANY ('{17,42,88,...}'::int[]))   -- ← the IN (...) batch

Two queries total, regardless of how many orders come back. That is the whole point of includes: it converts N+1 into 1+1.

includes(...).references(...) — forced single LEFT OUTER JOIN. The moment you reference the association in a where or order, ActiveRecord cannot split the work across two queries, because the filter lives on the joined table. It switches to the eager-load strategy and emits one statement:

Order.includes(:customer)
     .references(:customers)
     .where(customers: { region: "EU" })
     .explain
Hash Left Join  (cost=210.50..2900.00 rows=3900 width=88)
  Hash Cond: (orders.customer_id = customers.id)   -- ← single JOIN, not two queries
  Filter: (customers.region = 'EU')                -- ← filter on the joined table
  ->  Seq Scan on orders  (cost=0.00..1850.00 rows=42000 width=48)
  ->  Hash  (cost=180.00..180.00 rows=2440 width=40)
        ->  Seq Scan on customers  (cost=0.00..180.00 rows=2440 width=40)

The tell is Left Join (here executed as a Hash Left Join) with a Hash Cond on the foreign key. Rails preserves LEFT semantics so parents with no customer are not silently dropped. Whether this materializes as a hash, merge, or nested-loop join is the planner’s choice — see Merge Join vs Nested Loop for how that decision is made.

joins — INNER JOIN, no eager load. joins writes an INNER JOIN for filtering only. It does not populate the association:

Order.joins(:customer).where(customers: { region: "EU" }).explain
Hash Join  (cost=210.50..2600.00 rows=3900 width=48)
  Hash Cond: (orders.customer_id = customers.id)   -- ← INNER join: unmatched orders dropped
  ->  Seq Scan on orders  (cost=0.00..1850.00 rows=42000 width=48)
  ->  Hash  (cost=180.00..180.00 rows=2440 width=8)
        ->  Seq Scan on customers  (cost=0.00..180.00 rows=2440 width=8)

Note the narrower width=48 — only order columns are selected, because customer rows are never instantiated. Access order.customer afterward and you get a fresh query per order: N+1 returns.

eager_load — always a LEFT OUTER JOIN. eager_load forces the single-JOIN strategy unconditionally, even without a references. It produces the same Left Join plan shape as the includes + references case, but you opted into it explicitly rather than triggering it by accident.

ActiveRecord loading verbs mapped to PostgreSQL plan shapes Four ActiveRecord method chains and the PostgreSQL plan each emits: includes yields two queries with an IN list, includes plus references and eager_load yield a single LEFT OUTER JOIN, and joins yields an INNER JOIN with no eager load. Rails method chain PostgreSQL plan includes(:customer) includes + references eager_load(:customer) joins(:customer) 2 queries: parents + WHERE IN (...) single LEFT OUTER JOIN single LEFT OUTER JOIN (forced) INNER JOIN, no eager load The verb, not the model, decides the SQL shape

Step-by-Step Resolution Workflow #

Step 1 — Capture the plan #

Order.where(status: "pending").includes(:customer).explain(:analyze)

On Rails 7+, .explain(:analyze) prints real actual time and actual rows per node. Record the query count Rails reports and the top-node cost estimate.

Step 2 — Identify the loading verb #

Read the chain that produced the SQL. includes alone, includes + references, joins, or eager_load — each is a different contract. Nothing else in the plan tells you which one you used; you must read the Ruby.

Step 3 — Map the plan back to the verb #

Use these three fingerprints:

Step 4 — Confirm the association is actually loaded #

If the plan is an INNER JOIN from joins but the view later renders order.customer.name, you have hidden N+1. Switch to includes. If the association is only used inside a where, keep joins and accept that unmatched parents are excluded.

Step 5 — Turn on strict_loading #

# config/environments/development.rb
config.active_record.action_on_strict_loading_violation = :raise

Or per-relation with Order.strict_loading.includes(:customer). Any lazy association access now raises instead of silently issuing an extra query, so leftover N+1 surfaces during development rather than in production plans.

Step 6 — Validate #

Re-run .explain(:analyze) and confirm the query count fell to the expected number, and that no parents were dropped by an accidental INNER JOIN.

Before/After Plan Comparison #

-- BEFORE: joins used for eager loading — INNER JOIN drops orders with no customer,
-- and order.customer access later fires a lazy query per row (N+1 returns)
Hash Join  (rows=3900 width=48)          -- inner join, customers NOT instantiated
  Hash Cond: (orders.customer_id = customers.id)

-- AFTER: includes + references — one LEFT OUTER JOIN, customers loaded, no N+1
Hash Left Join  (rows=42000 width=88)    -- left join, all orders kept + customers loaded
  Hash Cond: (orders.customer_id = customers.id)

The Left keyword and the wider width (customer columns now selected) are the two signals that eager loading is genuinely happening and no parents are being filtered out.

Common Pitfalls #

includes silently switching strategies. Adding a where(customers: {...}) or an order on the association flips includes from two queries to a single LEFT OUTER JOIN. A change that looks like a harmless filter can turn a lean 1+1 pattern into a wide join that multiplies rows across a to-many association. Always re-run .explain after touching a query that references an included table.

Using joins when you meant includes. joins filters but never loads. The association looks populated in a quick test with one record, then explodes into N+1 in production where each parent lazily fetches its child. Reserve joins for filtering and reach for includes/eager_load whenever the view reads the association.

joins INNER JOIN dropping parents. INNER JOIN excludes any order whose customer_id has no match. A report counting orders will silently under-count because childless parents vanish. If you need every parent, use left_joins (or eager_load), whose plan shows Left Join.

pluck versus select over-fetch. pluck(:total) bypasses model instantiation and returns raw values — cheap. select(:total) still builds ActiveRecord objects for every row, so a large result set pays full instantiation cost even though you only wanted one column. Check the plan’s width: a narrow projection with pluck avoids hydrating objects the request never uses.

Frequently Asked Questions #

Why does includes sometimes run two queries and sometimes one JOIN? ActiveRecord’s includes picks its strategy at runtime. By default it preloads with a second WHERE id IN (...) query. But if the relation references the association in a where or order — or you add references — everything must resolve in one statement, so it upgrades to eager_load and emits a single LEFT OUTER JOIN. The Ruby barely changes; the plan changes completely.

Does joins populate the association like includes does? No. joins writes an INNER JOIN purely to filter, and it does not load the joined rows into memory. Accessing the association afterward triggers a fresh lazy query per parent, reintroducing N+1, and the INNER JOIN also silently drops parents that have no matching child. Use joins to filter and includes or eager_load to load.

How do I see the actual PostgreSQL plan from a Rails console? Call .explain on any relation for the planner estimate, or .explain(:analyze) on Rails 7+ to run the query and print real timings and row counts. To capture slow queries in production automatically, set config.active_record.auto_explain_threshold_in_seconds so any query over the threshold logs its plan.

Related