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