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