Expensive preload

I have an expensive query, and looking at the heroku postgres metrics, it seems that one of the preloads is taking around 0.5s to complete.

Offending query:

    from(tc in Timecards.Timecard,
      where: tc.timecard_setting_id == ^setting.id and tc.week_ending_date == ^we_date,
      preload: [
        :approvals,
        :offer,
        timecard_data: [:locations, :unit]
      ]
    )

There can be around 20’000 timecards returned from the query.

Timecards have a one to many relationship with TimecardData (0/1/2/3 TimecardData per Timecard)
Timecards have a one to many relationship with Approvals (0/1/2/3 Approvals per Timecard)

Struct sizes:

The approval struct is quite small (~10 columns)
The TimecardData struct is bigger (~ 20 columns)
The offer struct is horrifically large (many many columns) which is possibly affecting things? It’s on the todo list to remove it as a preload completely.

Metrics:

It seems like the preload: :approvals is a really expensive query, taking almost half a second:

It’s confusing that preloading approvals is so much slower than the preload: :timecard_data:

I recently asked a question here about using joins before preloads, and generally got the response that I shouldn’t join in this case as it is a one to many relationship: Advantages of Ecto preloads with/without joins

Any help or suggestions would be great!

Do you have indexes on foreign keys? For example index on approvals.timecard_id

1 Like

I do not. I will add.
Is that likely the difference between the approvals and the timecard_data?

My indexes are almost non-existent. I’ll go through and add where appropriate. Thanks a lot @hubertlepicki!

It is likely the cause, yes.

And please do keep us posted if this helped (and if so how much :))

That seems to have done the trick. Thanks!

2 Likes