Re: Performance of complicated query

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 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