From: Steve Crawford <scrawford@pinpointresearch.com>
To: Jonathan Morra <jonmorra@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Performance of complicated query
Date: Thu, 23 May 2013 13:01:48 -0700
Message-ID: <519E75AC.80103@pinpointresearch.com> (raw)
In-Reply-To: <CAF8LAAXxB=RKcrgd0ggiPhvZEaTjKj3Wk6dzCK6_e8w_Q2cvJg@mail.gmail.com>
References: <CAF8LAAUN7mJ3d-x2qAFvq07BUtQydBkDmRLk_mX0OCa0sFaJZw@mail.gmail.com>
<519E563A.4010401@pinpointresearch.com>
<CAF8LAAXxB=RKcrgd0ggiPhvZEaTjKj3Wk6dzCK6_e8w_Q2cvJg@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-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
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: scrawford@pinpointresearch.com, jonmorra@gmail.com
Subject: Re: Performance of complicated query
In-Reply-To: <519E75AC.80103@pinpointresearch.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox