find_by_sql query running incredibly slow

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?

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

Colin

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

Fred