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
228-- check ORDER BY distance to NULL
229SELECT (SELECT p FROM kd_point_tbl ORDER BY p <-> pt, p <-> '0,0' LIMIT 1)
230FROM (VALUES (point '1,2'), (NULL), ('1234,5678')) pts(pt);
231
232
233EXPLAIN (COSTS OFF)
234SELECT count(*) FROM radix_text_tbl WHERE t = 'P0123456789abcdef';
235SELECT count(*) FROM radix_text_tbl WHERE t = 'P0123456789abcdef';
236
237EXPLAIN (COSTS OFF)
238SELECT count(*) FROM radix_text_tbl WHERE t = 'P0123456789abcde';
239SELECT count(*) FROM radix_text_tbl WHERE t = 'P0123456789abcde';
240
241EXPLAIN (COSTS OFF)
242SELECT count(*) FROM radix_text_tbl WHERE t = 'P0123456789abcdefF';
243SELECT count(*) FROM radix_text_tbl WHERE t = 'P0123456789abcdefF';
244
245EXPLAIN (COSTS OFF)
246SELECT count(*) FROM radix_text_tbl WHERE t <    'Aztec                         Ct  ';
247SELECT count(*) FROM radix_text_tbl WHERE t <    'Aztec                         Ct  ';
248
249EXPLAIN (COSTS OFF)
250SELECT count(*) FROM radix_text_tbl WHERE t ~<~  'Aztec                         Ct  ';
251SELECT count(*) FROM radix_text_tbl WHERE t ~<~  'Aztec                         Ct  ';
252
253EXPLAIN (COSTS OFF)
254SELECT count(*) FROM radix_text_tbl WHERE t <=   'Aztec                         Ct  ';
255SELECT count(*) FROM radix_text_tbl WHERE t <=   'Aztec                         Ct  ';
256
257EXPLAIN (COSTS OFF)
258SELECT count(*) FROM radix_text_tbl WHERE t ~<=~ 'Aztec                         Ct  ';
259SELECT count(*) FROM radix_text_tbl WHERE t ~<=~ 'Aztec                         Ct  ';
260
261EXPLAIN (COSTS OFF)
262SELECT count(*) FROM radix_text_tbl WHERE t =    'Aztec                         Ct  ';
263SELECT count(*) FROM radix_text_tbl WHERE t =    'Aztec                         Ct  ';
264
265EXPLAIN (COSTS OFF)
266SELECT count(*) FROM radix_text_tbl WHERE t =    'Worth                         St  ';
267SELECT count(*) FROM radix_text_tbl WHERE t =    'Worth                         St  ';
268
269EXPLAIN (COSTS OFF)
270SELECT count(*) FROM radix_text_tbl WHERE t >=   'Worth                         St  ';
271SELECT count(*) FROM radix_text_tbl WHERE t >=   'Worth                         St  ';
272
273EXPLAIN (COSTS OFF)
274SELECT count(*) FROM radix_text_tbl WHERE t ~>=~ 'Worth                         St  ';
275SELECT count(*) FROM radix_text_tbl WHERE t ~>=~ 'Worth                         St  ';
276
277EXPLAIN (COSTS OFF)
278SELECT count(*) FROM radix_text_tbl WHERE t >    'Worth                         St  ';
279SELECT count(*) FROM radix_text_tbl WHERE t >    'Worth                         St  ';
280
281EXPLAIN (COSTS OFF)
282SELECT count(*) FROM radix_text_tbl WHERE t ~>~  'Worth                         St  ';
283SELECT count(*) FROM radix_text_tbl WHERE t ~>~  'Worth                         St  ';
284
285EXPLAIN (COSTS OFF)
286SELECT count(*) FROM radix_text_tbl WHERE t ^@	 'Worth';
287SELECT count(*) FROM radix_text_tbl WHERE t ^@	 'Worth';
288
289-- Now check the results from bitmap indexscan
290SET enable_seqscan = OFF;
291SET enable_indexscan = OFF;
292SET enable_bitmapscan = ON;
293
294EXPLAIN (COSTS OFF)
295SELECT count(*) FROM quad_point_tbl WHERE p IS NULL;
296SELECT count(*) FROM quad_point_tbl WHERE p IS NULL;
297
298EXPLAIN (COSTS OFF)
299SELECT count(*) FROM quad_point_tbl WHERE p IS NOT NULL;
300SELECT count(*) FROM quad_point_tbl WHERE p IS NOT NULL;
301
302EXPLAIN (COSTS OFF)
303SELECT count(*) FROM quad_point_tbl;
304SELECT count(*) FROM quad_point_tbl;
305
306EXPLAIN (COSTS OFF)
307SELECT count(*) FROM quad_point_tbl WHERE p <@ box '(200,200,1000,1000)';
308SELECT count(*) FROM quad_point_tbl WHERE p <@ box '(200,200,1000,1000)';
309
310EXPLAIN (COSTS OFF)
311SELECT count(*) FROM quad_point_tbl WHERE box '(200,200,1000,1000)' @> p;
312SELECT count(*) FROM quad_point_tbl WHERE box '(200,200,1000,1000)' @> p;
313
314EXPLAIN (COSTS OFF)
315SELECT count(*) FROM quad_point_tbl WHERE p << '(5000, 4000)';
316SELECT count(*) FROM quad_point_tbl WHERE p << '(5000, 4000)';
317
318EXPLAIN (COSTS OFF)
319SELECT count(*) FROM quad_point_tbl WHERE p >> '(5000, 4000)';
320SELECT count(*) FROM quad_point_tbl WHERE p >> '(5000, 4000)';
321
322EXPLAIN (COSTS OFF)
323SELECT count(*) FROM quad_point_tbl WHERE p <^ '(5000, 4000)';
324SELECT count(*) FROM quad_point_tbl WHERE p <^ '(5000, 4000)';
325
326EXPLAIN (COSTS OFF)
327SELECT count(*) FROM quad_point_tbl WHERE p >^ '(5000, 4000)';
328SELECT count(*) FROM quad_point_tbl WHERE p >^ '(5000, 4000)';
329
330EXPLAIN (COSTS OFF)
331SELECT count(*) FROM quad_point_tbl WHERE p ~= '(4585, 365)';
332SELECT count(*) FROM quad_point_tbl WHERE p ~= '(4585, 365)';
333
334EXPLAIN (COSTS OFF)
335SELECT count(*) FROM kd_point_tbl WHERE p <@ box '(200,200,1000,1000)';
336SELECT count(*) FROM kd_point_tbl WHERE p <@ box '(200,200,1000,1000)';
337
338EXPLAIN (COSTS OFF)
339SELECT count(*) FROM kd_point_tbl WHERE box '(200,200,1000,1000)' @> p;
340SELECT count(*) FROM kd_point_tbl WHERE box '(200,200,1000,1000)' @> p;
341
342EXPLAIN (COSTS OFF)
343SELECT count(*) FROM kd_point_tbl WHERE p << '(5000, 4000)';
344SELECT count(*) FROM kd_point_tbl WHERE p << '(5000, 4000)';
345
346EXPLAIN (COSTS OFF)
347SELECT count(*) FROM kd_point_tbl WHERE p >> '(5000, 4000)';
348SELECT count(*) FROM kd_point_tbl WHERE p >> '(5000, 4000)';
349
350EXPLAIN (COSTS OFF)
351SELECT count(*) FROM kd_point_tbl WHERE p <^ '(5000, 4000)';
352SELECT count(*) FROM kd_point_tbl WHERE p <^ '(5000, 4000)';
353
354EXPLAIN (COSTS OFF)
355SELECT count(*) FROM kd_point_tbl WHERE p >^ '(5000, 4000)';
356SELECT count(*) FROM kd_point_tbl WHERE p >^ '(5000, 4000)';
357
358EXPLAIN (COSTS OFF)
359SELECT count(*) FROM kd_point_tbl WHERE p ~= '(4585, 365)';
360SELECT count(*) FROM kd_point_tbl WHERE p ~= '(4585, 365)';
361
362EXPLAIN (COSTS OFF)
363SELECT count(*) FROM radix_text_tbl WHERE t = 'P0123456789abcdef';
364SELECT count(*) FROM radix_text_tbl WHERE t = 'P0123456789abcdef';
365
366EXPLAIN (COSTS OFF)
367SELECT count(*) FROM radix_text_tbl WHERE t = 'P0123456789abcde';
368SELECT count(*) FROM radix_text_tbl WHERE t = 'P0123456789abcde';
369
370EXPLAIN (COSTS OFF)
371SELECT count(*) FROM radix_text_tbl WHERE t = 'P0123456789abcdefF';
372SELECT count(*) FROM radix_text_tbl WHERE t = 'P0123456789abcdefF';
373
374EXPLAIN (COSTS OFF)
375SELECT count(*) FROM radix_text_tbl WHERE t <    'Aztec                         Ct  ';
376SELECT count(*) FROM radix_text_tbl WHERE t <    'Aztec                         Ct  ';
377
378EXPLAIN (COSTS OFF)
379SELECT count(*) FROM radix_text_tbl WHERE t ~<~  'Aztec                         Ct  ';
380SELECT count(*) FROM radix_text_tbl WHERE t ~<~  'Aztec                         Ct  ';
381
382EXPLAIN (COSTS OFF)
383SELECT count(*) FROM radix_text_tbl WHERE t <=   'Aztec                         Ct  ';
384SELECT count(*) FROM radix_text_tbl WHERE t <=   'Aztec                         Ct  ';
385
386EXPLAIN (COSTS OFF)
387SELECT count(*) FROM radix_text_tbl WHERE t ~<=~ 'Aztec                         Ct  ';
388SELECT count(*) FROM radix_text_tbl WHERE t ~<=~ 'Aztec                         Ct  ';
389
390EXPLAIN (COSTS OFF)
391SELECT count(*) FROM radix_text_tbl WHERE t =    'Aztec                         Ct  ';
392SELECT count(*) FROM radix_text_tbl WHERE t =    'Aztec                         Ct  ';
393
394EXPLAIN (COSTS OFF)
395SELECT count(*) FROM radix_text_tbl WHERE t =    'Worth                         St  ';
396SELECT count(*) FROM radix_text_tbl WHERE t =    'Worth                         St  ';
397
398EXPLAIN (COSTS OFF)
399SELECT count(*) FROM radix_text_tbl WHERE t >=   'Worth                         St  ';
400SELECT count(*) FROM radix_text_tbl WHERE t >=   'Worth                         St  ';
401
402EXPLAIN (COSTS OFF)
403SELECT count(*) FROM radix_text_tbl WHERE t ~>=~ 'Worth                         St  ';
404SELECT count(*) FROM radix_text_tbl WHERE t ~>=~ 'Worth                         St  ';
405
406EXPLAIN (COSTS OFF)
407SELECT count(*) FROM radix_text_tbl WHERE t >    'Worth                         St  ';
408SELECT count(*) FROM radix_text_tbl WHERE t >    'Worth                         St  ';
409
410EXPLAIN (COSTS OFF)
411SELECT count(*) FROM radix_text_tbl WHERE t ~>~  'Worth                         St  ';
412SELECT count(*) FROM radix_text_tbl WHERE t ~>~  'Worth                         St  ';
413
414EXPLAIN (COSTS OFF)
415SELECT count(*) FROM radix_text_tbl WHERE t ^@	 'Worth';
416SELECT count(*) FROM radix_text_tbl WHERE t ^@	 'Worth';
417
418RESET enable_seqscan;
419RESET enable_indexscan;
420RESET enable_bitmapscan;
421