1--
2-- Test GiST indexes.
3--
4-- There are other tests to test different GiST opclasses. This is for
5-- testing GiST code itself. Vacuuming in particular.
6create table gist_point_tbl(id int4, p point);
7create index gist_pointidx on gist_point_tbl using gist(p);
8-- Insert enough data to create a tree that's a couple of levels deep.
9insert into gist_point_tbl (id, p)
10select g,        point(g*10, g*10) from generate_series(1, 10000) g;
11insert into gist_point_tbl (id, p)
12select g+100000, point(g*10+1, g*10+1) from generate_series(1, 10000) g;
13-- To test vacuum, delete some entries from all over the index.
14delete from gist_point_tbl where id % 2 = 1;
15-- And also delete some concentration of values. (GiST doesn't currently
16-- attempt to delete pages even when they become empty, but if it did, this
17-- would exercise it)
18delete from gist_point_tbl where id < 10000;
19vacuum analyze gist_point_tbl;
20--
21-- Test Index-only plans on GiST indexes
22--
23create table gist_tbl (b box, p point, c circle);
24insert into gist_tbl
25select box(point(0.05*i, 0.05*i), point(0.05*i, 0.05*i)),
26       point(0.05*i, 0.05*i),
27       circle(point(0.05*i, 0.05*i), 1.0)
28from generate_series(0,10000) as i;
29vacuum analyze gist_tbl;
30set enable_seqscan=off;
31set enable_bitmapscan=off;
32set enable_indexonlyscan=on;
33-- Test index-only scan with point opclass
34create index gist_tbl_point_index on gist_tbl using gist (p);
35-- check that the planner chooses an index-only scan
36explain (costs off)
37select p from gist_tbl where p <@ box(point(0,0), point(0.5, 0.5));
38                       QUERY PLAN
39--------------------------------------------------------
40 Index Only Scan using gist_tbl_point_index on gist_tbl
41   Index Cond: (p <@ '(0.5,0.5),(0,0)'::box)
42(2 rows)
43
44-- execute the same
45select p from gist_tbl where p <@ box(point(0,0), point(0.5, 0.5));
46      p
47-------------
48 (0,0)
49 (0.05,0.05)
50 (0.1,0.1)
51 (0.15,0.15)
52 (0.2,0.2)
53 (0.25,0.25)
54 (0.3,0.3)
55 (0.35,0.35)
56 (0.4,0.4)
57 (0.45,0.45)
58 (0.5,0.5)
59(11 rows)
60
61-- Also test an index-only knn-search
62explain (costs off)
63select p from gist_tbl where p <@ box(point(0,0), point(0.5, 0.5))
64order by p <-> point(0.201, 0.201);
65                       QUERY PLAN
66--------------------------------------------------------
67 Index Only Scan using gist_tbl_point_index on gist_tbl
68   Index Cond: (p <@ '(0.5,0.5),(0,0)'::box)
69   Order By: (p <-> '(0.201,0.201)'::point)
70(3 rows)
71
72select p from gist_tbl where p <@ box(point(0,0), point(0.5, 0.5))
73order by p <-> point(0.201, 0.201);
74      p
75-------------
76 (0.2,0.2)
77 (0.25,0.25)
78 (0.15,0.15)
79 (0.3,0.3)
80 (0.1,0.1)
81 (0.35,0.35)
82 (0.05,0.05)
83 (0.4,0.4)
84 (0,0)
85 (0.45,0.45)
86 (0.5,0.5)
87(11 rows)
88
89-- Check commuted case as well
90explain (costs off)
91select p from gist_tbl where p <@ box(point(0,0), point(0.5, 0.5))
92order by point(0.101, 0.101) <-> p;
93                       QUERY PLAN
94--------------------------------------------------------
95 Index Only Scan using gist_tbl_point_index on gist_tbl
96   Index Cond: (p <@ '(0.5,0.5),(0,0)'::box)
97   Order By: (p <-> '(0.101,0.101)'::point)
98(3 rows)
99
100select p from gist_tbl where p <@ box(point(0,0), point(0.5, 0.5))
101order by point(0.101, 0.101) <-> p;
102      p
103-------------
104 (0.1,0.1)
105 (0.15,0.15)
106 (0.05,0.05)
107 (0.2,0.2)
108 (0,0)
109 (0.25,0.25)
110 (0.3,0.3)
111 (0.35,0.35)
112 (0.4,0.4)
113 (0.45,0.45)
114 (0.5,0.5)
115(11 rows)
116
117-- Check case with multiple rescans (bug #14641)
118explain (costs off)
119select p from
120  (values (box(point(0,0), point(0.5,0.5))),
121          (box(point(0.5,0.5), point(0.75,0.75))),
122          (box(point(0.8,0.8), point(1.0,1.0)))) as v(bb)
123cross join lateral
124  (select p from gist_tbl where p <@ bb order by p <-> bb[0] limit 2) ss;
125                             QUERY PLAN
126--------------------------------------------------------------------
127 Nested Loop
128   ->  Values Scan on "*VALUES*"
129   ->  Limit
130         ->  Index Only Scan using gist_tbl_point_index on gist_tbl
131               Index Cond: (p <@ "*VALUES*".column1)
132               Order By: (p <-> ("*VALUES*".column1)[0])
133(6 rows)
134
135select p from
136  (values (box(point(0,0), point(0.5,0.5))),
137          (box(point(0.5,0.5), point(0.75,0.75))),
138          (box(point(0.8,0.8), point(1.0,1.0)))) as v(bb)
139cross join lateral
140  (select p from gist_tbl where p <@ bb order by p <-> bb[0] limit 2) ss;
141      p
142-------------
143 (0.5,0.5)
144 (0.45,0.45)
145 (0.75,0.75)
146 (0.7,0.7)
147 (1,1)
148 (0.95,0.95)
149(6 rows)
150
151drop index gist_tbl_point_index;
152-- Test index-only scan with box opclass
153create index gist_tbl_box_index on gist_tbl using gist (b);
154-- check that the planner chooses an index-only scan
155explain (costs off)
156select b from gist_tbl where b <@ box(point(5,5), point(6,6));
157                      QUERY PLAN
158------------------------------------------------------
159 Index Only Scan using gist_tbl_box_index on gist_tbl
160   Index Cond: (b <@ '(6,6),(5,5)'::box)
161(2 rows)
162
163-- execute the same
164select b from gist_tbl where b <@ box(point(5,5), point(6,6));
165            b
166-------------------------
167 (5,5),(5,5)
168 (5.05,5.05),(5.05,5.05)
169 (5.1,5.1),(5.1,5.1)
170 (5.15,5.15),(5.15,5.15)
171 (5.2,5.2),(5.2,5.2)
172 (5.25,5.25),(5.25,5.25)
173 (5.3,5.3),(5.3,5.3)
174 (5.35,5.35),(5.35,5.35)
175 (5.4,5.4),(5.4,5.4)
176 (5.45,5.45),(5.45,5.45)
177 (5.5,5.5),(5.5,5.5)
178 (5.55,5.55),(5.55,5.55)
179 (5.6,5.6),(5.6,5.6)
180 (5.65,5.65),(5.65,5.65)
181 (5.7,5.7),(5.7,5.7)
182 (5.75,5.75),(5.75,5.75)
183 (5.8,5.8),(5.8,5.8)
184 (5.85,5.85),(5.85,5.85)
185 (5.9,5.9),(5.9,5.9)
186 (5.95,5.95),(5.95,5.95)
187 (6,6),(6,6)
188(21 rows)
189
190drop index gist_tbl_box_index;
191-- Test that an index-only scan is not chosen, when the query involves the
192-- circle column (the circle opclass does not support index-only scans).
193create index gist_tbl_multi_index on gist_tbl using gist (p, c);
194explain (costs off)
195select p, c from gist_tbl
196where p <@ box(point(5,5), point(6, 6));
197                    QUERY PLAN
198---------------------------------------------------
199 Index Scan using gist_tbl_multi_index on gist_tbl
200   Index Cond: (p <@ '(6,6),(5,5)'::box)
201(2 rows)
202
203-- execute the same
204select b, p from gist_tbl
205where b <@ box(point(4.5, 4.5), point(5.5, 5.5))
206and p <@ box(point(5,5), point(6, 6));
207            b            |      p
208-------------------------+-------------
209 (5,5),(5,5)             | (5,5)
210 (5.05,5.05),(5.05,5.05) | (5.05,5.05)
211 (5.1,5.1),(5.1,5.1)     | (5.1,5.1)
212 (5.15,5.15),(5.15,5.15) | (5.15,5.15)
213 (5.2,5.2),(5.2,5.2)     | (5.2,5.2)
214 (5.25,5.25),(5.25,5.25) | (5.25,5.25)
215 (5.3,5.3),(5.3,5.3)     | (5.3,5.3)
216 (5.35,5.35),(5.35,5.35) | (5.35,5.35)
217 (5.4,5.4),(5.4,5.4)     | (5.4,5.4)
218 (5.45,5.45),(5.45,5.45) | (5.45,5.45)
219 (5.5,5.5),(5.5,5.5)     | (5.5,5.5)
220(11 rows)
221
222drop index gist_tbl_multi_index;
223-- Clean up
224reset enable_seqscan;
225reset enable_bitmapscan;
226reset enable_indexonlyscan;
227drop table gist_tbl;
228