On Mon, Nov 9, 2020 at 1:11 PM Peter Coppens <peter.copp...@datylon.com>
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, (
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'
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
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).
