🐥 A subtlety of Ecto.Query.preload

A subtlety of Ecto.Query.preload is that it makes an extra DB query. To avoid this you need to explicitly do the join yourself.

E.g. see below how the first query loads the teams with a separate SELECT but the second loads the users and teams all at once.

So, if you do this:

Repo.all(from a in Activity, preload: :webhook_logs)

The generated SQL will be:

SELECT
  a0."id",
  a0."strava_id",
  a0."garmin_id",
  a0."inserted_at",
  a0."updated_at"
FROM
  "activity_activities" AS a0;

SELECT
  w0."id",
  w0."object_type",
  w0."aspect_type",
  w0."updates",
  w0."owner_id",
  w0."subscription_id",
  w0."event_time",
  w0."object_id",
  w0."inserted_at",
  w0."updated_at",
  w0."object_id"
FROM
  "webhook_logs" AS w0
WHERE
  (w0."object_id" = ANY(1, 2, 3 ]))
ORDER BY
  w0."object_id";

To avoid the extra query, you can do this instead:

Repo.all(from a in Activity, left_join: l in assoc(a, :webhook_logs), preload: [webhook_logs: l])

The generated SQL will be:

SELECT
  a0."id",
  a0."strava_id",
  a0."garmin_id",
  a0."inserted_at",
  a0."updated_at",
  w1."id",
  w1."object_type",
  w1."aspect_type",
  w1."updates",
  w1."owner_id",
  w1."subscription_id",
  w1."event_time",
  w1."object_id",
  w1."inserted_at",
  w1."updated_at"
FROM
  "activity_activities" AS a0
  LEFT OUTER JOIN "webhook_logs" AS w1 ON w1."object_id" = a0."strava_id"

source