Message-ID: <519E563A.4010401@pinpointresearch.com>
Date: Thu, 23 May 2013 10:47:38 -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>
In-Reply-To: 
 <CAF8LAAUN7mJ3d-x2qAFvq07BUtQydBkDmRLk_mX0OCa0sFaJZw@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: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


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