Re: Performance of complicated query
| От | Vladimir Sitnikov |
|---|---|
| Тема | Re: Performance of complicated query |
| Дата | |
| Msg-id | CAB=Je-FDkDGR6sN2O=4rLS07SK9EzNA-Yv8bmAWD8+CV6ghXgg@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: Performance of complicated query (Jonathan Morra <jonmorra@gmail.com>) |
| Ответы |
Re: Performance of complicated query
|
| Список | pgsql-performance |
>>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.
>>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?
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
В списке pgsql-performance по дате отправления: