SFuEaqKG

1drop table if exists t;
2
3-- Create temporary table with local datetime and time zone.
4create temporary table t (
5    id SERIAL primary key,
6    "datetime" timestamp without time zone not null default now(),
7    "zone" varchar(255) not null default 'Europe/Berlin'
8);
9
10-- Insert 262,800 generated rows.
11insert into t (
12    "datetime",
13    "zone"
14)
15select 
16    "datetime",
17    'Europe/Berlin'
18from
19    generate_series('2018-01-01T00:00:01'::timestamp, '2018-12-31T23:59:59'::timestamp, '2 minutes') as "datetime";
20
21-- Index on the "datetime" only.
22create index on t ("datetime", id);
23analyze;
24
25-- This index is used.
26explain
27select
28    "datetime",
29    "zone",
30    id
31from
32    t
33where
34    "datetime" >= '2018-11-02T09:00:00'
35and
36    "datetime" < '2018-11-02T10:00:00'
37;
38
39/*
40+------------------------------------------------------------------------------------------------------------------------------------------------------------+
41| QUERY PLAN                                                                                                                                                 |
42|------------------------------------------------------------------------------------------------------------------------------------------------------------|
43| Bitmap Heap Scan on t  (cost=37.89..1885.03 rows=1314 width=528)                                                                                           |
44|   Recheck Cond: ((datetime >= '2018-11-02 09:00:00'::timestamp without time zone) AND (datetime < '2018-11-01 10:00:00'::timestamp without time zone))     |
45|   ->  Bitmap Index Scan on t_datetime_id_idx  (cost=0.00..37.56 rows=1314 width=0)                                                                         |
46|         Index Cond: ((datetime >= '2018-11-02 09:00:00'::timestamp without time zone) AND (datetime < '2018-11-01 10:00:00'::timestamp without time zone)) |
47+------------------------------------------------------------------------------------------------------------------------------------------------------------+
48*/
49
50-- Converting the local datetime to UTC using the stored "zone", the index is not used.
51explain
52select
53    "datetime",
54    "zone",
55    id 
56from
57    t
58where
59    "datetime" at time zone "zone" at time zone 'UTC' >= '2018-11-02T09:00:00'
60and
61    "datetime" at time zone "zone" at time zone 'UTC' < '2018-11-02T10:00:00'
62;
63
64/*
65+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
66| QUERY PLAN
67|-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
68| Seq Scan on t  (cost=0.00..8503.00 rows=1314 width=528)
69|   Filter: ((timezone('UTC'::text, timezone((zone)::text, datetime)) >= '2018-11-02 09:00:00'::timestamp without time zone) AND (timezone('UTC'::text, timezone((zone)::text, datetime)) < '2018-11-01 10:00:00'::t
70+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
71*/
72
73-- Add an index on the used expression.
74create index on t (timezone('UTC'::text, timezone((zone)::text, datetime)));
75analyze;
76
77-- This index is used.
78explain
79select
80    "datetime",
81    "zone",
82    id 
83from
84    t
85where
86    "datetime" at time zone "zone" at time zone 'UTC' >= '2018-11-02T09:00:00'
87and
88    "datetime" at time zone "zone" at time zone 'UTC' < '2018-11-02T10:00:00'
89;
90
91/*
92CREATE INDEX
93+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
94| QUERY PLAN                                                                                                                                                                                                        
95|-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
96| Bitmap Heap Scan on t  (cost=29.89..1890.17 rows=1314 width=528)                                                                                                                                                  
97|   Recheck Cond: ((timezone('UTC'::text, timezone((zone)::text, datetime)) >= '2018-11-02 09:00:00'::timestamp without time zone) AND (timezone('UTC'::text, timezone((zone)::text, datetime)) < '2018-11-01 10:00:
98|   ->  Bitmap Index Scan on t_timezone_idx  (cost=0.00..29.56 rows=1314 width=0)                                                                                                                                   
99|         Index Cond: ((timezone('UTC'::text, timezone((zone)::text, datetime)) >= '2018-11-02 09:00:00'::timestamp without time zone) AND (timezone('UTC'::text, timezone((zone)::text, datetime)) < '2018-11-01 10
100+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
101*/