>>This leads to the WHERE clause, WHERE read_datetime = max_read, and hence I'm only summing the last read for each device for each patient.
>>3. Can I modify my tables to make this query (which is the crux of my application) run faster?
Can you have a second "reads" table that stores only up to date values?
Is "reads" table insert-only? Do you have updates/deletes of the "historical" rows?
>>3. Can I modify my tables to make this query (which is the crux of my application) run faster?
Can you have a second "reads" table that stores only up to date values?
That will eliminate max-over completely, enable efficient usage in other queries, and make your queries much easier to understand by humans and computers.
PS. read_datetime = max_read is prone to "what if two measurements have same date" errors.
PPS. distinct MAX(max_read) OVER(PARTITION BY patient_id) AS latest_read looks like a complete mess. Why don't you just use group by?
Regards,
Vladimir
