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!