1--
2-- SP-GiST index tests
3--
4
5CREATE TABLE quad_point_tbl AS
6    SELECT point(unique1,unique2) AS p FROM tenk1;
7
8INSERT INTO quad_point_tbl
9    SELECT '(333.0,400.0)'::point FROM generate_series(1,1000);
10
11INSERT INTO quad_point_tbl VALUES (NULL), (NULL), (NULL);
12
13CREATE INDEX sp_quad_ind ON quad_point_tbl USING spgist (p);
14
15CREATE TABLE kd_point_tbl AS SELECT * FROM quad_point_tbl;
16
17CREATE INDEX sp_kd_ind ON kd_point_tbl USING spgist (p kd_point_ops);
18
19CREATE TABLE radix_text_tbl AS
20    SELECT name AS t FROM road WHERE name !~ '^[0-9]';
21
22INSERT INTO radix_text_tbl
23    SELECT 'P0123456789abcdef' FROM generate_series(1,1000);
24INSERT INTO radix_text_tbl VALUES ('P0123456789abcde');
25INSERT INTO radix_text_tbl VALUES ('P0123456789abcdefF');
26
27CREATE INDEX sp_radix_ind ON radix_text_tbl USING spgist (t);
28
29-- get non-indexed results for comparison purposes
30
31SET enable_seqscan = ON;
32SET enable_indexscan = OFF;
33SET enable_bitmapscan = OFF;
34
35SELECT count(*) FROM quad_point_tbl WHERE p IS NULL;
36
37SELECT count(*) FROM quad_point_tbl WHERE p IS NOT NULL;
38
39SELECT count(*) FROM quad_point_tbl;
40
41SELECT count(*) FROM quad_point_tbl WHERE p <@ box '(200,200,1000,1000)';
42
43SELECT count(*) FROM quad_point_tbl WHERE box '(200,200,1000,1000)' @> p;
44
45SELECT count(*) FROM quad_point_tbl WHERE p << '(5000, 4000)';
46
47SELECT count(*) FROM quad_point_tbl WHERE p >> '(5000, 4000)';
48
49SELECT count(*) FROM quad_point_tbl WHERE p <^ '(5000, 4000)';
50
51SELECT count(*) FROM quad_point_tbl WHERE p >^ '(5000, 4000)';
52
53SELECT count(*) FROM quad_point_tbl WHERE p ~= '(4585, 365)';
54
55CREATE TEMP TABLE quad_point_tbl_ord_seq1 AS
56SELECT row_number() OVER (ORDER BY p <-> '0,0') n, p <-> '0,0' dist, p
57FROM quad_point_tbl;
58
59CREATE TEMP TABLE quad_point_tbl_ord_seq2 AS
60SELECT row_number() OVER (ORDER BY p <-> '0,0') n, p <-> '0,0' dist, p
61FROM quad_point_tbl WHERE p <@ box '(200,200,1000,1000)';
62
63CREATE TEMP TABLE quad_point_tbl_ord_seq3 AS
64SELECT row_number() OVER (ORDER BY p <-> '333,400') n, p <-> '333,400' dist, p
65FROM quad_point_tbl WHERE p IS NOT NULL;
66
67SELECT count(*) FROM radix_text_tbl WHERE t = 'P0123456789abcdef';
68
69SELECT count(*) FROM radix_text_tbl WHERE t = 'P0123456789abcde';
70
71SELECT count(*) FROM radix_text_tbl WHERE t = 'P0123456789abcdefF';
72
73SELECT count(*) FROM radix_text_tbl WHERE t <    'Aztec                         Ct  ';
74
75SELECT count(*) FROM radix_text_tbl WHERE t ~<~  'Aztec                         Ct  ';
76
77SELECT count(*) FROM radix_text_tbl WHERE t <=   'Aztec                         Ct  ';
78
79SELECT count(*) FROM radix_text_tbl WHERE t ~<=~ 'Aztec                         Ct  ';
80
81SELECT count(*) FROM radix_text_tbl WHERE t =    'Aztec                         Ct  ';
82
83SELECT count(*) FROM radix_text_tbl WHERE t =    'Worth                         St  ';
84
85SELECT count(*) FROM radix_text_tbl WHERE t >=   'Worth                         St  ';
86
87SELECT count(*) FROM radix_text_tbl WHERE t ~>=~ 'Worth                         St  ';
88
89SELECT count(*) FROM radix_text_tbl WHERE t >    'Worth                         St  ';
90
91SELECT count(*) FROM radix_text_tbl WHERE t ~>~  'Worth                         St  ';
92
93SELECT count(*) FROM radix_text_tbl WHERE t ^@  'Worth';
94
95-- Now check the results from plain indexscan
96SET enable_seqscan = OFF;
97SET enable_indexscan = ON;
98SET enable_bitmapscan = OFF;
99
100EXPLAIN (COSTS OFF)
101SELECT count(*) FROM quad_point_tbl WHERE p IS NULL;
102SELECT count(*) FROM quad_point_tbl WHERE p IS NULL;
103
104EXPLAIN (COSTS OFF)
105SELECT count(*) FROM quad_point_tbl WHERE p IS NOT NULL;
106SELECT count(*) FROM quad_point_tbl WHERE p IS NOT NULL;
107
108EXPLAIN (COSTS OFF)
109SELECT count(*) FROM quad_point_tbl;
110SELECT count(*) FROM quad_point_tbl;
111
112EXPLAIN (COSTS OFF)
113SELECT count(*) FROM quad_point_tbl WHERE p <@ box '(200,200,1000,1000)';
114SELECT count(*) FROM quad_point_tbl WHERE p <@ box '(200,200,1000,1000)';
115
116EXPLAIN (COSTS OFF)
117SELECT count(*) FROM quad_point_tbl WHERE box '(200,200,1000,1000)' @> p;
118SELECT count(*) FROM quad_point_tbl WHERE box '(200,200,1000,1000)' @> p;
119
120EXPLAIN (COSTS OFF)
121SELECT count(*) FROM quad_point_tbl WHERE p << '(5000, 4000)';
122SELECT count(*) FROM quad_point_tbl WHERE p << '(5000, 4000)';
123
124EXPLAIN (COSTS OFF)
125SELECT count(*) FROM quad_point_tbl WHERE p >> '(5000, 4000)';
126SELECT count(*) FROM quad_point_tbl WHERE p >> '(5000, 4000)';
127
128EXPLAIN (COSTS OFF)
129SELECT count(*) FROM quad_point_tbl WHERE p <^ '(5000, 4000)';
130SELECT count(*) FROM quad_point_tbl WHERE p <^ '(5000, 4000)';
131
132EXPLAIN (COSTS OFF)
133SELECT count(*) FROM quad_point_tbl WHERE p >^ '(5000, 4000)';
134SELECT count(*) FROM quad_point_tbl WHERE p >^ '(5000, 4000)';
135
136EXPLAIN (COSTS OFF)
137SELECT count(*) FROM quad_point_tbl WHERE p ~= '(4585, 365)';
138SELECT count(*) FROM quad_point_tbl WHERE p ~= '(4585, 365)';
139
140EXPLAIN (COSTS OFF)
141SELECT row_number() OVER (ORDER BY p <-> '0,0') n, p <-> '0,0' dist, p
142FROM quad_point_tbl;
143CREATE TEMP TABLE quad_point_tbl_ord_idx1 AS
144SELECT row_number() OVER (ORDER BY p <-> '0,0') n, p <-> '0,0' dist, p
145FROM quad_point_tbl;
146SELECT * FROM quad_point_tbl_ord_seq1 seq FULL JOIN quad_point_tbl_ord_idx1 idx
147ON seq.n = idx.n
148WHERE seq.dist IS DISTINCT FROM idx.dist;
149
150EXPLAIN (COSTS OFF)
151SELECT row_number() OVER (ORDER BY p <-> '0,0') n, p <-> '0,0' dist, p
152FROM quad_point_tbl WHERE p <@ box '(200,200,1000,1000)';
153CREATE TEMP TABLE quad_point_tbl_ord_idx2 AS
154SELECT row_number() OVER (ORDER BY p <-> '0,0') n, p <-> '0,0' dist, p
155FROM quad_point_tbl WHERE p <@ box '(200,200,1000,1000)';
156SELECT * FROM quad_point_tbl_ord_seq2 seq FULL JOIN quad_point_tbl_ord_idx2 idx
157ON seq.n = idx.n
158WHERE seq.dist IS DISTINCT FROM idx.dist;
159
160EXPLAIN (COSTS OFF)
161SELECT row_number() OVER (ORDER BY p <-> '333,400') n, p <-> '333,400' dist, p
162FROM quad_point_tbl WHERE p IS NOT NULL;
163CREATE TEMP TABLE quad_point_tbl_ord_idx3 AS
164SELECT row_number() OVER (ORDER BY p <-> '333,400') n, p <-> '333,400' dist, p
165FROM quad_point_tbl WHERE p IS NOT NULL;
166SELECT * FROM quad_point_tbl_ord_seq3 seq FULL JOIN quad_point_tbl_ord_idx3 idx
167ON seq.n = idx.n
168WHERE seq.dist IS DISTINCT FROM idx.dist;
169
170EXPLAIN (COSTS OFF)
171SELECT count(*) FROM kd_point_tbl WHERE p <@ box '(200,200,1000,1000)';
172SELECT count(*) FROM kd_point_tbl WHERE p <@ box '(200,200,1000,1000)';
173
174EXPLAIN (COSTS OFF)
175SELECT count(*) FROM kd_point_tbl WHERE box '(200,200,1000,1000)' @> p;
176SELECT count(*) FROM kd_point_tbl WHERE box '(200,200,1000,1000)' @> p;
177
178EXPLAIN (COSTS OFF)
179SELECT count(*) FROM kd_point_tbl WHERE p << '(5000, 4000)';
180SELECT count(*) FROM kd_point_tbl WHERE p << '(5000, 4000)';
181
182EXPLAIN (COSTS OFF)
183SELECT count(*) FROM kd_point_tbl WHERE p >> '(5000, 4000)';
184SELECT count(*) FROM kd_point_tbl WHERE p >> '(5000, 4000)';
185
186EXPLAIN (COSTS OFF)
187SELECT count(*) FROM kd_point_tbl WHERE p <^ '(5000, 4000)';
188SELECT count(*) FROM kd_point_tbl WHERE p <^ '(5000, 4000)';
189
190EXPLAIN (COSTS OFF)
191SELECT count(*) FROM kd_point_tbl WHERE p >^ '(5000, 4000)';
192SELECT count(*) FROM kd_point_tbl WHERE p >^ '(5000, 4000)';
193
194EXPLAIN (COSTS OFF)
195SELECT count(*) FROM kd_point_tbl WHERE p ~= '(4585, 365)';
196SELECT count(*) FROM kd_point_tbl WHERE p ~= '(4585, 365)';
197
198EXPLAIN (COSTS OFF)
199SELECT row_number() OVER (ORDER BY p <-> '0,0') n, p <-> '0,0' dist, p
200FROM kd_point_tbl;
201CREATE TEMP TABLE kd_point_tbl_ord_idx1 AS
202SELECT row_number() OVER (ORDER BY p <-> '0,0') n, p <-> '0,0' dist, p
203FROM kd_point_tbl;
204SELECT * FROM quad_point_tbl_ord_seq1 seq FULL JOIN kd_point_tbl_ord_idx1 idx
205ON seq.n = idx.n
206WHERE seq.dist IS DISTINCT FROM idx.dist;
207
208EXPLAIN (COSTS OFF)
209SELECT row_number() OVER (ORDER BY p <-> '0,0') n, p <-> '0,0' dist, p
210FROM kd_point_tbl WHERE p <@ box '(200,200,1000,1000)';
211CREATE TEMP TABLE kd_point_tbl_ord_idx2 AS
212SELECT row_number() OVER (ORDER BY p <-> '0,0') n, p <-> '0,0' dist, p
213FROM kd_point_tbl WHERE p <@ box '(200,200,1000,1000)';
214SELECT * FROM quad_point_tbl_ord_seq2 seq FULL JOIN kd_point_tbl_ord_idx2 idx
215ON seq.n = idx.n
216WHERE seq.dist IS DISTINCT FROM idx.dist;
217
218EXPLAIN (COSTS OFF)
219SELECT row_number() OVER (ORDER BY p <-> '333,400') n, p <-> '333,400' dist, p
220FROM kd_point_tbl WHERE p IS NOT NULL;
221CREATE TEMP TABLE kd_point_tbl_ord_idx3 AS
222SELECT row_number() OVER (ORDER BY p <-> '333,400') n, p <-> '333,400' dist, p
223FROM kd_point_tbl WHERE p IS NOT NULL;
224SELECT * FROM quad_point_tbl_ord_seq3 seq FULL JOIN kd_point_tbl_ord_idx3 idx
225ON seq.n = idx.n
226WHERE seq.dist IS DISTINCT FROM idx.dist;
227
228EXPLAIN (COSTS OFF)
229SELECT count(*) FROM radix_text_tbl WHERE t = 'P0123456789abcdef';
230SELECT count(*) FROM radix_text_tbl WHERE t = 'P0123456789abcdef';
231
232EXPLAIN (COSTS OFF)
233SELECT count(*) FROM radix_text_tbl WHERE t = 'P0123456789abcde';
234SELECT count(*) FROM radix_text_tbl WHERE t = 'P0123456789abcde';
235
236EXPLAIN (COSTS OFF)
237SELECT count(*) FROM radix_text_tbl WHERE t = 'P0123456789abcdefF';
238SELECT count(*) FROM radix_text_tbl WHERE t = 'P0123456789abcdefF';
239
240EXPLAIN (COSTS OFF)
241SELECT count(*) FROM radix_text_tbl WHERE t <    'Aztec                         Ct  ';
242SELECT count(*) FROM radix_text_tbl WHERE t <    'Aztec                         Ct  ';
243
244EXPLAIN (COSTS OFF)
245SELECT count(*) FROM radix_text_tbl WHERE t ~<~  'Aztec                         Ct  ';
246SELECT count(*) FROM radix_text_tbl WHERE t ~<~  'Aztec                         Ct  ';
247
248EXPLAIN (COSTS OFF)
249SELECT count(*) FROM radix_text_tbl WHERE t <=   'Aztec                         Ct  ';
250SELECT count(*) FROM radix_text_tbl WHERE t <=   'Aztec                         Ct  ';
251
252EXPLAIN (COSTS OFF)
253SELECT count(*) FROM radix_text_tbl WHERE t ~<=~ 'Aztec                         Ct  ';
254SELECT count(*) FROM radix_text_tbl WHERE t ~<=~ 'Aztec                         Ct  ';
255
256EXPLAIN (COSTS OFF)
257SELECT count(*) FROM radix_text_tbl WHERE t =    'Aztec                         Ct  ';
258SELECT count(*) FROM radix_text_tbl WHERE t =    'Aztec                         Ct  ';
259
260EXPLAIN (COSTS OFF)
261SELECT count(*) FROM radix_text_tbl WHERE t =    'Worth                         St  ';
262SELECT count(*) FROM radix_text_tbl WHERE t =    'Worth                         St  ';
263
264EXPLAIN (COSTS OFF)
265SELECT count(*) FROM radix_text_tbl WHERE t >=   'Worth                         St  ';
266SELECT count(*) FROM radix_text_tbl WHERE t >=   'Worth                         St  ';
267
268EXPLAIN (COSTS OFF)
269SELECT count(*) FROM radix_text_tbl WHERE t ~>=~ 'Worth                         St  ';
270SELECT count(*) FROM radix_text_tbl WHERE t ~>=~ 'Worth                         St  ';
271
272EXPLAIN (COSTS OFF)
273SELECT count(*) FROM radix_text_tbl WHERE t >    'Worth                         St  ';
274SELECT count(*) FROM radix_text_tbl WHERE t >    'Worth                         St  ';
275
276EXPLAIN (COSTS OFF)
277SELECT count(*) FROM radix_text_tbl WHERE t ~>~  'Worth                         St  ';
278SELECT count(*) FROM radix_text_tbl WHERE t ~>~  'Worth                         St  ';
279
280EXPLAIN (COSTS OFF)
281SELECT count(*) FROM radix_text_tbl WHERE t ^@	 'Worth';
282SELECT count(*) FROM radix_text_tbl WHERE t ^@	 'Worth';
283
284-- Now check the results from bitmap indexscan
285SET enable_seqscan = OFF;
286SET enable_indexscan = OFF;
287SET enable_bitmapscan = ON;
288
289EXPLAIN (COSTS OFF)
290SELECT count(*) FROM quad_point_tbl WHERE p IS NULL;
291SELECT count(*) FROM quad_point_tbl WHERE p IS NULL;
292
293EXPLAIN (COSTS OFF)
294SELECT count(*) FROM quad_point_tbl WHERE p IS NOT NULL;
295SELECT count(*) FROM quad_point_tbl WHERE p IS NOT NULL;
296
297EXPLAIN (COSTS OFF)
298SELECT count(*) FROM quad_point_tbl;
299SELECT count(*) FROM quad_point_tbl;
300
301EXPLAIN (COSTS OFF)
302SELECT count(*) FROM quad_point_tbl WHERE p <@ box '(200,200,1000,1000)';
303SELECT count(*) FROM quad_point_tbl WHERE p <@ box '(200,200,1000,1000)';
304
305EXPLAIN (COSTS OFF)
306SELECT count(*) FROM quad_point_tbl WHERE box '(200,200,1000,1000)' @> p;
307SELECT count(*) FROM quad_point_tbl WHERE box '(200,200,1000,1000)' @> p;
308
309EXPLAIN (COSTS OFF)
310SELECT count(*) FROM quad_point_tbl WHERE p << '(5000, 4000)';
311SELECT count(*) FROM quad_point_tbl WHERE p << '(5000, 4000)';
312
313EXPLAIN (COSTS OFF)
314SELECT count(*) FROM quad_point_tbl WHERE p >> '(5000, 4000)';
315SELECT count(*) FROM quad_point_tbl WHERE p >> '(5000, 4000)';
316
317EXPLAIN (COSTS OFF)
318SELECT count(*) FROM quad_point_tbl WHERE p <^ '(5000, 4000)';
319SELECT count(*) FROM quad_point_tbl WHERE p <^ '(5000, 4000)';
320
321EXPLAIN (COSTS OFF)
322SELECT count(*) FROM quad_point_tbl WHERE p >^ '(5000, 4000)';
323SELECT count(*) FROM quad_point_tbl WHERE p >^ '(5000, 4000)';
324
325EXPLAIN (COSTS OFF)
326SELECT count(*) FROM quad_point_tbl WHERE p ~= '(4585, 365)';
327SELECT count(*) FROM quad_point_tbl WHERE p ~= '(4585, 365)';
328
329EXPLAIN (COSTS OFF)
330SELECT count(*) FROM kd_point_tbl WHERE p <@ box '(200,200,1000,1000)';
331SELECT count(*) FROM kd_point_tbl WHERE p <@ box '(200,200,1000,1000)';
332
333EXPLAIN (COSTS OFF)
334SELECT count(*) FROM kd_point_tbl WHERE box '(200,200,1000,1000)' @> p;
335SELECT count(*) FROM kd_point_tbl WHERE box '(200,200,1000,1000)' @> p;
336
337EXPLAIN (COSTS OFF)
338SELECT count(*) FROM kd_point_tbl WHERE p << '(5000, 4000)';
339SELECT count(*) FROM kd_point_tbl WHERE p << '(5000, 4000)';
340
341EXPLAIN (COSTS OFF)
342SELECT count(*) FROM kd_point_tbl WHERE p >> '(5000, 4000)';
343SELECT count(*) FROM kd_point_tbl WHERE p >> '(5000, 4000)';
344
345EXPLAIN (COSTS OFF)
346SELECT count(*) FROM kd_point_tbl WHERE p <^ '(5000, 4000)';
347SELECT count(*) FROM kd_point_tbl WHERE p <^ '(5000, 4000)';
348
349EXPLAIN (COSTS OFF)
350SELECT count(*) FROM kd_point_tbl WHERE p >^ '(5000, 4000)';
351SELECT count(*) FROM kd_point_tbl WHERE p >^ '(5000, 4000)';
352
353EXPLAIN (COSTS OFF)
354SELECT count(*) FROM kd_point_tbl WHERE p ~= '(4585, 365)';
355SELECT count(*) FROM kd_point_tbl WHERE p ~= '(4585, 365)';
356
357EXPLAIN (COSTS OFF)
358SELECT count(*) FROM radix_text_tbl WHERE t = 'P0123456789abcdef';
359SELECT count(*) FROM radix_text_tbl WHERE t = 'P0123456789abcdef';
360
361EXPLAIN (COSTS OFF)
362SELECT count(*) FROM radix_text_tbl WHERE t = 'P0123456789abcde';
363SELECT count(*) FROM radix_text_tbl WHERE t = 'P0123456789abcde';
364
365EXPLAIN (COSTS OFF)
366SELECT count(*) FROM radix_text_tbl WHERE t = 'P0123456789abcdefF';
367SELECT count(*) FROM radix_text_tbl WHERE t = 'P0123456789abcdefF';
368
369EXPLAIN (COSTS OFF)
370SELECT count(*) FROM radix_text_tbl WHERE t <    'Aztec                         Ct  ';
371SELECT count(*) FROM radix_text_tbl WHERE t <    'Aztec                         Ct  ';
372
373EXPLAIN (COSTS OFF)
374SELECT count(*) FROM radix_text_tbl WHERE t ~<~  'Aztec                         Ct  ';
375SELECT count(*) FROM radix_text_tbl WHERE t ~<~  'Aztec                         Ct  ';
376
377EXPLAIN (COSTS OFF)
378SELECT count(*) FROM radix_text_tbl WHERE t <=   'Aztec                         Ct  ';
379SELECT count(*) FROM radix_text_tbl WHERE t <=   'Aztec                         Ct  ';
380
381EXPLAIN (COSTS OFF)
382SELECT count(*) FROM radix_text_tbl WHERE t ~<=~ 'Aztec                         Ct  ';
383SELECT count(*) FROM radix_text_tbl WHERE t ~<=~ 'Aztec                         Ct  ';
384
385EXPLAIN (COSTS OFF)
386SELECT count(*) FROM radix_text_tbl WHERE t =    'Aztec                         Ct  ';
387SELECT count(*) FROM radix_text_tbl WHERE t =    'Aztec                         Ct  ';
388
389EXPLAIN (COSTS OFF)
390SELECT count(*) FROM radix_text_tbl WHERE t =    'Worth                         St  ';
391SELECT count(*) FROM radix_text_tbl WHERE t =    'Worth                         St  ';
392
393EXPLAIN (COSTS OFF)
394SELECT count(*) FROM radix_text_tbl WHERE t >=   'Worth                         St  ';
395SELECT count(*) FROM radix_text_tbl WHERE t >=   'Worth                         St  ';
396
397EXPLAIN (COSTS OFF)
398SELECT count(*) FROM radix_text_tbl WHERE t ~>=~ 'Worth                         St  ';
399SELECT count(*) FROM radix_text_tbl WHERE t ~>=~ 'Worth                         St  ';
400
401EXPLAIN (COSTS OFF)
402SELECT count(*) FROM radix_text_tbl WHERE t >    'Worth                         St  ';
403SELECT count(*) FROM radix_text_tbl WHERE t >    'Worth                         St  ';
404
405EXPLAIN (COSTS OFF)
406SELECT count(*) FROM radix_text_tbl WHERE t ~>~  'Worth                         St  ';
407SELECT count(*) FROM radix_text_tbl WHERE t ~>~  'Worth                         St  ';
408
409EXPLAIN (COSTS OFF)
410SELECT count(*) FROM radix_text_tbl WHERE t ^@	 'Worth';
411SELECT count(*) FROM radix_text_tbl WHERE t ^@	 'Worth';
412
413RESET enable_seqscan;
414RESET enable_indexscan;
415RESET enable_bitmapscan;
416