N + 1 problem

Introduction

• The N+1 query problem happens when your code executes N additional query statements to fetch the same data that could have been retrieved when executing the primary query.

Example

SELECT "cookies".* FROM "cookies"
SELECT "toppings".* FROM "toppings" WHERE "toppings"."cookie_id" = 1
SELECT "toppings".* FROM "toppings" WHERE "toppings"."cookie_id" = 2
SELECT "toppings".* FROM "toppings" WHERE "toppings"."cookie_id" = 3

• N = fetch the list of topping based on number of cookies

• 1 = fetch the list of cookies

Solution

SELECT "cookies".* FROM "cookies"
SELECT "toppings".* FROM "toppings" WHERE "toppings"."cookie_id" IN (1, 2, 3)

References

Understanding and fixing N+1 queryMedium