1-- interval check
2CREATE TABLE intervaltmp (a interval);
3\copy intervaltmp from 'data/interval.data'
4SET enable_seqscan=on;
5SELECT count(*) FROM intervaltmp WHERE a <  '199 days 21:21:23';
6 count
7-------
8   329
9(1 row)
10
11SELECT count(*) FROM intervaltmp WHERE a <= '199 days 21:21:23';
12 count
13-------
14   330
15(1 row)
16
17SELECT count(*) FROM intervaltmp WHERE a  = '199 days 21:21:23';
18 count
19-------
20     1
21(1 row)
22
23SELECT count(*) FROM intervaltmp WHERE a >= '199 days 21:21:23';
24 count
25-------
26   271
27(1 row)
28
29SELECT count(*) FROM intervaltmp WHERE a >  '199 days 21:21:23';
30 count
31-------
32   270
33(1 row)
34
35SELECT a, a <-> '199 days 21:21:23' FROM intervaltmp ORDER BY a <-> '199 days 21:21:23' LIMIT 3;
36                  a                  |               ?column?
37-------------------------------------+--------------------------------------
38 @ 199 days 21 hours 21 mins 23 secs | @ 0
39 @ 183 days 6 hours 52 mins 48 secs  | @ 16 days 14 hours 28 mins 35 secs
40 @ 220 days 19 hours 5 mins 42 secs  | @ 21 days -2 hours -15 mins -41 secs
41(3 rows)
42
43CREATE INDEX intervalidx ON intervaltmp USING gist ( a );
44SET enable_seqscan=off;
45SELECT count(*) FROM intervaltmp WHERE a <  '199 days 21:21:23'::interval;
46 count
47-------
48   329
49(1 row)
50
51SELECT count(*) FROM intervaltmp WHERE a <= '199 days 21:21:23'::interval;
52 count
53-------
54   330
55(1 row)
56
57SELECT count(*) FROM intervaltmp WHERE a  = '199 days 21:21:23'::interval;
58 count
59-------
60     1
61(1 row)
62
63SELECT count(*) FROM intervaltmp WHERE a >= '199 days 21:21:23'::interval;
64 count
65-------
66   271
67(1 row)
68
69SELECT count(*) FROM intervaltmp WHERE a >  '199 days 21:21:23'::interval;
70 count
71-------
72   270
73(1 row)
74
75EXPLAIN (COSTS OFF)
76SELECT a, a <-> '199 days 21:21:23' FROM intervaltmp ORDER BY a <-> '199 days 21:21:23' LIMIT 3;
77                                QUERY PLAN
78---------------------------------------------------------------------------
79 Limit
80   ->  Index Only Scan using intervalidx on intervaltmp
81         Order By: (a <-> '@ 199 days 21 hours 21 mins 23 secs'::interval)
82(3 rows)
83
84SELECT a, a <-> '199 days 21:21:23' FROM intervaltmp ORDER BY a <-> '199 days 21:21:23' LIMIT 3;
85                  a                  |               ?column?
86-------------------------------------+--------------------------------------
87 @ 199 days 21 hours 21 mins 23 secs | @ 0
88 @ 183 days 6 hours 52 mins 48 secs  | @ 16 days 14 hours 28 mins 35 secs
89 @ 220 days 19 hours 5 mins 42 secs  | @ 21 days -2 hours -15 mins -41 secs
90(3 rows)
91
92SET enable_indexonlyscan=off;
93EXPLAIN (COSTS OFF)
94SELECT a, a <-> '199 days 21:21:23' FROM intervaltmp ORDER BY a <-> '199 days 21:21:23' LIMIT 3;
95                                QUERY PLAN
96---------------------------------------------------------------------------
97 Limit
98   ->  Index Scan using intervalidx on intervaltmp
99         Order By: (a <-> '@ 199 days 21 hours 21 mins 23 secs'::interval)
100(3 rows)
101
102SELECT a, a <-> '199 days 21:21:23' FROM intervaltmp ORDER BY a <-> '199 days 21:21:23' LIMIT 3;
103                  a                  |               ?column?
104-------------------------------------+--------------------------------------
105 @ 199 days 21 hours 21 mins 23 secs | @ 0
106 @ 183 days 6 hours 52 mins 48 secs  | @ 16 days 14 hours 28 mins 35 secs
107 @ 220 days 19 hours 5 mins 42 secs  | @ 21 days -2 hours -15 mins -41 secs
108(3 rows)
109
110