On 05/23/2013 05:21 PM, Jonathan Morra wrote:
Sorry for the messy query, I'm very new to writing these complex
queries. I'll try and make it easier to read by using WITH clauses.
However, just to clarify, the WITH clauses only increase readability
and not performance in any way, right?
It d
I have been working on this query, and I was able to modify it and get it's
run time cut in half. Here's where it is right now:
SELECT first_name, last_name, serial_number, latest_read, value,
lifetime_value, lifetime.patient_id
FROM (
SELECT DISTINCT patient_id, first_name, last_name, MAX(read_d
Sorry for the messy query, I'm very new to writing these complex queries.
I'll try and make it easier to read by using WITH clauses. However, just
to clarify, the WITH clauses only increase readability and not performance
in any way, right?
On Thu, May 23, 2013 at 4:22 PM, james wrote:
> On
On 23/05/2013 22:57, Jonathan Morra wrote:
I'm not sure I understand your proposed solution. There is also the
case to consider where the same patient can be assigned the same
device multiple times. In this case, the value may be reset at each
assignment (hence the line value - issued_value A
I'm not sure I understand your proposed solution. There is also the case
to consider where the same patient can be assigned the same device multiple
times. In this case, the value may be reset at each assignment (hence the
line value - issued_value AS value from the original query).
On Thu, May
On 05/23/2013 10:57 AM, Jonathan Morra wrote:
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
1. Reads is constantly inserted upon. It should never be updated or
deleted.
2. I suppose I can, but that will make my insertion logic very
complicated. I cannot guarantee the order of any of this data, so I might
get reads at any time and also get assignments at any time (historical as
well).
>>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.
Is "reads" table insert-only? Do you have updates/deletes of the
"historical" rows?
>>3. Can I modify my tables to make this query (which is the crux of my
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
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 a
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+
11 matches
Mail list logo
