Re: Performance of complicated query

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

 



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).  I suppose I could do that, but I'd like to avoid it if at all possible.
3.  2 measurements can have the same date, and that is fine.  The problem arises when the same device produces 2 reads at the same time and that isn't possible.
4.  I agree that a lot of this is a mess, however MAX(max_read) OVER(PARTITION BY patient_id) AS latest_read seems necessary as using a group by clause forces me to group by all elements I'm selecting, which I don't want to do.


On Thu, May 23, 2013 at 12:23 PM, Vladimir Sitnikov <sitnikov.vladimir@xxxxxxxxx> wrote:
>>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 application) run faster?
Can you have a second "reads" table that stores only up to date values?
That will eliminate max-over completely, enable efficient usage in other queries, and make your queries much easier to understand by humans and computers.

PS. read_datetime = max_read is prone to "what if two measurements have same date" errors.
PPS. distinct MAX(max_read) OVER(PARTITION BY patient_id) AS latest_read looks like a complete mess. Why don't you just use group by?

Regards,
Vladimir


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

  Powered by Linux