find_by_sql query running incredibly slow

Initially, I was using this query:

report_ids = []

if units.size > 0 do |id|

report_id = id).order(“time desc”).first

unless report_id.nil?

report_ids << report_id




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",])

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?

No, but have you got indexes on unit_id and time?


Run the query through explain (depending on your rails version, that might get logged for you automatically) and see what your database is doing.