You can actually execute the query and analyze the logs for the generated query. Generally speaking there is no performance difference, the preload one is using associations as the base abstraction and of course abstracts and limits the way you do the join.
I rarely use preloads in a query, as I think that this construct is foreign to the query. You also lose the ability to do nice things like this:
Order
|> join(:left, [o], u in assoc(o, :user), as: :user)
|> join(:left, [user: u], c in assoc(u, :customer), as: :customer)
|> select([o, user: u, customer: c], %{id: o.id, user: u.id, customer: c.id})
This feature is absolutely important when you have big queries, as passing a list and remember the ordering is not safe or readable.
As for preloads in general, I think preloads and associations are a good abstraction, I use Repo.preload/3 for fetching associations and custom queries with joins when I either need to optimize those joins or filter them.
