PostgreSQL “at time zone” construct does not use index

index-tuningpostgresql-9.4

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

Best Answer

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'
Related Question