Re: Performance of complicated query

On 05/23/2013 10:57 AM, Jonathan Morra wrote:
> Ultimately I'm going to deploy this to Heroku on a Linux machine (my 
> tests have so far indicated that Heroku is MUCH slower than my 
> machine), but I wanted to get it fast on my local machine first.  I 
> agree with your role partitioning, however, this is only a dev machine.
>
> For the sum vs. last, the idea is that each patient is issued a device 
> and reads are recorded.  The nature of the reads are that they are 
> incremental, so if a patient never changes devices there is no need 
> for a sum.  However, patients will be changing devices, and the 
> patient_device table records when each patient had a given device. 
>  What I want to sum up is the total value for a patient regardless of 
> how many times they changed devices

If the reads are always incremented - that is the read you want is 
always the largest read - then something along these lines might work 
well and be more readable (untested code);

-- distill out max value for each device
with device_maxreads as (
select
     device_id,
     max(value) as max_read
from
     reads
group by
     device_id)

-- then sum into a totals for each patient
patient_value as (
select
     p.patient_id,
     sum(max_read) patient_value
from
      device_maxreads d
      join patient_devices p on p.device_id = d.device_id
group by
     p.patient_id
)

select
     ...whatever...
from
     ...your tables.
     join patient_value p on p.patient_id = ...
;


If the values increment and decrement or patients are issued devices at 
overlapping times (i.e. using two devices at one time) then the query 
gets more complicated but "with..." is still a likely usable construct.

Cheers,
Steve