Imagine a table with roughly a couple of billion rows. A simplified schema for the table would be

started_on: datetime
recorded_on: datetime
image_id: integer

It's indexed by started_on.

One quirk of the data is that there can be many records with identical started_on values but they may have different recorded_on and image_id values. Further, for a given started_on, we only want the most recently recorded record.

Currently we get this by

1) pulling all the records within the started_on range, and then

2) using code to grab out the most recently recorded_on record for each started_on.

This works fine, but seems like a waste because we're serializing and sending all those extra records to our app.

Is there an efficient way to accomplish the job of occluding the overridden records in our initial query?

  • Does your query want the result for a single (sign_id, started_on) pair or for many? Apr 10 '19 at 19:52
  • For many pairs. Thinking about this, I should probably leave out sign_id, which is a distraction. The main thing is to input a range for started_on and then get the last recorded image_id for each started_on in the range. Apr 10 '19 at 20:15

In Postgres, typically the most efficient way is to use distinct on ()

select distinct on (started_on) started_on, recorded_on, image_id
from the_table
order by started_on, recorded_on desc
where started_on between ...;
  • This does the trick and is efficient. Apr 11 '19 at 10:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.