Re: Performance of complicated query

>>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.
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