1-- inet check
2CREATE TABLE inettmp (a inet);
3\copy inettmp from 'data/inet.data'
4SET enable_seqscan=on;
5SELECT count(*) FROM inettmp WHERE a <  '89.225.196.191';
6 count
7-------
8   213
9(1 row)
10
11SELECT count(*) FROM inettmp WHERE a <= '89.225.196.191';
12 count
13-------
14   214
15(1 row)
16
17SELECT count(*) FROM inettmp WHERE a  = '89.225.196.191';
18 count
19-------
20     1
21(1 row)
22
23SELECT count(*) FROM inettmp WHERE a >= '89.225.196.191';
24 count
25-------
26   387
27(1 row)
28
29SELECT count(*) FROM inettmp WHERE a >  '89.225.196.191';
30 count
31-------
32   386
33(1 row)
34
35CREATE INDEX inetidx ON inettmp USING gist ( a );
36SET enable_seqscan=off;
37SELECT count(*) FROM inettmp WHERE a <  '89.225.196.191'::inet;
38 count
39-------
40   213
41(1 row)
42
43SELECT count(*) FROM inettmp WHERE a <= '89.225.196.191'::inet;
44 count
45-------
46   214
47(1 row)
48
49SELECT count(*) FROM inettmp WHERE a  = '89.225.196.191'::inet;
50 count
51-------
52     1
53(1 row)
54
55SELECT count(*) FROM inettmp WHERE a >= '89.225.196.191'::inet;
56 count
57-------
58   387
59(1 row)
60
61SELECT count(*) FROM inettmp WHERE a >  '89.225.196.191'::inet;
62 count
63-------
64   386
65(1 row)
66
67VACUUM ANALYZE inettmp;
68-- gist_inet_ops lacks a fetch function, so this should not be index-only scan
69EXPLAIN (COSTS OFF)
70SELECT count(*) FROM inettmp WHERE a  = '89.225.196.191'::inet;
71                    QUERY PLAN
72--------------------------------------------------
73 Aggregate
74   ->  Index Scan using inetidx on inettmp
75         Index Cond: (a = '89.225.196.191'::inet)
76(3 rows)
77
78SELECT count(*) FROM inettmp WHERE a  = '89.225.196.191'::inet;
79 count
80-------
81     1
82(1 row)
83
84DROP INDEX inetidx;
85CREATE INDEX ON inettmp USING gist (a gist_inet_ops, a inet_ops);
86-- likewise here (checks for core planner bug)
87EXPLAIN (COSTS OFF)
88SELECT count(*) FROM inettmp WHERE a  = '89.225.196.191'::inet;
89                     QUERY PLAN
90----------------------------------------------------
91 Aggregate
92   ->  Index Scan using inettmp_a_a1_idx on inettmp
93         Index Cond: (a = '89.225.196.191'::inet)
94(3 rows)
95
96SELECT count(*) FROM inettmp WHERE a  = '89.225.196.191'::inet;
97 count
98-------
99     1
100(1 row)
101
102