1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
|
CREATE TABLE test_timestamp (
i timestamp
);
INSERT INTO test_timestamp VALUES
( '2004-10-26 03:55:08' ),
( '2004-10-26 04:55:08' ),
( '2004-10-26 05:55:08' ),
( '2004-10-26 08:55:08' ),
( '2004-10-26 09:55:08' ),
( '2004-10-26 10:55:08' )
;
SELECT i <=> '2004-10-26 06:24:08', i FROM test_timestamp ORDER BY 1, 2 ASC;
SELECT i <=| '2004-10-26 06:24:08', i FROM test_timestamp ORDER BY 1, 2 ASC;
SELECT i |=> '2004-10-26 06:24:08', i FROM test_timestamp ORDER BY 1, 2 ASC;
CREATE INDEX idx_timestamp ON test_timestamp USING rum (i);
set enable_seqscan=off;
explain (costs off)
SELECT * FROM test_timestamp WHERE i<'2004-10-26 08:55:08'::timestamp ORDER BY i;
SELECT * FROM test_timestamp WHERE i<'2004-10-26 08:55:08'::timestamp ORDER BY i;
explain (costs off)
SELECT * FROM test_timestamp WHERE i<='2004-10-26 08:55:08'::timestamp ORDER BY i;
SELECT * FROM test_timestamp WHERE i<='2004-10-26 08:55:08'::timestamp ORDER BY i;
explain (costs off)
SELECT * FROM test_timestamp WHERE i='2004-10-26 08:55:08'::timestamp ORDER BY i;
SELECT * FROM test_timestamp WHERE i='2004-10-26 08:55:08'::timestamp ORDER BY i;
explain (costs off)
SELECT * FROM test_timestamp WHERE i>='2004-10-26 08:55:08'::timestamp ORDER BY i;
SELECT * FROM test_timestamp WHERE i>='2004-10-26 08:55:08'::timestamp ORDER BY i;
explain (costs off)
SELECT * FROM test_timestamp WHERE i>'2004-10-26 08:55:08'::timestamp ORDER BY i;
SELECT * FROM test_timestamp WHERE i>'2004-10-26 08:55:08'::timestamp ORDER BY i;
explain (costs off)
SELECT *, i <=> '2004-10-26 08:55:08'::timestamp FROM test_timestamp
ORDER BY i <=> '2004-10-26 08:55:08'::timestamp;
SELECT *, i <=> '2004-10-26 08:55:08'::timestamp FROM test_timestamp
ORDER BY i <=> '2004-10-26 08:55:08'::timestamp;
explain (costs off)
SELECT *, i <=> '2004-10-26 05:00:00'::timestamp FROM test_timestamp
WHERE i>'2004-10-26 05:00:00'::timestamp ORDER BY i <=> '2004-10-26 05:00:00'::timestamp;
SELECT *, i <=> '2004-10-26 05:00:00'::timestamp FROM test_timestamp
WHERE i>'2004-10-26 05:00:00'::timestamp ORDER BY i <=> '2004-10-26 05:00:00'::timestamp;
-- Tests for timestamptz
SELECT i::timestamptz AS i INTO test_timestamptz FROM test_timestamp;
CREATE INDEX idx_timestamptz ON test_timestamptz USING rum (i);
explain (costs off)
SELECT * FROM test_timestamptz WHERE i>'2004-10-26 08:55:08'::timestamptz ORDER BY i;
SELECT * FROM test_timestamptz WHERE i>'2004-10-26 08:55:08'::timestamptz ORDER BY i;
explain (costs off)
SELECT *, i <=> '2004-10-26 08:55:08'::timestamptz FROM test_timestamptz
ORDER BY i <=> '2004-10-26 08:55:08'::timestamptz;
SELECT *, i <=> '2004-10-26 08:55:08'::timestamptz FROM test_timestamptz
ORDER BY i <=> '2004-10-26 08:55:08'::timestamptz;
explain (costs off)
SELECT *, i <=> '2004-10-26 05:00:00'::timestamptz FROM test_timestamptz
WHERE i>'2004-10-26 05:00:00'::timestamptz ORDER BY i <=> '2004-10-26 05:00:00'::timestamptz;
SELECT *, i <=> '2004-10-26 05:00:00'::timestamptz FROM test_timestamptz
WHERE i>'2004-10-26 05:00:00'::timestamptz ORDER BY i <=> '2004-10-26 05:00:00'::timestamptz;
|
