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