Re: Performance of complicated query

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. In
order to do this I have to sum up just the values of the last read for each
device a patient was assigned to. This leads to the WHERE clause, WHERE
read_datetime = max_read, and hence I'm only summing the last read for each
device for each patient. Ultimately I want to report the values listed in
the outer select for each patient. I will use these values to run other
queries, but those queries are currently very quick (< 50ms) and so I'm not
worried about them now.

On Thu, May 23, 2013 at 10:47 AM, Steve Crawford <
scrawford(at)pinpointresearch(dot)com> wrote:

> On 05/23/2013 10:19 AM, Jonathan Morra wrote:
>
>> I am fairly new to squeezing performance out of Postgres, but I hope this
>> mailing list can help me. I have read the instructions found at
>> http://wiki.postgresql.org/**wiki/Slow_Query_Questions<http://wiki.postgresql.org/wiki/Slow_Query_Questions>and have tried to abide by them the best that I can. I am running
>> "PostgreSQL 9.1.7, compiled by Visual C++ build 1500, 64-bit" on an x64
>> Windows 7 Professional Service Pack 1 machine with 8 GB of RAM.
>>
>
> I'm not sure under what constraints you are operating but you will find
> most people on the list will recommend running live systems on Linux/Unix
> for a variety of reasons.
>
> CREATE TABLE reads
>> ...
>>
>> ALTER TABLE reads
>> OWNER TO postgres;
>>
>
> To avoid future grief you should set up a user (see CREATE ROLE...) for
> your database that is not the cluster superuser (postgres). I assume you
> set up a database (see CREATE DATABASE...) for your app. The base databases
> (postgres, template*) should be used for administrative purposes only.
>
>
>> ...
>>
>> Ultimately what I want to do is to find a sum of values for each patient.
>> The scenario is that each patient is assigned a device and they get
>> incremental values on their device. Since these values are incremental if
>> a patient never switches devices, the reported value should be the last
>> value for a patient. However, if a patient switches devices then the
>> reported value should be the sum of the last value for each device that the
>> patient was assigned.
>>
>
> I'm afraid I'm a bit confused about what you are after due to switching
> between "sum" and "last".
>
> It sounds like a patient is issued a device which takes a number of
> readings. Do you want the sum of those readings for a given patient across
> all devices they have been issued, the sum of readings for a specific
> device, the most recent reading for a specific patient regardless of which
> device was in use for that reading, or the sum of the most recent readings
> on each device issued to a specific patient?
>
> Are you looking to generate a report across all patients/devices or lookup
> information on a specific patient or device?
>
> Cheers,
> Steve
>
>
>
>