1-- 2-- SP-GiST index tests 3-- 4CREATE TABLE quad_point_tbl AS 5 SELECT point(unique1,unique2) AS p FROM tenk1; 6INSERT INTO quad_point_tbl 7 SELECT '(333.0,400.0)'::point FROM generate_series(1,1000); 8INSERT INTO quad_point_tbl VALUES (NULL), (NULL), (NULL); 9CREATE INDEX sp_quad_ind ON quad_point_tbl USING spgist (p); 10CREATE TABLE kd_point_tbl AS SELECT * FROM quad_point_tbl; 11CREATE INDEX sp_kd_ind ON kd_point_tbl USING spgist (p kd_point_ops); 12CREATE TABLE radix_text_tbl AS 13 SELECT name AS t FROM road WHERE name !~ '^[0-9]'; 14INSERT INTO radix_text_tbl 15 SELECT 'P0123456789abcdef' FROM generate_series(1,1000); 16INSERT INTO radix_text_tbl VALUES ('P0123456789abcde'); 17INSERT INTO radix_text_tbl VALUES ('P0123456789abcdefF'); 18CREATE INDEX sp_radix_ind ON radix_text_tbl USING spgist (t); 19-- get non-indexed results for comparison purposes 20SET enable_seqscan = ON; 21SET enable_indexscan = OFF; 22SET enable_bitmapscan = OFF; 23SELECT count(*) FROM quad_point_tbl WHERE p IS NULL; 24 count 25------- 26 3 27(1 row) 28 29SELECT count(*) FROM quad_point_tbl WHERE p IS NOT NULL; 30 count 31------- 32 11000 33(1 row) 34 35SELECT count(*) FROM quad_point_tbl; 36 count 37------- 38 11003 39(1 row) 40 41SELECT count(*) FROM quad_point_tbl WHERE p <@ box '(200,200,1000,1000)'; 42 count 43------- 44 1057 45(1 row) 46 47SELECT count(*) FROM quad_point_tbl WHERE box '(200,200,1000,1000)' @> p; 48 count 49------- 50 1057 51(1 row) 52 53SELECT count(*) FROM quad_point_tbl WHERE p << '(5000, 4000)'; 54 count 55------- 56 6000 57(1 row) 58 59SELECT count(*) FROM quad_point_tbl WHERE p >> '(5000, 4000)'; 60 count 61------- 62 4999 63(1 row) 64 65SELECT count(*) FROM quad_point_tbl WHERE p <<| '(5000, 4000)'; 66 count 67------- 68 5000 69(1 row) 70 71SELECT count(*) FROM quad_point_tbl WHERE p |>> '(5000, 4000)'; 72 count 73------- 74 5999 75(1 row) 76 77SELECT count(*) FROM quad_point_tbl WHERE p ~= '(4585, 365)'; 78 count 79------- 80 1 81(1 row) 82 83CREATE TEMP TABLE quad_point_tbl_ord_seq1 AS 84SELECT row_number() OVER (ORDER BY p <-> '0,0') n, p <-> '0,0' dist, p 85FROM quad_point_tbl; 86CREATE TEMP TABLE quad_point_tbl_ord_seq2 AS 87SELECT row_number() OVER (ORDER BY p <-> '0,0') n, p <-> '0,0' dist, p 88FROM quad_point_tbl WHERE p <@ box '(200,200,1000,1000)'; 89CREATE TEMP TABLE quad_point_tbl_ord_seq3 AS 90SELECT row_number() OVER (ORDER BY p <-> '333,400') n, p <-> '333,400' dist, p 91FROM quad_point_tbl WHERE p IS NOT NULL; 92SELECT count(*) FROM radix_text_tbl WHERE t = 'P0123456789abcdef'; 93 count 94------- 95 1000 96(1 row) 97 98SELECT count(*) FROM radix_text_tbl WHERE t = 'P0123456789abcde'; 99 count 100------- 101 1 102(1 row) 103 104SELECT count(*) FROM radix_text_tbl WHERE t = 'P0123456789abcdefF'; 105 count 106------- 107 1 108(1 row) 109 110SELECT count(*) FROM radix_text_tbl WHERE t < 'Aztec Ct '; 111 count 112------- 113 272 114(1 row) 115 116SELECT count(*) FROM radix_text_tbl WHERE t ~<~ 'Aztec Ct '; 117 count 118------- 119 272 120(1 row) 121 122SELECT count(*) FROM radix_text_tbl WHERE t <= 'Aztec Ct '; 123 count 124------- 125 273 126(1 row) 127 128SELECT count(*) FROM radix_text_tbl WHERE t ~<=~ 'Aztec Ct '; 129 count 130------- 131 273 132(1 row) 133 134SELECT count(*) FROM radix_text_tbl WHERE t = 'Aztec Ct '; 135 count 136------- 137 1 138(1 row) 139 140SELECT count(*) FROM radix_text_tbl WHERE t = 'Worth St '; 141 count 142------- 143 2 144(1 row) 145 146SELECT count(*) FROM radix_text_tbl WHERE t >= 'Worth St '; 147 count 148------- 149 50 150(1 row) 151 152SELECT count(*) FROM radix_text_tbl WHERE t ~>=~ 'Worth St '; 153 count 154------- 155 50 156(1 row) 157 158SELECT count(*) FROM radix_text_tbl WHERE t > 'Worth St '; 159 count 160------- 161 48 162(1 row) 163 164SELECT count(*) FROM radix_text_tbl WHERE t ~>~ 'Worth St '; 165 count 166------- 167 48 168(1 row) 169 170SELECT count(*) FROM radix_text_tbl WHERE t ^@ 'Worth'; 171 count 172------- 173 2 174(1 row) 175 176-- Now check the results from plain indexscan 177SET enable_seqscan = OFF; 178SET enable_indexscan = ON; 179SET enable_bitmapscan = OFF; 180EXPLAIN (COSTS OFF) 181SELECT count(*) FROM quad_point_tbl WHERE p IS NULL; 182 QUERY PLAN 183----------------------------------------------------------- 184 Aggregate 185 -> Index Only Scan using sp_quad_ind on quad_point_tbl 186 Index Cond: (p IS NULL) 187(3 rows) 188 189SELECT count(*) FROM quad_point_tbl WHERE p IS NULL; 190 count 191------- 192 3 193(1 row) 194 195EXPLAIN (COSTS OFF) 196SELECT count(*) FROM quad_point_tbl WHERE p IS NOT NULL; 197 QUERY PLAN 198----------------------------------------------------------- 199 Aggregate 200 -> Index Only Scan using sp_quad_ind on quad_point_tbl 201 Index Cond: (p IS NOT NULL) 202(3 rows) 203 204SELECT count(*) FROM quad_point_tbl WHERE p IS NOT NULL; 205 count 206------- 207 11000 208(1 row) 209 210EXPLAIN (COSTS OFF) 211SELECT count(*) FROM quad_point_tbl; 212 QUERY PLAN 213----------------------------------------------------------- 214 Aggregate 215 -> Index Only Scan using sp_quad_ind on quad_point_tbl 216(2 rows) 217 218SELECT count(*) FROM quad_point_tbl; 219 count 220------- 221 11003 222(1 row) 223 224EXPLAIN (COSTS OFF) 225SELECT count(*) FROM quad_point_tbl WHERE p <@ box '(200,200,1000,1000)'; 226 QUERY PLAN 227----------------------------------------------------------- 228 Aggregate 229 -> Index Only Scan using sp_quad_ind on quad_point_tbl 230 Index Cond: (p <@ '(1000,1000),(200,200)'::box) 231(3 rows) 232 233SELECT count(*) FROM quad_point_tbl WHERE p <@ box '(200,200,1000,1000)'; 234 count 235------- 236 1057 237(1 row) 238 239EXPLAIN (COSTS OFF) 240SELECT count(*) FROM quad_point_tbl WHERE box '(200,200,1000,1000)' @> p; 241 QUERY PLAN 242----------------------------------------------------------- 243 Aggregate 244 -> Index Only Scan using sp_quad_ind on quad_point_tbl 245 Index Cond: (p <@ '(1000,1000),(200,200)'::box) 246(3 rows) 247 248SELECT count(*) FROM quad_point_tbl WHERE box '(200,200,1000,1000)' @> p; 249 count 250------- 251 1057 252(1 row) 253 254EXPLAIN (COSTS OFF) 255SELECT count(*) FROM quad_point_tbl WHERE p << '(5000, 4000)'; 256 QUERY PLAN 257----------------------------------------------------------- 258 Aggregate 259 -> Index Only Scan using sp_quad_ind on quad_point_tbl 260 Index Cond: (p << '(5000,4000)'::point) 261(3 rows) 262 263SELECT count(*) FROM quad_point_tbl WHERE p << '(5000, 4000)'; 264 count 265------- 266 6000 267(1 row) 268 269EXPLAIN (COSTS OFF) 270SELECT count(*) FROM quad_point_tbl WHERE p >> '(5000, 4000)'; 271 QUERY PLAN 272----------------------------------------------------------- 273 Aggregate 274 -> Index Only Scan using sp_quad_ind on quad_point_tbl 275 Index Cond: (p >> '(5000,4000)'::point) 276(3 rows) 277 278SELECT count(*) FROM quad_point_tbl WHERE p >> '(5000, 4000)'; 279 count 280------- 281 4999 282(1 row) 283 284EXPLAIN (COSTS OFF) 285SELECT count(*) FROM quad_point_tbl WHERE p <<| '(5000, 4000)'; 286 QUERY PLAN 287----------------------------------------------------------- 288 Aggregate 289 -> Index Only Scan using sp_quad_ind on quad_point_tbl 290 Index Cond: (p <<| '(5000,4000)'::point) 291(3 rows) 292 293SELECT count(*) FROM quad_point_tbl WHERE p <<| '(5000, 4000)'; 294 count 295------- 296 5000 297(1 row) 298 299EXPLAIN (COSTS OFF) 300SELECT count(*) FROM quad_point_tbl WHERE p |>> '(5000, 4000)'; 301 QUERY PLAN 302----------------------------------------------------------- 303 Aggregate 304 -> Index Only Scan using sp_quad_ind on quad_point_tbl 305 Index Cond: (p |>> '(5000,4000)'::point) 306(3 rows) 307 308SELECT count(*) FROM quad_point_tbl WHERE p |>> '(5000, 4000)'; 309 count 310------- 311 5999 312(1 row) 313 314EXPLAIN (COSTS OFF) 315SELECT count(*) FROM quad_point_tbl WHERE p ~= '(4585, 365)'; 316 QUERY PLAN 317----------------------------------------------------------- 318 Aggregate 319 -> Index Only Scan using sp_quad_ind on quad_point_tbl 320 Index Cond: (p ~= '(4585,365)'::point) 321(3 rows) 322 323SELECT count(*) FROM quad_point_tbl WHERE p ~= '(4585, 365)'; 324 count 325------- 326 1 327(1 row) 328 329EXPLAIN (COSTS OFF) 330SELECT row_number() OVER (ORDER BY p <-> '0,0') n, p <-> '0,0' dist, p 331FROM quad_point_tbl; 332 QUERY PLAN 333----------------------------------------------------------- 334 WindowAgg 335 -> Index Only Scan using sp_quad_ind on quad_point_tbl 336 Order By: (p <-> '(0,0)'::point) 337(3 rows) 338 339CREATE TEMP TABLE quad_point_tbl_ord_idx1 AS 340SELECT row_number() OVER (ORDER BY p <-> '0,0') n, p <-> '0,0' dist, p 341FROM quad_point_tbl; 342SELECT * FROM quad_point_tbl_ord_seq1 seq FULL JOIN quad_point_tbl_ord_idx1 idx 343ON seq.n = idx.n 344WHERE seq.dist IS DISTINCT FROM idx.dist; 345 n | dist | p | n | dist | p 346---+------+---+---+------+--- 347(0 rows) 348 349EXPLAIN (COSTS OFF) 350SELECT row_number() OVER (ORDER BY p <-> '0,0') n, p <-> '0,0' dist, p 351FROM quad_point_tbl WHERE p <@ box '(200,200,1000,1000)'; 352 QUERY PLAN 353----------------------------------------------------------- 354 WindowAgg 355 -> Index Only Scan using sp_quad_ind on quad_point_tbl 356 Index Cond: (p <@ '(1000,1000),(200,200)'::box) 357 Order By: (p <-> '(0,0)'::point) 358(4 rows) 359 360CREATE TEMP TABLE quad_point_tbl_ord_idx2 AS 361SELECT row_number() OVER (ORDER BY p <-> '0,0') n, p <-> '0,0' dist, p 362FROM quad_point_tbl WHERE p <@ box '(200,200,1000,1000)'; 363SELECT * FROM quad_point_tbl_ord_seq2 seq FULL JOIN quad_point_tbl_ord_idx2 idx 364ON seq.n = idx.n 365WHERE seq.dist IS DISTINCT FROM idx.dist; 366 n | dist | p | n | dist | p 367---+------+---+---+------+--- 368(0 rows) 369 370EXPLAIN (COSTS OFF) 371SELECT row_number() OVER (ORDER BY p <-> '333,400') n, p <-> '333,400' dist, p 372FROM quad_point_tbl WHERE p IS NOT NULL; 373 QUERY PLAN 374----------------------------------------------------------- 375 WindowAgg 376 -> Index Only Scan using sp_quad_ind on quad_point_tbl 377 Index Cond: (p IS NOT NULL) 378 Order By: (p <-> '(333,400)'::point) 379(4 rows) 380 381CREATE TEMP TABLE quad_point_tbl_ord_idx3 AS 382SELECT row_number() OVER (ORDER BY p <-> '333,400') n, p <-> '333,400' dist, p 383FROM quad_point_tbl WHERE p IS NOT NULL; 384SELECT * FROM quad_point_tbl_ord_seq3 seq FULL JOIN quad_point_tbl_ord_idx3 idx 385ON seq.n = idx.n 386WHERE seq.dist IS DISTINCT FROM idx.dist; 387 n | dist | p | n | dist | p 388---+------+---+---+------+--- 389(0 rows) 390 391EXPLAIN (COSTS OFF) 392SELECT count(*) FROM kd_point_tbl WHERE p <@ box '(200,200,1000,1000)'; 393 QUERY PLAN 394--------------------------------------------------------- 395 Aggregate 396 -> Index Only Scan using sp_kd_ind on kd_point_tbl 397 Index Cond: (p <@ '(1000,1000),(200,200)'::box) 398(3 rows) 399 400SELECT count(*) FROM kd_point_tbl WHERE p <@ box '(200,200,1000,1000)'; 401 count 402------- 403 1057 404(1 row) 405 406EXPLAIN (COSTS OFF) 407SELECT count(*) FROM kd_point_tbl WHERE box '(200,200,1000,1000)' @> p; 408 QUERY PLAN 409--------------------------------------------------------- 410 Aggregate 411 -> Index Only Scan using sp_kd_ind on kd_point_tbl 412 Index Cond: (p <@ '(1000,1000),(200,200)'::box) 413(3 rows) 414 415SELECT count(*) FROM kd_point_tbl WHERE box '(200,200,1000,1000)' @> p; 416 count 417------- 418 1057 419(1 row) 420 421EXPLAIN (COSTS OFF) 422SELECT count(*) FROM kd_point_tbl WHERE p << '(5000, 4000)'; 423 QUERY PLAN 424------------------------------------------------------- 425 Aggregate 426 -> Index Only Scan using sp_kd_ind on kd_point_tbl 427 Index Cond: (p << '(5000,4000)'::point) 428(3 rows) 429 430SELECT count(*) FROM kd_point_tbl WHERE p << '(5000, 4000)'; 431 count 432------- 433 6000 434(1 row) 435 436EXPLAIN (COSTS OFF) 437SELECT count(*) FROM kd_point_tbl WHERE p >> '(5000, 4000)'; 438 QUERY PLAN 439------------------------------------------------------- 440 Aggregate 441 -> Index Only Scan using sp_kd_ind on kd_point_tbl 442 Index Cond: (p >> '(5000,4000)'::point) 443(3 rows) 444 445SELECT count(*) FROM kd_point_tbl WHERE p >> '(5000, 4000)'; 446 count 447------- 448 4999 449(1 row) 450 451EXPLAIN (COSTS OFF) 452SELECT count(*) FROM kd_point_tbl WHERE p <<| '(5000, 4000)'; 453 QUERY PLAN 454------------------------------------------------------- 455 Aggregate 456 -> Index Only Scan using sp_kd_ind on kd_point_tbl 457 Index Cond: (p <<| '(5000,4000)'::point) 458(3 rows) 459 460SELECT count(*) FROM kd_point_tbl WHERE p <<| '(5000, 4000)'; 461 count 462------- 463 5000 464(1 row) 465 466EXPLAIN (COSTS OFF) 467SELECT count(*) FROM kd_point_tbl WHERE p |>> '(5000, 4000)'; 468 QUERY PLAN 469------------------------------------------------------- 470 Aggregate 471 -> Index Only Scan using sp_kd_ind on kd_point_tbl 472 Index Cond: (p |>> '(5000,4000)'::point) 473(3 rows) 474 475SELECT count(*) FROM kd_point_tbl WHERE p |>> '(5000, 4000)'; 476 count 477------- 478 5999 479(1 row) 480 481EXPLAIN (COSTS OFF) 482SELECT count(*) FROM kd_point_tbl WHERE p ~= '(4585, 365)'; 483 QUERY PLAN 484------------------------------------------------------- 485 Aggregate 486 -> Index Only Scan using sp_kd_ind on kd_point_tbl 487 Index Cond: (p ~= '(4585,365)'::point) 488(3 rows) 489 490SELECT count(*) FROM kd_point_tbl WHERE p ~= '(4585, 365)'; 491 count 492------- 493 1 494(1 row) 495 496EXPLAIN (COSTS OFF) 497SELECT row_number() OVER (ORDER BY p <-> '0,0') n, p <-> '0,0' dist, p 498FROM kd_point_tbl; 499 QUERY PLAN 500------------------------------------------------------- 501 WindowAgg 502 -> Index Only Scan using sp_kd_ind on kd_point_tbl 503 Order By: (p <-> '(0,0)'::point) 504(3 rows) 505 506CREATE TEMP TABLE kd_point_tbl_ord_idx1 AS 507SELECT row_number() OVER (ORDER BY p <-> '0,0') n, p <-> '0,0' dist, p 508FROM kd_point_tbl; 509SELECT * FROM quad_point_tbl_ord_seq1 seq FULL JOIN kd_point_tbl_ord_idx1 idx 510ON seq.n = idx.n 511WHERE seq.dist IS DISTINCT FROM idx.dist; 512 n | dist | p | n | dist | p 513---+------+---+---+------+--- 514(0 rows) 515 516EXPLAIN (COSTS OFF) 517SELECT row_number() OVER (ORDER BY p <-> '0,0') n, p <-> '0,0' dist, p 518FROM kd_point_tbl WHERE p <@ box '(200,200,1000,1000)'; 519 QUERY PLAN 520--------------------------------------------------------- 521 WindowAgg 522 -> Index Only Scan using sp_kd_ind on kd_point_tbl 523 Index Cond: (p <@ '(1000,1000),(200,200)'::box) 524 Order By: (p <-> '(0,0)'::point) 525(4 rows) 526 527CREATE TEMP TABLE kd_point_tbl_ord_idx2 AS 528SELECT row_number() OVER (ORDER BY p <-> '0,0') n, p <-> '0,0' dist, p 529FROM kd_point_tbl WHERE p <@ box '(200,200,1000,1000)'; 530SELECT * FROM quad_point_tbl_ord_seq2 seq FULL JOIN kd_point_tbl_ord_idx2 idx 531ON seq.n = idx.n 532WHERE seq.dist IS DISTINCT FROM idx.dist; 533 n | dist | p | n | dist | p 534---+------+---+---+------+--- 535(0 rows) 536 537EXPLAIN (COSTS OFF) 538SELECT row_number() OVER (ORDER BY p <-> '333,400') n, p <-> '333,400' dist, p 539FROM kd_point_tbl WHERE p IS NOT NULL; 540 QUERY PLAN 541------------------------------------------------------- 542 WindowAgg 543 -> Index Only Scan using sp_kd_ind on kd_point_tbl 544 Index Cond: (p IS NOT NULL) 545 Order By: (p <-> '(333,400)'::point) 546(4 rows) 547 548CREATE TEMP TABLE kd_point_tbl_ord_idx3 AS 549SELECT row_number() OVER (ORDER BY p <-> '333,400') n, p <-> '333,400' dist, p 550FROM kd_point_tbl WHERE p IS NOT NULL; 551SELECT * FROM quad_point_tbl_ord_seq3 seq FULL JOIN kd_point_tbl_ord_idx3 idx 552ON seq.n = idx.n 553WHERE seq.dist IS DISTINCT FROM idx.dist; 554 n | dist | p | n | dist | p 555---+------+---+---+------+--- 556(0 rows) 557 558-- test KNN scan with included columns 559-- the distance numbers are not exactly the same across platforms 560SET extra_float_digits = 0; 561CREATE INDEX ON quad_point_tbl_ord_seq1 USING spgist(p) INCLUDE(dist); 562EXPLAIN (COSTS OFF) 563SELECT p, dist FROM quad_point_tbl_ord_seq1 ORDER BY p <-> '0,0' LIMIT 10; 564 QUERY PLAN 565------------------------------------------------------------------------------------------- 566 Limit 567 -> Index Only Scan using quad_point_tbl_ord_seq1_p_dist_idx on quad_point_tbl_ord_seq1 568 Order By: (p <-> '(0,0)'::point) 569(3 rows) 570 571SELECT p, dist FROM quad_point_tbl_ord_seq1 ORDER BY p <-> '0,0' LIMIT 10; 572 p | dist 573-----------+------------------ 574 (59,21) | 62.6258732474047 575 (88,104) | 136.235090927411 576 (39,143) | 148.222805262888 577 (139,160) | 211.945747775227 578 (209,38) | 212.42645786248 579 (157,156) | 221.325552072055 580 (175,150) | 230.488611432322 581 (236,34) | 238.436574375661 582 (263,28) | 264.486294540946 583 (322,53) | 326.33265236565 584(10 rows) 585 586RESET extra_float_digits; 587-- check ORDER BY distance to NULL 588SELECT (SELECT p FROM kd_point_tbl ORDER BY p <-> pt, p <-> '0,0' LIMIT 1) 589FROM (VALUES (point '1,2'), (NULL), ('1234,5678')) pts(pt); 590 p 591------------- 592 (59,21) 593 (59,21) 594 (1239,5647) 595(3 rows) 596 597EXPLAIN (COSTS OFF) 598SELECT count(*) FROM radix_text_tbl WHERE t = 'P0123456789abcdef'; 599 QUERY PLAN 600------------------------------------------------------------ 601 Aggregate 602 -> Index Only Scan using sp_radix_ind on radix_text_tbl 603 Index Cond: (t = 'P0123456789abcdef'::text) 604(3 rows) 605 606SELECT count(*) FROM radix_text_tbl WHERE t = 'P0123456789abcdef'; 607 count 608------- 609 1000 610(1 row) 611 612EXPLAIN (COSTS OFF) 613SELECT count(*) FROM radix_text_tbl WHERE t = 'P0123456789abcde'; 614 QUERY PLAN 615------------------------------------------------------------ 616 Aggregate 617 -> Index Only Scan using sp_radix_ind on radix_text_tbl 618 Index Cond: (t = 'P0123456789abcde'::text) 619(3 rows) 620 621SELECT count(*) FROM radix_text_tbl WHERE t = 'P0123456789abcde'; 622 count 623------- 624 1 625(1 row) 626 627EXPLAIN (COSTS OFF) 628SELECT count(*) FROM radix_text_tbl WHERE t = 'P0123456789abcdefF'; 629 QUERY PLAN 630------------------------------------------------------------ 631 Aggregate 632 -> Index Only Scan using sp_radix_ind on radix_text_tbl 633 Index Cond: (t = 'P0123456789abcdefF'::text) 634(3 rows) 635 636SELECT count(*) FROM radix_text_tbl WHERE t = 'P0123456789abcdefF'; 637 count 638------- 639 1 640(1 row) 641 642EXPLAIN (COSTS OFF) 643SELECT count(*) FROM radix_text_tbl WHERE t < 'Aztec Ct '; 644 QUERY PLAN 645---------------------------------------------------------------------- 646 Aggregate 647 -> Index Only Scan using sp_radix_ind on radix_text_tbl 648 Index Cond: (t < 'Aztec Ct '::text) 649(3 rows) 650 651SELECT count(*) FROM radix_text_tbl WHERE t < 'Aztec Ct '; 652 count 653------- 654 272 655(1 row) 656 657EXPLAIN (COSTS OFF) 658SELECT count(*) FROM radix_text_tbl WHERE t ~<~ 'Aztec Ct '; 659 QUERY PLAN 660------------------------------------------------------------------------ 661 Aggregate 662 -> Index Only Scan using sp_radix_ind on radix_text_tbl 663 Index Cond: (t ~<~ 'Aztec Ct '::text) 664(3 rows) 665 666SELECT count(*) FROM radix_text_tbl WHERE t ~<~ 'Aztec Ct '; 667 count 668------- 669 272 670(1 row) 671 672EXPLAIN (COSTS OFF) 673SELECT count(*) FROM radix_text_tbl WHERE t <= 'Aztec Ct '; 674 QUERY PLAN 675----------------------------------------------------------------------- 676 Aggregate 677 -> Index Only Scan using sp_radix_ind on radix_text_tbl 678 Index Cond: (t <= 'Aztec Ct '::text) 679(3 rows) 680 681SELECT count(*) FROM radix_text_tbl WHERE t <= 'Aztec Ct '; 682 count 683------- 684 273 685(1 row) 686 687EXPLAIN (COSTS OFF) 688SELECT count(*) FROM radix_text_tbl WHERE t ~<=~ 'Aztec Ct '; 689 QUERY PLAN 690------------------------------------------------------------------------- 691 Aggregate 692 -> Index Only Scan using sp_radix_ind on radix_text_tbl 693 Index Cond: (t ~<=~ 'Aztec Ct '::text) 694(3 rows) 695 696SELECT count(*) FROM radix_text_tbl WHERE t ~<=~ 'Aztec Ct '; 697 count 698------- 699 273 700(1 row) 701 702EXPLAIN (COSTS OFF) 703SELECT count(*) FROM radix_text_tbl WHERE t = 'Aztec Ct '; 704 QUERY PLAN 705---------------------------------------------------------------------- 706 Aggregate 707 -> Index Only Scan using sp_radix_ind on radix_text_tbl 708 Index Cond: (t = 'Aztec Ct '::text) 709(3 rows) 710 711SELECT count(*) FROM radix_text_tbl WHERE t = 'Aztec Ct '; 712 count 713------- 714 1 715(1 row) 716 717EXPLAIN (COSTS OFF) 718SELECT count(*) FROM radix_text_tbl WHERE t = 'Worth St '; 719 QUERY PLAN 720---------------------------------------------------------------------- 721 Aggregate 722 -> Index Only Scan using sp_radix_ind on radix_text_tbl 723 Index Cond: (t = 'Worth St '::text) 724(3 rows) 725 726SELECT count(*) FROM radix_text_tbl WHERE t = 'Worth St '; 727 count 728------- 729 2 730(1 row) 731 732EXPLAIN (COSTS OFF) 733SELECT count(*) FROM radix_text_tbl WHERE t >= 'Worth St '; 734 QUERY PLAN 735----------------------------------------------------------------------- 736 Aggregate 737 -> Index Only Scan using sp_radix_ind on radix_text_tbl 738 Index Cond: (t >= 'Worth St '::text) 739(3 rows) 740 741SELECT count(*) FROM radix_text_tbl WHERE t >= 'Worth St '; 742 count 743------- 744 50 745(1 row) 746 747EXPLAIN (COSTS OFF) 748SELECT count(*) FROM radix_text_tbl WHERE t ~>=~ 'Worth St '; 749 QUERY PLAN 750------------------------------------------------------------------------- 751 Aggregate 752 -> Index Only Scan using sp_radix_ind on radix_text_tbl 753 Index Cond: (t ~>=~ 'Worth St '::text) 754(3 rows) 755 756SELECT count(*) FROM radix_text_tbl WHERE t ~>=~ 'Worth St '; 757 count 758------- 759 50 760(1 row) 761 762EXPLAIN (COSTS OFF) 763SELECT count(*) FROM radix_text_tbl WHERE t > 'Worth St '; 764 QUERY PLAN 765---------------------------------------------------------------------- 766 Aggregate 767 -> Index Only Scan using sp_radix_ind on radix_text_tbl 768 Index Cond: (t > 'Worth St '::text) 769(3 rows) 770 771SELECT count(*) FROM radix_text_tbl WHERE t > 'Worth St '; 772 count 773------- 774 48 775(1 row) 776 777EXPLAIN (COSTS OFF) 778SELECT count(*) FROM radix_text_tbl WHERE t ~>~ 'Worth St '; 779 QUERY PLAN 780------------------------------------------------------------------------ 781 Aggregate 782 -> Index Only Scan using sp_radix_ind on radix_text_tbl 783 Index Cond: (t ~>~ 'Worth St '::text) 784(3 rows) 785 786SELECT count(*) FROM radix_text_tbl WHERE t ~>~ 'Worth St '; 787 count 788------- 789 48 790(1 row) 791 792EXPLAIN (COSTS OFF) 793SELECT count(*) FROM radix_text_tbl WHERE t ^@ 'Worth'; 794 QUERY PLAN 795------------------------------------------------------------ 796 Aggregate 797 -> Index Only Scan using sp_radix_ind on radix_text_tbl 798 Index Cond: (t ^@ 'Worth'::text) 799(3 rows) 800 801SELECT count(*) FROM radix_text_tbl WHERE t ^@ 'Worth'; 802 count 803------- 804 2 805(1 row) 806 807-- Now check the results from bitmap indexscan 808SET enable_seqscan = OFF; 809SET enable_indexscan = OFF; 810SET enable_bitmapscan = ON; 811EXPLAIN (COSTS OFF) 812SELECT count(*) FROM quad_point_tbl WHERE p IS NULL; 813 QUERY PLAN 814---------------------------------------------- 815 Aggregate 816 -> Bitmap Heap Scan on quad_point_tbl 817 Recheck Cond: (p IS NULL) 818 -> Bitmap Index Scan on sp_quad_ind 819 Index Cond: (p IS NULL) 820(5 rows) 821 822SELECT count(*) FROM quad_point_tbl WHERE p IS NULL; 823 count 824------- 825 3 826(1 row) 827 828EXPLAIN (COSTS OFF) 829SELECT count(*) FROM quad_point_tbl WHERE p IS NOT NULL; 830 QUERY PLAN 831---------------------------------------------- 832 Aggregate 833 -> Bitmap Heap Scan on quad_point_tbl 834 Recheck Cond: (p IS NOT NULL) 835 -> Bitmap Index Scan on sp_quad_ind 836 Index Cond: (p IS NOT NULL) 837(5 rows) 838 839SELECT count(*) FROM quad_point_tbl WHERE p IS NOT NULL; 840 count 841------- 842 11000 843(1 row) 844 845EXPLAIN (COSTS OFF) 846SELECT count(*) FROM quad_point_tbl; 847 QUERY PLAN 848---------------------------------------------- 849 Aggregate 850 -> Bitmap Heap Scan on quad_point_tbl 851 -> Bitmap Index Scan on sp_quad_ind 852(3 rows) 853 854SELECT count(*) FROM quad_point_tbl; 855 count 856------- 857 11003 858(1 row) 859 860EXPLAIN (COSTS OFF) 861SELECT count(*) FROM quad_point_tbl WHERE p <@ box '(200,200,1000,1000)'; 862 QUERY PLAN 863--------------------------------------------------------------- 864 Aggregate 865 -> Bitmap Heap Scan on quad_point_tbl 866 Recheck Cond: (p <@ '(1000,1000),(200,200)'::box) 867 -> Bitmap Index Scan on sp_quad_ind 868 Index Cond: (p <@ '(1000,1000),(200,200)'::box) 869(5 rows) 870 871SELECT count(*) FROM quad_point_tbl WHERE p <@ box '(200,200,1000,1000)'; 872 count 873------- 874 1057 875(1 row) 876 877EXPLAIN (COSTS OFF) 878SELECT count(*) FROM quad_point_tbl WHERE box '(200,200,1000,1000)' @> p; 879 QUERY PLAN 880--------------------------------------------------------------- 881 Aggregate 882 -> Bitmap Heap Scan on quad_point_tbl 883 Recheck Cond: ('(1000,1000),(200,200)'::box @> p) 884 -> Bitmap Index Scan on sp_quad_ind 885 Index Cond: (p <@ '(1000,1000),(200,200)'::box) 886(5 rows) 887 888SELECT count(*) FROM quad_point_tbl WHERE box '(200,200,1000,1000)' @> p; 889 count 890------- 891 1057 892(1 row) 893 894EXPLAIN (COSTS OFF) 895SELECT count(*) FROM quad_point_tbl WHERE p << '(5000, 4000)'; 896 QUERY PLAN 897------------------------------------------------------- 898 Aggregate 899 -> Bitmap Heap Scan on quad_point_tbl 900 Recheck Cond: (p << '(5000,4000)'::point) 901 -> Bitmap Index Scan on sp_quad_ind 902 Index Cond: (p << '(5000,4000)'::point) 903(5 rows) 904 905SELECT count(*) FROM quad_point_tbl WHERE p << '(5000, 4000)'; 906 count 907------- 908 6000 909(1 row) 910 911EXPLAIN (COSTS OFF) 912SELECT count(*) FROM quad_point_tbl WHERE p >> '(5000, 4000)'; 913 QUERY PLAN 914------------------------------------------------------- 915 Aggregate 916 -> Bitmap Heap Scan on quad_point_tbl 917 Recheck Cond: (p >> '(5000,4000)'::point) 918 -> Bitmap Index Scan on sp_quad_ind 919 Index Cond: (p >> '(5000,4000)'::point) 920(5 rows) 921 922SELECT count(*) FROM quad_point_tbl WHERE p >> '(5000, 4000)'; 923 count 924------- 925 4999 926(1 row) 927 928EXPLAIN (COSTS OFF) 929SELECT count(*) FROM quad_point_tbl WHERE p <<| '(5000, 4000)'; 930 QUERY PLAN 931-------------------------------------------------------- 932 Aggregate 933 -> Bitmap Heap Scan on quad_point_tbl 934 Recheck Cond: (p <<| '(5000,4000)'::point) 935 -> Bitmap Index Scan on sp_quad_ind 936 Index Cond: (p <<| '(5000,4000)'::point) 937(5 rows) 938 939SELECT count(*) FROM quad_point_tbl WHERE p <<| '(5000, 4000)'; 940 count 941------- 942 5000 943(1 row) 944 945EXPLAIN (COSTS OFF) 946SELECT count(*) FROM quad_point_tbl WHERE p |>> '(5000, 4000)'; 947 QUERY PLAN 948-------------------------------------------------------- 949 Aggregate 950 -> Bitmap Heap Scan on quad_point_tbl 951 Recheck Cond: (p |>> '(5000,4000)'::point) 952 -> Bitmap Index Scan on sp_quad_ind 953 Index Cond: (p |>> '(5000,4000)'::point) 954(5 rows) 955 956SELECT count(*) FROM quad_point_tbl WHERE p |>> '(5000, 4000)'; 957 count 958------- 959 5999 960(1 row) 961 962EXPLAIN (COSTS OFF) 963SELECT count(*) FROM quad_point_tbl WHERE p ~= '(4585, 365)'; 964 QUERY PLAN 965------------------------------------------------------ 966 Aggregate 967 -> Bitmap Heap Scan on quad_point_tbl 968 Recheck Cond: (p ~= '(4585,365)'::point) 969 -> Bitmap Index Scan on sp_quad_ind 970 Index Cond: (p ~= '(4585,365)'::point) 971(5 rows) 972 973SELECT count(*) FROM quad_point_tbl WHERE p ~= '(4585, 365)'; 974 count 975------- 976 1 977(1 row) 978 979EXPLAIN (COSTS OFF) 980SELECT count(*) FROM kd_point_tbl WHERE p <@ box '(200,200,1000,1000)'; 981 QUERY PLAN 982--------------------------------------------------------------- 983 Aggregate 984 -> Bitmap Heap Scan on kd_point_tbl 985 Recheck Cond: (p <@ '(1000,1000),(200,200)'::box) 986 -> Bitmap Index Scan on sp_kd_ind 987 Index Cond: (p <@ '(1000,1000),(200,200)'::box) 988(5 rows) 989 990SELECT count(*) FROM kd_point_tbl WHERE p <@ box '(200,200,1000,1000)'; 991 count 992------- 993 1057 994(1 row) 995 996EXPLAIN (COSTS OFF) 997SELECT count(*) FROM kd_point_tbl WHERE box '(200,200,1000,1000)' @> p; 998 QUERY PLAN 999--------------------------------------------------------------- 1000 Aggregate 1001 -> Bitmap Heap Scan on kd_point_tbl 1002 Recheck Cond: ('(1000,1000),(200,200)'::box @> p) 1003 -> Bitmap Index Scan on sp_kd_ind 1004 Index Cond: (p <@ '(1000,1000),(200,200)'::box) 1005(5 rows) 1006 1007SELECT count(*) FROM kd_point_tbl WHERE box '(200,200,1000,1000)' @> p; 1008 count 1009------- 1010 1057 1011(1 row) 1012 1013EXPLAIN (COSTS OFF) 1014SELECT count(*) FROM kd_point_tbl WHERE p << '(5000, 4000)'; 1015 QUERY PLAN 1016------------------------------------------------------- 1017 Aggregate 1018 -> Bitmap Heap Scan on kd_point_tbl 1019 Recheck Cond: (p << '(5000,4000)'::point) 1020 -> Bitmap Index Scan on sp_kd_ind 1021 Index Cond: (p << '(5000,4000)'::point) 1022(5 rows) 1023 1024SELECT count(*) FROM kd_point_tbl WHERE p << '(5000, 4000)'; 1025 count 1026------- 1027 6000 1028(1 row) 1029 1030EXPLAIN (COSTS OFF) 1031SELECT count(*) FROM kd_point_tbl WHERE p >> '(5000, 4000)'; 1032 QUERY PLAN 1033------------------------------------------------------- 1034 Aggregate 1035 -> Bitmap Heap Scan on kd_point_tbl 1036 Recheck Cond: (p >> '(5000,4000)'::point) 1037 -> Bitmap Index Scan on sp_kd_ind 1038 Index Cond: (p >> '(5000,4000)'::point) 1039(5 rows) 1040 1041SELECT count(*) FROM kd_point_tbl WHERE p >> '(5000, 4000)'; 1042 count 1043------- 1044 4999 1045(1 row) 1046 1047EXPLAIN (COSTS OFF) 1048SELECT count(*) FROM kd_point_tbl WHERE p <<| '(5000, 4000)'; 1049 QUERY PLAN 1050-------------------------------------------------------- 1051 Aggregate 1052 -> Bitmap Heap Scan on kd_point_tbl 1053 Recheck Cond: (p <<| '(5000,4000)'::point) 1054 -> Bitmap Index Scan on sp_kd_ind 1055 Index Cond: (p <<| '(5000,4000)'::point) 1056(5 rows) 1057 1058SELECT count(*) FROM kd_point_tbl WHERE p <<| '(5000, 4000)'; 1059 count 1060------- 1061 5000 1062(1 row) 1063 1064EXPLAIN (COSTS OFF) 1065SELECT count(*) FROM kd_point_tbl WHERE p |>> '(5000, 4000)'; 1066 QUERY PLAN 1067-------------------------------------------------------- 1068 Aggregate 1069 -> Bitmap Heap Scan on kd_point_tbl 1070 Recheck Cond: (p |>> '(5000,4000)'::point) 1071 -> Bitmap Index Scan on sp_kd_ind 1072 Index Cond: (p |>> '(5000,4000)'::point) 1073(5 rows) 1074 1075SELECT count(*) FROM kd_point_tbl WHERE p |>> '(5000, 4000)'; 1076 count 1077------- 1078 5999 1079(1 row) 1080 1081EXPLAIN (COSTS OFF) 1082SELECT count(*) FROM kd_point_tbl WHERE p ~= '(4585, 365)'; 1083 QUERY PLAN 1084------------------------------------------------------ 1085 Aggregate 1086 -> Bitmap Heap Scan on kd_point_tbl 1087 Recheck Cond: (p ~= '(4585,365)'::point) 1088 -> Bitmap Index Scan on sp_kd_ind 1089 Index Cond: (p ~= '(4585,365)'::point) 1090(5 rows) 1091 1092SELECT count(*) FROM kd_point_tbl WHERE p ~= '(4585, 365)'; 1093 count 1094------- 1095 1 1096(1 row) 1097 1098EXPLAIN (COSTS OFF) 1099SELECT count(*) FROM radix_text_tbl WHERE t = 'P0123456789abcdef'; 1100 QUERY PLAN 1101----------------------------------------------------------- 1102 Aggregate 1103 -> Bitmap Heap Scan on radix_text_tbl 1104 Recheck Cond: (t = 'P0123456789abcdef'::text) 1105 -> Bitmap Index Scan on sp_radix_ind 1106 Index Cond: (t = 'P0123456789abcdef'::text) 1107(5 rows) 1108 1109SELECT count(*) FROM radix_text_tbl WHERE t = 'P0123456789abcdef'; 1110 count 1111------- 1112 1000 1113(1 row) 1114 1115EXPLAIN (COSTS OFF) 1116SELECT count(*) FROM radix_text_tbl WHERE t = 'P0123456789abcde'; 1117 QUERY PLAN 1118---------------------------------------------------------- 1119 Aggregate 1120 -> Bitmap Heap Scan on radix_text_tbl 1121 Recheck Cond: (t = 'P0123456789abcde'::text) 1122 -> Bitmap Index Scan on sp_radix_ind 1123 Index Cond: (t = 'P0123456789abcde'::text) 1124(5 rows) 1125 1126SELECT count(*) FROM radix_text_tbl WHERE t = 'P0123456789abcde'; 1127 count 1128------- 1129 1 1130(1 row) 1131 1132EXPLAIN (COSTS OFF) 1133SELECT count(*) FROM radix_text_tbl WHERE t = 'P0123456789abcdefF'; 1134 QUERY PLAN 1135------------------------------------------------------------ 1136 Aggregate 1137 -> Bitmap Heap Scan on radix_text_tbl 1138 Recheck Cond: (t = 'P0123456789abcdefF'::text) 1139 -> Bitmap Index Scan on sp_radix_ind 1140 Index Cond: (t = 'P0123456789abcdefF'::text) 1141(5 rows) 1142 1143SELECT count(*) FROM radix_text_tbl WHERE t = 'P0123456789abcdefF'; 1144 count 1145------- 1146 1 1147(1 row) 1148 1149EXPLAIN (COSTS OFF) 1150SELECT count(*) FROM radix_text_tbl WHERE t < 'Aztec Ct '; 1151 QUERY PLAN 1152---------------------------------------------------------------------------- 1153 Aggregate 1154 -> Bitmap Heap Scan on radix_text_tbl 1155 Recheck Cond: (t < 'Aztec Ct '::text) 1156 -> Bitmap Index Scan on sp_radix_ind 1157 Index Cond: (t < 'Aztec Ct '::text) 1158(5 rows) 1159 1160SELECT count(*) FROM radix_text_tbl WHERE t < 'Aztec Ct '; 1161 count 1162------- 1163 272 1164(1 row) 1165 1166EXPLAIN (COSTS OFF) 1167SELECT count(*) FROM radix_text_tbl WHERE t ~<~ 'Aztec Ct '; 1168 QUERY PLAN 1169------------------------------------------------------------------------------ 1170 Aggregate 1171 -> Bitmap Heap Scan on radix_text_tbl 1172 Recheck Cond: (t ~<~ 'Aztec Ct '::text) 1173 -> Bitmap Index Scan on sp_radix_ind 1174 Index Cond: (t ~<~ 'Aztec Ct '::text) 1175(5 rows) 1176 1177SELECT count(*) FROM radix_text_tbl WHERE t ~<~ 'Aztec Ct '; 1178 count 1179------- 1180 272 1181(1 row) 1182 1183EXPLAIN (COSTS OFF) 1184SELECT count(*) FROM radix_text_tbl WHERE t <= 'Aztec Ct '; 1185 QUERY PLAN 1186----------------------------------------------------------------------------- 1187 Aggregate 1188 -> Bitmap Heap Scan on radix_text_tbl 1189 Recheck Cond: (t <= 'Aztec Ct '::text) 1190 -> Bitmap Index Scan on sp_radix_ind 1191 Index Cond: (t <= 'Aztec Ct '::text) 1192(5 rows) 1193 1194SELECT count(*) FROM radix_text_tbl WHERE t <= 'Aztec Ct '; 1195 count 1196------- 1197 273 1198(1 row) 1199 1200EXPLAIN (COSTS OFF) 1201SELECT count(*) FROM radix_text_tbl WHERE t ~<=~ 'Aztec Ct '; 1202 QUERY PLAN 1203------------------------------------------------------------------------------- 1204 Aggregate 1205 -> Bitmap Heap Scan on radix_text_tbl 1206 Recheck Cond: (t ~<=~ 'Aztec Ct '::text) 1207 -> Bitmap Index Scan on sp_radix_ind 1208 Index Cond: (t ~<=~ 'Aztec Ct '::text) 1209(5 rows) 1210 1211SELECT count(*) FROM radix_text_tbl WHERE t ~<=~ 'Aztec Ct '; 1212 count 1213------- 1214 273 1215(1 row) 1216 1217EXPLAIN (COSTS OFF) 1218SELECT count(*) FROM radix_text_tbl WHERE t = 'Aztec Ct '; 1219 QUERY PLAN 1220---------------------------------------------------------------------------- 1221 Aggregate 1222 -> Bitmap Heap Scan on radix_text_tbl 1223 Recheck Cond: (t = 'Aztec Ct '::text) 1224 -> Bitmap Index Scan on sp_radix_ind 1225 Index Cond: (t = 'Aztec Ct '::text) 1226(5 rows) 1227 1228SELECT count(*) FROM radix_text_tbl WHERE t = 'Aztec Ct '; 1229 count 1230------- 1231 1 1232(1 row) 1233 1234EXPLAIN (COSTS OFF) 1235SELECT count(*) FROM radix_text_tbl WHERE t = 'Worth St '; 1236 QUERY PLAN 1237---------------------------------------------------------------------------- 1238 Aggregate 1239 -> Bitmap Heap Scan on radix_text_tbl 1240 Recheck Cond: (t = 'Worth St '::text) 1241 -> Bitmap Index Scan on sp_radix_ind 1242 Index Cond: (t = 'Worth St '::text) 1243(5 rows) 1244 1245SELECT count(*) FROM radix_text_tbl WHERE t = 'Worth St '; 1246 count 1247------- 1248 2 1249(1 row) 1250 1251EXPLAIN (COSTS OFF) 1252SELECT count(*) FROM radix_text_tbl WHERE t >= 'Worth St '; 1253 QUERY PLAN 1254----------------------------------------------------------------------------- 1255 Aggregate 1256 -> Bitmap Heap Scan on radix_text_tbl 1257 Recheck Cond: (t >= 'Worth St '::text) 1258 -> Bitmap Index Scan on sp_radix_ind 1259 Index Cond: (t >= 'Worth St '::text) 1260(5 rows) 1261 1262SELECT count(*) FROM radix_text_tbl WHERE t >= 'Worth St '; 1263 count 1264------- 1265 50 1266(1 row) 1267 1268EXPLAIN (COSTS OFF) 1269SELECT count(*) FROM radix_text_tbl WHERE t ~>=~ 'Worth St '; 1270 QUERY PLAN 1271------------------------------------------------------------------------------- 1272 Aggregate 1273 -> Bitmap Heap Scan on radix_text_tbl 1274 Recheck Cond: (t ~>=~ 'Worth St '::text) 1275 -> Bitmap Index Scan on sp_radix_ind 1276 Index Cond: (t ~>=~ 'Worth St '::text) 1277(5 rows) 1278 1279SELECT count(*) FROM radix_text_tbl WHERE t ~>=~ 'Worth St '; 1280 count 1281------- 1282 50 1283(1 row) 1284 1285EXPLAIN (COSTS OFF) 1286SELECT count(*) FROM radix_text_tbl WHERE t > 'Worth St '; 1287 QUERY PLAN 1288---------------------------------------------------------------------------- 1289 Aggregate 1290 -> Bitmap Heap Scan on radix_text_tbl 1291 Recheck Cond: (t > 'Worth St '::text) 1292 -> Bitmap Index Scan on sp_radix_ind 1293 Index Cond: (t > 'Worth St '::text) 1294(5 rows) 1295 1296SELECT count(*) FROM radix_text_tbl WHERE t > 'Worth St '; 1297 count 1298------- 1299 48 1300(1 row) 1301 1302EXPLAIN (COSTS OFF) 1303SELECT count(*) FROM radix_text_tbl WHERE t ~>~ 'Worth St '; 1304 QUERY PLAN 1305------------------------------------------------------------------------------ 1306 Aggregate 1307 -> Bitmap Heap Scan on radix_text_tbl 1308 Recheck Cond: (t ~>~ 'Worth St '::text) 1309 -> Bitmap Index Scan on sp_radix_ind 1310 Index Cond: (t ~>~ 'Worth St '::text) 1311(5 rows) 1312 1313SELECT count(*) FROM radix_text_tbl WHERE t ~>~ 'Worth St '; 1314 count 1315------- 1316 48 1317(1 row) 1318 1319EXPLAIN (COSTS OFF) 1320SELECT count(*) FROM radix_text_tbl WHERE t ^@ 'Worth'; 1321 QUERY PLAN 1322------------------------------------------------ 1323 Aggregate 1324 -> Bitmap Heap Scan on radix_text_tbl 1325 Recheck Cond: (t ^@ 'Worth'::text) 1326 -> Bitmap Index Scan on sp_radix_ind 1327 Index Cond: (t ^@ 'Worth'::text) 1328(5 rows) 1329 1330SELECT count(*) FROM radix_text_tbl WHERE t ^@ 'Worth'; 1331 count 1332------- 1333 2 1334(1 row) 1335 1336RESET enable_seqscan; 1337RESET enable_indexscan; 1338RESET enable_bitmapscan; 1339