Mapping queries to code with annotations

Where does that query come from?

Does this scenario sound familiar? You’re investigating a performance problem in your Ruby on Rails application. Your search leads you to a particular SQL query that is run a bunch of times, and performs quite poorly. Your database logs tell you the query itself — and you mostly recognise what it does by the tables it reads… but there are a ton of places in your application where you query that particular table. What particular piece of code actually led to this particular query?

If you don’t recognise this scenario, that’s good news! But a lot of us do - so it should come as no surprise that Rails has some facilities to deal with this problem. The most important one is annotations: the ability to add comments to an SQL query, so you can cross-reference your code with your query logs.

Applying annotations

Annotating queries in Ruby on Rails is straight-forward using the ActiveRecord::QueryMethods#annotate method. You call it on a relation with your annotation as a string:

@articles = Article.published.limit(10).annotate('recent articles')

This stores your annotation on the relation. When the time comes to actually run the SQL query for this relation, Rails will append the annotation as comments to the query. The query the database will receive from our application will look like this:

select * from articles
where published_at is not null
limit 10 /* recent articles */

Stacking annotations

It’s interesting to see what happens when multiple annotations are added. Let’s assume we have a base scope in our Article model:

class Article < ApplicationRecord
  scope :published, -> { where.not(published_at: nil) }
end

From this scope, you could either load the ten most recently published articles, or load a single published article:

@recent_articles = Article.published.limit(10)
@article = Article.published.find(params[:id])

We could annotate these queries:

@recent_articles = Article.published.limit(10).annotate('recent articles')
@article = Article.published.annotate('single article').find(params[:id])

But, since they both use the same base relation Article.published, we could also add a shared annotation to that:

class Article < ApplicationRecord
  scope :published, -> { where.not(published_at: nil).annotate('published') }
end

This means that queries would now include both annotations:

select * from articles
where published_at is not null
limit 10 /* published */ /* recent articles */

select * from articles
where published_at is not null
and id = $1 /* published */ /* single article */

As you start combining many relations into more complex relations using common table expressions, subqueries, and merging, this is a great way of tracking all the different parts that were involved with a particular query.

Annotating using a block

One final interesting way to use annotations is applying them to a bunch of queries in a block. Using the same stacking approach as before, this allows you to ‘tag’ queries depending on their context.

Let’s say we have a method on our Article model that loads the articles with the most comments:

def index
  @top_articles = Article.with_most_comments
end

That’s useful to display on our blog home page. But we might also have a Rake task to email ourselves the list of articles with the most comments every day:

task report_comments: :environment do
  ReportMailer
    .article_comments(Article.with_most_comments)
    .deliver_later
end

It would be great to be able to distinguish between the two different use cases — even without knowing all the details of how the with_most_comments method is implemented. We can apply an annotation on a model using a block:

task report_comments: :environment do
  Article.annotate('report comments') do
    ReportMailer
      .article_comments(Article.with_most_comments)
      .deliver_later
  end
end

Now, all queries for the Article model will be tagged with the report comments comment.

Conclusion

Query annotations are useful to map queries in your database logs back to application code. With plain annotations, stacking, and blocks, Rails gives you the tools to make those connections. Now all you have to do is figure out how to actually make your queries faster!