1-- 2-- CREATE_INDEX 3-- Create ancillary data structures (i.e. indices) 4-- 5-- 6-- BTREE 7-- 8CREATE INDEX onek_unique1 ON onek USING btree(unique1 int4_ops); 9CREATE INDEX IF NOT EXISTS onek_unique1 ON onek USING btree(unique1 int4_ops); 10NOTICE: relation "onek_unique1" already exists, skipping 11CREATE INDEX IF NOT EXISTS ON onek USING btree(unique1 int4_ops); 12ERROR: syntax error at or near "ON" 13LINE 1: CREATE INDEX IF NOT EXISTS ON onek USING btree(unique1 int4_... 14 ^ 15CREATE INDEX onek_unique2 ON onek USING btree(unique2 int4_ops); 16CREATE INDEX onek_hundred ON onek USING btree(hundred int4_ops); 17CREATE INDEX onek_stringu1 ON onek USING btree(stringu1 name_ops); 18CREATE INDEX tenk1_unique1 ON tenk1 USING btree(unique1 int4_ops); 19CREATE INDEX tenk1_unique2 ON tenk1 USING btree(unique2 int4_ops); 20CREATE INDEX tenk1_hundred ON tenk1 USING btree(hundred int4_ops); 21CREATE INDEX tenk1_thous_tenthous ON tenk1 (thousand, tenthous); 22CREATE INDEX tenk2_unique1 ON tenk2 USING btree(unique1 int4_ops); 23CREATE INDEX tenk2_unique2 ON tenk2 USING btree(unique2 int4_ops); 24CREATE INDEX tenk2_hundred ON tenk2 USING btree(hundred int4_ops); 25CREATE INDEX rix ON road USING btree (name text_ops); 26CREATE INDEX iix ON ihighway USING btree (name text_ops); 27CREATE INDEX six ON shighway USING btree (name text_ops); 28-- test comments 29COMMENT ON INDEX six_wrong IS 'bad index'; 30ERROR: relation "six_wrong" does not exist 31COMMENT ON INDEX six IS 'good index'; 32COMMENT ON INDEX six IS NULL; 33-- 34-- BTREE ascending/descending cases 35-- 36-- we load int4/text from pure descending data (each key is a new 37-- low key) and name/f8 from pure ascending data (each key is a new 38-- high key). we had a bug where new low keys would sometimes be 39-- "lost". 40-- 41CREATE INDEX bt_i4_index ON bt_i4_heap USING btree (seqno int4_ops); 42CREATE INDEX bt_name_index ON bt_name_heap USING btree (seqno name_ops); 43CREATE INDEX bt_txt_index ON bt_txt_heap USING btree (seqno text_ops); 44CREATE INDEX bt_f8_index ON bt_f8_heap USING btree (seqno float8_ops); 45-- 46-- BTREE partial indices 47-- 48CREATE INDEX onek2_u1_prtl ON onek2 USING btree(unique1 int4_ops) 49 where unique1 < 20 or unique1 > 980; 50CREATE INDEX onek2_u2_prtl ON onek2 USING btree(unique2 int4_ops) 51 where stringu1 < 'B'; 52CREATE INDEX onek2_stu1_prtl ON onek2 USING btree(stringu1 name_ops) 53 where onek2.stringu1 >= 'J' and onek2.stringu1 < 'K'; 54-- 55-- GiST (rtree-equivalent opclasses only) 56-- 57CREATE INDEX grect2ind ON fast_emp4000 USING gist (home_base); 58CREATE INDEX gpolygonind ON polygon_tbl USING gist (f1); 59CREATE INDEX gcircleind ON circle_tbl USING gist (f1); 60INSERT INTO POINT_TBL(f1) VALUES (NULL); 61CREATE INDEX gpointind ON point_tbl USING gist (f1); 62CREATE TEMP TABLE gpolygon_tbl AS 63 SELECT polygon(home_base) AS f1 FROM slow_emp4000; 64INSERT INTO gpolygon_tbl VALUES ( '(1000,0,0,1000)' ); 65INSERT INTO gpolygon_tbl VALUES ( '(0,1000,1000,1000)' ); 66CREATE TEMP TABLE gcircle_tbl AS 67 SELECT circle(home_base) AS f1 FROM slow_emp4000; 68CREATE INDEX ggpolygonind ON gpolygon_tbl USING gist (f1); 69CREATE INDEX ggcircleind ON gcircle_tbl USING gist (f1); 70-- 71-- SP-GiST 72-- 73CREATE TABLE quad_point_tbl AS 74 SELECT point(unique1,unique2) AS p FROM tenk1; 75INSERT INTO quad_point_tbl 76 SELECT '(333.0,400.0)'::point FROM generate_series(1,1000); 77INSERT INTO quad_point_tbl VALUES (NULL), (NULL), (NULL); 78CREATE INDEX sp_quad_ind ON quad_point_tbl USING spgist (p); 79CREATE TABLE kd_point_tbl AS SELECT * FROM quad_point_tbl; 80CREATE INDEX sp_kd_ind ON kd_point_tbl USING spgist (p kd_point_ops); 81CREATE TABLE radix_text_tbl AS 82 SELECT name AS t FROM road WHERE name !~ '^[0-9]'; 83INSERT INTO radix_text_tbl 84 SELECT 'P0123456789abcdef' FROM generate_series(1,1000); 85INSERT INTO radix_text_tbl VALUES ('P0123456789abcde'); 86INSERT INTO radix_text_tbl VALUES ('P0123456789abcdefF'); 87CREATE INDEX sp_radix_ind ON radix_text_tbl USING spgist (t); 88-- 89-- Test GiST and SP-GiST indexes 90-- 91-- get non-indexed results for comparison purposes 92SET enable_seqscan = ON; 93SET enable_indexscan = OFF; 94SET enable_bitmapscan = OFF; 95SELECT * FROM fast_emp4000 96 WHERE home_base @ '(200,200),(2000,1000)'::box 97 ORDER BY (home_base[0])[0]; 98 home_base 99----------------------- 100 (337,455),(240,359) 101 (1444,403),(1346,344) 102(2 rows) 103 104SELECT count(*) FROM fast_emp4000 WHERE home_base && '(1000,1000,0,0)'::box; 105 count 106------- 107 2 108(1 row) 109 110SELECT count(*) FROM fast_emp4000 WHERE home_base IS NULL; 111 count 112------- 113 278 114(1 row) 115 116SELECT * FROM polygon_tbl WHERE f1 ~ '((1,1),(2,2),(2,1))'::polygon 117 ORDER BY (poly_center(f1))[0]; 118 f1 119--------------------- 120 ((2,0),(2,4),(0,0)) 121(1 row) 122 123SELECT * FROM circle_tbl WHERE f1 && circle(point(1,-2), 1) 124 ORDER BY area(f1); 125 f1 126--------------- 127 <(1,2),3> 128 <(1,3),5> 129 <(1,2),100> 130 <(100,1),115> 131(4 rows) 132 133SELECT count(*) FROM gpolygon_tbl WHERE f1 && '(1000,1000,0,0)'::polygon; 134 count 135------- 136 2 137(1 row) 138 139SELECT count(*) FROM gcircle_tbl WHERE f1 && '<(500,500),500>'::circle; 140 count 141------- 142 2 143(1 row) 144 145SELECT count(*) FROM point_tbl WHERE f1 <@ box '(0,0,100,100)'; 146 count 147------- 148 3 149(1 row) 150 151SELECT count(*) FROM point_tbl WHERE box '(0,0,100,100)' @> f1; 152 count 153------- 154 3 155(1 row) 156 157SELECT count(*) FROM point_tbl WHERE f1 <@ polygon '(0,0),(0,100),(100,100),(50,50),(100,0),(0,0)'; 158 count 159------- 160 3 161(1 row) 162 163SELECT count(*) FROM point_tbl WHERE f1 <@ circle '<(50,50),50>'; 164 count 165------- 166 1 167(1 row) 168 169SELECT count(*) FROM point_tbl p WHERE p.f1 << '(0.0, 0.0)'; 170 count 171------- 172 3 173(1 row) 174 175SELECT count(*) FROM point_tbl p WHERE p.f1 >> '(0.0, 0.0)'; 176 count 177------- 178 2 179(1 row) 180 181SELECT count(*) FROM point_tbl p WHERE p.f1 <^ '(0.0, 0.0)'; 182 count 183------- 184 1 185(1 row) 186 187SELECT count(*) FROM point_tbl p WHERE p.f1 >^ '(0.0, 0.0)'; 188 count 189------- 190 3 191(1 row) 192 193SELECT count(*) FROM point_tbl p WHERE p.f1 ~= '(-5, -12)'; 194 count 195------- 196 1 197(1 row) 198 199SELECT * FROM point_tbl ORDER BY f1 <-> '0,1'; 200 f1 201------------ 202 (0,0) 203 (-3,4) 204 (-10,0) 205 (10,10) 206 (-5,-12) 207 (5.1,34.5) 208 209(7 rows) 210 211SELECT * FROM point_tbl WHERE f1 IS NULL; 212 f1 213---- 214 215(1 row) 216 217SELECT * FROM point_tbl WHERE f1 IS NOT NULL ORDER BY f1 <-> '0,1'; 218 f1 219------------ 220 (0,0) 221 (-3,4) 222 (-10,0) 223 (10,10) 224 (-5,-12) 225 (5.1,34.5) 226(6 rows) 227 228SELECT * FROM point_tbl WHERE f1 <@ '(-10,-10),(10,10)':: box ORDER BY f1 <-> '0,1'; 229 f1 230--------- 231 (0,0) 232 (-3,4) 233 (-10,0) 234 (10,10) 235(4 rows) 236 237SELECT count(*) FROM quad_point_tbl WHERE p IS NULL; 238 count 239------- 240 3 241(1 row) 242 243SELECT count(*) FROM quad_point_tbl WHERE p IS NOT NULL; 244 count 245------- 246 11000 247(1 row) 248 249SELECT count(*) FROM quad_point_tbl; 250 count 251------- 252 11003 253(1 row) 254 255SELECT count(*) FROM quad_point_tbl WHERE p <@ box '(200,200,1000,1000)'; 256 count 257------- 258 1057 259(1 row) 260 261SELECT count(*) FROM quad_point_tbl WHERE box '(200,200,1000,1000)' @> p; 262 count 263------- 264 1057 265(1 row) 266 267SELECT count(*) FROM quad_point_tbl WHERE p << '(5000, 4000)'; 268 count 269------- 270 6000 271(1 row) 272 273SELECT count(*) FROM quad_point_tbl WHERE p >> '(5000, 4000)'; 274 count 275------- 276 4999 277(1 row) 278 279SELECT count(*) FROM quad_point_tbl WHERE p <^ '(5000, 4000)'; 280 count 281------- 282 5000 283(1 row) 284 285SELECT count(*) FROM quad_point_tbl WHERE p >^ '(5000, 4000)'; 286 count 287------- 288 5999 289(1 row) 290 291SELECT count(*) FROM quad_point_tbl WHERE p ~= '(4585, 365)'; 292 count 293------- 294 1 295(1 row) 296 297SELECT count(*) FROM radix_text_tbl WHERE t = 'P0123456789abcdef'; 298 count 299------- 300 1000 301(1 row) 302 303SELECT count(*) FROM radix_text_tbl WHERE t = 'P0123456789abcde'; 304 count 305------- 306 1 307(1 row) 308 309SELECT count(*) FROM radix_text_tbl WHERE t = 'P0123456789abcdefF'; 310 count 311------- 312 1 313(1 row) 314 315SELECT count(*) FROM radix_text_tbl WHERE t < 'Aztec Ct '; 316 count 317------- 318 272 319(1 row) 320 321SELECT count(*) FROM radix_text_tbl WHERE t ~<~ 'Aztec Ct '; 322 count 323------- 324 272 325(1 row) 326 327SELECT count(*) FROM radix_text_tbl WHERE t <= 'Aztec Ct '; 328 count 329------- 330 273 331(1 row) 332 333SELECT count(*) FROM radix_text_tbl WHERE t ~<=~ 'Aztec Ct '; 334 count 335------- 336 273 337(1 row) 338 339SELECT count(*) FROM radix_text_tbl WHERE t = 'Aztec Ct '; 340 count 341------- 342 1 343(1 row) 344 345SELECT count(*) FROM radix_text_tbl WHERE t = 'Worth St '; 346 count 347------- 348 2 349(1 row) 350 351SELECT count(*) FROM radix_text_tbl WHERE t >= 'Worth St '; 352 count 353------- 354 50 355(1 row) 356 357SELECT count(*) FROM radix_text_tbl WHERE t ~>=~ 'Worth St '; 358 count 359------- 360 50 361(1 row) 362 363SELECT count(*) FROM radix_text_tbl WHERE t > 'Worth St '; 364 count 365------- 366 48 367(1 row) 368 369SELECT count(*) FROM radix_text_tbl WHERE t ~>~ 'Worth St '; 370 count 371------- 372 48 373(1 row) 374 375SELECT count(*) FROM radix_text_tbl WHERE t ^@ 'Worth'; 376 count 377------- 378 2 379(1 row) 380 381SELECT * FROM gpolygon_tbl ORDER BY f1 <-> '(0,0)'::point LIMIT 10; 382 f1 383------------------------------------------------- 384 ((240,359),(240,455),(337,455),(337,359)) 385 ((662,163),(662,187),(759,187),(759,163)) 386 ((1000,0),(0,1000)) 387 ((0,1000),(1000,1000)) 388 ((1346,344),(1346,403),(1444,403),(1444,344)) 389 ((278,1409),(278,1457),(369,1457),(369,1409)) 390 ((907,1156),(907,1201),(948,1201),(948,1156)) 391 ((1517,971),(1517,1043),(1594,1043),(1594,971)) 392 ((175,1820),(175,1850),(259,1850),(259,1820)) 393 ((2424,81),(2424,160),(2424,160),(2424,81)) 394(10 rows) 395 396SELECT circle_center(f1), round(radius(f1)) as radius FROM gcircle_tbl ORDER BY f1 <-> '(200,300)'::point LIMIT 10; 397 circle_center | radius 398----------------+-------- 399 (288.5,407) | 68 400 (710.5,175) | 50 401 (323.5,1433) | 51 402 (927.5,1178.5) | 30 403 (1395,373.5) | 57 404 (1555.5,1007) | 53 405 (217,1835) | 45 406 (489,2421.5) | 22 407 (2424,120.5) | 40 408 (751.5,2655) | 20 409(10 rows) 410 411-- Now check the results from plain indexscan 412SET enable_seqscan = OFF; 413SET enable_indexscan = ON; 414SET enable_bitmapscan = OFF; 415EXPLAIN (COSTS OFF) 416SELECT * FROM fast_emp4000 417 WHERE home_base @ '(200,200),(2000,1000)'::box 418 ORDER BY (home_base[0])[0]; 419 QUERY PLAN 420---------------------------------------------------------------- 421 Sort 422 Sort Key: ((home_base[0])[0]) 423 -> Index Only Scan using grect2ind on fast_emp4000 424 Index Cond: (home_base @ '(2000,1000),(200,200)'::box) 425(4 rows) 426 427SELECT * FROM fast_emp4000 428 WHERE home_base @ '(200,200),(2000,1000)'::box 429 ORDER BY (home_base[0])[0]; 430 home_base 431----------------------- 432 (337,455),(240,359) 433 (1444,403),(1346,344) 434(2 rows) 435 436EXPLAIN (COSTS OFF) 437SELECT count(*) FROM fast_emp4000 WHERE home_base && '(1000,1000,0,0)'::box; 438 QUERY PLAN 439------------------------------------------------------------- 440 Aggregate 441 -> Index Only Scan using grect2ind on fast_emp4000 442 Index Cond: (home_base && '(1000,1000),(0,0)'::box) 443(3 rows) 444 445SELECT count(*) FROM fast_emp4000 WHERE home_base && '(1000,1000,0,0)'::box; 446 count 447------- 448 2 449(1 row) 450 451EXPLAIN (COSTS OFF) 452SELECT count(*) FROM fast_emp4000 WHERE home_base IS NULL; 453 QUERY PLAN 454------------------------------------------------------- 455 Aggregate 456 -> Index Only Scan using grect2ind on fast_emp4000 457 Index Cond: (home_base IS NULL) 458(3 rows) 459 460SELECT count(*) FROM fast_emp4000 WHERE home_base IS NULL; 461 count 462------- 463 278 464(1 row) 465 466EXPLAIN (COSTS OFF) 467SELECT * FROM polygon_tbl WHERE f1 ~ '((1,1),(2,2),(2,1))'::polygon 468 ORDER BY (poly_center(f1))[0]; 469 QUERY PLAN 470----------------------------------------------------------- 471 Sort 472 Sort Key: ((poly_center(f1))[0]) 473 -> Index Scan using gpolygonind on polygon_tbl 474 Index Cond: (f1 ~ '((1,1),(2,2),(2,1))'::polygon) 475(4 rows) 476 477SELECT * FROM polygon_tbl WHERE f1 ~ '((1,1),(2,2),(2,1))'::polygon 478 ORDER BY (poly_center(f1))[0]; 479 f1 480--------------------- 481 ((2,0),(2,4),(0,0)) 482(1 row) 483 484EXPLAIN (COSTS OFF) 485SELECT * FROM circle_tbl WHERE f1 && circle(point(1,-2), 1) 486 ORDER BY area(f1); 487 QUERY PLAN 488-------------------------------------------------- 489 Sort 490 Sort Key: (area(f1)) 491 -> Index Scan using gcircleind on circle_tbl 492 Index Cond: (f1 && '<(1,-2),1>'::circle) 493(4 rows) 494 495SELECT * FROM circle_tbl WHERE f1 && circle(point(1,-2), 1) 496 ORDER BY area(f1); 497 f1 498--------------- 499 <(1,2),3> 500 <(1,3),5> 501 <(1,2),100> 502 <(100,1),115> 503(4 rows) 504 505EXPLAIN (COSTS OFF) 506SELECT count(*) FROM gpolygon_tbl WHERE f1 && '(1000,1000,0,0)'::polygon; 507 QUERY PLAN 508------------------------------------------------------------ 509 Aggregate 510 -> Index Scan using ggpolygonind on gpolygon_tbl 511 Index Cond: (f1 && '((1000,1000),(0,0))'::polygon) 512(3 rows) 513 514SELECT count(*) FROM gpolygon_tbl WHERE f1 && '(1000,1000,0,0)'::polygon; 515 count 516------- 517 2 518(1 row) 519 520EXPLAIN (COSTS OFF) 521SELECT count(*) FROM gcircle_tbl WHERE f1 && '<(500,500),500>'::circle; 522 QUERY PLAN 523------------------------------------------------------- 524 Aggregate 525 -> Index Scan using ggcircleind on gcircle_tbl 526 Index Cond: (f1 && '<(500,500),500>'::circle) 527(3 rows) 528 529SELECT count(*) FROM gcircle_tbl WHERE f1 && '<(500,500),500>'::circle; 530 count 531------- 532 2 533(1 row) 534 535EXPLAIN (COSTS OFF) 536SELECT count(*) FROM point_tbl WHERE f1 <@ box '(0,0,100,100)'; 537 QUERY PLAN 538---------------------------------------------------- 539 Aggregate 540 -> Index Only Scan using gpointind on point_tbl 541 Index Cond: (f1 <@ '(100,100),(0,0)'::box) 542(3 rows) 543 544SELECT count(*) FROM point_tbl WHERE f1 <@ box '(0,0,100,100)'; 545 count 546------- 547 3 548(1 row) 549 550EXPLAIN (COSTS OFF) 551SELECT count(*) FROM point_tbl WHERE box '(0,0,100,100)' @> f1; 552 QUERY PLAN 553---------------------------------------------------- 554 Aggregate 555 -> Index Only Scan using gpointind on point_tbl 556 Index Cond: (f1 <@ '(100,100),(0,0)'::box) 557(3 rows) 558 559SELECT count(*) FROM point_tbl WHERE box '(0,0,100,100)' @> f1; 560 count 561------- 562 3 563(1 row) 564 565EXPLAIN (COSTS OFF) 566SELECT count(*) FROM point_tbl WHERE f1 <@ polygon '(0,0),(0,100),(100,100),(50,50),(100,0),(0,0)'; 567 QUERY PLAN 568---------------------------------------------------------------------------------------- 569 Aggregate 570 -> Index Only Scan using gpointind on point_tbl 571 Index Cond: (f1 <@ '((0,0),(0,100),(100,100),(50,50),(100,0),(0,0))'::polygon) 572(3 rows) 573 574SELECT count(*) FROM point_tbl WHERE f1 <@ polygon '(0,0),(0,100),(100,100),(50,50),(100,0),(0,0)'; 575 count 576------- 577 3 578(1 row) 579 580EXPLAIN (COSTS OFF) 581SELECT count(*) FROM point_tbl WHERE f1 <@ circle '<(50,50),50>'; 582 QUERY PLAN 583---------------------------------------------------- 584 Aggregate 585 -> Index Only Scan using gpointind on point_tbl 586 Index Cond: (f1 <@ '<(50,50),50>'::circle) 587(3 rows) 588 589SELECT count(*) FROM point_tbl WHERE f1 <@ circle '<(50,50),50>'; 590 count 591------- 592 1 593(1 row) 594 595EXPLAIN (COSTS OFF) 596SELECT count(*) FROM point_tbl p WHERE p.f1 << '(0.0, 0.0)'; 597 QUERY PLAN 598------------------------------------------------------ 599 Aggregate 600 -> Index Only Scan using gpointind on point_tbl p 601 Index Cond: (f1 << '(0,0)'::point) 602(3 rows) 603 604SELECT count(*) FROM point_tbl p WHERE p.f1 << '(0.0, 0.0)'; 605 count 606------- 607 3 608(1 row) 609 610EXPLAIN (COSTS OFF) 611SELECT count(*) FROM point_tbl p WHERE p.f1 >> '(0.0, 0.0)'; 612 QUERY PLAN 613------------------------------------------------------ 614 Aggregate 615 -> Index Only Scan using gpointind on point_tbl p 616 Index Cond: (f1 >> '(0,0)'::point) 617(3 rows) 618 619SELECT count(*) FROM point_tbl p WHERE p.f1 >> '(0.0, 0.0)'; 620 count 621------- 622 2 623(1 row) 624 625EXPLAIN (COSTS OFF) 626SELECT count(*) FROM point_tbl p WHERE p.f1 <^ '(0.0, 0.0)'; 627 QUERY PLAN 628------------------------------------------------------ 629 Aggregate 630 -> Index Only Scan using gpointind on point_tbl p 631 Index Cond: (f1 <^ '(0,0)'::point) 632(3 rows) 633 634SELECT count(*) FROM point_tbl p WHERE p.f1 <^ '(0.0, 0.0)'; 635 count 636------- 637 1 638(1 row) 639 640EXPLAIN (COSTS OFF) 641SELECT count(*) FROM point_tbl p WHERE p.f1 >^ '(0.0, 0.0)'; 642 QUERY PLAN 643------------------------------------------------------ 644 Aggregate 645 -> Index Only Scan using gpointind on point_tbl p 646 Index Cond: (f1 >^ '(0,0)'::point) 647(3 rows) 648 649SELECT count(*) FROM point_tbl p WHERE p.f1 >^ '(0.0, 0.0)'; 650 count 651------- 652 3 653(1 row) 654 655EXPLAIN (COSTS OFF) 656SELECT count(*) FROM point_tbl p WHERE p.f1 ~= '(-5, -12)'; 657 QUERY PLAN 658------------------------------------------------------ 659 Aggregate 660 -> Index Only Scan using gpointind on point_tbl p 661 Index Cond: (f1 ~= '(-5,-12)'::point) 662(3 rows) 663 664SELECT count(*) FROM point_tbl p WHERE p.f1 ~= '(-5, -12)'; 665 count 666------- 667 1 668(1 row) 669 670EXPLAIN (COSTS OFF) 671SELECT * FROM point_tbl ORDER BY f1 <-> '0,1'; 672 QUERY PLAN 673---------------------------------------------- 674 Index Only Scan using gpointind on point_tbl 675 Order By: (f1 <-> '(0,1)'::point) 676(2 rows) 677 678SELECT * FROM point_tbl ORDER BY f1 <-> '0,1'; 679 f1 680------------ 681 (0,0) 682 (-3,4) 683 (-10,0) 684 (10,10) 685 (-5,-12) 686 (5.1,34.5) 687 688(7 rows) 689 690EXPLAIN (COSTS OFF) 691SELECT * FROM point_tbl WHERE f1 IS NULL; 692 QUERY PLAN 693---------------------------------------------- 694 Index Only Scan using gpointind on point_tbl 695 Index Cond: (f1 IS NULL) 696(2 rows) 697 698SELECT * FROM point_tbl WHERE f1 IS NULL; 699 f1 700---- 701 702(1 row) 703 704EXPLAIN (COSTS OFF) 705SELECT * FROM point_tbl WHERE f1 IS NOT NULL ORDER BY f1 <-> '0,1'; 706 QUERY PLAN 707---------------------------------------------- 708 Index Only Scan using gpointind on point_tbl 709 Index Cond: (f1 IS NOT NULL) 710 Order By: (f1 <-> '(0,1)'::point) 711(3 rows) 712 713SELECT * FROM point_tbl WHERE f1 IS NOT NULL ORDER BY f1 <-> '0,1'; 714 f1 715------------ 716 (0,0) 717 (-3,4) 718 (-10,0) 719 (10,10) 720 (-5,-12) 721 (5.1,34.5) 722(6 rows) 723 724EXPLAIN (COSTS OFF) 725SELECT * FROM point_tbl WHERE f1 <@ '(-10,-10),(10,10)':: box ORDER BY f1 <-> '0,1'; 726 QUERY PLAN 727------------------------------------------------ 728 Index Only Scan using gpointind on point_tbl 729 Index Cond: (f1 <@ '(10,10),(-10,-10)'::box) 730 Order By: (f1 <-> '(0,1)'::point) 731(3 rows) 732 733SELECT * FROM point_tbl WHERE f1 <@ '(-10,-10),(10,10)':: box ORDER BY f1 <-> '0,1'; 734 f1 735--------- 736 (0,0) 737 (-3,4) 738 (-10,0) 739 (10,10) 740(4 rows) 741 742EXPLAIN (COSTS OFF) 743SELECT count(*) FROM quad_point_tbl WHERE p IS NULL; 744 QUERY PLAN 745----------------------------------------------------------- 746 Aggregate 747 -> Index Only Scan using sp_quad_ind on quad_point_tbl 748 Index Cond: (p IS NULL) 749(3 rows) 750 751SELECT count(*) FROM quad_point_tbl WHERE p IS NULL; 752 count 753------- 754 3 755(1 row) 756 757EXPLAIN (COSTS OFF) 758SELECT count(*) FROM quad_point_tbl WHERE p IS NOT NULL; 759 QUERY PLAN 760----------------------------------------------------------- 761 Aggregate 762 -> Index Only Scan using sp_quad_ind on quad_point_tbl 763 Index Cond: (p IS NOT NULL) 764(3 rows) 765 766SELECT count(*) FROM quad_point_tbl WHERE p IS NOT NULL; 767 count 768------- 769 11000 770(1 row) 771 772EXPLAIN (COSTS OFF) 773SELECT count(*) FROM quad_point_tbl; 774 QUERY PLAN 775----------------------------------------------------------- 776 Aggregate 777 -> Index Only Scan using sp_quad_ind on quad_point_tbl 778(2 rows) 779 780SELECT count(*) FROM quad_point_tbl; 781 count 782------- 783 11003 784(1 row) 785 786EXPLAIN (COSTS OFF) 787SELECT count(*) FROM quad_point_tbl WHERE p <@ box '(200,200,1000,1000)'; 788 QUERY PLAN 789----------------------------------------------------------- 790 Aggregate 791 -> Index Only Scan using sp_quad_ind on quad_point_tbl 792 Index Cond: (p <@ '(1000,1000),(200,200)'::box) 793(3 rows) 794 795SELECT count(*) FROM quad_point_tbl WHERE p <@ box '(200,200,1000,1000)'; 796 count 797------- 798 1057 799(1 row) 800 801EXPLAIN (COSTS OFF) 802SELECT count(*) FROM quad_point_tbl WHERE box '(200,200,1000,1000)' @> p; 803 QUERY PLAN 804----------------------------------------------------------- 805 Aggregate 806 -> Index Only Scan using sp_quad_ind on quad_point_tbl 807 Index Cond: (p <@ '(1000,1000),(200,200)'::box) 808(3 rows) 809 810SELECT count(*) FROM quad_point_tbl WHERE box '(200,200,1000,1000)' @> p; 811 count 812------- 813 1057 814(1 row) 815 816EXPLAIN (COSTS OFF) 817SELECT count(*) FROM quad_point_tbl WHERE p << '(5000, 4000)'; 818 QUERY PLAN 819----------------------------------------------------------- 820 Aggregate 821 -> Index Only Scan using sp_quad_ind on quad_point_tbl 822 Index Cond: (p << '(5000,4000)'::point) 823(3 rows) 824 825SELECT count(*) FROM quad_point_tbl WHERE p << '(5000, 4000)'; 826 count 827------- 828 6000 829(1 row) 830 831EXPLAIN (COSTS OFF) 832SELECT count(*) FROM quad_point_tbl WHERE p >> '(5000, 4000)'; 833 QUERY PLAN 834----------------------------------------------------------- 835 Aggregate 836 -> Index Only Scan using sp_quad_ind on quad_point_tbl 837 Index Cond: (p >> '(5000,4000)'::point) 838(3 rows) 839 840SELECT count(*) FROM quad_point_tbl WHERE p >> '(5000, 4000)'; 841 count 842------- 843 4999 844(1 row) 845 846EXPLAIN (COSTS OFF) 847SELECT count(*) FROM quad_point_tbl WHERE p <^ '(5000, 4000)'; 848 QUERY PLAN 849----------------------------------------------------------- 850 Aggregate 851 -> Index Only Scan using sp_quad_ind on quad_point_tbl 852 Index Cond: (p <^ '(5000,4000)'::point) 853(3 rows) 854 855SELECT count(*) FROM quad_point_tbl WHERE p <^ '(5000, 4000)'; 856 count 857------- 858 5000 859(1 row) 860 861EXPLAIN (COSTS OFF) 862SELECT count(*) FROM quad_point_tbl WHERE p >^ '(5000, 4000)'; 863 QUERY PLAN 864----------------------------------------------------------- 865 Aggregate 866 -> Index Only Scan using sp_quad_ind on quad_point_tbl 867 Index Cond: (p >^ '(5000,4000)'::point) 868(3 rows) 869 870SELECT count(*) FROM quad_point_tbl WHERE p >^ '(5000, 4000)'; 871 count 872------- 873 5999 874(1 row) 875 876EXPLAIN (COSTS OFF) 877SELECT count(*) FROM quad_point_tbl WHERE p ~= '(4585, 365)'; 878 QUERY PLAN 879----------------------------------------------------------- 880 Aggregate 881 -> Index Only Scan using sp_quad_ind on quad_point_tbl 882 Index Cond: (p ~= '(4585,365)'::point) 883(3 rows) 884 885SELECT count(*) FROM quad_point_tbl WHERE p ~= '(4585, 365)'; 886 count 887------- 888 1 889(1 row) 890 891EXPLAIN (COSTS OFF) 892SELECT count(*) FROM kd_point_tbl WHERE p <@ box '(200,200,1000,1000)'; 893 QUERY PLAN 894--------------------------------------------------------- 895 Aggregate 896 -> Index Only Scan using sp_kd_ind on kd_point_tbl 897 Index Cond: (p <@ '(1000,1000),(200,200)'::box) 898(3 rows) 899 900SELECT count(*) FROM kd_point_tbl WHERE p <@ box '(200,200,1000,1000)'; 901 count 902------- 903 1057 904(1 row) 905 906EXPLAIN (COSTS OFF) 907SELECT count(*) FROM kd_point_tbl WHERE box '(200,200,1000,1000)' @> p; 908 QUERY PLAN 909--------------------------------------------------------- 910 Aggregate 911 -> Index Only Scan using sp_kd_ind on kd_point_tbl 912 Index Cond: (p <@ '(1000,1000),(200,200)'::box) 913(3 rows) 914 915SELECT count(*) FROM kd_point_tbl WHERE box '(200,200,1000,1000)' @> p; 916 count 917------- 918 1057 919(1 row) 920 921EXPLAIN (COSTS OFF) 922SELECT count(*) FROM kd_point_tbl WHERE p << '(5000, 4000)'; 923 QUERY PLAN 924------------------------------------------------------- 925 Aggregate 926 -> Index Only Scan using sp_kd_ind on kd_point_tbl 927 Index Cond: (p << '(5000,4000)'::point) 928(3 rows) 929 930SELECT count(*) FROM kd_point_tbl WHERE p << '(5000, 4000)'; 931 count 932------- 933 6000 934(1 row) 935 936EXPLAIN (COSTS OFF) 937SELECT count(*) FROM kd_point_tbl WHERE p >> '(5000, 4000)'; 938 QUERY PLAN 939------------------------------------------------------- 940 Aggregate 941 -> Index Only Scan using sp_kd_ind on kd_point_tbl 942 Index Cond: (p >> '(5000,4000)'::point) 943(3 rows) 944 945SELECT count(*) FROM kd_point_tbl WHERE p >> '(5000, 4000)'; 946 count 947------- 948 4999 949(1 row) 950 951EXPLAIN (COSTS OFF) 952SELECT count(*) FROM kd_point_tbl WHERE p <^ '(5000, 4000)'; 953 QUERY PLAN 954------------------------------------------------------- 955 Aggregate 956 -> Index Only Scan using sp_kd_ind on kd_point_tbl 957 Index Cond: (p <^ '(5000,4000)'::point) 958(3 rows) 959 960SELECT count(*) FROM kd_point_tbl WHERE p <^ '(5000, 4000)'; 961 count 962------- 963 5000 964(1 row) 965 966EXPLAIN (COSTS OFF) 967SELECT count(*) FROM kd_point_tbl WHERE p >^ '(5000, 4000)'; 968 QUERY PLAN 969------------------------------------------------------- 970 Aggregate 971 -> Index Only Scan using sp_kd_ind on kd_point_tbl 972 Index Cond: (p >^ '(5000,4000)'::point) 973(3 rows) 974 975SELECT count(*) FROM kd_point_tbl WHERE p >^ '(5000, 4000)'; 976 count 977------- 978 5999 979(1 row) 980 981EXPLAIN (COSTS OFF) 982SELECT count(*) FROM kd_point_tbl WHERE p ~= '(4585, 365)'; 983 QUERY PLAN 984------------------------------------------------------- 985 Aggregate 986 -> Index Only Scan using sp_kd_ind on kd_point_tbl 987 Index Cond: (p ~= '(4585,365)'::point) 988(3 rows) 989 990SELECT count(*) FROM kd_point_tbl WHERE p ~= '(4585, 365)'; 991 count 992------- 993 1 994(1 row) 995 996EXPLAIN (COSTS OFF) 997SELECT count(*) FROM radix_text_tbl WHERE t = 'P0123456789abcdef'; 998 QUERY PLAN 999------------------------------------------------------------ 1000 Aggregate 1001 -> Index Only Scan using sp_radix_ind on radix_text_tbl 1002 Index Cond: (t = 'P0123456789abcdef'::text) 1003(3 rows) 1004 1005SELECT count(*) FROM radix_text_tbl WHERE t = 'P0123456789abcdef'; 1006 count 1007------- 1008 1000 1009(1 row) 1010 1011EXPLAIN (COSTS OFF) 1012SELECT count(*) FROM radix_text_tbl WHERE t = 'P0123456789abcde'; 1013 QUERY PLAN 1014------------------------------------------------------------ 1015 Aggregate 1016 -> Index Only Scan using sp_radix_ind on radix_text_tbl 1017 Index Cond: (t = 'P0123456789abcde'::text) 1018(3 rows) 1019 1020SELECT count(*) FROM radix_text_tbl WHERE t = 'P0123456789abcde'; 1021 count 1022------- 1023 1 1024(1 row) 1025 1026EXPLAIN (COSTS OFF) 1027SELECT count(*) FROM radix_text_tbl WHERE t = 'P0123456789abcdefF'; 1028 QUERY PLAN 1029------------------------------------------------------------ 1030 Aggregate 1031 -> Index Only Scan using sp_radix_ind on radix_text_tbl 1032 Index Cond: (t = 'P0123456789abcdefF'::text) 1033(3 rows) 1034 1035SELECT count(*) FROM radix_text_tbl WHERE t = 'P0123456789abcdefF'; 1036 count 1037------- 1038 1 1039(1 row) 1040 1041EXPLAIN (COSTS OFF) 1042SELECT count(*) FROM radix_text_tbl WHERE t < 'Aztec Ct '; 1043 QUERY PLAN 1044---------------------------------------------------------------------- 1045 Aggregate 1046 -> Index Only Scan using sp_radix_ind on radix_text_tbl 1047 Index Cond: (t < 'Aztec Ct '::text) 1048(3 rows) 1049 1050SELECT count(*) FROM radix_text_tbl WHERE t < 'Aztec Ct '; 1051 count 1052------- 1053 272 1054(1 row) 1055 1056EXPLAIN (COSTS OFF) 1057SELECT count(*) FROM radix_text_tbl WHERE t ~<~ 'Aztec Ct '; 1058 QUERY PLAN 1059------------------------------------------------------------------------ 1060 Aggregate 1061 -> Index Only Scan using sp_radix_ind on radix_text_tbl 1062 Index Cond: (t ~<~ 'Aztec Ct '::text) 1063(3 rows) 1064 1065SELECT count(*) FROM radix_text_tbl WHERE t ~<~ 'Aztec Ct '; 1066 count 1067------- 1068 272 1069(1 row) 1070 1071EXPLAIN (COSTS OFF) 1072SELECT count(*) FROM radix_text_tbl WHERE t <= 'Aztec Ct '; 1073 QUERY PLAN 1074----------------------------------------------------------------------- 1075 Aggregate 1076 -> Index Only Scan using sp_radix_ind on radix_text_tbl 1077 Index Cond: (t <= 'Aztec Ct '::text) 1078(3 rows) 1079 1080SELECT count(*) FROM radix_text_tbl WHERE t <= 'Aztec Ct '; 1081 count 1082------- 1083 273 1084(1 row) 1085 1086EXPLAIN (COSTS OFF) 1087SELECT count(*) FROM radix_text_tbl WHERE t ~<=~ 'Aztec Ct '; 1088 QUERY PLAN 1089------------------------------------------------------------------------- 1090 Aggregate 1091 -> Index Only Scan using sp_radix_ind on radix_text_tbl 1092 Index Cond: (t ~<=~ 'Aztec Ct '::text) 1093(3 rows) 1094 1095SELECT count(*) FROM radix_text_tbl WHERE t ~<=~ 'Aztec Ct '; 1096 count 1097------- 1098 273 1099(1 row) 1100 1101EXPLAIN (COSTS OFF) 1102SELECT count(*) FROM radix_text_tbl WHERE t = 'Aztec Ct '; 1103 QUERY PLAN 1104---------------------------------------------------------------------- 1105 Aggregate 1106 -> Index Only Scan using sp_radix_ind on radix_text_tbl 1107 Index Cond: (t = 'Aztec Ct '::text) 1108(3 rows) 1109 1110SELECT count(*) FROM radix_text_tbl WHERE t = 'Aztec Ct '; 1111 count 1112------- 1113 1 1114(1 row) 1115 1116EXPLAIN (COSTS OFF) 1117SELECT count(*) FROM radix_text_tbl WHERE t = 'Worth St '; 1118 QUERY PLAN 1119---------------------------------------------------------------------- 1120 Aggregate 1121 -> Index Only Scan using sp_radix_ind on radix_text_tbl 1122 Index Cond: (t = 'Worth St '::text) 1123(3 rows) 1124 1125SELECT count(*) FROM radix_text_tbl WHERE t = 'Worth St '; 1126 count 1127------- 1128 2 1129(1 row) 1130 1131EXPLAIN (COSTS OFF) 1132SELECT count(*) FROM radix_text_tbl WHERE t >= 'Worth St '; 1133 QUERY PLAN 1134----------------------------------------------------------------------- 1135 Aggregate 1136 -> Index Only Scan using sp_radix_ind on radix_text_tbl 1137 Index Cond: (t >= 'Worth St '::text) 1138(3 rows) 1139 1140SELECT count(*) FROM radix_text_tbl WHERE t >= 'Worth St '; 1141 count 1142------- 1143 50 1144(1 row) 1145 1146EXPLAIN (COSTS OFF) 1147SELECT count(*) FROM radix_text_tbl WHERE t ~>=~ 'Worth St '; 1148 QUERY PLAN 1149------------------------------------------------------------------------- 1150 Aggregate 1151 -> Index Only Scan using sp_radix_ind on radix_text_tbl 1152 Index Cond: (t ~>=~ 'Worth St '::text) 1153(3 rows) 1154 1155SELECT count(*) FROM radix_text_tbl WHERE t ~>=~ 'Worth St '; 1156 count 1157------- 1158 50 1159(1 row) 1160 1161EXPLAIN (COSTS OFF) 1162SELECT count(*) FROM radix_text_tbl WHERE t > 'Worth St '; 1163 QUERY PLAN 1164---------------------------------------------------------------------- 1165 Aggregate 1166 -> Index Only Scan using sp_radix_ind on radix_text_tbl 1167 Index Cond: (t > 'Worth St '::text) 1168(3 rows) 1169 1170SELECT count(*) FROM radix_text_tbl WHERE t > 'Worth St '; 1171 count 1172------- 1173 48 1174(1 row) 1175 1176EXPLAIN (COSTS OFF) 1177SELECT count(*) FROM radix_text_tbl WHERE t ~>~ 'Worth St '; 1178 QUERY PLAN 1179------------------------------------------------------------------------ 1180 Aggregate 1181 -> Index Only Scan using sp_radix_ind on radix_text_tbl 1182 Index Cond: (t ~>~ 'Worth St '::text) 1183(3 rows) 1184 1185SELECT count(*) FROM radix_text_tbl WHERE t ~>~ 'Worth St '; 1186 count 1187------- 1188 48 1189(1 row) 1190 1191EXPLAIN (COSTS OFF) 1192SELECT count(*) FROM radix_text_tbl WHERE t ^@ 'Worth'; 1193 QUERY PLAN 1194------------------------------------------------------------ 1195 Aggregate 1196 -> Index Only Scan using sp_radix_ind on radix_text_tbl 1197 Index Cond: (t ^@ 'Worth'::text) 1198(3 rows) 1199 1200SELECT count(*) FROM radix_text_tbl WHERE t ^@ 'Worth'; 1201 count 1202------- 1203 2 1204(1 row) 1205 1206EXPLAIN (COSTS OFF) 1207SELECT * FROM gpolygon_tbl ORDER BY f1 <-> '(0,0)'::point LIMIT 10; 1208 QUERY PLAN 1209----------------------------------------------------- 1210 Limit 1211 -> Index Scan using ggpolygonind on gpolygon_tbl 1212 Order By: (f1 <-> '(0,0)'::point) 1213(3 rows) 1214 1215SELECT * FROM gpolygon_tbl ORDER BY f1 <-> '(0,0)'::point LIMIT 10; 1216 f1 1217------------------------------------------------- 1218 ((240,359),(240,455),(337,455),(337,359)) 1219 ((662,163),(662,187),(759,187),(759,163)) 1220 ((1000,0),(0,1000)) 1221 ((0,1000),(1000,1000)) 1222 ((1346,344),(1346,403),(1444,403),(1444,344)) 1223 ((278,1409),(278,1457),(369,1457),(369,1409)) 1224 ((907,1156),(907,1201),(948,1201),(948,1156)) 1225 ((1517,971),(1517,1043),(1594,1043),(1594,971)) 1226 ((175,1820),(175,1850),(259,1850),(259,1820)) 1227 ((2424,81),(2424,160),(2424,160),(2424,81)) 1228(10 rows) 1229 1230EXPLAIN (COSTS OFF) 1231SELECT circle_center(f1), round(radius(f1)) as radius FROM gcircle_tbl ORDER BY f1 <-> '(200,300)'::point LIMIT 10; 1232 QUERY PLAN 1233--------------------------------------------------- 1234 Limit 1235 -> Index Scan using ggcircleind on gcircle_tbl 1236 Order By: (f1 <-> '(200,300)'::point) 1237(3 rows) 1238 1239SELECT circle_center(f1), round(radius(f1)) as radius FROM gcircle_tbl ORDER BY f1 <-> '(200,300)'::point LIMIT 10; 1240 circle_center | radius 1241----------------+-------- 1242 (288.5,407) | 68 1243 (710.5,175) | 50 1244 (323.5,1433) | 51 1245 (927.5,1178.5) | 30 1246 (1395,373.5) | 57 1247 (1555.5,1007) | 53 1248 (217,1835) | 45 1249 (489,2421.5) | 22 1250 (2424,120.5) | 40 1251 (751.5,2655) | 20 1252(10 rows) 1253 1254-- Now check the results from bitmap indexscan 1255SET enable_seqscan = OFF; 1256SET enable_indexscan = OFF; 1257SET enable_bitmapscan = ON; 1258EXPLAIN (COSTS OFF) 1259SELECT * FROM point_tbl WHERE f1 <@ '(-10,-10),(10,10)':: box ORDER BY f1 <-> '0,1'; 1260 QUERY PLAN 1261------------------------------------------------------------ 1262 Sort 1263 Sort Key: ((f1 <-> '(0,1)'::point)) 1264 -> Bitmap Heap Scan on point_tbl 1265 Recheck Cond: (f1 <@ '(10,10),(-10,-10)'::box) 1266 -> Bitmap Index Scan on gpointind 1267 Index Cond: (f1 <@ '(10,10),(-10,-10)'::box) 1268(6 rows) 1269 1270SELECT * FROM point_tbl WHERE f1 <@ '(-10,-10),(10,10)':: box ORDER BY f1 <-> '0,1'; 1271 f1 1272--------- 1273 (0,0) 1274 (-3,4) 1275 (-10,0) 1276 (10,10) 1277(4 rows) 1278 1279EXPLAIN (COSTS OFF) 1280SELECT count(*) FROM quad_point_tbl WHERE p IS NULL; 1281 QUERY PLAN 1282---------------------------------------------- 1283 Aggregate 1284 -> Bitmap Heap Scan on quad_point_tbl 1285 Recheck Cond: (p IS NULL) 1286 -> Bitmap Index Scan on sp_quad_ind 1287 Index Cond: (p IS NULL) 1288(5 rows) 1289 1290SELECT count(*) FROM quad_point_tbl WHERE p IS NULL; 1291 count 1292------- 1293 3 1294(1 row) 1295 1296EXPLAIN (COSTS OFF) 1297SELECT count(*) FROM quad_point_tbl WHERE p IS NOT NULL; 1298 QUERY PLAN 1299---------------------------------------------- 1300 Aggregate 1301 -> Bitmap Heap Scan on quad_point_tbl 1302 Recheck Cond: (p IS NOT NULL) 1303 -> Bitmap Index Scan on sp_quad_ind 1304 Index Cond: (p IS NOT NULL) 1305(5 rows) 1306 1307SELECT count(*) FROM quad_point_tbl WHERE p IS NOT NULL; 1308 count 1309------- 1310 11000 1311(1 row) 1312 1313EXPLAIN (COSTS OFF) 1314SELECT count(*) FROM quad_point_tbl; 1315 QUERY PLAN 1316---------------------------------------------- 1317 Aggregate 1318 -> Bitmap Heap Scan on quad_point_tbl 1319 -> Bitmap Index Scan on sp_quad_ind 1320(3 rows) 1321 1322SELECT count(*) FROM quad_point_tbl; 1323 count 1324------- 1325 11003 1326(1 row) 1327 1328EXPLAIN (COSTS OFF) 1329SELECT count(*) FROM quad_point_tbl WHERE p <@ box '(200,200,1000,1000)'; 1330 QUERY PLAN 1331--------------------------------------------------------------- 1332 Aggregate 1333 -> Bitmap Heap Scan on quad_point_tbl 1334 Recheck Cond: (p <@ '(1000,1000),(200,200)'::box) 1335 -> Bitmap Index Scan on sp_quad_ind 1336 Index Cond: (p <@ '(1000,1000),(200,200)'::box) 1337(5 rows) 1338 1339SELECT count(*) FROM quad_point_tbl WHERE p <@ box '(200,200,1000,1000)'; 1340 count 1341------- 1342 1057 1343(1 row) 1344 1345EXPLAIN (COSTS OFF) 1346SELECT count(*) FROM quad_point_tbl WHERE box '(200,200,1000,1000)' @> p; 1347 QUERY PLAN 1348--------------------------------------------------------------- 1349 Aggregate 1350 -> Bitmap Heap Scan on quad_point_tbl 1351 Recheck Cond: ('(1000,1000),(200,200)'::box @> p) 1352 -> Bitmap Index Scan on sp_quad_ind 1353 Index Cond: ('(1000,1000),(200,200)'::box @> p) 1354(5 rows) 1355 1356SELECT count(*) FROM quad_point_tbl WHERE box '(200,200,1000,1000)' @> p; 1357 count 1358------- 1359 1057 1360(1 row) 1361 1362EXPLAIN (COSTS OFF) 1363SELECT count(*) FROM quad_point_tbl WHERE p << '(5000, 4000)'; 1364 QUERY PLAN 1365------------------------------------------------------- 1366 Aggregate 1367 -> Bitmap Heap Scan on quad_point_tbl 1368 Recheck Cond: (p << '(5000,4000)'::point) 1369 -> Bitmap Index Scan on sp_quad_ind 1370 Index Cond: (p << '(5000,4000)'::point) 1371(5 rows) 1372 1373SELECT count(*) FROM quad_point_tbl WHERE p << '(5000, 4000)'; 1374 count 1375------- 1376 6000 1377(1 row) 1378 1379EXPLAIN (COSTS OFF) 1380SELECT count(*) FROM quad_point_tbl WHERE p >> '(5000, 4000)'; 1381 QUERY PLAN 1382------------------------------------------------------- 1383 Aggregate 1384 -> Bitmap Heap Scan on quad_point_tbl 1385 Recheck Cond: (p >> '(5000,4000)'::point) 1386 -> Bitmap Index Scan on sp_quad_ind 1387 Index Cond: (p >> '(5000,4000)'::point) 1388(5 rows) 1389 1390SELECT count(*) FROM quad_point_tbl WHERE p >> '(5000, 4000)'; 1391 count 1392------- 1393 4999 1394(1 row) 1395 1396EXPLAIN (COSTS OFF) 1397SELECT count(*) FROM quad_point_tbl WHERE p <^ '(5000, 4000)'; 1398 QUERY PLAN 1399------------------------------------------------------- 1400 Aggregate 1401 -> Bitmap Heap Scan on quad_point_tbl 1402 Recheck Cond: (p <^ '(5000,4000)'::point) 1403 -> Bitmap Index Scan on sp_quad_ind 1404 Index Cond: (p <^ '(5000,4000)'::point) 1405(5 rows) 1406 1407SELECT count(*) FROM quad_point_tbl WHERE p <^ '(5000, 4000)'; 1408 count 1409------- 1410 5000 1411(1 row) 1412 1413EXPLAIN (COSTS OFF) 1414SELECT count(*) FROM quad_point_tbl WHERE p >^ '(5000, 4000)'; 1415 QUERY PLAN 1416------------------------------------------------------- 1417 Aggregate 1418 -> Bitmap Heap Scan on quad_point_tbl 1419 Recheck Cond: (p >^ '(5000,4000)'::point) 1420 -> Bitmap Index Scan on sp_quad_ind 1421 Index Cond: (p >^ '(5000,4000)'::point) 1422(5 rows) 1423 1424SELECT count(*) FROM quad_point_tbl WHERE p >^ '(5000, 4000)'; 1425 count 1426------- 1427 5999 1428(1 row) 1429 1430EXPLAIN (COSTS OFF) 1431SELECT count(*) FROM quad_point_tbl WHERE p ~= '(4585, 365)'; 1432 QUERY PLAN 1433------------------------------------------------------ 1434 Aggregate 1435 -> Bitmap Heap Scan on quad_point_tbl 1436 Recheck Cond: (p ~= '(4585,365)'::point) 1437 -> Bitmap Index Scan on sp_quad_ind 1438 Index Cond: (p ~= '(4585,365)'::point) 1439(5 rows) 1440 1441SELECT count(*) FROM quad_point_tbl WHERE p ~= '(4585, 365)'; 1442 count 1443------- 1444 1 1445(1 row) 1446 1447EXPLAIN (COSTS OFF) 1448SELECT count(*) FROM kd_point_tbl WHERE p <@ box '(200,200,1000,1000)'; 1449 QUERY PLAN 1450--------------------------------------------------------------- 1451 Aggregate 1452 -> Bitmap Heap Scan on kd_point_tbl 1453 Recheck Cond: (p <@ '(1000,1000),(200,200)'::box) 1454 -> Bitmap Index Scan on sp_kd_ind 1455 Index Cond: (p <@ '(1000,1000),(200,200)'::box) 1456(5 rows) 1457 1458SELECT count(*) FROM kd_point_tbl WHERE p <@ box '(200,200,1000,1000)'; 1459 count 1460------- 1461 1057 1462(1 row) 1463 1464EXPLAIN (COSTS OFF) 1465SELECT count(*) FROM kd_point_tbl WHERE box '(200,200,1000,1000)' @> p; 1466 QUERY PLAN 1467--------------------------------------------------------------- 1468 Aggregate 1469 -> Bitmap Heap Scan on kd_point_tbl 1470 Recheck Cond: ('(1000,1000),(200,200)'::box @> p) 1471 -> Bitmap Index Scan on sp_kd_ind 1472 Index Cond: ('(1000,1000),(200,200)'::box @> p) 1473(5 rows) 1474 1475SELECT count(*) FROM kd_point_tbl WHERE box '(200,200,1000,1000)' @> p; 1476 count 1477------- 1478 1057 1479(1 row) 1480 1481EXPLAIN (COSTS OFF) 1482SELECT count(*) FROM kd_point_tbl WHERE p << '(5000, 4000)'; 1483 QUERY PLAN 1484------------------------------------------------------- 1485 Aggregate 1486 -> Bitmap Heap Scan on kd_point_tbl 1487 Recheck Cond: (p << '(5000,4000)'::point) 1488 -> Bitmap Index Scan on sp_kd_ind 1489 Index Cond: (p << '(5000,4000)'::point) 1490(5 rows) 1491 1492SELECT count(*) FROM kd_point_tbl WHERE p << '(5000, 4000)'; 1493 count 1494------- 1495 6000 1496(1 row) 1497 1498EXPLAIN (COSTS OFF) 1499SELECT count(*) FROM kd_point_tbl WHERE p >> '(5000, 4000)'; 1500 QUERY PLAN 1501------------------------------------------------------- 1502 Aggregate 1503 -> Bitmap Heap Scan on kd_point_tbl 1504 Recheck Cond: (p >> '(5000,4000)'::point) 1505 -> Bitmap Index Scan on sp_kd_ind 1506 Index Cond: (p >> '(5000,4000)'::point) 1507(5 rows) 1508 1509SELECT count(*) FROM kd_point_tbl WHERE p >> '(5000, 4000)'; 1510 count 1511------- 1512 4999 1513(1 row) 1514 1515EXPLAIN (COSTS OFF) 1516SELECT count(*) FROM kd_point_tbl WHERE p <^ '(5000, 4000)'; 1517 QUERY PLAN 1518------------------------------------------------------- 1519 Aggregate 1520 -> Bitmap Heap Scan on kd_point_tbl 1521 Recheck Cond: (p <^ '(5000,4000)'::point) 1522 -> Bitmap Index Scan on sp_kd_ind 1523 Index Cond: (p <^ '(5000,4000)'::point) 1524(5 rows) 1525 1526SELECT count(*) FROM kd_point_tbl WHERE p <^ '(5000, 4000)'; 1527 count 1528------- 1529 5000 1530(1 row) 1531 1532EXPLAIN (COSTS OFF) 1533SELECT count(*) FROM kd_point_tbl WHERE p >^ '(5000, 4000)'; 1534 QUERY PLAN 1535------------------------------------------------------- 1536 Aggregate 1537 -> Bitmap Heap Scan on kd_point_tbl 1538 Recheck Cond: (p >^ '(5000,4000)'::point) 1539 -> Bitmap Index Scan on sp_kd_ind 1540 Index Cond: (p >^ '(5000,4000)'::point) 1541(5 rows) 1542 1543SELECT count(*) FROM kd_point_tbl WHERE p >^ '(5000, 4000)'; 1544 count 1545------- 1546 5999 1547(1 row) 1548 1549EXPLAIN (COSTS OFF) 1550SELECT count(*) FROM kd_point_tbl WHERE p ~= '(4585, 365)'; 1551 QUERY PLAN 1552------------------------------------------------------ 1553 Aggregate 1554 -> Bitmap Heap Scan on kd_point_tbl 1555 Recheck Cond: (p ~= '(4585,365)'::point) 1556 -> Bitmap Index Scan on sp_kd_ind 1557 Index Cond: (p ~= '(4585,365)'::point) 1558(5 rows) 1559 1560SELECT count(*) FROM kd_point_tbl WHERE p ~= '(4585, 365)'; 1561 count 1562------- 1563 1 1564(1 row) 1565 1566EXPLAIN (COSTS OFF) 1567SELECT count(*) FROM radix_text_tbl WHERE t = 'P0123456789abcdef'; 1568 QUERY PLAN 1569----------------------------------------------------------- 1570 Aggregate 1571 -> Bitmap Heap Scan on radix_text_tbl 1572 Recheck Cond: (t = 'P0123456789abcdef'::text) 1573 -> Bitmap Index Scan on sp_radix_ind 1574 Index Cond: (t = 'P0123456789abcdef'::text) 1575(5 rows) 1576 1577SELECT count(*) FROM radix_text_tbl WHERE t = 'P0123456789abcdef'; 1578 count 1579------- 1580 1000 1581(1 row) 1582 1583EXPLAIN (COSTS OFF) 1584SELECT count(*) FROM radix_text_tbl WHERE t = 'P0123456789abcde'; 1585 QUERY PLAN 1586---------------------------------------------------------- 1587 Aggregate 1588 -> Bitmap Heap Scan on radix_text_tbl 1589 Recheck Cond: (t = 'P0123456789abcde'::text) 1590 -> Bitmap Index Scan on sp_radix_ind 1591 Index Cond: (t = 'P0123456789abcde'::text) 1592(5 rows) 1593 1594SELECT count(*) FROM radix_text_tbl WHERE t = 'P0123456789abcde'; 1595 count 1596------- 1597 1 1598(1 row) 1599 1600EXPLAIN (COSTS OFF) 1601SELECT count(*) FROM radix_text_tbl WHERE t = 'P0123456789abcdefF'; 1602 QUERY PLAN 1603------------------------------------------------------------ 1604 Aggregate 1605 -> Bitmap Heap Scan on radix_text_tbl 1606 Recheck Cond: (t = 'P0123456789abcdefF'::text) 1607 -> Bitmap Index Scan on sp_radix_ind 1608 Index Cond: (t = 'P0123456789abcdefF'::text) 1609(5 rows) 1610 1611SELECT count(*) FROM radix_text_tbl WHERE t = 'P0123456789abcdefF'; 1612 count 1613------- 1614 1 1615(1 row) 1616 1617EXPLAIN (COSTS OFF) 1618SELECT count(*) FROM radix_text_tbl WHERE t < 'Aztec Ct '; 1619 QUERY PLAN 1620---------------------------------------------------------------------------- 1621 Aggregate 1622 -> Bitmap Heap Scan on radix_text_tbl 1623 Recheck Cond: (t < 'Aztec Ct '::text) 1624 -> Bitmap Index Scan on sp_radix_ind 1625 Index Cond: (t < 'Aztec Ct '::text) 1626(5 rows) 1627 1628SELECT count(*) FROM radix_text_tbl WHERE t < 'Aztec Ct '; 1629 count 1630------- 1631 272 1632(1 row) 1633 1634EXPLAIN (COSTS OFF) 1635SELECT count(*) FROM radix_text_tbl WHERE t ~<~ 'Aztec Ct '; 1636 QUERY PLAN 1637------------------------------------------------------------------------------ 1638 Aggregate 1639 -> Bitmap Heap Scan on radix_text_tbl 1640 Recheck Cond: (t ~<~ 'Aztec Ct '::text) 1641 -> Bitmap Index Scan on sp_radix_ind 1642 Index Cond: (t ~<~ 'Aztec Ct '::text) 1643(5 rows) 1644 1645SELECT count(*) FROM radix_text_tbl WHERE t ~<~ 'Aztec Ct '; 1646 count 1647------- 1648 272 1649(1 row) 1650 1651EXPLAIN (COSTS OFF) 1652SELECT count(*) FROM radix_text_tbl WHERE t <= 'Aztec Ct '; 1653 QUERY PLAN 1654----------------------------------------------------------------------------- 1655 Aggregate 1656 -> Bitmap Heap Scan on radix_text_tbl 1657 Recheck Cond: (t <= 'Aztec Ct '::text) 1658 -> Bitmap Index Scan on sp_radix_ind 1659 Index Cond: (t <= 'Aztec Ct '::text) 1660(5 rows) 1661 1662SELECT count(*) FROM radix_text_tbl WHERE t <= 'Aztec Ct '; 1663 count 1664------- 1665 273 1666(1 row) 1667 1668EXPLAIN (COSTS OFF) 1669SELECT count(*) FROM radix_text_tbl WHERE t ~<=~ 'Aztec Ct '; 1670 QUERY PLAN 1671------------------------------------------------------------------------------- 1672 Aggregate 1673 -> Bitmap Heap Scan on radix_text_tbl 1674 Recheck Cond: (t ~<=~ 'Aztec Ct '::text) 1675 -> Bitmap Index Scan on sp_radix_ind 1676 Index Cond: (t ~<=~ 'Aztec Ct '::text) 1677(5 rows) 1678 1679SELECT count(*) FROM radix_text_tbl WHERE t ~<=~ 'Aztec Ct '; 1680 count 1681------- 1682 273 1683(1 row) 1684 1685EXPLAIN (COSTS OFF) 1686SELECT count(*) FROM radix_text_tbl WHERE t = 'Aztec Ct '; 1687 QUERY PLAN 1688---------------------------------------------------------------------------- 1689 Aggregate 1690 -> Bitmap Heap Scan on radix_text_tbl 1691 Recheck Cond: (t = 'Aztec Ct '::text) 1692 -> Bitmap Index Scan on sp_radix_ind 1693 Index Cond: (t = 'Aztec Ct '::text) 1694(5 rows) 1695 1696SELECT count(*) FROM radix_text_tbl WHERE t = 'Aztec Ct '; 1697 count 1698------- 1699 1 1700(1 row) 1701 1702EXPLAIN (COSTS OFF) 1703SELECT count(*) FROM radix_text_tbl WHERE t = 'Worth St '; 1704 QUERY PLAN 1705---------------------------------------------------------------------------- 1706 Aggregate 1707 -> Bitmap Heap Scan on radix_text_tbl 1708 Recheck Cond: (t = 'Worth St '::text) 1709 -> Bitmap Index Scan on sp_radix_ind 1710 Index Cond: (t = 'Worth St '::text) 1711(5 rows) 1712 1713SELECT count(*) FROM radix_text_tbl WHERE t = 'Worth St '; 1714 count 1715------- 1716 2 1717(1 row) 1718 1719EXPLAIN (COSTS OFF) 1720SELECT count(*) FROM radix_text_tbl WHERE t >= 'Worth St '; 1721 QUERY PLAN 1722----------------------------------------------------------------------------- 1723 Aggregate 1724 -> Bitmap Heap Scan on radix_text_tbl 1725 Recheck Cond: (t >= 'Worth St '::text) 1726 -> Bitmap Index Scan on sp_radix_ind 1727 Index Cond: (t >= 'Worth St '::text) 1728(5 rows) 1729 1730SELECT count(*) FROM radix_text_tbl WHERE t >= 'Worth St '; 1731 count 1732------- 1733 50 1734(1 row) 1735 1736EXPLAIN (COSTS OFF) 1737SELECT count(*) FROM radix_text_tbl WHERE t ~>=~ 'Worth St '; 1738 QUERY PLAN 1739------------------------------------------------------------------------------- 1740 Aggregate 1741 -> Bitmap Heap Scan on radix_text_tbl 1742 Recheck Cond: (t ~>=~ 'Worth St '::text) 1743 -> Bitmap Index Scan on sp_radix_ind 1744 Index Cond: (t ~>=~ 'Worth St '::text) 1745(5 rows) 1746 1747SELECT count(*) FROM radix_text_tbl WHERE t ~>=~ 'Worth St '; 1748 count 1749------- 1750 50 1751(1 row) 1752 1753EXPLAIN (COSTS OFF) 1754SELECT count(*) FROM radix_text_tbl WHERE t > 'Worth St '; 1755 QUERY PLAN 1756---------------------------------------------------------------------------- 1757 Aggregate 1758 -> Bitmap Heap Scan on radix_text_tbl 1759 Recheck Cond: (t > 'Worth St '::text) 1760 -> Bitmap Index Scan on sp_radix_ind 1761 Index Cond: (t > 'Worth St '::text) 1762(5 rows) 1763 1764SELECT count(*) FROM radix_text_tbl WHERE t > 'Worth St '; 1765 count 1766------- 1767 48 1768(1 row) 1769 1770EXPLAIN (COSTS OFF) 1771SELECT count(*) FROM radix_text_tbl WHERE t ~>~ 'Worth St '; 1772 QUERY PLAN 1773------------------------------------------------------------------------------ 1774 Aggregate 1775 -> Bitmap Heap Scan on radix_text_tbl 1776 Recheck Cond: (t ~>~ 'Worth St '::text) 1777 -> Bitmap Index Scan on sp_radix_ind 1778 Index Cond: (t ~>~ 'Worth St '::text) 1779(5 rows) 1780 1781SELECT count(*) FROM radix_text_tbl WHERE t ~>~ 'Worth St '; 1782 count 1783------- 1784 48 1785(1 row) 1786 1787EXPLAIN (COSTS OFF) 1788SELECT count(*) FROM radix_text_tbl WHERE t ^@ 'Worth'; 1789 QUERY PLAN 1790------------------------------------------------ 1791 Aggregate 1792 -> Bitmap Heap Scan on radix_text_tbl 1793 Recheck Cond: (t ^@ 'Worth'::text) 1794 -> Bitmap Index Scan on sp_radix_ind 1795 Index Cond: (t ^@ 'Worth'::text) 1796(5 rows) 1797 1798SELECT count(*) FROM radix_text_tbl WHERE t ^@ 'Worth'; 1799 count 1800------- 1801 2 1802(1 row) 1803 1804RESET enable_seqscan; 1805RESET enable_indexscan; 1806RESET enable_bitmapscan; 1807-- 1808-- GIN over int[] and text[] 1809-- 1810-- Note: GIN currently supports only bitmap scans, not plain indexscans 1811-- 1812SET enable_seqscan = OFF; 1813SET enable_indexscan = OFF; 1814SET enable_bitmapscan = ON; 1815CREATE INDEX intarrayidx ON array_index_op_test USING gin (i); 1816explain (costs off) 1817SELECT * FROM array_index_op_test WHERE i @> '{32}' ORDER BY seqno; 1818 QUERY PLAN 1819---------------------------------------------------- 1820 Sort 1821 Sort Key: seqno 1822 -> Bitmap Heap Scan on array_index_op_test 1823 Recheck Cond: (i @> '{32}'::integer[]) 1824 -> Bitmap Index Scan on intarrayidx 1825 Index Cond: (i @> '{32}'::integer[]) 1826(6 rows) 1827 1828SELECT * FROM array_index_op_test WHERE i @> '{32}' ORDER BY seqno; 1829 seqno | i | t 1830-------+---------------------------------+------------------------------------------------------------------------------------------------------------------------------------ 1831 6 | {39,35,5,94,17,92,60,32} | {AAAAAAAAAAAAAAA35875,AAAAAAAAAAAAAAAA23657} 1832 74 | {32} | {AAAAAAAAAAAAAAAA1729,AAAAAAAAAAAAA22860,AAAAAA99807,AAAAA17383,AAAAAAAAAAAAAAA67062,AAAAAAAAAAA15165,AAAAAAAAAAA50956} 1833 77 | {97,15,32,17,55,59,18,37,50,39} | {AAAAAAAAAAAA67946,AAAAAA54032,AAAAAAAA81587,55847,AAAAAAAAAAAAAA28620,AAAAAAAAAAAAAAAAA43052,AAAAAA75463,AAAA49534,AAAAAAAA44066} 1834 89 | {40,32,17,6,30,88} | {AA44673,AAAAAAAAAAA6119,AAAAAAAAAAAAAAAA23657,AAAAAAAAAAAAAAAAAA47955,AAAAAAAAAAAAAAAA33598,AAAAAAAAAAA33576,AA44673} 1835 98 | {38,34,32,89} | {AAAAAAAAAAAAAAAAAA71621,AAAA8857,AAAAAAAAAAAAAAAAAAA65037,AAAAAAAAAAAAAAAA31334,AAAAAAAAAA48845} 1836 100 | {85,32,57,39,49,84,32,3,30} | {AAAAAAA80240,AAAAAAAAAAAAAAAA1729,AAAAA60038,AAAAAAAAAAA92631,AAAAAAAA9523} 1837(6 rows) 1838 1839SELECT * FROM array_index_op_test WHERE i && '{32}' ORDER BY seqno; 1840 seqno | i | t 1841-------+---------------------------------+------------------------------------------------------------------------------------------------------------------------------------ 1842 6 | {39,35,5,94,17,92,60,32} | {AAAAAAAAAAAAAAA35875,AAAAAAAAAAAAAAAA23657} 1843 74 | {32} | {AAAAAAAAAAAAAAAA1729,AAAAAAAAAAAAA22860,AAAAAA99807,AAAAA17383,AAAAAAAAAAAAAAA67062,AAAAAAAAAAA15165,AAAAAAAAAAA50956} 1844 77 | {97,15,32,17,55,59,18,37,50,39} | {AAAAAAAAAAAA67946,AAAAAA54032,AAAAAAAA81587,55847,AAAAAAAAAAAAAA28620,AAAAAAAAAAAAAAAAA43052,AAAAAA75463,AAAA49534,AAAAAAAA44066} 1845 89 | {40,32,17,6,30,88} | {AA44673,AAAAAAAAAAA6119,AAAAAAAAAAAAAAAA23657,AAAAAAAAAAAAAAAAAA47955,AAAAAAAAAAAAAAAA33598,AAAAAAAAAAA33576,AA44673} 1846 98 | {38,34,32,89} | {AAAAAAAAAAAAAAAAAA71621,AAAA8857,AAAAAAAAAAAAAAAAAAA65037,AAAAAAAAAAAAAAAA31334,AAAAAAAAAA48845} 1847 100 | {85,32,57,39,49,84,32,3,30} | {AAAAAAA80240,AAAAAAAAAAAAAAAA1729,AAAAA60038,AAAAAAAAAAA92631,AAAAAAAA9523} 1848(6 rows) 1849 1850SELECT * FROM array_index_op_test WHERE i @> '{17}' ORDER BY seqno; 1851 seqno | i | t 1852-------+---------------------------------+------------------------------------------------------------------------------------------------------------------------------------ 1853 6 | {39,35,5,94,17,92,60,32} | {AAAAAAAAAAAAAAA35875,AAAAAAAAAAAAAAAA23657} 1854 12 | {17,99,18,52,91,72,0,43,96,23} | {AAAAA33250,AAAAAAAAAAAAAAAAAAA85420,AAAAAAAAAAA33576} 1855 15 | {17,14,16,63,67} | {AA6416,AAAAAAAAAA646,AAAAA95309} 1856 19 | {52,82,17,74,23,46,69,51,75} | {AAAAAAAAAAAAA73084,AAAAA75968,AAAAAAAAAAAAAAAA14047,AAAAAAA80240,AAAAAAAAAAAAAAAAAAA1205,A68938} 1857 53 | {38,17} | {AAAAAAAAAAA21658} 1858 65 | {61,5,76,59,17} | {AAAAAA99807,AAAAA64741,AAAAAAAAAAA53908,AA21643,AAAAAAAAA10012} 1859 77 | {97,15,32,17,55,59,18,37,50,39} | {AAAAAAAAAAAA67946,AAAAAA54032,AAAAAAAA81587,55847,AAAAAAAAAAAAAA28620,AAAAAAAAAAAAAAAAA43052,AAAAAA75463,AAAA49534,AAAAAAAA44066} 1860 89 | {40,32,17,6,30,88} | {AA44673,AAAAAAAAAAA6119,AAAAAAAAAAAAAAAA23657,AAAAAAAAAAAAAAAAAA47955,AAAAAAAAAAAAAAAA33598,AAAAAAAAAAA33576,AA44673} 1861(8 rows) 1862 1863SELECT * FROM array_index_op_test WHERE i && '{17}' ORDER BY seqno; 1864 seqno | i | t 1865-------+---------------------------------+------------------------------------------------------------------------------------------------------------------------------------ 1866 6 | {39,35,5,94,17,92,60,32} | {AAAAAAAAAAAAAAA35875,AAAAAAAAAAAAAAAA23657} 1867 12 | {17,99,18,52,91,72,0,43,96,23} | {AAAAA33250,AAAAAAAAAAAAAAAAAAA85420,AAAAAAAAAAA33576} 1868 15 | {17,14,16,63,67} | {AA6416,AAAAAAAAAA646,AAAAA95309} 1869 19 | {52,82,17,74,23,46,69,51,75} | {AAAAAAAAAAAAA73084,AAAAA75968,AAAAAAAAAAAAAAAA14047,AAAAAAA80240,AAAAAAAAAAAAAAAAAAA1205,A68938} 1870 53 | {38,17} | {AAAAAAAAAAA21658} 1871 65 | {61,5,76,59,17} | {AAAAAA99807,AAAAA64741,AAAAAAAAAAA53908,AA21643,AAAAAAAAA10012} 1872 77 | {97,15,32,17,55,59,18,37,50,39} | {AAAAAAAAAAAA67946,AAAAAA54032,AAAAAAAA81587,55847,AAAAAAAAAAAAAA28620,AAAAAAAAAAAAAAAAA43052,AAAAAA75463,AAAA49534,AAAAAAAA44066} 1873 89 | {40,32,17,6,30,88} | {AA44673,AAAAAAAAAAA6119,AAAAAAAAAAAAAAAA23657,AAAAAAAAAAAAAAAAAA47955,AAAAAAAAAAAAAAAA33598,AAAAAAAAAAA33576,AA44673} 1874(8 rows) 1875 1876SELECT * FROM array_index_op_test WHERE i @> '{32,17}' ORDER BY seqno; 1877 seqno | i | t 1878-------+---------------------------------+------------------------------------------------------------------------------------------------------------------------------------ 1879 6 | {39,35,5,94,17,92,60,32} | {AAAAAAAAAAAAAAA35875,AAAAAAAAAAAAAAAA23657} 1880 77 | {97,15,32,17,55,59,18,37,50,39} | {AAAAAAAAAAAA67946,AAAAAA54032,AAAAAAAA81587,55847,AAAAAAAAAAAAAA28620,AAAAAAAAAAAAAAAAA43052,AAAAAA75463,AAAA49534,AAAAAAAA44066} 1881 89 | {40,32,17,6,30,88} | {AA44673,AAAAAAAAAAA6119,AAAAAAAAAAAAAAAA23657,AAAAAAAAAAAAAAAAAA47955,AAAAAAAAAAAAAAAA33598,AAAAAAAAAAA33576,AA44673} 1882(3 rows) 1883 1884SELECT * FROM array_index_op_test WHERE i && '{32,17}' ORDER BY seqno; 1885 seqno | i | t 1886-------+---------------------------------+------------------------------------------------------------------------------------------------------------------------------------ 1887 6 | {39,35,5,94,17,92,60,32} | {AAAAAAAAAAAAAAA35875,AAAAAAAAAAAAAAAA23657} 1888 12 | {17,99,18,52,91,72,0,43,96,23} | {AAAAA33250,AAAAAAAAAAAAAAAAAAA85420,AAAAAAAAAAA33576} 1889 15 | {17,14,16,63,67} | {AA6416,AAAAAAAAAA646,AAAAA95309} 1890 19 | {52,82,17,74,23,46,69,51,75} | {AAAAAAAAAAAAA73084,AAAAA75968,AAAAAAAAAAAAAAAA14047,AAAAAAA80240,AAAAAAAAAAAAAAAAAAA1205,A68938} 1891 53 | {38,17} | {AAAAAAAAAAA21658} 1892 65 | {61,5,76,59,17} | {AAAAAA99807,AAAAA64741,AAAAAAAAAAA53908,AA21643,AAAAAAAAA10012} 1893 74 | {32} | {AAAAAAAAAAAAAAAA1729,AAAAAAAAAAAAA22860,AAAAAA99807,AAAAA17383,AAAAAAAAAAAAAAA67062,AAAAAAAAAAA15165,AAAAAAAAAAA50956} 1894 77 | {97,15,32,17,55,59,18,37,50,39} | {AAAAAAAAAAAA67946,AAAAAA54032,AAAAAAAA81587,55847,AAAAAAAAAAAAAA28620,AAAAAAAAAAAAAAAAA43052,AAAAAA75463,AAAA49534,AAAAAAAA44066} 1895 89 | {40,32,17,6,30,88} | {AA44673,AAAAAAAAAAA6119,AAAAAAAAAAAAAAAA23657,AAAAAAAAAAAAAAAAAA47955,AAAAAAAAAAAAAAAA33598,AAAAAAAAAAA33576,AA44673} 1896 98 | {38,34,32,89} | {AAAAAAAAAAAAAAAAAA71621,AAAA8857,AAAAAAAAAAAAAAAAAAA65037,AAAAAAAAAAAAAAAA31334,AAAAAAAAAA48845} 1897 100 | {85,32,57,39,49,84,32,3,30} | {AAAAAAA80240,AAAAAAAAAAAAAAAA1729,AAAAA60038,AAAAAAAAAAA92631,AAAAAAAA9523} 1898(11 rows) 1899 1900SELECT * FROM array_index_op_test WHERE i <@ '{38,34,32,89}' ORDER BY seqno; 1901 seqno | i | t 1902-------+---------------+---------------------------------------------------------------------------------------------------------------------------- 1903 40 | {34} | {AAAAAAAAAAAAAA10611,AAAAAAAAAAAAAAAAAAA1205,AAAAAAAAAAA50956,AAAAAAAAAAAAAAAA31334,AAAAA70466,AAAAAAAA81587,AAAAAAA74623} 1904 74 | {32} | {AAAAAAAAAAAAAAAA1729,AAAAAAAAAAAAA22860,AAAAAA99807,AAAAA17383,AAAAAAAAAAAAAAA67062,AAAAAAAAAAA15165,AAAAAAAAAAA50956} 1905 98 | {38,34,32,89} | {AAAAAAAAAAAAAAAAAA71621,AAAA8857,AAAAAAAAAAAAAAAAAAA65037,AAAAAAAAAAAAAAAA31334,AAAAAAAAAA48845} 1906 101 | {} | {} 1907(4 rows) 1908 1909SELECT * FROM array_index_op_test WHERE i = '{47,77}' ORDER BY seqno; 1910 seqno | i | t 1911-------+---------+----------------------------------------------------------------------------------------------------------------- 1912 95 | {47,77} | {AAAAAAAAAAAAAAAAA764,AAAAAAAAAAA74076,AAAAAAAAAA18107,AAAAA40681,AAAAAAAAAAAAAAA35875,AAAAA60038,AAAAAAA56483} 1913(1 row) 1914 1915SELECT * FROM array_index_op_test WHERE i = '{}' ORDER BY seqno; 1916 seqno | i | t 1917-------+----+---- 1918 101 | {} | {} 1919(1 row) 1920 1921SELECT * FROM array_index_op_test WHERE i @> '{}' ORDER BY seqno; 1922 seqno | i | t 1923-------+---------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 1924 1 | {92,75,71,52,64,83} | {AAAAAAAA44066,AAAAAA1059,AAAAAAAAAAA176,AAAAAAA48038} 1925 2 | {3,6} | {AAAAAA98232,AAAAAAAA79710,AAAAAAAAAAAAAAAAA69675,AAAAAAAAAAAAAAAA55798,AAAAAAAAA12793} 1926 3 | {37,64,95,43,3,41,13,30,11,43} | {AAAAAAAAAA48845,AAAAA75968,AAAAA95309,AAA54451,AAAAAAAAAA22292,AAAAAAA99836,A96617,AA17009,AAAAAAAAAAAAAA95246} 1927 4 | {71,39,99,55,33,75,45} | {AAAAAAAAA53663,AAAAAAAAAAAAAAA67062,AAAAAAAAAA64777,AAA99043,AAAAAAAAAAAAAAAAAAA91804,39557} 1928 5 | {50,42,77,50,4} | {AAAAAAAAAAAAAAAAA26540,AAAAAAA79710,AAAAAAAAAAAAAAAAAAA1205,AAAAAAAAAAA176,AAAAA95309,AAAAAAAAAAA46154,AAAAAA66777,AAAAAAAAA27249,AAAAAAAAAA64777,AAAAAAAAAAAAAAAAAAA70104} 1929 6 | {39,35,5,94,17,92,60,32} | {AAAAAAAAAAAAAAA35875,AAAAAAAAAAAAAAAA23657} 1930 7 | {12,51,88,64,8} | {AAAAAAAAAAAAAAAAAA12591,AAAAAAAAAAAAAAAAA50407,AAAAAAAAAAAA67946} 1931 8 | {60,84} | {AAAAAAA81898,AAAAAA1059,AAAAAAAAAAAA81511,AAAAA961,AAAAAAAAAAAAAAAA31334,AAAAA64741,AA6416,AAAAAAAAAAAAAAAAAA32918,AAAAAAAAAAAAAAAAA50407} 1932 9 | {56,52,35,27,80,44,81,22} | {AAAAAAAAAAAAAAA73034,AAAAAAAAAAAAA7929,AAAAAAA66161,AA88409,39557,A27153,AAAAAAAA9523,AAAAAAAAAAA99000} 1933 10 | {71,5,45} | {AAAAAAAAAAA21658,AAAAAAAAAAAA21089,AAA54451,AAAAAAAAAAAAAAAAAA54141,AAAAAAAAAAAAAA28620,AAAAAAAAAAA21658,AAAAAAAAAAA74076,AAAAAAAAA27249} 1934 11 | {41,86,74,48,22,74,47,50} | {AAAAAAAA9523,AAAAAAAAAAAA37562,AAAAAAAAAAAAAAAA14047,AAAAAAAAAAA46154,AAAA41702,AAAAAAAAAAAAAAAAA764,AAAAA62737,39557} 1935 12 | {17,99,18,52,91,72,0,43,96,23} | {AAAAA33250,AAAAAAAAAAAAAAAAAAA85420,AAAAAAAAAAA33576} 1936 13 | {3,52,34,23} | {AAAAAA98232,AAAA49534,AAAAAAAAAAA21658} 1937 14 | {78,57,19} | {AAAA8857,AAAAAAAAAAAAAAA73034,AAAAAAAA81587,AAAAAAAAAAAAAAA68526,AAAAA75968,AAAAAAAAAAAAAA65909,AAAAAAAAA10012,AAAAAAAAAAAAAA65909} 1938 15 | {17,14,16,63,67} | {AA6416,AAAAAAAAAA646,AAAAA95309} 1939 16 | {14,63,85,11} | {AAAAAA66777} 1940 17 | {7,10,81,85} | {AAAAAA43678,AAAAAAA12144,AAAAAAAAAAA50956,AAAAAAAAAAAAAAAAAAA15356} 1941 18 | {1} | {AAAAAAAAAAA33576,AAAAA95309,64261,AAA59323,AAAAAAAAAAAAAA95246,55847,AAAAAAAAAAAA67946,AAAAAAAAAAAAAAAAAA64374} 1942 19 | {52,82,17,74,23,46,69,51,75} | {AAAAAAAAAAAAA73084,AAAAA75968,AAAAAAAAAAAAAAAA14047,AAAAAAA80240,AAAAAAAAAAAAAAAAAAA1205,A68938} 1943 20 | {72,89,70,51,54,37,8,49,79} | {AAAAAA58494} 1944 21 | {2,8,65,10,5,79,43} | {AAAAAAAAAAAAAAAAA88852,AAAAAAAAAAAAAAAAAAA91804,AAAAA64669,AAAAAAAAAAAAAAAA1443,AAAAAAAAAAAAAAAA23657,AAAAA12179,AAAAAAAAAAAAAAAAA88852,AAAAAAAAAAAAAAAA31334,AAAAAAAAAAAAAAAA41303,AAAAAAAAAAAAAAAAAAA85420} 1945 22 | {11,6,56,62,53,30} | {AAAAAAAA72908} 1946 23 | {40,90,5,38,72,40,30,10,43,55} | {A6053,AAAAAAAAAAA6119,AA44673,AAAAAAAAAAAAAAAAA764,AA17009,AAAAA17383,AAAAA70514,AAAAA33250,AAAAA95309,AAAAAAAAAAAA37562} 1947 24 | {94,61,99,35,48} | {AAAAAAAAAAA50956,AAAAAAAAAAA15165,AAAA85070,AAAAAAAAAAAAAAA36627,AAAAA961,AAAAAAAAAA55219} 1948 25 | {31,1,10,11,27,79,38} | {AAAAAAAAAAAAAAAAAA59334,45449} 1949 26 | {71,10,9,69,75} | {47735,AAAAAAA21462,AAAAAAAAAAAAAAAAA6897,AAAAAAAAAAAAAAAAAAA91804,AAAAAAAAA72121,AAAAAAAAAAAAAAAAAAA1205,AAAAA41597,AAAA8857,AAAAAAAAAAAAAAAAAAA15356,AA17009} 1950 27 | {94} | {AA6416,A6053,AAAAAAA21462,AAAAAAA57334,AAAAAAAAAAAAAAAAAA12591,AA88409,AAAAAAAAAAAAA70254} 1951 28 | {14,33,6,34,14} | {AAAAAAAAAAAAAAA13198,AAAAAAAA69452,AAAAAAAAAAA82945,AAAAAAA12144,AAAAAAAAA72121,AAAAAAAAAA18601} 1952 29 | {39,21} | {AAAAAAAAAAAAAAAAA6897,AAAAAAAAAAAAAAAAAAA38885,AAAA85070,AAAAAAAAAAAAAAAAAAA70104,AAAAA66674,AAAAAAAAAAAAA62007,AAAAAAAA69452,AAAAAAA1242,AAAAAAAAAAAAAAAA1729,AAAA35194} 1953 30 | {26,81,47,91,34} | {AAAAAAAAAAAAAAAAAAA70104,AAAAAAA80240} 1954 31 | {80,24,18,21,54} | {AAAAAAAAAAAAAAA13198,AAAAAAAAAAAAAAAAAAA70415,A27153,AAAAAAAAA53663,AAAAAAAAAAAAAAAAA50407,A68938} 1955 32 | {58,79,82,80,67,75,98,10,41} | {AAAAAAAAAAAAAAAAAA61286,AAA54451,AAAAAAAAAAAAAAAAAAA87527,A96617,51533} 1956 33 | {74,73} | {A85417,AAAAAAA56483,AAAAA17383,AAAAAAAAAAAAA62159,AAAAAAAAAAAA52814,AAAAAAAAAAAAA85723,AAAAAAAAAAAAAAAAAA55796} 1957 34 | {70,45} | {AAAAAAAAAAAAAAAAAA71621,AAAAAAAAAAAAAA28620,AAAAAAAAAA55219,AAAAAAAA23648,AAAAAAAAAA22292,AAAAAAA1242} 1958 35 | {23,40} | {AAAAAAAAAAAA52814,AAAA48949,AAAAAAAAA34727,AAAA8857,AAAAAAAAAAAAAAAAAAA62179,AAAAAAAAAAAAAAA68526,AAAAAAA99836,AAAAAAAA50094,AAAA91194,AAAAAAAAAAAAA73084} 1959 36 | {79,82,14,52,30,5,79} | {AAAAAAAAA53663,AAAAAAAAAAAAAAAA55798,AAAAAAAAAAAAAAAAAAA89194,AA88409,AAAAAAAAAAAAAAA81326,AAAAAAAAAAAAAAAAA63050,AAAAAAAAAAAAAAAA33598} 1960 37 | {53,11,81,39,3,78,58,64,74} | {AAAAAAAAAAAAAAAAAAA17075,AAAAAAA66161,AAAAAAAA23648,AAAAAAAAAAAAAA10611} 1961 38 | {59,5,4,95,28} | {AAAAAAAAAAA82945,A96617,47735,AAAAA12179,AAAAA64669,AAAAAA99807,AA74433,AAAAAAAAAAAAAAAAA59387} 1962 39 | {82,43,99,16,74} | {AAAAAAAAAAAAAAA67062,AAAAAAA57334,AAAAAAAAAAAAAA65909,A27153,AAAAAAAAAAAAAAAAAAA17075,AAAAAAAAAAAAAAAAA43052,AAAAAAAAAA64777,AAAAAAAAAAAA81511,AAAAAAAAAAAAAA65909,AAAAAAAAAAAAAA28620} 1963 40 | {34} | {AAAAAAAAAAAAAA10611,AAAAAAAAAAAAAAAAAAA1205,AAAAAAAAAAA50956,AAAAAAAAAAAAAAAA31334,AAAAA70466,AAAAAAAA81587,AAAAAAA74623} 1964 41 | {19,26,63,12,93,73,27,94} | {AAAAAAA79710,AAAAAAAAAA55219,AAAA41702,AAAAAAAAAAAAAAAAAAA17075,AAAAAAAAAAAAAAAAAA71621,AAAAAAAAAAAAAAAAA63050,AAAAAAA99836,AAAAAAAAAAAAAA8666} 1965 42 | {15,76,82,75,8,91} | {AAAAAAAAAAA176,AAAAAA38063,45449,AAAAAA54032,AAAAAAA81898,AA6416,AAAAAAAAAAAAAAAAAAA62179,45449,AAAAA60038,AAAAAAAA81587} 1966 43 | {39,87,91,97,79,28} | {AAAAAAAAAAA74076,A96617,AAAAAAAAAAAAAAAAAAA89194,AAAAAAAAAAAAAAAAAA55796,AAAAAAAAAAAAAAAA23657,AAAAAAAAAAAA67946} 1967 44 | {40,58,68,29,54} | {AAAAAAA81898,AAAAAA66777,AAAAAA98232} 1968 45 | {99,45} | {AAAAAAAA72908,AAAAAAAAAAAAAAAAAAA17075,AA88409,AAAAAAAAAAAAAAAAAA36842,AAAAAAA48038,AAAAAAAAAAAAAA10611} 1969 46 | {53,24} | {AAAAAAAAAAA53908,AAAAAA54032,AAAAA17383,AAAA48949,AAAAAAAAAA18601,AAAAA64669,45449,AAAAAAAAAAA98051,AAAAAAAAAAAAAAAAAA71621} 1970 47 | {98,23,64,12,75,61} | {AAA59323,AAAAA95309,AAAAAAAAAAAAAAAA31334,AAAAAAAAA27249,AAAAA17383,AAAAAAAAAAAA37562,AAAAAA1059,A84822,55847,AAAAA70466} 1971 48 | {76,14} | {AAAAAAAAAAAAA59671,AAAAAAAAAAAAAAAAAAA91804,AAAAAA66777,AAAAAAAAAAAAAAAAAAA89194,AAAAAAAAAAAAAAA36627,AAAAAAAAAAAAAAAAAAA17075,AAAAAAAAAAAAA73084,AAAAAAA79710,AAAAAAAAAAAAAAA40402,AAAAAAAAAAAAAAAAAAA65037} 1972 49 | {56,5,54,37,49} | {AA21643,AAAAAAAAAAA92631,AAAAAAAA81587} 1973 50 | {20,12,37,64,93} | {AAAAAAAAAA5483,AAAAAAAAAAAAAAAAAAA1205,AA6416,AAAAAAAAAAAAAAAAA63050,AAAAAAAAAAAAAAAAAA47955} 1974 51 | {47} | {AAAAAAAAAAAAAA96505,AAAAAAAAAAAAAAAAAA36842,AAAAA95309,AAAAAAAA81587,AA6416,AAAA91194,AAAAAA58494,AAAAAA1059,AAAAAAAA69452} 1975 52 | {89,0} | {AAAAAAAAAAAAAAAAAA47955,AAAAAAA48038,AAAAAAAAAAAAAAAAA43052,AAAAAAAAAAAAA73084,AAAAA70466,AAAAAAAAAAAAAAAAA764,AAAAAAAAAAA46154,AA66862} 1976 53 | {38,17} | {AAAAAAAAAAA21658} 1977 54 | {70,47} | {AAAAAAAAAAAAAAAAAA54141,AAAAA40681,AAAAAAA48038,AAAAAAAAAAAAAAAA29150,AAAAA41597,AAAAAAAAAAAAAAAAAA59334,AA15322} 1978 55 | {47,79,47,64,72,25,71,24,93} | {AAAAAAAAAAAAAAAAAA55796,AAAAA62737} 1979 56 | {33,7,60,54,93,90,77,85,39} | {AAAAAAAAAAAAAAAAAA32918,AA42406} 1980 57 | {23,45,10,42,36,21,9,96} | {AAAAAAAAAAAAAAAAAAA70415} 1981 58 | {92} | {AAAAAAAAAAAAAAAA98414,AAAAAAAA23648,AAAAAAAAAAAAAAAAAA55796,AA25381,AAAAAAAAAAA6119} 1982 59 | {9,69,46,77} | {39557,AAAAAAA89932,AAAAAAAAAAAAAAAAA43052,AAAAAAAAAAAAAAAAA26540,AAA20874,AA6416,AAAAAAAAAAAAAAAAAA47955} 1983 60 | {62,2,59,38,89} | {AAAAAAA89932,AAAAAAAAAAAAAAAAAAA15356,AA99927,AA17009,AAAAAAAAAAAAAAA35875} 1984 61 | {72,2,44,95,54,54,13} | {AAAAAAAAAAAAAAAAAAA91804} 1985 62 | {83,72,29,73} | {AAAAAAAAAAAAA15097,AAAA8857,AAAAAAAAAAAA35809,AAAAAAAAAAAA52814,AAAAAAAAAAAAAAAAAAA38885,AAAAAAAAAAAAAAAAAA24183,AAAAAA43678,A96617} 1986 63 | {11,4,61,87} | {AAAAAAAAA27249,AAAAAAAAAAAAAAAAAA32918,AAAAAAAAAAAAAAA13198,AAA20874,39557,51533,AAAAAAAAAAA53908,AAAAAAAAAAAAAA96505,AAAAAAAA78938} 1987 64 | {26,19,34,24,81,78} | {A96617,AAAAAAAAAAAAAAAAAAA70104,A68938,AAAAAAAAAAA53908,AAAAAAAAAAAAAAA453,AA17009,AAAAAAA80240} 1988 65 | {61,5,76,59,17} | {AAAAAA99807,AAAAA64741,AAAAAAAAAAA53908,AA21643,AAAAAAAAA10012} 1989 66 | {31,23,70,52,4,33,48,25} | {AAAAAAAAAAAAAAAAA69675,AAAAAAAA50094,AAAAAAAAAAA92631,AAAA35194,39557,AAAAAAA99836} 1990 67 | {31,94,7,10} | {AAAAAA38063,A96617,AAAA35194,AAAAAAAAAAAA67946} 1991 68 | {90,43,38} | {AA75092,AAAAAAAAAAAAAAAAA69675,AAAAAAAAAAA92631,AAAAAAAAA10012,AAAAAAAAAAAAA7929,AA21643} 1992 69 | {67,35,99,85,72,86,44} | {AAAAAAAAAAAAAAAAAAA1205,AAAAAAAA50094,AAAAAAAAAAAAAAAA1729,AAAAAAAAAAAAAAAAAA47955} 1993 70 | {56,70,83} | {AAAA41702,AAAAAAAAAAA82945,AA21643,AAAAAAAAAAA99000,A27153,AA25381,AAAAAAAAAAAAAA96505,AAAAAAA1242} 1994 71 | {74,26} | {AAAAAAAAAAA50956,AA74433,AAAAAAA21462,AAAAAAAAAAAAAAAAAAA17075,AAAAAAAAAAAAAAA36627,AAAAAAAAAAAAA70254,AAAAAAAAAA43419,39557} 1995 72 | {22,1,16,78,20,91,83} | {47735,AAAAAAA56483,AAAAAAAAAAAAA93788,AA42406,AAAAAAAAAAAAA73084,AAAAAAAA72908,AAAAAAAAAAAAAAAAAA61286,AAAAA66674,AAAAAAAAAAAAAAAAA50407} 1996 73 | {88,25,96,78,65,15,29,19} | {AAA54451,AAAAAAAAA27249,AAAAAAA9228,AAAAAAAAAAAAAAA67062,AAAAAAAAAAAAAAAAAAA70415,AAAAA17383,AAAAAAAAAAAAAAAA33598} 1997 74 | {32} | {AAAAAAAAAAAAAAAA1729,AAAAAAAAAAAAA22860,AAAAAA99807,AAAAA17383,AAAAAAAAAAAAAAA67062,AAAAAAAAAAA15165,AAAAAAAAAAA50956} 1998 75 | {12,96,83,24,71,89,55} | {AAAA48949,AAAAAAAA29716,AAAAAAAAAAAAAAAAAAA1205,AAAAAAAAAAAA67946,AAAAAAAAAAAAAAAA29150,AAA28075,AAAAAAAAAAAAAAAAA43052} 1999 76 | {92,55,10,7} | {AAAAAAAAAAAAAAA67062} 2000 77 | {97,15,32,17,55,59,18,37,50,39} | {AAAAAAAAAAAA67946,AAAAAA54032,AAAAAAAA81587,55847,AAAAAAAAAAAAAA28620,AAAAAAAAAAAAAAAAA43052,AAAAAA75463,AAAA49534,AAAAAAAA44066} 2001 78 | {55,89,44,84,34} | {AAAAAAAAAAA6119,AAAAAAAAAAAAAA8666,AA99927,AA42406,AAAAAAA81898,AAAAAAA9228,AAAAAAAAAAA92631,AA21643,AAAAAAAAAAAAAA28620} 2002 79 | {45} | {AAAAAAAAAA646,AAAAAAAAAAAAAAAAAAA70415,AAAAAA43678,AAAAAAAA72908} 2003 80 | {74,89,44,80,0} | {AAAA35194,AAAAAAAA79710,AAA20874,AAAAAAAAAAAAAAAAAAA70104,AAAAAAAAAAAAA73084,AAAAAAA57334,AAAAAAA9228,AAAAAAAAAAAAA62007} 2004 81 | {63,77,54,48,61,53,97} | {AAAAAAAAAAAAAAA81326,AAAAAAAAAA22292,AA25381,AAAAAAAAAAA74076,AAAAAAA81898,AAAAAAAAA72121} 2005 82 | {34,60,4,79,78,16,86,89,42,50} | {AAAAA40681,AAAAAAAAAAAAAAAAAA12591,AAAAAAA80240,AAAAAAAAAAAAAAAA55798,AAAAAAAAAAAAAAAAAAA70104} 2006 83 | {14,10} | {AAAAAAAAAA22292,AAAAAAAAAAAAA70254,AAAAAAAAAAA6119} 2007 84 | {11,83,35,13,96,94} | {AAAAA95309,AAAAAAAAAAAAAAAAAA32918,AAAAAAAAAAAAAAAAAA24183} 2008 85 | {39,60} | {AAAAAAAAAAAAAAAA55798,AAAAAAAAAA22292,AAAAAAA66161,AAAAAAA21462,AAAAAAAAAAAAAAAAAA12591,55847,AAAAAA98232,AAAAAAAAAAA46154} 2009 86 | {33,81,72,74,45,36,82} | {AAAAAAAA81587,AAAAAAAAAAAAAA96505,45449,AAAA80176} 2010 87 | {57,27,50,12,97,68} | {AAAAAAAAAAAAAAAAA26540,AAAAAAAAA10012,AAAAAAAAAAAA35809,AAAAAAAAAAAAAAAA29150,AAAAAAAAAAA82945,AAAAAA66777,31228,AAAAAAAAAAAAAAAA23657,AAAAAAAAAAAAAA28620,AAAAAAAAAAAAAA96505} 2011 88 | {41,90,77,24,6,24} | {AAAA35194,AAAA35194,AAAAAAA80240,AAAAAAAAAAA46154,AAAAAA58494,AAAAAAAAAAAAAAAAAAA17075,AAAAAAAAAAAAAAAAAA59334,AAAAAAAAAAAAAAAAAAA91804,AA74433} 2012 89 | {40,32,17,6,30,88} | {AA44673,AAAAAAAAAAA6119,AAAAAAAAAAAAAAAA23657,AAAAAAAAAAAAAAAAAA47955,AAAAAAAAAAAAAAAA33598,AAAAAAAAAAA33576,AA44673} 2013 90 | {88,75} | {AAAAA60038,AAAAAAAA23648,AAAAAAAAAAA99000,AAAA41702,AAAAAAAAAAAAA22860,AAAAAAAAAAAAAAA68526} 2014 91 | {78} | {AAAAAAAAAAAAA62007,AAA99043} 2015 92 | {85,63,49,45} | {AAAAAAA89932,AAAAAAAAAAAAA22860,AAAAAAAAAAAAAAAAAAA1205,AAAAAAAAAAAA21089} 2016 93 | {11} | {AAAAAAAAAAA176,AAAAAAAAAAAAAA8666,AAAAAAAAAAAAAAA453,AAAAAAAAAAAAA85723,A68938,AAAAAAAAAAAAA9821,AAAAAAA48038,AAAAAAAAAAAAAAAAA59387,AA99927,AAAAA17383} 2017 94 | {98,9,85,62,88,91,60,61,38,86} | {AAAAAAAA81587,AAAAA17383,AAAAAAAA81587} 2018 95 | {47,77} | {AAAAAAAAAAAAAAAAA764,AAAAAAAAAAA74076,AAAAAAAAAA18107,AAAAA40681,AAAAAAAAAAAAAAA35875,AAAAA60038,AAAAAAA56483} 2019 96 | {23,97,43} | {AAAAAAAAAA646,A87088} 2020 97 | {54,2,86,65} | {47735,AAAAAAA99836,AAAAAAAAAAAAAAAAA6897,AAAAAAAAAAAAAAAA29150,AAAAAAA80240,AAAAAAAAAAAAAAAA98414,AAAAAAA56483,AAAAAAAAAAAAAAAA29150,AAAAAAA39692,AA21643} 2021 98 | {38,34,32,89} | {AAAAAAAAAAAAAAAAAA71621,AAAA8857,AAAAAAAAAAAAAAAAAAA65037,AAAAAAAAAAAAAAAA31334,AAAAAAAAAA48845} 2022 99 | {37,86} | {AAAAAAAAAAAAAAAAAA32918,AAAAA70514,AAAAAAAAA10012,AAAAAAAAAAAAAAAAA59387,AAAAAAAAAA64777,AAAAAAAAAAAAAAAAAAA15356} 2023 100 | {85,32,57,39,49,84,32,3,30} | {AAAAAAA80240,AAAAAAAAAAAAAAAA1729,AAAAA60038,AAAAAAAAAAA92631,AAAAAAAA9523} 2024 101 | {} | {} 2025 102 | {NULL} | {NULL} 2026(102 rows) 2027 2028SELECT * FROM array_index_op_test WHERE i && '{}' ORDER BY seqno; 2029 seqno | i | t 2030-------+---+--- 2031(0 rows) 2032 2033SELECT * FROM array_index_op_test WHERE i <@ '{}' ORDER BY seqno; 2034 seqno | i | t 2035-------+----+---- 2036 101 | {} | {} 2037(1 row) 2038 2039SELECT * FROM array_op_test WHERE i = '{NULL}' ORDER BY seqno; 2040 seqno | i | t 2041-------+--------+-------- 2042 102 | {NULL} | {NULL} 2043(1 row) 2044 2045SELECT * FROM array_op_test WHERE i @> '{NULL}' ORDER BY seqno; 2046 seqno | i | t 2047-------+---+--- 2048(0 rows) 2049 2050SELECT * FROM array_op_test WHERE i && '{NULL}' ORDER BY seqno; 2051 seqno | i | t 2052-------+---+--- 2053(0 rows) 2054 2055SELECT * FROM array_op_test WHERE i <@ '{NULL}' ORDER BY seqno; 2056 seqno | i | t 2057-------+----+---- 2058 101 | {} | {} 2059(1 row) 2060 2061CREATE INDEX textarrayidx ON array_index_op_test USING gin (t); 2062explain (costs off) 2063SELECT * FROM array_index_op_test WHERE t @> '{AAAAAAAA72908}' ORDER BY seqno; 2064 QUERY PLAN 2065------------------------------------------------------------ 2066 Sort 2067 Sort Key: seqno 2068 -> Bitmap Heap Scan on array_index_op_test 2069 Recheck Cond: (t @> '{AAAAAAAA72908}'::text[]) 2070 -> Bitmap Index Scan on textarrayidx 2071 Index Cond: (t @> '{AAAAAAAA72908}'::text[]) 2072(6 rows) 2073 2074SELECT * FROM array_index_op_test WHERE t @> '{AAAAAAAA72908}' ORDER BY seqno; 2075 seqno | i | t 2076-------+-----------------------+-------------------------------------------------------------------------------------------------------------------------------------------- 2077 22 | {11,6,56,62,53,30} | {AAAAAAAA72908} 2078 45 | {99,45} | {AAAAAAAA72908,AAAAAAAAAAAAAAAAAAA17075,AA88409,AAAAAAAAAAAAAAAAAA36842,AAAAAAA48038,AAAAAAAAAAAAAA10611} 2079 72 | {22,1,16,78,20,91,83} | {47735,AAAAAAA56483,AAAAAAAAAAAAA93788,AA42406,AAAAAAAAAAAAA73084,AAAAAAAA72908,AAAAAAAAAAAAAAAAAA61286,AAAAA66674,AAAAAAAAAAAAAAAAA50407} 2080 79 | {45} | {AAAAAAAAAA646,AAAAAAAAAAAAAAAAAAA70415,AAAAAA43678,AAAAAAAA72908} 2081(4 rows) 2082 2083SELECT * FROM array_index_op_test WHERE t && '{AAAAAAAA72908}' ORDER BY seqno; 2084 seqno | i | t 2085-------+-----------------------+-------------------------------------------------------------------------------------------------------------------------------------------- 2086 22 | {11,6,56,62,53,30} | {AAAAAAAA72908} 2087 45 | {99,45} | {AAAAAAAA72908,AAAAAAAAAAAAAAAAAAA17075,AA88409,AAAAAAAAAAAAAAAAAA36842,AAAAAAA48038,AAAAAAAAAAAAAA10611} 2088 72 | {22,1,16,78,20,91,83} | {47735,AAAAAAA56483,AAAAAAAAAAAAA93788,AA42406,AAAAAAAAAAAAA73084,AAAAAAAA72908,AAAAAAAAAAAAAAAAAA61286,AAAAA66674,AAAAAAAAAAAAAAAAA50407} 2089 79 | {45} | {AAAAAAAAAA646,AAAAAAAAAAAAAAAAAAA70415,AAAAAA43678,AAAAAAAA72908} 2090(4 rows) 2091 2092SELECT * FROM array_index_op_test WHERE t @> '{AAAAAAAAAA646}' ORDER BY seqno; 2093 seqno | i | t 2094-------+------------------+-------------------------------------------------------------------- 2095 15 | {17,14,16,63,67} | {AA6416,AAAAAAAAAA646,AAAAA95309} 2096 79 | {45} | {AAAAAAAAAA646,AAAAAAAAAAAAAAAAAAA70415,AAAAAA43678,AAAAAAAA72908} 2097 96 | {23,97,43} | {AAAAAAAAAA646,A87088} 2098(3 rows) 2099 2100SELECT * FROM array_index_op_test WHERE t && '{AAAAAAAAAA646}' ORDER BY seqno; 2101 seqno | i | t 2102-------+------------------+-------------------------------------------------------------------- 2103 15 | {17,14,16,63,67} | {AA6416,AAAAAAAAAA646,AAAAA95309} 2104 79 | {45} | {AAAAAAAAAA646,AAAAAAAAAAAAAAAAAAA70415,AAAAAA43678,AAAAAAAA72908} 2105 96 | {23,97,43} | {AAAAAAAAAA646,A87088} 2106(3 rows) 2107 2108SELECT * FROM array_index_op_test WHERE t @> '{AAAAAAAA72908,AAAAAAAAAA646}' ORDER BY seqno; 2109 seqno | i | t 2110-------+------+-------------------------------------------------------------------- 2111 79 | {45} | {AAAAAAAAAA646,AAAAAAAAAAAAAAAAAAA70415,AAAAAA43678,AAAAAAAA72908} 2112(1 row) 2113 2114SELECT * FROM array_index_op_test WHERE t && '{AAAAAAAA72908,AAAAAAAAAA646}' ORDER BY seqno; 2115 seqno | i | t 2116-------+-----------------------+-------------------------------------------------------------------------------------------------------------------------------------------- 2117 15 | {17,14,16,63,67} | {AA6416,AAAAAAAAAA646,AAAAA95309} 2118 22 | {11,6,56,62,53,30} | {AAAAAAAA72908} 2119 45 | {99,45} | {AAAAAAAA72908,AAAAAAAAAAAAAAAAAAA17075,AA88409,AAAAAAAAAAAAAAAAAA36842,AAAAAAA48038,AAAAAAAAAAAAAA10611} 2120 72 | {22,1,16,78,20,91,83} | {47735,AAAAAAA56483,AAAAAAAAAAAAA93788,AA42406,AAAAAAAAAAAAA73084,AAAAAAAA72908,AAAAAAAAAAAAAAAAAA61286,AAAAA66674,AAAAAAAAAAAAAAAAA50407} 2121 79 | {45} | {AAAAAAAAAA646,AAAAAAAAAAAAAAAAAAA70415,AAAAAA43678,AAAAAAAA72908} 2122 96 | {23,97,43} | {AAAAAAAAAA646,A87088} 2123(6 rows) 2124 2125SELECT * FROM array_index_op_test WHERE t <@ '{AAAAAAAA72908,AAAAAAAAAAAAAAAAAAA17075,AA88409,AAAAAAAAAAAAAAAAAA36842,AAAAAAA48038,AAAAAAAAAAAAAA10611}' ORDER BY seqno; 2126 seqno | i | t 2127-------+--------------------+----------------------------------------------------------------------------------------------------------- 2128 22 | {11,6,56,62,53,30} | {AAAAAAAA72908} 2129 45 | {99,45} | {AAAAAAAA72908,AAAAAAAAAAAAAAAAAAA17075,AA88409,AAAAAAAAAAAAAAAAAA36842,AAAAAAA48038,AAAAAAAAAAAAAA10611} 2130 101 | {} | {} 2131(3 rows) 2132 2133SELECT * FROM array_index_op_test WHERE t = '{AAAAAAAAAA646,A87088}' ORDER BY seqno; 2134 seqno | i | t 2135-------+------------+------------------------ 2136 96 | {23,97,43} | {AAAAAAAAAA646,A87088} 2137(1 row) 2138 2139SELECT * FROM array_index_op_test WHERE t = '{}' ORDER BY seqno; 2140 seqno | i | t 2141-------+----+---- 2142 101 | {} | {} 2143(1 row) 2144 2145SELECT * FROM array_index_op_test WHERE t @> '{}' ORDER BY seqno; 2146 seqno | i | t 2147-------+---------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 2148 1 | {92,75,71,52,64,83} | {AAAAAAAA44066,AAAAAA1059,AAAAAAAAAAA176,AAAAAAA48038} 2149 2 | {3,6} | {AAAAAA98232,AAAAAAAA79710,AAAAAAAAAAAAAAAAA69675,AAAAAAAAAAAAAAAA55798,AAAAAAAAA12793} 2150 3 | {37,64,95,43,3,41,13,30,11,43} | {AAAAAAAAAA48845,AAAAA75968,AAAAA95309,AAA54451,AAAAAAAAAA22292,AAAAAAA99836,A96617,AA17009,AAAAAAAAAAAAAA95246} 2151 4 | {71,39,99,55,33,75,45} | {AAAAAAAAA53663,AAAAAAAAAAAAAAA67062,AAAAAAAAAA64777,AAA99043,AAAAAAAAAAAAAAAAAAA91804,39557} 2152 5 | {50,42,77,50,4} | {AAAAAAAAAAAAAAAAA26540,AAAAAAA79710,AAAAAAAAAAAAAAAAAAA1205,AAAAAAAAAAA176,AAAAA95309,AAAAAAAAAAA46154,AAAAAA66777,AAAAAAAAA27249,AAAAAAAAAA64777,AAAAAAAAAAAAAAAAAAA70104} 2153 6 | {39,35,5,94,17,92,60,32} | {AAAAAAAAAAAAAAA35875,AAAAAAAAAAAAAAAA23657} 2154 7 | {12,51,88,64,8} | {AAAAAAAAAAAAAAAAAA12591,AAAAAAAAAAAAAAAAA50407,AAAAAAAAAAAA67946} 2155 8 | {60,84} | {AAAAAAA81898,AAAAAA1059,AAAAAAAAAAAA81511,AAAAA961,AAAAAAAAAAAAAAAA31334,AAAAA64741,AA6416,AAAAAAAAAAAAAAAAAA32918,AAAAAAAAAAAAAAAAA50407} 2156 9 | {56,52,35,27,80,44,81,22} | {AAAAAAAAAAAAAAA73034,AAAAAAAAAAAAA7929,AAAAAAA66161,AA88409,39557,A27153,AAAAAAAA9523,AAAAAAAAAAA99000} 2157 10 | {71,5,45} | {AAAAAAAAAAA21658,AAAAAAAAAAAA21089,AAA54451,AAAAAAAAAAAAAAAAAA54141,AAAAAAAAAAAAAA28620,AAAAAAAAAAA21658,AAAAAAAAAAA74076,AAAAAAAAA27249} 2158 11 | {41,86,74,48,22,74,47,50} | {AAAAAAAA9523,AAAAAAAAAAAA37562,AAAAAAAAAAAAAAAA14047,AAAAAAAAAAA46154,AAAA41702,AAAAAAAAAAAAAAAAA764,AAAAA62737,39557} 2159 12 | {17,99,18,52,91,72,0,43,96,23} | {AAAAA33250,AAAAAAAAAAAAAAAAAAA85420,AAAAAAAAAAA33576} 2160 13 | {3,52,34,23} | {AAAAAA98232,AAAA49534,AAAAAAAAAAA21658} 2161 14 | {78,57,19} | {AAAA8857,AAAAAAAAAAAAAAA73034,AAAAAAAA81587,AAAAAAAAAAAAAAA68526,AAAAA75968,AAAAAAAAAAAAAA65909,AAAAAAAAA10012,AAAAAAAAAAAAAA65909} 2162 15 | {17,14,16,63,67} | {AA6416,AAAAAAAAAA646,AAAAA95309} 2163 16 | {14,63,85,11} | {AAAAAA66777} 2164 17 | {7,10,81,85} | {AAAAAA43678,AAAAAAA12144,AAAAAAAAAAA50956,AAAAAAAAAAAAAAAAAAA15356} 2165 18 | {1} | {AAAAAAAAAAA33576,AAAAA95309,64261,AAA59323,AAAAAAAAAAAAAA95246,55847,AAAAAAAAAAAA67946,AAAAAAAAAAAAAAAAAA64374} 2166 19 | {52,82,17,74,23,46,69,51,75} | {AAAAAAAAAAAAA73084,AAAAA75968,AAAAAAAAAAAAAAAA14047,AAAAAAA80240,AAAAAAAAAAAAAAAAAAA1205,A68938} 2167 20 | {72,89,70,51,54,37,8,49,79} | {AAAAAA58494} 2168 21 | {2,8,65,10,5,79,43} | {AAAAAAAAAAAAAAAAA88852,AAAAAAAAAAAAAAAAAAA91804,AAAAA64669,AAAAAAAAAAAAAAAA1443,AAAAAAAAAAAAAAAA23657,AAAAA12179,AAAAAAAAAAAAAAAAA88852,AAAAAAAAAAAAAAAA31334,AAAAAAAAAAAAAAAA41303,AAAAAAAAAAAAAAAAAAA85420} 2169 22 | {11,6,56,62,53,30} | {AAAAAAAA72908} 2170 23 | {40,90,5,38,72,40,30,10,43,55} | {A6053,AAAAAAAAAAA6119,AA44673,AAAAAAAAAAAAAAAAA764,AA17009,AAAAA17383,AAAAA70514,AAAAA33250,AAAAA95309,AAAAAAAAAAAA37562} 2171 24 | {94,61,99,35,48} | {AAAAAAAAAAA50956,AAAAAAAAAAA15165,AAAA85070,AAAAAAAAAAAAAAA36627,AAAAA961,AAAAAAAAAA55219} 2172 25 | {31,1,10,11,27,79,38} | {AAAAAAAAAAAAAAAAAA59334,45449} 2173 26 | {71,10,9,69,75} | {47735,AAAAAAA21462,AAAAAAAAAAAAAAAAA6897,AAAAAAAAAAAAAAAAAAA91804,AAAAAAAAA72121,AAAAAAAAAAAAAAAAAAA1205,AAAAA41597,AAAA8857,AAAAAAAAAAAAAAAAAAA15356,AA17009} 2174 27 | {94} | {AA6416,A6053,AAAAAAA21462,AAAAAAA57334,AAAAAAAAAAAAAAAAAA12591,AA88409,AAAAAAAAAAAAA70254} 2175 28 | {14,33,6,34,14} | {AAAAAAAAAAAAAAA13198,AAAAAAAA69452,AAAAAAAAAAA82945,AAAAAAA12144,AAAAAAAAA72121,AAAAAAAAAA18601} 2176 29 | {39,21} | {AAAAAAAAAAAAAAAAA6897,AAAAAAAAAAAAAAAAAAA38885,AAAA85070,AAAAAAAAAAAAAAAAAAA70104,AAAAA66674,AAAAAAAAAAAAA62007,AAAAAAAA69452,AAAAAAA1242,AAAAAAAAAAAAAAAA1729,AAAA35194} 2177 30 | {26,81,47,91,34} | {AAAAAAAAAAAAAAAAAAA70104,AAAAAAA80240} 2178 31 | {80,24,18,21,54} | {AAAAAAAAAAAAAAA13198,AAAAAAAAAAAAAAAAAAA70415,A27153,AAAAAAAAA53663,AAAAAAAAAAAAAAAAA50407,A68938} 2179 32 | {58,79,82,80,67,75,98,10,41} | {AAAAAAAAAAAAAAAAAA61286,AAA54451,AAAAAAAAAAAAAAAAAAA87527,A96617,51533} 2180 33 | {74,73} | {A85417,AAAAAAA56483,AAAAA17383,AAAAAAAAAAAAA62159,AAAAAAAAAAAA52814,AAAAAAAAAAAAA85723,AAAAAAAAAAAAAAAAAA55796} 2181 34 | {70,45} | {AAAAAAAAAAAAAAAAAA71621,AAAAAAAAAAAAAA28620,AAAAAAAAAA55219,AAAAAAAA23648,AAAAAAAAAA22292,AAAAAAA1242} 2182 35 | {23,40} | {AAAAAAAAAAAA52814,AAAA48949,AAAAAAAAA34727,AAAA8857,AAAAAAAAAAAAAAAAAAA62179,AAAAAAAAAAAAAAA68526,AAAAAAA99836,AAAAAAAA50094,AAAA91194,AAAAAAAAAAAAA73084} 2183 36 | {79,82,14,52,30,5,79} | {AAAAAAAAA53663,AAAAAAAAAAAAAAAA55798,AAAAAAAAAAAAAAAAAAA89194,AA88409,AAAAAAAAAAAAAAA81326,AAAAAAAAAAAAAAAAA63050,AAAAAAAAAAAAAAAA33598} 2184 37 | {53,11,81,39,3,78,58,64,74} | {AAAAAAAAAAAAAAAAAAA17075,AAAAAAA66161,AAAAAAAA23648,AAAAAAAAAAAAAA10611} 2185 38 | {59,5,4,95,28} | {AAAAAAAAAAA82945,A96617,47735,AAAAA12179,AAAAA64669,AAAAAA99807,AA74433,AAAAAAAAAAAAAAAAA59387} 2186 39 | {82,43,99,16,74} | {AAAAAAAAAAAAAAA67062,AAAAAAA57334,AAAAAAAAAAAAAA65909,A27153,AAAAAAAAAAAAAAAAAAA17075,AAAAAAAAAAAAAAAAA43052,AAAAAAAAAA64777,AAAAAAAAAAAA81511,AAAAAAAAAAAAAA65909,AAAAAAAAAAAAAA28620} 2187 40 | {34} | {AAAAAAAAAAAAAA10611,AAAAAAAAAAAAAAAAAAA1205,AAAAAAAAAAA50956,AAAAAAAAAAAAAAAA31334,AAAAA70466,AAAAAAAA81587,AAAAAAA74623} 2188 41 | {19,26,63,12,93,73,27,94} | {AAAAAAA79710,AAAAAAAAAA55219,AAAA41702,AAAAAAAAAAAAAAAAAAA17075,AAAAAAAAAAAAAAAAAA71621,AAAAAAAAAAAAAAAAA63050,AAAAAAA99836,AAAAAAAAAAAAAA8666} 2189 42 | {15,76,82,75,8,91} | {AAAAAAAAAAA176,AAAAAA38063,45449,AAAAAA54032,AAAAAAA81898,AA6416,AAAAAAAAAAAAAAAAAAA62179,45449,AAAAA60038,AAAAAAAA81587} 2190 43 | {39,87,91,97,79,28} | {AAAAAAAAAAA74076,A96617,AAAAAAAAAAAAAAAAAAA89194,AAAAAAAAAAAAAAAAAA55796,AAAAAAAAAAAAAAAA23657,AAAAAAAAAAAA67946} 2191 44 | {40,58,68,29,54} | {AAAAAAA81898,AAAAAA66777,AAAAAA98232} 2192 45 | {99,45} | {AAAAAAAA72908,AAAAAAAAAAAAAAAAAAA17075,AA88409,AAAAAAAAAAAAAAAAAA36842,AAAAAAA48038,AAAAAAAAAAAAAA10611} 2193 46 | {53,24} | {AAAAAAAAAAA53908,AAAAAA54032,AAAAA17383,AAAA48949,AAAAAAAAAA18601,AAAAA64669,45449,AAAAAAAAAAA98051,AAAAAAAAAAAAAAAAAA71621} 2194 47 | {98,23,64,12,75,61} | {AAA59323,AAAAA95309,AAAAAAAAAAAAAAAA31334,AAAAAAAAA27249,AAAAA17383,AAAAAAAAAAAA37562,AAAAAA1059,A84822,55847,AAAAA70466} 2195 48 | {76,14} | {AAAAAAAAAAAAA59671,AAAAAAAAAAAAAAAAAAA91804,AAAAAA66777,AAAAAAAAAAAAAAAAAAA89194,AAAAAAAAAAAAAAA36627,AAAAAAAAAAAAAAAAAAA17075,AAAAAAAAAAAAA73084,AAAAAAA79710,AAAAAAAAAAAAAAA40402,AAAAAAAAAAAAAAAAAAA65037} 2196 49 | {56,5,54,37,49} | {AA21643,AAAAAAAAAAA92631,AAAAAAAA81587} 2197 50 | {20,12,37,64,93} | {AAAAAAAAAA5483,AAAAAAAAAAAAAAAAAAA1205,AA6416,AAAAAAAAAAAAAAAAA63050,AAAAAAAAAAAAAAAAAA47955} 2198 51 | {47} | {AAAAAAAAAAAAAA96505,AAAAAAAAAAAAAAAAAA36842,AAAAA95309,AAAAAAAA81587,AA6416,AAAA91194,AAAAAA58494,AAAAAA1059,AAAAAAAA69452} 2199 52 | {89,0} | {AAAAAAAAAAAAAAAAAA47955,AAAAAAA48038,AAAAAAAAAAAAAAAAA43052,AAAAAAAAAAAAA73084,AAAAA70466,AAAAAAAAAAAAAAAAA764,AAAAAAAAAAA46154,AA66862} 2200 53 | {38,17} | {AAAAAAAAAAA21658} 2201 54 | {70,47} | {AAAAAAAAAAAAAAAAAA54141,AAAAA40681,AAAAAAA48038,AAAAAAAAAAAAAAAA29150,AAAAA41597,AAAAAAAAAAAAAAAAAA59334,AA15322} 2202 55 | {47,79,47,64,72,25,71,24,93} | {AAAAAAAAAAAAAAAAAA55796,AAAAA62737} 2203 56 | {33,7,60,54,93,90,77,85,39} | {AAAAAAAAAAAAAAAAAA32918,AA42406} 2204 57 | {23,45,10,42,36,21,9,96} | {AAAAAAAAAAAAAAAAAAA70415} 2205 58 | {92} | {AAAAAAAAAAAAAAAA98414,AAAAAAAA23648,AAAAAAAAAAAAAAAAAA55796,AA25381,AAAAAAAAAAA6119} 2206 59 | {9,69,46,77} | {39557,AAAAAAA89932,AAAAAAAAAAAAAAAAA43052,AAAAAAAAAAAAAAAAA26540,AAA20874,AA6416,AAAAAAAAAAAAAAAAAA47955} 2207 60 | {62,2,59,38,89} | {AAAAAAA89932,AAAAAAAAAAAAAAAAAAA15356,AA99927,AA17009,AAAAAAAAAAAAAAA35875} 2208 61 | {72,2,44,95,54,54,13} | {AAAAAAAAAAAAAAAAAAA91804} 2209 62 | {83,72,29,73} | {AAAAAAAAAAAAA15097,AAAA8857,AAAAAAAAAAAA35809,AAAAAAAAAAAA52814,AAAAAAAAAAAAAAAAAAA38885,AAAAAAAAAAAAAAAAAA24183,AAAAAA43678,A96617} 2210 63 | {11,4,61,87} | {AAAAAAAAA27249,AAAAAAAAAAAAAAAAAA32918,AAAAAAAAAAAAAAA13198,AAA20874,39557,51533,AAAAAAAAAAA53908,AAAAAAAAAAAAAA96505,AAAAAAAA78938} 2211 64 | {26,19,34,24,81,78} | {A96617,AAAAAAAAAAAAAAAAAAA70104,A68938,AAAAAAAAAAA53908,AAAAAAAAAAAAAAA453,AA17009,AAAAAAA80240} 2212 65 | {61,5,76,59,17} | {AAAAAA99807,AAAAA64741,AAAAAAAAAAA53908,AA21643,AAAAAAAAA10012} 2213 66 | {31,23,70,52,4,33,48,25} | {AAAAAAAAAAAAAAAAA69675,AAAAAAAA50094,AAAAAAAAAAA92631,AAAA35194,39557,AAAAAAA99836} 2214 67 | {31,94,7,10} | {AAAAAA38063,A96617,AAAA35194,AAAAAAAAAAAA67946} 2215 68 | {90,43,38} | {AA75092,AAAAAAAAAAAAAAAAA69675,AAAAAAAAAAA92631,AAAAAAAAA10012,AAAAAAAAAAAAA7929,AA21643} 2216 69 | {67,35,99,85,72,86,44} | {AAAAAAAAAAAAAAAAAAA1205,AAAAAAAA50094,AAAAAAAAAAAAAAAA1729,AAAAAAAAAAAAAAAAAA47955} 2217 70 | {56,70,83} | {AAAA41702,AAAAAAAAAAA82945,AA21643,AAAAAAAAAAA99000,A27153,AA25381,AAAAAAAAAAAAAA96505,AAAAAAA1242} 2218 71 | {74,26} | {AAAAAAAAAAA50956,AA74433,AAAAAAA21462,AAAAAAAAAAAAAAAAAAA17075,AAAAAAAAAAAAAAA36627,AAAAAAAAAAAAA70254,AAAAAAAAAA43419,39557} 2219 72 | {22,1,16,78,20,91,83} | {47735,AAAAAAA56483,AAAAAAAAAAAAA93788,AA42406,AAAAAAAAAAAAA73084,AAAAAAAA72908,AAAAAAAAAAAAAAAAAA61286,AAAAA66674,AAAAAAAAAAAAAAAAA50407} 2220 73 | {88,25,96,78,65,15,29,19} | {AAA54451,AAAAAAAAA27249,AAAAAAA9228,AAAAAAAAAAAAAAA67062,AAAAAAAAAAAAAAAAAAA70415,AAAAA17383,AAAAAAAAAAAAAAAA33598} 2221 74 | {32} | {AAAAAAAAAAAAAAAA1729,AAAAAAAAAAAAA22860,AAAAAA99807,AAAAA17383,AAAAAAAAAAAAAAA67062,AAAAAAAAAAA15165,AAAAAAAAAAA50956} 2222 75 | {12,96,83,24,71,89,55} | {AAAA48949,AAAAAAAA29716,AAAAAAAAAAAAAAAAAAA1205,AAAAAAAAAAAA67946,AAAAAAAAAAAAAAAA29150,AAA28075,AAAAAAAAAAAAAAAAA43052} 2223 76 | {92,55,10,7} | {AAAAAAAAAAAAAAA67062} 2224 77 | {97,15,32,17,55,59,18,37,50,39} | {AAAAAAAAAAAA67946,AAAAAA54032,AAAAAAAA81587,55847,AAAAAAAAAAAAAA28620,AAAAAAAAAAAAAAAAA43052,AAAAAA75463,AAAA49534,AAAAAAAA44066} 2225 78 | {55,89,44,84,34} | {AAAAAAAAAAA6119,AAAAAAAAAAAAAA8666,AA99927,AA42406,AAAAAAA81898,AAAAAAA9228,AAAAAAAAAAA92631,AA21643,AAAAAAAAAAAAAA28620} 2226 79 | {45} | {AAAAAAAAAA646,AAAAAAAAAAAAAAAAAAA70415,AAAAAA43678,AAAAAAAA72908} 2227 80 | {74,89,44,80,0} | {AAAA35194,AAAAAAAA79710,AAA20874,AAAAAAAAAAAAAAAAAAA70104,AAAAAAAAAAAAA73084,AAAAAAA57334,AAAAAAA9228,AAAAAAAAAAAAA62007} 2228 81 | {63,77,54,48,61,53,97} | {AAAAAAAAAAAAAAA81326,AAAAAAAAAA22292,AA25381,AAAAAAAAAAA74076,AAAAAAA81898,AAAAAAAAA72121} 2229 82 | {34,60,4,79,78,16,86,89,42,50} | {AAAAA40681,AAAAAAAAAAAAAAAAAA12591,AAAAAAA80240,AAAAAAAAAAAAAAAA55798,AAAAAAAAAAAAAAAAAAA70104} 2230 83 | {14,10} | {AAAAAAAAAA22292,AAAAAAAAAAAAA70254,AAAAAAAAAAA6119} 2231 84 | {11,83,35,13,96,94} | {AAAAA95309,AAAAAAAAAAAAAAAAAA32918,AAAAAAAAAAAAAAAAAA24183} 2232 85 | {39,60} | {AAAAAAAAAAAAAAAA55798,AAAAAAAAAA22292,AAAAAAA66161,AAAAAAA21462,AAAAAAAAAAAAAAAAAA12591,55847,AAAAAA98232,AAAAAAAAAAA46154} 2233 86 | {33,81,72,74,45,36,82} | {AAAAAAAA81587,AAAAAAAAAAAAAA96505,45449,AAAA80176} 2234 87 | {57,27,50,12,97,68} | {AAAAAAAAAAAAAAAAA26540,AAAAAAAAA10012,AAAAAAAAAAAA35809,AAAAAAAAAAAAAAAA29150,AAAAAAAAAAA82945,AAAAAA66777,31228,AAAAAAAAAAAAAAAA23657,AAAAAAAAAAAAAA28620,AAAAAAAAAAAAAA96505} 2235 88 | {41,90,77,24,6,24} | {AAAA35194,AAAA35194,AAAAAAA80240,AAAAAAAAAAA46154,AAAAAA58494,AAAAAAAAAAAAAAAAAAA17075,AAAAAAAAAAAAAAAAAA59334,AAAAAAAAAAAAAAAAAAA91804,AA74433} 2236 89 | {40,32,17,6,30,88} | {AA44673,AAAAAAAAAAA6119,AAAAAAAAAAAAAAAA23657,AAAAAAAAAAAAAAAAAA47955,AAAAAAAAAAAAAAAA33598,AAAAAAAAAAA33576,AA44673} 2237 90 | {88,75} | {AAAAA60038,AAAAAAAA23648,AAAAAAAAAAA99000,AAAA41702,AAAAAAAAAAAAA22860,AAAAAAAAAAAAAAA68526} 2238 91 | {78} | {AAAAAAAAAAAAA62007,AAA99043} 2239 92 | {85,63,49,45} | {AAAAAAA89932,AAAAAAAAAAAAA22860,AAAAAAAAAAAAAAAAAAA1205,AAAAAAAAAAAA21089} 2240 93 | {11} | {AAAAAAAAAAA176,AAAAAAAAAAAAAA8666,AAAAAAAAAAAAAAA453,AAAAAAAAAAAAA85723,A68938,AAAAAAAAAAAAA9821,AAAAAAA48038,AAAAAAAAAAAAAAAAA59387,AA99927,AAAAA17383} 2241 94 | {98,9,85,62,88,91,60,61,38,86} | {AAAAAAAA81587,AAAAA17383,AAAAAAAA81587} 2242 95 | {47,77} | {AAAAAAAAAAAAAAAAA764,AAAAAAAAAAA74076,AAAAAAAAAA18107,AAAAA40681,AAAAAAAAAAAAAAA35875,AAAAA60038,AAAAAAA56483} 2243 96 | {23,97,43} | {AAAAAAAAAA646,A87088} 2244 97 | {54,2,86,65} | {47735,AAAAAAA99836,AAAAAAAAAAAAAAAAA6897,AAAAAAAAAAAAAAAA29150,AAAAAAA80240,AAAAAAAAAAAAAAAA98414,AAAAAAA56483,AAAAAAAAAAAAAAAA29150,AAAAAAA39692,AA21643} 2245 98 | {38,34,32,89} | {AAAAAAAAAAAAAAAAAA71621,AAAA8857,AAAAAAAAAAAAAAAAAAA65037,AAAAAAAAAAAAAAAA31334,AAAAAAAAAA48845} 2246 99 | {37,86} | {AAAAAAAAAAAAAAAAAA32918,AAAAA70514,AAAAAAAAA10012,AAAAAAAAAAAAAAAAA59387,AAAAAAAAAA64777,AAAAAAAAAAAAAAAAAAA15356} 2247 100 | {85,32,57,39,49,84,32,3,30} | {AAAAAAA80240,AAAAAAAAAAAAAAAA1729,AAAAA60038,AAAAAAAAAAA92631,AAAAAAAA9523} 2248 101 | {} | {} 2249 102 | {NULL} | {NULL} 2250(102 rows) 2251 2252SELECT * FROM array_index_op_test WHERE t && '{}' ORDER BY seqno; 2253 seqno | i | t 2254-------+---+--- 2255(0 rows) 2256 2257SELECT * FROM array_index_op_test WHERE t <@ '{}' ORDER BY seqno; 2258 seqno | i | t 2259-------+----+---- 2260 101 | {} | {} 2261(1 row) 2262 2263-- And try it with a multicolumn GIN index 2264DROP INDEX intarrayidx, textarrayidx; 2265CREATE INDEX botharrayidx ON array_index_op_test USING gin (i, t); 2266SELECT * FROM array_index_op_test WHERE i @> '{32}' ORDER BY seqno; 2267 seqno | i | t 2268-------+---------------------------------+------------------------------------------------------------------------------------------------------------------------------------ 2269 6 | {39,35,5,94,17,92,60,32} | {AAAAAAAAAAAAAAA35875,AAAAAAAAAAAAAAAA23657} 2270 74 | {32} | {AAAAAAAAAAAAAAAA1729,AAAAAAAAAAAAA22860,AAAAAA99807,AAAAA17383,AAAAAAAAAAAAAAA67062,AAAAAAAAAAA15165,AAAAAAAAAAA50956} 2271 77 | {97,15,32,17,55,59,18,37,50,39} | {AAAAAAAAAAAA67946,AAAAAA54032,AAAAAAAA81587,55847,AAAAAAAAAAAAAA28620,AAAAAAAAAAAAAAAAA43052,AAAAAA75463,AAAA49534,AAAAAAAA44066} 2272 89 | {40,32,17,6,30,88} | {AA44673,AAAAAAAAAAA6119,AAAAAAAAAAAAAAAA23657,AAAAAAAAAAAAAAAAAA47955,AAAAAAAAAAAAAAAA33598,AAAAAAAAAAA33576,AA44673} 2273 98 | {38,34,32,89} | {AAAAAAAAAAAAAAAAAA71621,AAAA8857,AAAAAAAAAAAAAAAAAAA65037,AAAAAAAAAAAAAAAA31334,AAAAAAAAAA48845} 2274 100 | {85,32,57,39,49,84,32,3,30} | {AAAAAAA80240,AAAAAAAAAAAAAAAA1729,AAAAA60038,AAAAAAAAAAA92631,AAAAAAAA9523} 2275(6 rows) 2276 2277SELECT * FROM array_index_op_test WHERE i && '{32}' ORDER BY seqno; 2278 seqno | i | t 2279-------+---------------------------------+------------------------------------------------------------------------------------------------------------------------------------ 2280 6 | {39,35,5,94,17,92,60,32} | {AAAAAAAAAAAAAAA35875,AAAAAAAAAAAAAAAA23657} 2281 74 | {32} | {AAAAAAAAAAAAAAAA1729,AAAAAAAAAAAAA22860,AAAAAA99807,AAAAA17383,AAAAAAAAAAAAAAA67062,AAAAAAAAAAA15165,AAAAAAAAAAA50956} 2282 77 | {97,15,32,17,55,59,18,37,50,39} | {AAAAAAAAAAAA67946,AAAAAA54032,AAAAAAAA81587,55847,AAAAAAAAAAAAAA28620,AAAAAAAAAAAAAAAAA43052,AAAAAA75463,AAAA49534,AAAAAAAA44066} 2283 89 | {40,32,17,6,30,88} | {AA44673,AAAAAAAAAAA6119,AAAAAAAAAAAAAAAA23657,AAAAAAAAAAAAAAAAAA47955,AAAAAAAAAAAAAAAA33598,AAAAAAAAAAA33576,AA44673} 2284 98 | {38,34,32,89} | {AAAAAAAAAAAAAAAAAA71621,AAAA8857,AAAAAAAAAAAAAAAAAAA65037,AAAAAAAAAAAAAAAA31334,AAAAAAAAAA48845} 2285 100 | {85,32,57,39,49,84,32,3,30} | {AAAAAAA80240,AAAAAAAAAAAAAAAA1729,AAAAA60038,AAAAAAAAAAA92631,AAAAAAAA9523} 2286(6 rows) 2287 2288SELECT * FROM array_index_op_test WHERE t @> '{AAAAAAA80240}' ORDER BY seqno; 2289 seqno | i | t 2290-------+--------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------- 2291 19 | {52,82,17,74,23,46,69,51,75} | {AAAAAAAAAAAAA73084,AAAAA75968,AAAAAAAAAAAAAAAA14047,AAAAAAA80240,AAAAAAAAAAAAAAAAAAA1205,A68938} 2292 30 | {26,81,47,91,34} | {AAAAAAAAAAAAAAAAAAA70104,AAAAAAA80240} 2293 64 | {26,19,34,24,81,78} | {A96617,AAAAAAAAAAAAAAAAAAA70104,A68938,AAAAAAAAAAA53908,AAAAAAAAAAAAAAA453,AA17009,AAAAAAA80240} 2294 82 | {34,60,4,79,78,16,86,89,42,50} | {AAAAA40681,AAAAAAAAAAAAAAAAAA12591,AAAAAAA80240,AAAAAAAAAAAAAAAA55798,AAAAAAAAAAAAAAAAAAA70104} 2295 88 | {41,90,77,24,6,24} | {AAAA35194,AAAA35194,AAAAAAA80240,AAAAAAAAAAA46154,AAAAAA58494,AAAAAAAAAAAAAAAAAAA17075,AAAAAAAAAAAAAAAAAA59334,AAAAAAAAAAAAAAAAAAA91804,AA74433} 2296 97 | {54,2,86,65} | {47735,AAAAAAA99836,AAAAAAAAAAAAAAAAA6897,AAAAAAAAAAAAAAAA29150,AAAAAAA80240,AAAAAAAAAAAAAAAA98414,AAAAAAA56483,AAAAAAAAAAAAAAAA29150,AAAAAAA39692,AA21643} 2297 100 | {85,32,57,39,49,84,32,3,30} | {AAAAAAA80240,AAAAAAAAAAAAAAAA1729,AAAAA60038,AAAAAAAAAAA92631,AAAAAAAA9523} 2298(7 rows) 2299 2300SELECT * FROM array_index_op_test WHERE t && '{AAAAAAA80240}' ORDER BY seqno; 2301 seqno | i | t 2302-------+--------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------- 2303 19 | {52,82,17,74,23,46,69,51,75} | {AAAAAAAAAAAAA73084,AAAAA75968,AAAAAAAAAAAAAAAA14047,AAAAAAA80240,AAAAAAAAAAAAAAAAAAA1205,A68938} 2304 30 | {26,81,47,91,34} | {AAAAAAAAAAAAAAAAAAA70104,AAAAAAA80240} 2305 64 | {26,19,34,24,81,78} | {A96617,AAAAAAAAAAAAAAAAAAA70104,A68938,AAAAAAAAAAA53908,AAAAAAAAAAAAAAA453,AA17009,AAAAAAA80240} 2306 82 | {34,60,4,79,78,16,86,89,42,50} | {AAAAA40681,AAAAAAAAAAAAAAAAAA12591,AAAAAAA80240,AAAAAAAAAAAAAAAA55798,AAAAAAAAAAAAAAAAAAA70104} 2307 88 | {41,90,77,24,6,24} | {AAAA35194,AAAA35194,AAAAAAA80240,AAAAAAAAAAA46154,AAAAAA58494,AAAAAAAAAAAAAAAAAAA17075,AAAAAAAAAAAAAAAAAA59334,AAAAAAAAAAAAAAAAAAA91804,AA74433} 2308 97 | {54,2,86,65} | {47735,AAAAAAA99836,AAAAAAAAAAAAAAAAA6897,AAAAAAAAAAAAAAAA29150,AAAAAAA80240,AAAAAAAAAAAAAAAA98414,AAAAAAA56483,AAAAAAAAAAAAAAAA29150,AAAAAAA39692,AA21643} 2309 100 | {85,32,57,39,49,84,32,3,30} | {AAAAAAA80240,AAAAAAAAAAAAAAAA1729,AAAAA60038,AAAAAAAAAAA92631,AAAAAAAA9523} 2310(7 rows) 2311 2312SELECT * FROM array_index_op_test WHERE i @> '{32}' AND t && '{AAAAAAA80240}' ORDER BY seqno; 2313 seqno | i | t 2314-------+-----------------------------+------------------------------------------------------------------------------ 2315 100 | {85,32,57,39,49,84,32,3,30} | {AAAAAAA80240,AAAAAAAAAAAAAAAA1729,AAAAA60038,AAAAAAAAAAA92631,AAAAAAAA9523} 2316(1 row) 2317 2318SELECT * FROM array_index_op_test WHERE i && '{32}' AND t @> '{AAAAAAA80240}' ORDER BY seqno; 2319 seqno | i | t 2320-------+-----------------------------+------------------------------------------------------------------------------ 2321 100 | {85,32,57,39,49,84,32,3,30} | {AAAAAAA80240,AAAAAAAAAAAAAAAA1729,AAAAA60038,AAAAAAAAAAA92631,AAAAAAAA9523} 2322(1 row) 2323 2324SELECT * FROM array_index_op_test WHERE t = '{}' ORDER BY seqno; 2325 seqno | i | t 2326-------+----+---- 2327 101 | {} | {} 2328(1 row) 2329 2330SELECT * FROM array_op_test WHERE i = '{NULL}' ORDER BY seqno; 2331 seqno | i | t 2332-------+--------+-------- 2333 102 | {NULL} | {NULL} 2334(1 row) 2335 2336SELECT * FROM array_op_test WHERE i <@ '{NULL}' ORDER BY seqno; 2337 seqno | i | t 2338-------+----+---- 2339 101 | {} | {} 2340(1 row) 2341 2342RESET enable_seqscan; 2343RESET enable_indexscan; 2344RESET enable_bitmapscan; 2345-- 2346-- Try a GIN index with a lot of items with same key. (GIN creates a posting 2347-- tree when there are enough duplicates) 2348-- 2349CREATE TABLE array_gin_test (a int[]); 2350INSERT INTO array_gin_test SELECT ARRAY[1, g%5, g] FROM generate_series(1, 10000) g; 2351CREATE INDEX array_gin_test_idx ON array_gin_test USING gin (a); 2352SELECT COUNT(*) FROM array_gin_test WHERE a @> '{2}'; 2353 count 2354------- 2355 2000 2356(1 row) 2357 2358DROP TABLE array_gin_test; 2359-- 2360-- Test GIN index's reloptions 2361-- 2362CREATE INDEX gin_relopts_test ON array_index_op_test USING gin (i) 2363 WITH (FASTUPDATE=on, GIN_PENDING_LIST_LIMIT=128); 2364\d+ gin_relopts_test 2365 Index "public.gin_relopts_test" 2366 Column | Type | Key? | Definition | Storage | Stats target 2367--------+---------+------+------------+---------+-------------- 2368 i | integer | yes | i | plain | 2369gin, for table "public.array_index_op_test" 2370Options: fastupdate=on, gin_pending_list_limit=128 2371 2372-- 2373-- HASH 2374-- 2375CREATE INDEX hash_i4_index ON hash_i4_heap USING hash (random int4_ops); 2376CREATE INDEX hash_name_index ON hash_name_heap USING hash (random name_ops); 2377CREATE INDEX hash_txt_index ON hash_txt_heap USING hash (random text_ops); 2378CREATE INDEX hash_f8_index ON hash_f8_heap USING hash (random float8_ops) WITH (fillfactor=60); 2379CREATE UNLOGGED TABLE unlogged_hash_table (id int4); 2380CREATE INDEX unlogged_hash_index ON unlogged_hash_table USING hash (id int4_ops); 2381DROP TABLE unlogged_hash_table; 2382-- CREATE INDEX hash_ovfl_index ON hash_ovfl_heap USING hash (x int4_ops); 2383-- Test hash index build tuplesorting. Force hash tuplesort using low 2384-- maintenance_work_mem setting and fillfactor: 2385SET maintenance_work_mem = '1MB'; 2386CREATE INDEX hash_tuplesort_idx ON tenk1 USING hash (stringu1 name_ops) WITH (fillfactor = 10); 2387EXPLAIN (COSTS OFF) 2388SELECT count(*) FROM tenk1 WHERE stringu1 = 'TVAAAA'; 2389 QUERY PLAN 2390------------------------------------------------------- 2391 Aggregate 2392 -> Bitmap Heap Scan on tenk1 2393 Recheck Cond: (stringu1 = 'TVAAAA'::name) 2394 -> Bitmap Index Scan on hash_tuplesort_idx 2395 Index Cond: (stringu1 = 'TVAAAA'::name) 2396(5 rows) 2397 2398SELECT count(*) FROM tenk1 WHERE stringu1 = 'TVAAAA'; 2399 count 2400------- 2401 14 2402(1 row) 2403 2404DROP INDEX hash_tuplesort_idx; 2405RESET maintenance_work_mem; 2406-- 2407-- Test functional index 2408-- 2409CREATE TABLE func_index_heap (f1 text, f2 text); 2410CREATE UNIQUE INDEX func_index_index on func_index_heap (textcat(f1,f2)); 2411INSERT INTO func_index_heap VALUES('ABC','DEF'); 2412INSERT INTO func_index_heap VALUES('AB','CDEFG'); 2413INSERT INTO func_index_heap VALUES('QWE','RTY'); 2414-- this should fail because of unique index: 2415INSERT INTO func_index_heap VALUES('ABCD', 'EF'); 2416ERROR: duplicate key value violates unique constraint "func_index_index" 2417DETAIL: Key (textcat(f1, f2))=(ABCDEF) already exists. 2418-- but this shouldn't: 2419INSERT INTO func_index_heap VALUES('QWERTY'); 2420-- while we're here, see that the metadata looks sane 2421\d func_index_heap 2422 Table "public.func_index_heap" 2423 Column | Type | Collation | Nullable | Default 2424--------+------+-----------+----------+--------- 2425 f1 | text | | | 2426 f2 | text | | | 2427Indexes: 2428 "func_index_index" UNIQUE, btree (textcat(f1, f2)) 2429 2430\d func_index_index 2431 Index "public.func_index_index" 2432 Column | Type | Key? | Definition 2433---------+------+------+----------------- 2434 textcat | text | yes | textcat(f1, f2) 2435unique, btree, for table "public.func_index_heap" 2436 2437-- 2438-- Same test, expressional index 2439-- 2440DROP TABLE func_index_heap; 2441CREATE TABLE func_index_heap (f1 text, f2 text); 2442CREATE UNIQUE INDEX func_index_index on func_index_heap ((f1 || f2) text_ops); 2443INSERT INTO func_index_heap VALUES('ABC','DEF'); 2444INSERT INTO func_index_heap VALUES('AB','CDEFG'); 2445INSERT INTO func_index_heap VALUES('QWE','RTY'); 2446-- this should fail because of unique index: 2447INSERT INTO func_index_heap VALUES('ABCD', 'EF'); 2448ERROR: duplicate key value violates unique constraint "func_index_index" 2449DETAIL: Key ((f1 || f2))=(ABCDEF) already exists. 2450-- but this shouldn't: 2451INSERT INTO func_index_heap VALUES('QWERTY'); 2452-- while we're here, see that the metadata looks sane 2453\d func_index_heap 2454 Table "public.func_index_heap" 2455 Column | Type | Collation | Nullable | Default 2456--------+------+-----------+----------+--------- 2457 f1 | text | | | 2458 f2 | text | | | 2459Indexes: 2460 "func_index_index" UNIQUE, btree ((f1 || f2)) 2461 2462\d func_index_index 2463 Index "public.func_index_index" 2464 Column | Type | Key? | Definition 2465--------+------+------+------------ 2466 expr | text | yes | (f1 || f2) 2467unique, btree, for table "public.func_index_heap" 2468 2469-- this should fail because of unsafe column type (anonymous record) 2470create index on func_index_heap ((f1 || f2), (row(f1, f2))); 2471ERROR: column "row" has pseudo-type record 2472-- 2473-- Test unique index with included columns 2474-- 2475CREATE TABLE covering_index_heap (f1 int, f2 int, f3 text); 2476CREATE UNIQUE INDEX covering_index_index on covering_index_heap (f1,f2) INCLUDE(f3); 2477INSERT INTO covering_index_heap VALUES(1,1,'AAA'); 2478INSERT INTO covering_index_heap VALUES(1,2,'AAA'); 2479-- this should fail because of unique index on f1,f2: 2480INSERT INTO covering_index_heap VALUES(1,2,'BBB'); 2481ERROR: duplicate key value violates unique constraint "covering_index_index" 2482DETAIL: Key (f1, f2)=(1, 2) already exists. 2483-- and this shouldn't: 2484INSERT INTO covering_index_heap VALUES(1,4,'AAA'); 2485-- Try to build index on table that already contains data 2486CREATE UNIQUE INDEX covering_pkey on covering_index_heap (f1,f2) INCLUDE(f3); 2487-- Try to use existing covering index as primary key 2488ALTER TABLE covering_index_heap ADD CONSTRAINT covering_pkey PRIMARY KEY USING INDEX 2489covering_pkey; 2490DROP TABLE covering_index_heap; 2491-- 2492-- Also try building functional, expressional, and partial indexes on 2493-- tables that already contain data. 2494-- 2495create unique index hash_f8_index_1 on hash_f8_heap(abs(random)); 2496create unique index hash_f8_index_2 on hash_f8_heap((seqno + 1), random); 2497create unique index hash_f8_index_3 on hash_f8_heap(random) where seqno > 1000; 2498-- 2499-- Try some concurrent index builds 2500-- 2501-- Unfortunately this only tests about half the code paths because there are 2502-- no concurrent updates happening to the table at the same time. 2503CREATE TABLE concur_heap (f1 text, f2 text); 2504-- empty table 2505CREATE INDEX CONCURRENTLY concur_index1 ON concur_heap(f2,f1); 2506CREATE INDEX CONCURRENTLY IF NOT EXISTS concur_index1 ON concur_heap(f2,f1); 2507NOTICE: relation "concur_index1" already exists, skipping 2508INSERT INTO concur_heap VALUES ('a','b'); 2509INSERT INTO concur_heap VALUES ('b','b'); 2510-- unique index 2511CREATE UNIQUE INDEX CONCURRENTLY concur_index2 ON concur_heap(f1); 2512CREATE UNIQUE INDEX CONCURRENTLY IF NOT EXISTS concur_index2 ON concur_heap(f1); 2513NOTICE: relation "concur_index2" already exists, skipping 2514-- check if constraint is set up properly to be enforced 2515INSERT INTO concur_heap VALUES ('b','x'); 2516ERROR: duplicate key value violates unique constraint "concur_index2" 2517DETAIL: Key (f1)=(b) already exists. 2518-- check if constraint is enforced properly at build time 2519CREATE UNIQUE INDEX CONCURRENTLY concur_index3 ON concur_heap(f2); 2520ERROR: could not create unique index "concur_index3" 2521DETAIL: Key (f2)=(b) is duplicated. 2522-- test that expression indexes and partial indexes work concurrently 2523CREATE INDEX CONCURRENTLY concur_index4 on concur_heap(f2) WHERE f1='a'; 2524CREATE INDEX CONCURRENTLY concur_index5 on concur_heap(f2) WHERE f1='x'; 2525-- here we also check that you can default the index name 2526CREATE INDEX CONCURRENTLY on concur_heap((f2||f1)); 2527-- You can't do a concurrent index build in a transaction 2528BEGIN; 2529CREATE INDEX CONCURRENTLY concur_index7 ON concur_heap(f1); 2530ERROR: CREATE INDEX CONCURRENTLY cannot run inside a transaction block 2531COMMIT; 2532-- test where predicate is able to do a transactional update during 2533-- a concurrent build before switching pg_index state flags. 2534CREATE FUNCTION predicate_stable() RETURNS bool IMMUTABLE 2535LANGUAGE plpgsql AS $$ 2536BEGIN 2537 EXECUTE 'SELECT txid_current()'; 2538 RETURN true; 2539END; $$; 2540CREATE INDEX CONCURRENTLY concur_index8 ON concur_heap (f1) 2541 WHERE predicate_stable(); 2542DROP INDEX concur_index8; 2543DROP FUNCTION predicate_stable(); 2544-- But you can do a regular index build in a transaction 2545BEGIN; 2546CREATE INDEX std_index on concur_heap(f2); 2547COMMIT; 2548-- Failed builds are left invalid by VACUUM FULL, fixed by REINDEX 2549VACUUM FULL concur_heap; 2550REINDEX TABLE concur_heap; 2551ERROR: could not create unique index "concur_index3" 2552DETAIL: Key (f2)=(b) is duplicated. 2553DELETE FROM concur_heap WHERE f1 = 'b'; 2554VACUUM FULL concur_heap; 2555\d concur_heap 2556 Table "public.concur_heap" 2557 Column | Type | Collation | Nullable | Default 2558--------+------+-----------+----------+--------- 2559 f1 | text | | | 2560 f2 | text | | | 2561Indexes: 2562 "concur_index2" UNIQUE, btree (f1) 2563 "concur_index3" UNIQUE, btree (f2) INVALID 2564 "concur_heap_expr_idx" btree ((f2 || f1)) 2565 "concur_index1" btree (f2, f1) 2566 "concur_index4" btree (f2) WHERE f1 = 'a'::text 2567 "concur_index5" btree (f2) WHERE f1 = 'x'::text 2568 "std_index" btree (f2) 2569 2570REINDEX TABLE concur_heap; 2571\d concur_heap 2572 Table "public.concur_heap" 2573 Column | Type | Collation | Nullable | Default 2574--------+------+-----------+----------+--------- 2575 f1 | text | | | 2576 f2 | text | | | 2577Indexes: 2578 "concur_index2" UNIQUE, btree (f1) 2579 "concur_index3" UNIQUE, btree (f2) 2580 "concur_heap_expr_idx" btree ((f2 || f1)) 2581 "concur_index1" btree (f2, f1) 2582 "concur_index4" btree (f2) WHERE f1 = 'a'::text 2583 "concur_index5" btree (f2) WHERE f1 = 'x'::text 2584 "std_index" btree (f2) 2585 2586-- Temporary tables with concurrent builds and on-commit actions 2587-- CONCURRENTLY used with CREATE INDEX and DROP INDEX is ignored. 2588-- PRESERVE ROWS, the default. 2589CREATE TEMP TABLE concur_temp (f1 int, f2 text) 2590 ON COMMIT PRESERVE ROWS; 2591INSERT INTO concur_temp VALUES (1, 'foo'), (2, 'bar'); 2592CREATE INDEX CONCURRENTLY concur_temp_ind ON concur_temp(f1); 2593DROP INDEX CONCURRENTLY concur_temp_ind; 2594DROP TABLE concur_temp; 2595-- ON COMMIT DROP 2596BEGIN; 2597CREATE TEMP TABLE concur_temp (f1 int, f2 text) 2598 ON COMMIT DROP; 2599INSERT INTO concur_temp VALUES (1, 'foo'), (2, 'bar'); 2600-- Fails when running in a transaction. 2601CREATE INDEX CONCURRENTLY concur_temp_ind ON concur_temp(f1); 2602ERROR: CREATE INDEX CONCURRENTLY cannot run inside a transaction block 2603COMMIT; 2604-- ON COMMIT DELETE ROWS 2605CREATE TEMP TABLE concur_temp (f1 int, f2 text) 2606 ON COMMIT DELETE ROWS; 2607INSERT INTO concur_temp VALUES (1, 'foo'), (2, 'bar'); 2608CREATE INDEX CONCURRENTLY concur_temp_ind ON concur_temp(f1); 2609DROP INDEX CONCURRENTLY concur_temp_ind; 2610DROP TABLE concur_temp; 2611-- 2612-- Try some concurrent index drops 2613-- 2614DROP INDEX CONCURRENTLY "concur_index2"; -- works 2615DROP INDEX CONCURRENTLY IF EXISTS "concur_index2"; -- notice 2616NOTICE: index "concur_index2" does not exist, skipping 2617-- failures 2618DROP INDEX CONCURRENTLY "concur_index2", "concur_index3"; 2619ERROR: DROP INDEX CONCURRENTLY does not support dropping multiple objects 2620BEGIN; 2621DROP INDEX CONCURRENTLY "concur_index5"; 2622ERROR: DROP INDEX CONCURRENTLY cannot run inside a transaction block 2623ROLLBACK; 2624-- successes 2625DROP INDEX CONCURRENTLY IF EXISTS "concur_index3"; 2626DROP INDEX CONCURRENTLY "concur_index4"; 2627DROP INDEX CONCURRENTLY "concur_index5"; 2628DROP INDEX CONCURRENTLY "concur_index1"; 2629DROP INDEX CONCURRENTLY "concur_heap_expr_idx"; 2630\d concur_heap 2631 Table "public.concur_heap" 2632 Column | Type | Collation | Nullable | Default 2633--------+------+-----------+----------+--------- 2634 f1 | text | | | 2635 f2 | text | | | 2636Indexes: 2637 "std_index" btree (f2) 2638 2639DROP TABLE concur_heap; 2640-- 2641-- Test ADD CONSTRAINT USING INDEX 2642-- 2643CREATE TABLE cwi_test( a int , b varchar(10), c char); 2644-- add some data so that all tests have something to work with. 2645INSERT INTO cwi_test VALUES(1, 2), (3, 4), (5, 6); 2646CREATE UNIQUE INDEX cwi_uniq_idx ON cwi_test(a , b); 2647ALTER TABLE cwi_test ADD primary key USING INDEX cwi_uniq_idx; 2648\d cwi_test 2649 Table "public.cwi_test" 2650 Column | Type | Collation | Nullable | Default 2651--------+-----------------------+-----------+----------+--------- 2652 a | integer | | not null | 2653 b | character varying(10) | | not null | 2654 c | character(1) | | | 2655Indexes: 2656 "cwi_uniq_idx" PRIMARY KEY, btree (a, b) 2657 2658\d cwi_uniq_idx 2659 Index "public.cwi_uniq_idx" 2660 Column | Type | Key? | Definition 2661--------+-----------------------+------+------------ 2662 a | integer | yes | a 2663 b | character varying(10) | yes | b 2664primary key, btree, for table "public.cwi_test" 2665 2666CREATE UNIQUE INDEX cwi_uniq2_idx ON cwi_test(b , a); 2667ALTER TABLE cwi_test DROP CONSTRAINT cwi_uniq_idx, 2668 ADD CONSTRAINT cwi_replaced_pkey PRIMARY KEY 2669 USING INDEX cwi_uniq2_idx; 2670NOTICE: ALTER TABLE / ADD CONSTRAINT USING INDEX will rename index "cwi_uniq2_idx" to "cwi_replaced_pkey" 2671\d cwi_test 2672 Table "public.cwi_test" 2673 Column | Type | Collation | Nullable | Default 2674--------+-----------------------+-----------+----------+--------- 2675 a | integer | | not null | 2676 b | character varying(10) | | not null | 2677 c | character(1) | | | 2678Indexes: 2679 "cwi_replaced_pkey" PRIMARY KEY, btree (b, a) 2680 2681\d cwi_replaced_pkey 2682 Index "public.cwi_replaced_pkey" 2683 Column | Type | Key? | Definition 2684--------+-----------------------+------+------------ 2685 b | character varying(10) | yes | b 2686 a | integer | yes | a 2687primary key, btree, for table "public.cwi_test" 2688 2689DROP INDEX cwi_replaced_pkey; -- Should fail; a constraint depends on it 2690ERROR: cannot drop index cwi_replaced_pkey because constraint cwi_replaced_pkey on table cwi_test requires it 2691HINT: You can drop constraint cwi_replaced_pkey on table cwi_test instead. 2692DROP TABLE cwi_test; 2693-- ADD CONSTRAINT USING INDEX is forbidden on partitioned tables 2694CREATE TABLE cwi_test(a int) PARTITION BY hash (a); 2695create unique index on cwi_test (a); 2696alter table cwi_test add primary key using index cwi_test_a_idx ; 2697ERROR: ALTER TABLE / ADD CONSTRAINT USING INDEX is not supported on partitioned tables 2698DROP TABLE cwi_test; 2699-- 2700-- Check handling of indexes on system columns 2701-- 2702CREATE TABLE oid_table (a INT) WITH OIDS; 2703-- An index on the OID column should be allowed 2704CREATE INDEX ON oid_table (oid); 2705-- Other system columns cannot be indexed 2706CREATE INDEX ON oid_table (ctid); 2707ERROR: index creation on system columns is not supported 2708-- nor used in expressions 2709CREATE INDEX ON oid_table ((ctid >= '(1000,0)')); 2710ERROR: index creation on system columns is not supported 2711-- nor used in predicates 2712CREATE INDEX ON oid_table (a) WHERE ctid >= '(1000,0)'; 2713ERROR: index creation on system columns is not supported 2714DROP TABLE oid_table; 2715-- 2716-- Tests for IS NULL/IS NOT NULL with b-tree indexes 2717-- 2718SELECT unique1, unique2 INTO onek_with_null FROM onek; 2719INSERT INTO onek_with_null (unique1,unique2) VALUES (NULL, -1), (NULL, NULL); 2720CREATE UNIQUE INDEX onek_nulltest ON onek_with_null (unique2,unique1); 2721SET enable_seqscan = OFF; 2722SET enable_indexscan = ON; 2723SET enable_bitmapscan = ON; 2724SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL; 2725 count 2726------- 2727 2 2728(1 row) 2729 2730SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL AND unique2 IS NULL; 2731 count 2732------- 2733 1 2734(1 row) 2735 2736SELECT count(*) FROM onek_with_null WHERE unique1 IS NOT NULL; 2737 count 2738------- 2739 1000 2740(1 row) 2741 2742SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL AND unique2 IS NOT NULL; 2743 count 2744------- 2745 1 2746(1 row) 2747 2748SELECT count(*) FROM onek_with_null WHERE unique1 IS NOT NULL AND unique1 > 500; 2749 count 2750------- 2751 499 2752(1 row) 2753 2754SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL AND unique1 > 500; 2755 count 2756------- 2757 0 2758(1 row) 2759 2760DROP INDEX onek_nulltest; 2761CREATE UNIQUE INDEX onek_nulltest ON onek_with_null (unique2 desc,unique1); 2762SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL; 2763 count 2764------- 2765 2 2766(1 row) 2767 2768SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL AND unique2 IS NULL; 2769 count 2770------- 2771 1 2772(1 row) 2773 2774SELECT count(*) FROM onek_with_null WHERE unique1 IS NOT NULL; 2775 count 2776------- 2777 1000 2778(1 row) 2779 2780SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL AND unique2 IS NOT NULL; 2781 count 2782------- 2783 1 2784(1 row) 2785 2786SELECT count(*) FROM onek_with_null WHERE unique1 IS NOT NULL AND unique1 > 500; 2787 count 2788------- 2789 499 2790(1 row) 2791 2792SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL AND unique1 > 500; 2793 count 2794------- 2795 0 2796(1 row) 2797 2798DROP INDEX onek_nulltest; 2799CREATE UNIQUE INDEX onek_nulltest ON onek_with_null (unique2 desc nulls last,unique1); 2800SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL; 2801 count 2802------- 2803 2 2804(1 row) 2805 2806SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL AND unique2 IS NULL; 2807 count 2808------- 2809 1 2810(1 row) 2811 2812SELECT count(*) FROM onek_with_null WHERE unique1 IS NOT NULL; 2813 count 2814------- 2815 1000 2816(1 row) 2817 2818SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL AND unique2 IS NOT NULL; 2819 count 2820------- 2821 1 2822(1 row) 2823 2824SELECT count(*) FROM onek_with_null WHERE unique1 IS NOT NULL AND unique1 > 500; 2825 count 2826------- 2827 499 2828(1 row) 2829 2830SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL AND unique1 > 500; 2831 count 2832------- 2833 0 2834(1 row) 2835 2836DROP INDEX onek_nulltest; 2837CREATE UNIQUE INDEX onek_nulltest ON onek_with_null (unique2 nulls first,unique1); 2838SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL; 2839 count 2840------- 2841 2 2842(1 row) 2843 2844SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL AND unique2 IS NULL; 2845 count 2846------- 2847 1 2848(1 row) 2849 2850SELECT count(*) FROM onek_with_null WHERE unique1 IS NOT NULL; 2851 count 2852------- 2853 1000 2854(1 row) 2855 2856SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL AND unique2 IS NOT NULL; 2857 count 2858------- 2859 1 2860(1 row) 2861 2862SELECT count(*) FROM onek_with_null WHERE unique1 IS NOT NULL AND unique1 > 500; 2863 count 2864------- 2865 499 2866(1 row) 2867 2868SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL AND unique1 > 500; 2869 count 2870------- 2871 0 2872(1 row) 2873 2874DROP INDEX onek_nulltest; 2875-- Check initial-positioning logic too 2876CREATE UNIQUE INDEX onek_nulltest ON onek_with_null (unique2); 2877SET enable_seqscan = OFF; 2878SET enable_indexscan = ON; 2879SET enable_bitmapscan = OFF; 2880SELECT unique1, unique2 FROM onek_with_null 2881 ORDER BY unique2 LIMIT 2; 2882 unique1 | unique2 2883---------+--------- 2884 | -1 2885 147 | 0 2886(2 rows) 2887 2888SELECT unique1, unique2 FROM onek_with_null WHERE unique2 >= -1 2889 ORDER BY unique2 LIMIT 2; 2890 unique1 | unique2 2891---------+--------- 2892 | -1 2893 147 | 0 2894(2 rows) 2895 2896SELECT unique1, unique2 FROM onek_with_null WHERE unique2 >= 0 2897 ORDER BY unique2 LIMIT 2; 2898 unique1 | unique2 2899---------+--------- 2900 147 | 0 2901 931 | 1 2902(2 rows) 2903 2904SELECT unique1, unique2 FROM onek_with_null 2905 ORDER BY unique2 DESC LIMIT 2; 2906 unique1 | unique2 2907---------+--------- 2908 | 2909 278 | 999 2910(2 rows) 2911 2912SELECT unique1, unique2 FROM onek_with_null WHERE unique2 >= -1 2913 ORDER BY unique2 DESC LIMIT 2; 2914 unique1 | unique2 2915---------+--------- 2916 278 | 999 2917 0 | 998 2918(2 rows) 2919 2920SELECT unique1, unique2 FROM onek_with_null WHERE unique2 < 999 2921 ORDER BY unique2 DESC LIMIT 2; 2922 unique1 | unique2 2923---------+--------- 2924 0 | 998 2925 744 | 997 2926(2 rows) 2927 2928RESET enable_seqscan; 2929RESET enable_indexscan; 2930RESET enable_bitmapscan; 2931DROP TABLE onek_with_null; 2932-- 2933-- Check bitmap index path planning 2934-- 2935EXPLAIN (COSTS OFF) 2936SELECT * FROM tenk1 2937 WHERE thousand = 42 AND (tenthous = 1 OR tenthous = 3 OR tenthous = 42); 2938 QUERY PLAN 2939----------------------------------------------------------------------------------------------------------------------------------------- 2940 Bitmap Heap Scan on tenk1 2941 Recheck Cond: (((thousand = 42) AND (tenthous = 1)) OR ((thousand = 42) AND (tenthous = 3)) OR ((thousand = 42) AND (tenthous = 42))) 2942 -> BitmapOr 2943 -> Bitmap Index Scan on tenk1_thous_tenthous 2944 Index Cond: ((thousand = 42) AND (tenthous = 1)) 2945 -> Bitmap Index Scan on tenk1_thous_tenthous 2946 Index Cond: ((thousand = 42) AND (tenthous = 3)) 2947 -> Bitmap Index Scan on tenk1_thous_tenthous 2948 Index Cond: ((thousand = 42) AND (tenthous = 42)) 2949(9 rows) 2950 2951SELECT * FROM tenk1 2952 WHERE thousand = 42 AND (tenthous = 1 OR tenthous = 3 OR tenthous = 42); 2953 unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 2954---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+--------- 2955 42 | 5530 | 0 | 2 | 2 | 2 | 42 | 42 | 42 | 42 | 42 | 84 | 85 | QBAAAA | SEIAAA | OOOOxx 2956(1 row) 2957 2958EXPLAIN (COSTS OFF) 2959SELECT count(*) FROM tenk1 2960 WHERE hundred = 42 AND (thousand = 42 OR thousand = 99); 2961 QUERY PLAN 2962--------------------------------------------------------------------------------- 2963 Aggregate 2964 -> Bitmap Heap Scan on tenk1 2965 Recheck Cond: ((hundred = 42) AND ((thousand = 42) OR (thousand = 99))) 2966 -> BitmapAnd 2967 -> Bitmap Index Scan on tenk1_hundred 2968 Index Cond: (hundred = 42) 2969 -> BitmapOr 2970 -> Bitmap Index Scan on tenk1_thous_tenthous 2971 Index Cond: (thousand = 42) 2972 -> Bitmap Index Scan on tenk1_thous_tenthous 2973 Index Cond: (thousand = 99) 2974(11 rows) 2975 2976SELECT count(*) FROM tenk1 2977 WHERE hundred = 42 AND (thousand = 42 OR thousand = 99); 2978 count 2979------- 2980 10 2981(1 row) 2982 2983-- 2984-- Check behavior with duplicate index column contents 2985-- 2986CREATE TABLE dupindexcols AS 2987 SELECT unique1 as id, stringu2::text as f1 FROM tenk1; 2988CREATE INDEX dupindexcols_i ON dupindexcols (f1, id, f1 text_pattern_ops); 2989ANALYZE dupindexcols; 2990EXPLAIN (COSTS OFF) 2991 SELECT count(*) FROM dupindexcols 2992 WHERE f1 BETWEEN 'WA' AND 'ZZZ' and id < 1000 and f1 ~<~ 'YX'; 2993 QUERY PLAN 2994---------------------------------------------------------------------------------------------------------------- 2995 Aggregate 2996 -> Bitmap Heap Scan on dupindexcols 2997 Recheck Cond: ((f1 >= 'WA'::text) AND (f1 <= 'ZZZ'::text) AND (id < 1000) AND (f1 ~<~ 'YX'::text)) 2998 -> Bitmap Index Scan on dupindexcols_i 2999 Index Cond: ((f1 >= 'WA'::text) AND (f1 <= 'ZZZ'::text) AND (id < 1000) AND (f1 ~<~ 'YX'::text)) 3000(5 rows) 3001 3002SELECT count(*) FROM dupindexcols 3003 WHERE f1 BETWEEN 'WA' AND 'ZZZ' and id < 1000 and f1 ~<~ 'YX'; 3004 count 3005------- 3006 97 3007(1 row) 3008 3009-- 3010-- Check ordering of =ANY indexqual results (bug in 9.2.0) 3011-- 3012vacuum tenk1; -- ensure we get consistent plans here 3013explain (costs off) 3014SELECT unique1 FROM tenk1 3015WHERE unique1 IN (1,42,7) 3016ORDER BY unique1; 3017 QUERY PLAN 3018------------------------------------------------------- 3019 Index Only Scan using tenk1_unique1 on tenk1 3020 Index Cond: (unique1 = ANY ('{1,42,7}'::integer[])) 3021(2 rows) 3022 3023SELECT unique1 FROM tenk1 3024WHERE unique1 IN (1,42,7) 3025ORDER BY unique1; 3026 unique1 3027--------- 3028 1 3029 7 3030 42 3031(3 rows) 3032 3033explain (costs off) 3034SELECT thousand, tenthous FROM tenk1 3035WHERE thousand < 2 AND tenthous IN (1001,3000) 3036ORDER BY thousand; 3037 QUERY PLAN 3038------------------------------------------------------- 3039 Index Only Scan using tenk1_thous_tenthous on tenk1 3040 Index Cond: (thousand < 2) 3041 Filter: (tenthous = ANY ('{1001,3000}'::integer[])) 3042(3 rows) 3043 3044SELECT thousand, tenthous FROM tenk1 3045WHERE thousand < 2 AND tenthous IN (1001,3000) 3046ORDER BY thousand; 3047 thousand | tenthous 3048----------+---------- 3049 0 | 3000 3050 1 | 1001 3051(2 rows) 3052 3053SET enable_indexonlyscan = OFF; 3054explain (costs off) 3055SELECT thousand, tenthous FROM tenk1 3056WHERE thousand < 2 AND tenthous IN (1001,3000) 3057ORDER BY thousand; 3058 QUERY PLAN 3059-------------------------------------------------------------------------------------- 3060 Sort 3061 Sort Key: thousand 3062 -> Index Scan using tenk1_thous_tenthous on tenk1 3063 Index Cond: ((thousand < 2) AND (tenthous = ANY ('{1001,3000}'::integer[]))) 3064(4 rows) 3065 3066SELECT thousand, tenthous FROM tenk1 3067WHERE thousand < 2 AND tenthous IN (1001,3000) 3068ORDER BY thousand; 3069 thousand | tenthous 3070----------+---------- 3071 0 | 3000 3072 1 | 1001 3073(2 rows) 3074 3075RESET enable_indexonlyscan; 3076-- 3077-- Check elimination of constant-NULL subexpressions 3078-- 3079explain (costs off) 3080 select * from tenk1 where (thousand, tenthous) in ((1,1001), (null,null)); 3081 QUERY PLAN 3082------------------------------------------------------ 3083 Index Scan using tenk1_thous_tenthous on tenk1 3084 Index Cond: ((thousand = 1) AND (tenthous = 1001)) 3085(2 rows) 3086 3087-- 3088-- Check matching of boolean index columns to WHERE conditions and sort keys 3089-- 3090create temp table boolindex (b bool, i int, unique(b, i), junk float); 3091explain (costs off) 3092 select * from boolindex order by b, i limit 10; 3093 QUERY PLAN 3094------------------------------------------------------- 3095 Limit 3096 -> Index Scan using boolindex_b_i_key on boolindex 3097(2 rows) 3098 3099explain (costs off) 3100 select * from boolindex where b order by i limit 10; 3101 QUERY PLAN 3102------------------------------------------------------- 3103 Limit 3104 -> Index Scan using boolindex_b_i_key on boolindex 3105 Index Cond: (b = true) 3106 Filter: b 3107(4 rows) 3108 3109explain (costs off) 3110 select * from boolindex where b = true order by i desc limit 10; 3111 QUERY PLAN 3112---------------------------------------------------------------- 3113 Limit 3114 -> Index Scan Backward using boolindex_b_i_key on boolindex 3115 Index Cond: (b = true) 3116 Filter: b 3117(4 rows) 3118 3119explain (costs off) 3120 select * from boolindex where not b order by i limit 10; 3121 QUERY PLAN 3122------------------------------------------------------- 3123 Limit 3124 -> Index Scan using boolindex_b_i_key on boolindex 3125 Index Cond: (b = false) 3126 Filter: (NOT b) 3127(4 rows) 3128 3129-- 3130-- Test for multilevel page deletion 3131-- 3132CREATE TABLE delete_test_table (a bigint, b bigint, c bigint, d bigint); 3133INSERT INTO delete_test_table SELECT i, 1, 2, 3 FROM generate_series(1,80000) i; 3134ALTER TABLE delete_test_table ADD PRIMARY KEY (a,b,c,d); 3135DELETE FROM delete_test_table WHERE a > 40000; 3136VACUUM delete_test_table; 3137DELETE FROM delete_test_table WHERE a > 10; 3138VACUUM delete_test_table; 3139-- 3140-- REINDEX (VERBOSE) 3141-- 3142CREATE TABLE reindex_verbose(id integer primary key); 3143\set VERBOSITY terse 3144REINDEX (VERBOSE) TABLE reindex_verbose; 3145INFO: index "reindex_verbose_pkey" was reindexed 3146DROP TABLE reindex_verbose; 3147-- 3148-- REINDEX SCHEMA 3149-- 3150REINDEX SCHEMA schema_to_reindex; -- failure, schema does not exist 3151ERROR: schema "schema_to_reindex" does not exist 3152CREATE SCHEMA schema_to_reindex; 3153SET search_path = 'schema_to_reindex'; 3154CREATE TABLE table1(col1 SERIAL PRIMARY KEY); 3155INSERT INTO table1 SELECT generate_series(1,400); 3156CREATE TABLE table2(col1 SERIAL PRIMARY KEY, col2 TEXT NOT NULL); 3157INSERT INTO table2 SELECT generate_series(1,400), 'abc'; 3158CREATE INDEX ON table2(col2); 3159CREATE MATERIALIZED VIEW matview AS SELECT col1 FROM table2; 3160CREATE INDEX ON matview(col1); 3161CREATE VIEW view AS SELECT col2 FROM table2; 3162CREATE TABLE reindex_before AS 3163SELECT oid, relname, relfilenode, relkind, reltoastrelid 3164 FROM pg_class 3165 where relnamespace = (SELECT oid FROM pg_namespace WHERE nspname = 'schema_to_reindex'); 3166INSERT INTO reindex_before 3167SELECT oid, 'pg_toast_TABLE', relfilenode, relkind, reltoastrelid 3168FROM pg_class WHERE oid IN 3169 (SELECT reltoastrelid FROM reindex_before WHERE reltoastrelid > 0); 3170INSERT INTO reindex_before 3171SELECT oid, 'pg_toast_TABLE_index', relfilenode, relkind, reltoastrelid 3172FROM pg_class where oid in 3173 (select indexrelid from pg_index where indrelid in 3174 (select reltoastrelid from reindex_before where reltoastrelid > 0)); 3175REINDEX SCHEMA schema_to_reindex; 3176CREATE TABLE reindex_after AS SELECT oid, relname, relfilenode, relkind 3177 FROM pg_class 3178 where relnamespace = (SELECT oid FROM pg_namespace WHERE nspname = 'schema_to_reindex'); 3179SELECT b.relname, 3180 b.relkind, 3181 CASE WHEN a.relfilenode = b.relfilenode THEN 'relfilenode is unchanged' 3182 ELSE 'relfilenode has changed' END 3183 FROM reindex_before b JOIN pg_class a ON b.oid = a.oid 3184 ORDER BY 1; 3185 relname | relkind | case 3186----------------------+---------+-------------------------- 3187 matview | m | relfilenode is unchanged 3188 matview_col1_idx | i | relfilenode has changed 3189 pg_toast_TABLE | t | relfilenode is unchanged 3190 pg_toast_TABLE_index | i | relfilenode has changed 3191 table1 | r | relfilenode is unchanged 3192 table1_col1_seq | S | relfilenode is unchanged 3193 table1_pkey | i | relfilenode has changed 3194 table2 | r | relfilenode is unchanged 3195 table2_col1_seq | S | relfilenode is unchanged 3196 table2_col2_idx | i | relfilenode has changed 3197 table2_pkey | i | relfilenode has changed 3198 view | v | relfilenode is unchanged 3199(12 rows) 3200 3201REINDEX SCHEMA schema_to_reindex; 3202BEGIN; 3203REINDEX SCHEMA schema_to_reindex; -- failure, cannot run in a transaction 3204ERROR: REINDEX SCHEMA cannot run inside a transaction block 3205END; 3206-- Failure for unauthorized user 3207CREATE ROLE regress_reindexuser NOLOGIN; 3208SET SESSION ROLE regress_reindexuser; 3209REINDEX SCHEMA schema_to_reindex; 3210ERROR: must be owner of schema schema_to_reindex 3211-- Permission failures with toast tables and indexes (pg_proc's toast here) 3212RESET ROLE; 3213GRANT USAGE ON SCHEMA pg_toast TO regress_reindexuser; 3214SET SESSION ROLE regress_reindexuser; 3215REINDEX TABLE pg_toast.pg_toast_1255; 3216ERROR: must be owner of table pg_toast_1255 3217REINDEX INDEX pg_toast.pg_toast_1255_index; 3218ERROR: must be owner of index pg_toast_1255_index 3219-- Clean up 3220RESET ROLE; 3221REVOKE USAGE ON SCHEMA pg_toast FROM regress_reindexuser; 3222DROP ROLE regress_reindexuser; 3223\set VERBOSITY terse \\ -- suppress cascade details 3224DROP SCHEMA schema_to_reindex CASCADE; 3225NOTICE: drop cascades to 6 other objects 3226