Message-ID: <519E75AC.80103@pinpointresearch.com>
Date: Thu, 23 May 2013 13:01:48 -0700
From: Steve Crawford <scrawford@pinpointresearch.com>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
	rv:17.0) Gecko/20130510 Thunderbird/17.0.6
MIME-Version: 1.0
To: Jonathan Morra <jonmorra@gmail.com>
CC: pgsql-performance@postgresql.org
Subject: Re: Performance of complicated query
References: 
 <CAF8LAAUN7mJ3d-x2qAFvq07BUtQydBkDmRLk_mX0OCa0sFaJZw@mail.gmail.com>
	<519E563A.4010401@pinpointresearch.com>
	<CAF8LAAXxB=RKcrgd0ggiPhvZEaTjKj3Wk6dzCK6_e8w_Q2cvJg@mail.gmail.com>
In-Reply-To: 
 <CAF8LAAXxB=RKcrgd0ggiPhvZEaTjKj3Wk6dzCK6_e8w_Q2cvJg@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

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


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance