Significant latency in Ash.load

[nallwhy]

Describe the bug

Ash.load exhibits significant performance issues when loading large resources -> large has_many associations.

For example, there 5000 Bills and 10000 Pays.

defmodule Bill do
  use Ash.Resource, ...

  relationships do
     has_many :pays, Pay
  end

  actions do
    defaults [:read]
  end
end

defmodule Pay do
  use Ash.Resource, ...

  relationships do
    belongs_to :bill, Bill
  end

  actions do
    default [:read]

    read :list_by_bill_ids do
      argument :bill_ids, {:array, :integer}

      filter expr(bill_id in ^arg(:bill_ids))
    end
  end
end

# too slow (>= 10s), but db query is not slow. (~= 200ms)

Bill.read!() |> Ash.load([:pays])

# much faster (~= 200ms)

bills = Bill.read!()
bill_ids = bills |> Enum.map(& &1.id)
bill_id_pays_map = Pay.list_by_bill_ids(%{bill_ids: bill_ids}) |> Enum.group_by(& &1.bill_id)

bills
|> Enum.map(fn bill -> %Bill{bill | pays: bill_id_pays_map |> Map.get(bill.id, [])} end)

To Reproduce

I haven’t yet replicated the issue, but I plan to set up a minimal test environment within a few days. We use PostgreSQL and multitenancy, which may affect the behavior.

Expected behavior

Ash.load is not too slow.

Runtime

• Elixir version: 1.17.2
• Erlang version: 27.0.1
• OS: mac
• Ash version: 3.4.36
• any related extension versions
  • AshPostgres: 2.4.11
  • AshSql: 0.2.38

[zachdaniel]

Let me know once you have a reproduction. Something to try to see if it impacts anything is setting this:

# config/test.exs
config :ash, :disable_async?, true

[zachdaniel]

Did some benchmarking of reading 5k records each with 10 related records, and we perform only slightly worse than ecto (would still be worth optimizing of course):

Do you perhaps have any calculations or preparations on the relevant read actions that could be coming into play?