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