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