INNER JOIN で eager loading

INNER JOIN じゃないと不味いが joins だと N + 1 問題が発生してしまう場合にどうするかを調べた。

結論から言うと joins + preload または joins + eager_load を使うと INNER JOIN を使って結合した上で関連オブジェクトも即時フェッチしてくれる。

試したのは Rails 4.1.6。

joins + preload

• INNER JOIN で結合
• クエリ複数回(指定した関連オブジェクトの分)
• 即時フェッチ

> Post.joins(:comments).preload(:comments)
Post Load (0.3ms)  SELECT "posts".* FROM "posts" INNER JOIN "comments" ON "comments"."post_id" = "posts"."id"
Comment Load (0.3ms)  SELECT "comments".* FROM "comments"  WHERE "comments"."post_id" IN (2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31)

joins + eager_load

• INNER JOIN で結合
• クエリ一発
• 即時フェッチ

> Post.joins(:comments).eager_load(:comments)
SQL (0.4ms)  SELECT "posts"."id" AS t0_r0, "posts"."title" AS t0_r1, "posts"."body" AS t0_r2, "posts"."lock_version" AS t0_r3, "posts"."created_at" AS t0_r4, "posts"."updated_at" AS t0_r5, "comments"."id" AS t1_r0, "comments"."post_id" AS t1_r1, "comments"."name" AS t1_r2, "comments"."body" AS t1_r3, "comments"."lock_version" AS t1_r4, "comments"."created_at" AS t1_r5, "comments"."updated_at" AS t1_r6 FROM "posts" INNER JOIN "comments" ON "comments"."post_id" = "posts"."id"

ちなみに他のメソッドだと以下のようになる。

joins

• INNER JOIN で結合
• 遅延フェッチ

> Post.joins(:comments)
Post Load (0.3ms)  SELECT "posts".* FROM "posts" INNER JOIN "comments" ON "comments"."post_id" = "posts"."id"

includes

• JOIN 使わない
• クエリ複数回(指定した関連オブジェクトの分)
• 即時フェッチ

> Post.includes(:comments)
Post Load (0.3ms)  SELECT "posts".* FROM "posts"
Comment Load (0.3ms)  SELECT "comments".* FROM "comments"  WHERE "comments"."post_id" IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31)

includes + references

• LEFT OUTER JOIN で結合
• クエリ一発
• 即時フェッチ

> Post.includes(:comments).references(:comments)
SQL (0.4ms)  SELECT "posts"."id" AS t0_r0, "posts"."title" AS t0_r1, "posts"."body" AS t0_r2, "posts"."lock_version" AS t0_r3, "posts"."created_at" AS t0_r4, "posts"."updated_at" AS t0_r5, "comments"."id" AS t1_r0, "comments"."post_id" AS t1_r1, "comments"."name" AS t1_r2, "comments"."body" AS t1_r3, "comments"."lock_version" AS t1_r4, "comments"."created_at" AS t1_r5, "comments"."updated_at" AS t1_r6 FROM "posts" LEFT OUTER JOIN "comments" ON "comments"."post_id" = "posts"."id"

eager_load

• includes + references と同じ

> Post.eager_load(:comments)
SQL (0.4ms)  SELECT "posts"."id" AS t0_r0, "posts"."title" AS t0_r1, "posts"."body" AS t0_r2, "posts"."lock_version" AS t0_r3, "posts"."created_at" AS t0_r4, "posts"."updated_at" AS t0_r5, "comments"."id" AS t1_r0, "comments"."post_id" AS t1_r1, "comments"."name" AS t1_r2, "comments"."body" AS t1_r3, "comments"."lock_version" AS t1_r4, "comments"."created_at" AS t1_r5, "comments"."updated_at" AS t1_r6 FROM "posts" LEFT OUTER JOIN "comments" ON "comments"."post_id" = "posts"."id"

preload

• includes と同じ

> Post.preload(:comments)
Post Load (0.3ms)  SELECT "posts".* FROM "posts"
Comment Load (0.5ms)  SELECT "comments".* FROM "comments"  WHERE "comments"."post_id" IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31)

参考

3 ways to do eager loading (preloading) in Rails 3 & 4 - Arkency Blog
http://blog.arkency.com/2013/12/rails4-preloading/

Preload, Eagerload, Includes and Joins · BigBinary Blog
http://blog.bigbinary.com/2013/07/01/preload-vs-eager-load-vs-joins-vs-includes.html

Rails - ActiveRecordのjoinsとpreloadとincludesとeager_loadの違い - Qiita
http://qiita.com/k0kubun/items/80c5a5494f53bb88dc58