Understanding Rails Eager-Loading
Tags: ruby, rails, activerecord, sql
Published: 2019-06-17 04:13:41

---
title: Understanding Rails Eager-Loading
published: true
description: How `eager_load`, `preload`, `includes`, and `joins` differ in Active Record, and why you'd want each.
tags: ruby, rails, activerecord, sql
cover_image: https://thepracticaldev.s3.amazonaws.com/i/zap9l3bvu6cf6a51mfub.jpg
---

[Cheat sheet gist](https://gist.github.com/johncip/17c496f04dcd032d594a785e2ad09824)

_Note: this is an article that I posted a few years ago on Medium. I'm reposting here as it's likely to be more useful to someone that way._

If you're like me, nothing makes your eyes glaze over faster than a wet, steaming pile of machine-generated SQL. My aim was to write a Rails eager-loading guide that's heavy on motivation and light on log statements. Hopefully I can provide you with the explanation I never had.

This post assumes that you understand model associations, are comfortable with JOINs, and are familiar with [the N+1 query problem](https://secure.phabricator.com/book/phabcontrib/article/n_plus_one/). If any of that sounds foreign, I recommend [playing with](https://sqlzoo.net/) plain SQL first (SELECT, JOIN, & WHERE).

(That's the real trick to making this simple. While the way Active Record makes objects from records is intuitive enough, its query-generation abilities won't save you from having to understand how the queries execute in the db.)

Let's begin!



## Why do we need eager loading?

In other words: why are N+1 queries bad? It's because database trips are expensive. Queries have overhead, transactions have overhead, and talking to a remote database server is slow. Not something you want to do in a loop, but ORMs make it easy to shoot yourself in the foot here.

The good news is that if we tell Active Record about the associations we plan to use later, it can preload the associated records with a small number of queries, so that any looping can happen over records that are already in memory.



## `eager_load`: One query, with JOINs

Look at this example:

```ruby
users = User.eager_load(:address)
cities = users.map do |user|
  user.addresses.city
end
```

Using the magic of a SQL JOIN, Active Record can fetch the users and addresses with a single query. And because `eager_load` uses a [left join](https://www.zentut.com/sql-tutorial/sql-outer-join/), you'll get all the users, not just the ones with addresses.

I'll spare you the full SQL output, but know that the SELECT clause contains many entries like `"user.name" AS t0_r0`. Now you may have noticed that `t0_r0` is an emoji who cries at a right angle, but did you know that he and his numbered friends make **every column** from both tables available during the query? This means we can reference any attributes from an associated model in chained _where_ or _group_ calls, e.g.:

```ruby
users = User.eager_load(:address).where(address: { city: 'New Haven' })
streets = users.map do |user|
  user.address.street
end
```

So we've got everything in one query, and our where calls work. Are we done?

Maybe not: it turns out that those factory-farm `eager_load` queries aren't just hard on the eyes -- they can also be slower than the queries our next option produces (by an order of magnitude or more!). This is partly because joining is expensive, and partly because joins can introduce duplication that increases the number of objects to create.

And Codd help you if the joins aren't on indexed columns.



## `preload`: One query per table

This one is simple :  AR generates a query for each referenced table. The lack of joins means that this tends to be faster even when taking the extra query overhead into account.

But while our first example works fine with `preload` swapped out for `eager_load`, our second, which needs to use `addresses.city` in the generated WHERE clause, will fail. Since the users query only SELECTs from users, the WHERE clause for that query can't refer to columns from the address table. (There addresses query won't help here because it's done separately.)



## `includes`: `preload` by default, `eager_load` if necessary


This is the the most commonly-used method, and if you've read up to this point, it hopefully requires the least explanation. When using `includes`, by default AR loads each table separately, just like with `preload`. However, `includes` will switch to `eager_load`'s join-all-the-things behavior **if you mention the associated table in a chained `where` or `group` call**.

_Tip: if you're calling `where` or `group` with SQL strings rather than hashes, you can use `references` to trigger a join._


## `joins`: ¯\\\_(ツ)_/¯

Unsurprisingly, `joins` also generates a query which uses JOINs (inner joins, this time).

The main advantage here is that you get more control over the generated query. Remember that `eager_load` and `includes` will return **every column** from the joined tables. That's true even when `select` is called!

Often you'll reach for `joins` when the result set dictates it -- when you only want the records described by an INNER JOIN, or when a `where` relies on columns from associated tables, but you don't want to drag in the rest of those tables' columns.

So although `joins` tends to appear in articles about eager-loading, **it's mostly used to _restrict_ what gets loaded**. It can be used together with `select` for some surgical loads. Let's look at one:

```ruby
users = User.joins(:address).select('addresses.city AS city')
cities = users.map do |user|
  user.city
end
```

Here, `city` becomes a new method on the user instances, because we used an AS alias in the argument to select.

_Tip: this will lead to some objects having methods that others of the same class don't. This can potentially lead to confusing code -- you can mitigate that by also defining the extra method on the model class. That way, preloading is just an optional speedup, rather than required to make the calls work. Might be a good time to introduce a [query object](https://codeclimate.com/blog/7-ways-to-decompose-fat-activerecord-models/), though._

Note that with `joins`, the "select + alias → new method" trick is **the only way to use the eager-loaded values**. Calling `user.address.city` instead in that loop will ignore the in-memory values and fire as many new queries as there are users.


## Other optimizations

It can be confusing to pick apart which speedups come from which parts of Active Record. For the sake of completeness, let's look at a couple of unrelated tricks that AR uses to avoid querying.

First, AR **uses proxy objects to represent queries**, and only replaces them with result sets when you call a method like `all`, `first`, `count`, or `each`. This design is also what enables us to build queries via chained calls.

AR also **caches results for recent queries**. If you fire the same query multiple times, the database is only contacted once. (When the log says CACHE instead of LOAD, the results are being read from memory rather than the db.)

Unfortunately, neither of these helps with the N+1 problem, where loops in application code fire multiple _unique_ queries. To solve that we still need eager-loading.

## Final tips

* There are usually multiple ways to fetch the same data. Understanding each means that you can pick the fastest, or the most readable.
* When benchmarking, try to use a setup resembling your production environment.  A local database will have much less overhead per-query than a remote one.
* You should also index on columns you intend to JOIN on, but note that indexing isn't a cure-all. Indexes slow down INSERTs and UPDATEs, and can even slow down reads (if "storage order" suffices, e.g. fetching the entire table).
* When eager-loading still isn't enough, a useful pattern is to build a hash map connecting one model to another, and look up associated values "manually" using the map. This can be especially useful in a [Presenter / View Object](http://blog.jayfields.com/2007/03/rails-presenter-pattern.html).
* There are also libraries like [goldiloader](https://github.com/salsify/goldiloader) which attempt to automate eager-loading beyond what AR provides.
* When you only want certain columns, use [`pluck`](https://apidock.com/rails/ActiveRecord/Calculations/pluck).

## Suggested reading

* Rails Guides: [Active Record Query Interface: Eager Loading Associations](http://guides.rubyonrails.org/active_record_querying.html#eager-loading-associations)
* BigBinary: [Preload, Eagerload, Includes and Joins](http://blog.bigbinary.com/2013/07/01/preload-vs-eager-load-vs-joins-vs-includes.html)
* Wendi's Blog: [preload, eager_load, includes, references, and joins in Rails](http://blog.ifyouseewendy.com/blog/2015/11/11/preload-eager_load-includes-references-joins/)
* AkitaOnRails: [Rolling with Rails 2.1 - The First Full Tutorial - Part 2](http://www.akitaonrails.com/2008/5/26/rolling-with-rails-2-1-the-first-full-tutorial-part-2)
* APIDock: [`references`](http://apidock.com/rails/ActiveRecord/QueryMethods/references)