Understanding the Problem

Anytime we need to do something like load an Ecto schema has_many association in a resolver, then query child values, we can quickly find ourselves mired in what’s referred to as the “N + 1 problem” and performing more database work than we expect. Let’s say you want to get the category for a bunch of menu items. The best way to go is to collate all the category_id values found within your set of menu items and then do a single SQL query for categories with those IDs. The N+1 problem happens when instead, you do an SQL query for each individual menu item’s category; this is where the problem gets its name. There’s 1 query to get the menu items themselves, and then N queries afterward, where N is the number of menu items.

To illustrate this, let’s review how a document is executed with a naive example and see what happens. We’ll add a small piece of middleware to our schema that will print out each field that executes. To do so, we need to reorganize how we apply middleware a little so that we can more easily compose different options. Start with the def middleware refactor:

Functionally, this is exactly the same, but it gives us a little more flexibility. Previously, we were pattern matching for objects or fields within the middleware/3 function heads, which meant each situation was handled totally separately. If we wanted to apply common middleware to all fields, we’d need to add it to every clause of the middleware/3 block.

With this refactor in place, we can trivially add a debug middleware to every field. In order to make it easy to turn on and off, we’ll only run it if we do DEBUG=true when starting our IEx session. We want the debugger to print at the start of every field’s resolution, and also at the end. One option for doing this is to put the middleware at both the beginning and the end:

Instead, though, we’re going to just put it at the beginning with a :start option, and then add the :finish part inside the middleware itself, which is a trick that will be useful to know later on:

Let’s take a look at the Middleware.Debug module:

Here we’ve got two call function clauses where we’re pattern matching on our option argument, :start or :finish. The first corresponds to the option we specified in our schema middleware callback. In this clause, we get the current path in the document and then print out a message providing info about the field we’ve started resolving.

Now the cool part: we can actually change what future middleware will run on this specific field. One of the keys under the %Absinthe.Resolution{} struct is :middleware, and it contains a list of all remaining middleware queued up to run on this field. By adding the Debug module at the end of the list with the :finish option, we ensure that it will be called again after everything else. While we’re at it, we also pass along the path variable we’ve calculated, just so that we don’t need to compute it again.

When Debug is called the second time with the {:finish, path} option, we can print some info about the value that’s been resolved. Let’s give this a try! We’ll open up an IEx session (don’t forget DEBUG=true) and run a query:

There’s a decent bit of output here, but most of that is just from inspecting the menu item itself (which we have omitted from the book for space reasons). As you might expect, our tracing middleware indicates that Absinthe starts at the :menu_items field, then moves to the :name and :id fields. At the :menu_items field, the source value is just an empty map, because there isn’t any data yet at the top level. That field returns a list with a single menu item. When Absinthe moves to the :name and :id fields, it does so using that specific menu item struct as the source value.

You May Need to Use env
	If you’re using a non-standard shell like the friendly interactive shell (fish)[29] and didn’t see the expected output, remember that you may need to prefix commands that use ad hoc environment variables with env—for example, env DEBUG=true iex -S mix.

If we do a slightly broader search and just match for "on", we get two results: “Croque Monsieur” and “Lemonade.”

The menu items field once again starts with an empty map as the source, but now it returns a list of two different menu items. When we look at the next lines of tracing output, we see that Absinthe goes through each in turn and executes the :name and :id fields our query document selected. It gets the name and ID field on the “Croque Monsieur” item, and then the name and ID field from the “Lemonade” item. All of this happens in a single process, one item and one field after another. If we return N items from the menu_items field, the name and ID field resolvers will each run N times.

This is both the simplest and most optimal way to proceed when all those fields do is a Map.get on their source, but this approach won’t serve if we need to execute fields in parallel, or if we want to work with a group of fields all together. A good example of the latter situation is if we’re doing a database call. Every menu item belongs to a category, and if we want to let people query the category on a menu item, we really want to do a single database query to get all of those categories, instead of N.

Let’s start by creating the problem we want to fix: a naive implementation where we directly get the category for each menu item. We’ll start by making sure we have the correct fields and objects set up in our menu_types.ex file.

We already have a :category object from all the way back in Chapter 2, ​Building a Schema​, so all we need to do is add in the field on our :menu_item object connecting it to a category, then give it a resolver.

In the resolver, we just use the Ecto.assoc/2 function to build an Ecto query, which is then run by our Repo. If we run some queries and pay attention to the [debug] database logging, we’ll see that for each menu item in the result, we’re having to do a lookup in the categories table:

If we look at our tracing, it isn’t hard to see why: we’re fully resolving the category field on each menu item before moving on. Given how we’ve built our resolver, this isn’t surprising. The solutions we’re going to look at next make use of Absinthe plugins to alter how each of these fields are executed, providing us opportunities to load this data more efficiently.