PostgreSQL "at time zone" construct does not use index

Question

I have a simple table and am unable to understand why a certain query does not use an obvious index, and runs so slow.

This is the table:

create table impulse2 (
   account     smallint        NOT NULL,
   sensor      smallint        NOT NULL,
   datetime    timestamp       NOT NULL,
   last_value  numeric(10,2)
);
alter table impulse2 add constraint impulse_pkey2 primary key (account, sensor, datetime);

The slow query is here:

select
date_trunc('minute', datetime at time zone 'UTC' at time zone 'Europe/Luxembourg') as datetime,
round(avg(last_value), 1) as last_value 
from impulse2 
where account = 1 
and sensor = 2 
and date_trunc('hour', datetime at time zone 'UTC' at time zone 'Europe/Luxembourg') between '2016-09-08 00:00:00' and '2016-09-08 00:10:00'
group by 1 
order by 1

Explain analyse returns this:

QUERY PLAN
GroupAggregate  (cost=38120.73..38263.54 rows=5193 width=14) (actual time=931.191..931.278 rows=60 loops=1)
  Group Key: (date_trunc('minute'::text, timezone('Europe/Luxembourg'::text, (datetime)::timestamp with time zone)))
  ->  Sort  (cost=38120.73..38133.72 rows=5193 width=14) (actual time=931.172..931.187 rows=165 loops=1)
        Sort Key: (date_trunc('minute'::text, timezone('Europe/Luxembourg'::text, (datetime)::timestamp with time zone)))
        Sort Method: quicksort  Memory: 32kB
        ->  Seq Scan on impulse2  (cost=0.00..37800.26 rows=5193 width=14) (actual time=537.536..931.131 rows=165 loops=1)
              Filter: ((account = 1) AND (sensor = 2) AND (date_trunc('hour'::text, timezone('Europe/Luxembourg'::text, (datetime)::timestamp with time zone)) >= '2016-09-08 00:00:00'::timestamp without time zone) AND (date_trunc('hour'::text, timezone('Europe/Luxembourg'::text, (datetime)::timestamp with time zone)) <= '2016-09-08 00:10:00'::timestamp without time zone))
              Rows Removed by Filter: 1038445
Planning time: 0.195 ms
Execution time: 931.341 ms

The date_trunc() is causing this. It can't use the index for datetime and has to scan a lot of index pages or the full table. You should change that condition to something like datetime >= (some-complex-expression) and datetime < (same-expression + interval '10 hour') — ypercubeᵀᴹ
– ypercubeᵀᴹ, Commented Oct 8, 2016 at 10:12
I tried that, even before posting here. But it does not change anything, strangely. — yglodt
– yglodt, Commented Oct 8, 2016 at 10:39
I tried using "and datetime at time zone 'UTC' at time zone 'Europe/Luxembourg' between '2016-09-08 00:00:00' and '2016-09-08 00:20:00'" e.g. without the date_trunc function — yglodt
– yglodt, Commented Oct 8, 2016 at 10:45
No, the datetime should be as it is, without any function or conversion, on one side of the <=. You can use any function on the other side, to do the opposite conversion. That's why I said it would be rather complex. I can't test right now and timezones is rather confusing, buggy territory. I'm sure someone will answer. — ypercubeᵀᴹ
– ypercubeᵀᴹ, Commented Oct 8, 2016 at 10:48
As a starting point, try where ... and datetime >= '2016-09-08 00:00:00' - interval 2 hours' and datetime < '2016-09-08 10:00:00' - interval '2 hours', just to check if the index is used and the performance is good. — ypercubeᵀᴹ
– ypercubeᵀᴹ, Commented Oct 8, 2016 at 10:50

yglodt · Accepted Answer · 2016-10-27 19:43:41Z

I found a way to "cheat" which uses the index, and also has the comfort of timezone conversion.

I basically extend the date range of selection to be sure to include enough values whatever timezone the user has, and then use a wrapper select which filters the result a second time and performs the timezone conversion:

select * from (
    select
    date_trunc('minute', datetime at time zone 'UTC' at time zone 'Europe/Luxembourg') as datetime,
    round(avg(last_value), 1) as last_value 
    from impulse2 
    where account = 1 
    and sensor = 2 
    and datetime between '2016-09-07 00:00' and '2016-09-09 23:59' -- uses index
    group by 1 
    order by 1
) as r
where 
r.datetime at time zone 'UTC' at time zone 'Europe/Luxembourg' between '2016-09-08 00:00:00' and '2016-09-08 00:20:00'

Stack Exchange Network

PostgreSQL "at time zone" construct does not use index

1 Answer 1

Your Answer

Hot Network Questions

PostgreSQL "at time zone" construct does not use index

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Related

Hot Network Questions