Re: Performance of complicated query

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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





[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux