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@xxxxxxxxxxxxxxxxxxxx> wrote:
On 05/23/2013 10:19 AM, Jonathan Morra wrote: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.
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.
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
