Solving this n+1 query

Questions / Help
,
1

Hi everyone,

I’m reading Programming Phoenix > 1.4 and in the Persisting Annotations chapter there is the following code:

​- 	​def​ list_annotations(%Video{} = video) ​do​ 
​10:   Repo.all(
​- 	    from a ​in​ Ecto.assoc(video, ​:annotations​),
​- 	      ​order_by:​ [​asc:​ a.at, ​asc:​ a.id],
​- 	      ​limit:​ 500,
​- 	      ​preload:​ [​:user​]
​15:   )
​- 	​end​

Looks like this query will fetch the user for every annotation, regardless of whether the user was previously fetched.

What options do we have to solve this?

2

Hi @shawarma have you looked at the SQL run in debug logging? This does not do N+1. Rather, it grabs the annotations in 1 SQL query, and then it grabs all of the users in just 1 additional SQL query.

1 Like
3

Haha I did not :see_no_evil:, but nice ! I’m guessing Ecto has been made smart enough to optimize this? Does it just throw all the preloads into a unique set ?

4

Better, it does: select * from users where id = ANY?(list_of_uniq_ids_in_annotations) and then creates a Map.new of the user id to the user, which lets it then fan that into the annotations list.

4 Likes