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