On Mon, Nov 9, 2020 at 1:11 PM Peter Coppens <peter.coppens@xxxxxxxxxxx> wrote:
Adding the tzn.utc_offset results in the fact that the execution plan no longer considers to use the index on the measurement_value table. Is there any way the SQL can be rewritten so that the index is used? Or any other solution so that the query with the timezone offset returns in a comparable time?
I am not aware of a best practice to handle this. Your where condition on mv.timestamp now depends on several joins to do a filtering that used to be a static range that can be scanned into the index as a first node in the plan. I have sometimes used a sub-query on a broader condition that allows the use of the index, and then fully reducing the set later. Something like this-
select d.short_id,mv.timestamp,mv.I64_01
from device d, device_configuration dc, (
from device d, device_configuration dc, (
select mv.*
from measurement_value AS mv_inner
where mv.timestamp > '2020-11-06'::timestamp - interval '1 day' and mv.timestamp < '2020-11-07'::timestamp + interval '1 day'
where mv.timestamp > '2020-11-06'::timestamp - interval '1 day' and mv.timestamp < '2020-11-07'::timestamp + interval '1 day'
offset 0 /* to prevent in-lining the join to the outside set */
) mv, pg_timezone_names tzn
where mv.device_id=d.short_id and dc.device_id = d.id and dc.latest=true and dc.timezone=tzn.name and
mv.timestamp > '2020-11-06'::timestamp - tzn.utc_offset and mv.timestamp < '2020-11-07'::timestamp - tzn.utc_offset
where mv.device_id=d.short_id and dc.device_id = d.id and dc.latest=true and dc.timezone=tzn.name and
mv.timestamp > '2020-11-06'::timestamp - tzn.utc_offset and mv.timestamp < '2020-11-07'::timestamp - tzn.utc_offset
By the way, it seems a little odd to be exclusive on both the begin and end. I'd usually expect timestamp >= start_date and timestamp < end_date + interval '1 day' to fully capture a 24 hour period. Right now, you are excluding any data that happens to have a timestamp value with .000000 seconds (midnight exactly).
