From: | Michael Lewis <mlewis(at)entrata(dot)com> |
---|---|
To: | Peter Coppens <peter(dot)coppens(at)datylon(dot)com> |
Cc: | PostgreSQL General <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | Re: Execution plan does not use index |
Date: | 2020-11-10 00:15:52 |
Message-ID: | CAHOFxGrR-pOGQEiZdYzTB7EwoeW_3PsP0pxT0RfG3GQV9gPQCg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Mon, Nov 9, 2020 at 1:11 PM Peter Coppens <peter(dot)coppens(at)datylon(dot)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).
From | Date | Subject | |
---|---|---|---|
Next Message | Merlin Moncure | 2020-11-10 01:46:21 | Re: New "function tables" in V13 documentation |
Previous Message | David G. Johnston | 2020-11-09 23:16:23 | Re: New "function tables" in V13 documentation |