Rails avoiding N+1 Queries

Question

A post has likers and comments children. I want to sort posts based on them.

class Post < ApplicationRecord
  scope :latest, -> {
    all.sort_by(&:ranking)
  }

  def ranking
    likers.count + comments.count
  end
end

This calls queries like below:

  Post Load (0.7ms)  SELECT "posts".* FROM "posts"
   (0.4ms)  SELECT COUNT(*) FROM "comments" WHERE "comments"."post_id" = $1  [["post_id", 52]]
   (0.4ms)  SELECT COUNT(*) FROM "users" INNER JOIN "user_post_likes" ON "users"."id" = "user_post_likes"."user_id" WHERE "user_post_likes"."post_id" = $1  [["post_id", 52]]
   (0.2ms)  SELECT COUNT(*) FROM "comments" WHERE "comments"."post_id" = $1  [["post_id", 53]]
   (0.3ms)  SELECT COUNT(*) FROM "users" INNER JOIN "user_post_likes" ON "users"."id" = "user_post_likes"."user_id" WHERE "user_post_likes"."post_id" = $1  [["post_id", 53]]

So I try the following instead:

Post.includes(:comments, :likers).all.sort_by(&:ranking)

This calls queries like below:

  Post Load (0.7ms)  SELECT "posts".* FROM "posts"
  Comment Load (0.4ms)  SELECT "comments".* FROM "comments" WHERE "comments"."post_id" IN (52, 53, 54, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 55, 56, 57, 58, 59, 60, 61, 62, 63, 64, 65, 66, 67, 68, 69, 70, 71)
  UserPostLike Load (0.3ms)  SELECT "user_post_likes".* FROM "user_post_likes" WHERE "user_post_likes"."post_id" IN (52, 53, 54, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 55, 56, 57, 58, 59, 60, 61, 62, 63, 64, 65, 66, 67, 68, 69, 70, 71)
  User Load (0.3ms)  SELECT "users".* FROM "users" WHERE "users"."id" = 46
   (0.3ms)  SELECT COUNT(*) FROM "comments" WHERE "comments"."post_id" = $1  [["post_id", 52]]
   (0.3ms)  SELECT COUNT(*) FROM "users" INNER JOIN "user_post_likes" ON "users"."id" = "user_post_likes"."user_id" WHERE "user_post_likes"."post_id" = $1  [["post_id", 52]]
   (0.2ms)  SELECT COUNT(*) FROM "comments" WHERE "comments"."post_id" = $1  [["post_id", 53]]
   (0.3ms)  SELECT COUNT(*) FROM "users" INNER JOIN "user_post_likes" ON "users"."id" = "user_post_likes"."user_id" WHERE "user_post_likes"."post_id" = $1  [["post_id", 53]]

Why is this happening, and how can I solve it?

UPDATE:

I figured out how to solve it, but an answer with a very nice explanation will be nice:

I had to replace count with size.

Initial:

class Post < ApplicationRecord
  scope :latest, -> { 
    all.sort_by(&:ranking)
  }

  def ranking
    likers.count + comments.count
  end
end

After:

class Post < ApplicationRecord
  ...

  def ranking
    likers.size + comments.size
  end
end

Then, N+1 Query is gone. I got the hint from the fact that when you use counter_cache, the same thing happens. In this case, I didn't use counter_cache but I still had to use size instead of count. I assume calling count forces Rails to call COUNT SQL query and calling size makes it use the loaded records in memory.

take a look at this http://stackoverflow.com/a/9209705/4758119 — Deepak Mahakale, Nov 18 '16 at 06:09

score 2 · Answer 1 · answered Nov 18 '16 at 06:12

2

You can use eager_load like that:

Post.eager_load(:comments, :likers).sort_by(&:ranking)

eager loading loads all association in a single query using LEFT OUTER JOIN.

Eager Loading Associations

3 ways to do eager loading (preloading) in Rails 3 & 4

answered Nov 18 '16 at 06:12

Roman Kiselenko

43,210
9
91
103

The issue here is not the eager loading, as you can see from the query his associations are loaded up. – fbelanger Nov 18 '16 at 06:15
Thanks for the answer but this does not solve the problem. – Maximus S Nov 18 '16 at 06:16
The eager_load perform only 1 query and load all data(assosation records) in the memory. – Roman Kiselenko Nov 18 '16 at 06:23
@MaximusS Nice catch, I forgot about the difference between count and size, size it for ruby enumerables. – Roman Kiselenko Nov 18 '16 at 06:27

score 0 · Answer 2 · edited May 23 '17 at 12:07

0

The issue here is two folds:

First, sort_by immediately raises a flag for me: http://apidock.com/ruby/Array/sort%21

It is an Array method, meaning you are no longer building an ActiveRecord query, you're doing array transformations.

Since you're including comments and likers the queries aren't as bad as they could be, but here is the other issue.

The way .count works is that's a prefabricated count query SELECT * FROM table.

To get the result you want, you will need to build your own count and sort query.

Take a look at this post and hopefully this will give you a better idea on how to optimize this further: Rails 3 ActiveRecord: Order by count on association

edited May 23 '17 at 12:07

Community

1
1

answered Nov 18 '16 at 06:17

fbelanger

3,522
1
17
32

You post an example for mysql, in Postgres you need to aggregate all attributes. – Roman Kiselenko Nov 18 '16 at 06:21
@Зелёный what do you mean aggregate all attributes? I was eluding to building a query to count and sort the ranking, nothing more. – fbelanger Nov 18 '16 at 06:39

Oleh Sobchuk · Answer 3 · 2016-11-18T07:36:11.953

in your case best way is to use counter_cache for likers and comments. More detail you can read SHORT ARTICLE. It's very easy and it'll safe time and memory.

If you use counter_cache, you shouldn't make several requests to your DB. and now your method will be:

def ranking
  likers_count + comments_count
end

on the other hand, if you don't want to add columns to table just use includes:

class Post < ApplicationRecord
  scope :latest, -> { 
    includes(:likers, :comments).sort_by(&:ranking)
  }

  def ranking 
    likers.count + comments.count
  end
end

but in this case you'll calculate likers and comments each time when method calls

Rails avoiding N+1 Queries

3 Answers3