Re: Performance of complicated query - Mailing list pgsql-performance

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