1-- int2 check
2CREATE TABLE int2tmp (a int2);
3\copy int2tmp from 'data/int2.data'
4SET enable_seqscan=on;
5SELECT count(*) FROM int2tmp WHERE a <  237;
6 count
7-------
8   297
9(1 row)
10
11SELECT count(*) FROM int2tmp WHERE a <= 237;
12 count
13-------
14   298
15(1 row)
16
17SELECT count(*) FROM int2tmp WHERE a  = 237;
18 count
19-------
20     1
21(1 row)
22
23SELECT count(*) FROM int2tmp WHERE a >= 237;
24 count
25-------
26   249
27(1 row)
28
29SELECT count(*) FROM int2tmp WHERE a >  237;
30 count
31-------
32   248
33(1 row)
34
35SELECT a, a <-> '237' FROM int2tmp ORDER BY a <-> '237' LIMIT 3;
36  a  | ?column?
37-----+----------
38 237 |        0
39 232 |        5
40 228 |        9
41(3 rows)
42
43CREATE INDEX int2idx ON int2tmp USING gist ( a );
44SET enable_seqscan=off;
45SELECT count(*) FROM int2tmp WHERE a <  237::int2;
46 count
47-------
48   297
49(1 row)
50
51SELECT count(*) FROM int2tmp WHERE a <= 237::int2;
52 count
53-------
54   298
55(1 row)
56
57SELECT count(*) FROM int2tmp WHERE a  = 237::int2;
58 count
59-------
60     1
61(1 row)
62
63SELECT count(*) FROM int2tmp WHERE a >= 237::int2;
64 count
65-------
66   249
67(1 row)
68
69SELECT count(*) FROM int2tmp WHERE a >  237::int2;
70 count
71-------
72   248
73(1 row)
74
75EXPLAIN (COSTS OFF)
76SELECT a, a <-> '237' FROM int2tmp ORDER BY a <-> '237' LIMIT 3;
77                   QUERY PLAN
78------------------------------------------------
79 Limit
80   ->  Index Only Scan using int2idx on int2tmp
81         Order By: (a <-> '237'::smallint)
82(3 rows)
83
84SELECT a, a <-> '237' FROM int2tmp ORDER BY a <-> '237' LIMIT 3;
85  a  | ?column?
86-----+----------
87 237 |        0
88 232 |        5
89 228 |        9
90(3 rows)
91
92