Initially, I was using this query:
report_ids =
if units.size > 0
units.map(&:id).uniq.each do |id|
report_id = Report.select(:id).where(unit_id: id).order(“time desc”).first
unless report_id.nil?
report_ids << report_id
end
end
end
reports = Report.where(id: report_ids).order(“longitude desc”)
It looks sloppy, and it took around 7 or 8 seconds to load all the data.
So I wanted to create a cleaner, quicker query, so I tried this:
reports = Report.find_by_sql(["SELECT DISTINCT ON (unit_id) r.*
FROM reports r
WHERE r.unit_id IN (?)
ORDER BY unit_id, time DESC",
units.map(&:id)])
But this is running incredibly slow. In fact, after 15 minutes, I just cancelled the process.
Any idea why the second query is taking markedly longer than the first?