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  (Jonathan Morra <jonmorra@gmail.com>)
Список 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. 
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 по дате отправления:

Предыдущее
От: Josh Berkus
Дата:
Сообщение: Re: Setting vacuum_freeze_min_age really low
Следующее
От: Jonathan Morra
Дата:
Сообщение: Re: Performance of complicated query