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); 2338WARNING: hash indexes are not WAL-logged and their use is discouraged 2339CREATE INDEX hash_name_index ON hash_name_heap USING hash (random name_ops); 2340WARNING: hash indexes are not WAL-logged and their use is discouraged 2341CREATE INDEX hash_txt_index ON hash_txt_heap USING hash (random text_ops); 2342WARNING: hash indexes are not WAL-logged and their use is discouraged 2343CREATE INDEX hash_f8_index ON hash_f8_heap USING hash (random float8_ops); 2344WARNING: hash indexes are not WAL-logged and their use is discouraged 2345CREATE UNLOGGED TABLE unlogged_hash_table (id int4); 2346CREATE INDEX unlogged_hash_index ON unlogged_hash_table USING hash (id int4_ops); 2347DROP TABLE unlogged_hash_table; 2348-- CREATE INDEX hash_ovfl_index ON hash_ovfl_heap USING hash (x int4_ops); 2349-- Test hash index build tuplesorting. Force hash tuplesort using low 2350-- maintenance_work_mem setting and fillfactor: 2351SET maintenance_work_mem = '1MB'; 2352CREATE INDEX hash_tuplesort_idx ON tenk1 USING hash (stringu1 name_ops) WITH (fillfactor = 10); 2353WARNING: hash indexes are not WAL-logged and their use is discouraged 2354EXPLAIN (COSTS OFF) 2355SELECT count(*) FROM tenk1 WHERE stringu1 = 'TVAAAA'; 2356 QUERY PLAN 2357------------------------------------------------------- 2358 Aggregate 2359 -> Bitmap Heap Scan on tenk1 2360 Recheck Cond: (stringu1 = 'TVAAAA'::name) 2361 -> Bitmap Index Scan on hash_tuplesort_idx 2362 Index Cond: (stringu1 = 'TVAAAA'::name) 2363(5 rows) 2364 2365SELECT count(*) FROM tenk1 WHERE stringu1 = 'TVAAAA'; 2366 count 2367------- 2368 14 2369(1 row) 2370 2371DROP INDEX hash_tuplesort_idx; 2372RESET maintenance_work_mem; 2373-- 2374-- Test functional index 2375-- 2376CREATE TABLE func_index_heap (f1 text, f2 text); 2377CREATE UNIQUE INDEX func_index_index on func_index_heap (textcat(f1,f2)); 2378INSERT INTO func_index_heap VALUES('ABC','DEF'); 2379INSERT INTO func_index_heap VALUES('AB','CDEFG'); 2380INSERT INTO func_index_heap VALUES('QWE','RTY'); 2381-- this should fail because of unique index: 2382INSERT INTO func_index_heap VALUES('ABCD', 'EF'); 2383ERROR: duplicate key value violates unique constraint "func_index_index" 2384DETAIL: Key (textcat(f1, f2))=(ABCDEF) already exists. 2385-- but this shouldn't: 2386INSERT INTO func_index_heap VALUES('QWERTY'); 2387-- while we're here, see that the metadata looks sane 2388\d func_index_heap 2389Table "public.func_index_heap" 2390 Column | Type | Modifiers 2391--------+------+----------- 2392 f1 | text | 2393 f2 | text | 2394Indexes: 2395 "func_index_index" UNIQUE, btree (textcat(f1, f2)) 2396 2397\d func_index_index 2398 Index "public.func_index_index" 2399 Column | Type | Definition 2400---------+------+----------------- 2401 textcat | text | textcat(f1, f2) 2402unique, btree, for table "public.func_index_heap" 2403 2404-- 2405-- Same test, expressional index 2406-- 2407DROP TABLE func_index_heap; 2408CREATE TABLE func_index_heap (f1 text, f2 text); 2409CREATE UNIQUE INDEX func_index_index on func_index_heap ((f1 || f2) text_ops); 2410INSERT INTO func_index_heap VALUES('ABC','DEF'); 2411INSERT INTO func_index_heap VALUES('AB','CDEFG'); 2412INSERT INTO func_index_heap VALUES('QWE','RTY'); 2413-- this should fail because of unique index: 2414INSERT INTO func_index_heap VALUES('ABCD', 'EF'); 2415ERROR: duplicate key value violates unique constraint "func_index_index" 2416DETAIL: Key ((f1 || f2))=(ABCDEF) already exists. 2417-- but this shouldn't: 2418INSERT INTO func_index_heap VALUES('QWERTY'); 2419-- while we're here, see that the metadata looks sane 2420\d func_index_heap 2421Table "public.func_index_heap" 2422 Column | Type | Modifiers 2423--------+------+----------- 2424 f1 | text | 2425 f2 | text | 2426Indexes: 2427 "func_index_index" UNIQUE, btree ((f1 || f2)) 2428 2429\d func_index_index 2430Index "public.func_index_index" 2431 Column | Type | Definition 2432--------+------+------------ 2433 expr | text | (f1 || f2) 2434unique, btree, for table "public.func_index_heap" 2435 2436-- this should fail because of unsafe column type (anonymous record) 2437create index on func_index_heap ((f1 || f2), (row(f1, f2))); 2438ERROR: column "row" has pseudo-type record 2439-- 2440-- Also try building functional, expressional, and partial indexes on 2441-- tables that already contain data. 2442-- 2443create unique index hash_f8_index_1 on hash_f8_heap(abs(random)); 2444create unique index hash_f8_index_2 on hash_f8_heap((seqno + 1), random); 2445create unique index hash_f8_index_3 on hash_f8_heap(random) where seqno > 1000; 2446-- 2447-- Try some concurrent index builds 2448-- 2449-- Unfortunately this only tests about half the code paths because there are 2450-- no concurrent updates happening to the table at the same time. 2451CREATE TABLE concur_heap (f1 text, f2 text); 2452-- empty table 2453CREATE INDEX CONCURRENTLY concur_index1 ON concur_heap(f2,f1); 2454CREATE INDEX CONCURRENTLY IF NOT EXISTS concur_index1 ON concur_heap(f2,f1); 2455NOTICE: relation "concur_index1" already exists, skipping 2456INSERT INTO concur_heap VALUES ('a','b'); 2457INSERT INTO concur_heap VALUES ('b','b'); 2458-- unique index 2459CREATE UNIQUE INDEX CONCURRENTLY concur_index2 ON concur_heap(f1); 2460CREATE UNIQUE INDEX CONCURRENTLY IF NOT EXISTS concur_index2 ON concur_heap(f1); 2461NOTICE: relation "concur_index2" already exists, skipping 2462-- check if constraint is set up properly to be enforced 2463INSERT INTO concur_heap VALUES ('b','x'); 2464ERROR: duplicate key value violates unique constraint "concur_index2" 2465DETAIL: Key (f1)=(b) already exists. 2466-- check if constraint is enforced properly at build time 2467CREATE UNIQUE INDEX CONCURRENTLY concur_index3 ON concur_heap(f2); 2468ERROR: could not create unique index "concur_index3" 2469DETAIL: Key (f2)=(b) is duplicated. 2470-- test that expression indexes and partial indexes work concurrently 2471CREATE INDEX CONCURRENTLY concur_index4 on concur_heap(f2) WHERE f1='a'; 2472CREATE INDEX CONCURRENTLY concur_index5 on concur_heap(f2) WHERE f1='x'; 2473-- here we also check that you can default the index name 2474CREATE INDEX CONCURRENTLY on concur_heap((f2||f1)); 2475-- You can't do a concurrent index build in a transaction 2476BEGIN; 2477CREATE INDEX CONCURRENTLY concur_index7 ON concur_heap(f1); 2478ERROR: CREATE INDEX CONCURRENTLY cannot run inside a transaction block 2479COMMIT; 2480-- test where predicate is able to do a transactional update during 2481-- a concurrent build before switching pg_index state flags. 2482CREATE FUNCTION predicate_stable() RETURNS bool IMMUTABLE 2483LANGUAGE plpgsql AS $$ 2484BEGIN 2485 EXECUTE 'SELECT txid_current()'; 2486 RETURN true; 2487END; $$; 2488CREATE INDEX CONCURRENTLY concur_index8 ON concur_heap (f1) 2489 WHERE predicate_stable(); 2490DROP INDEX concur_index8; 2491DROP FUNCTION predicate_stable(); 2492-- But you can do a regular index build in a transaction 2493BEGIN; 2494CREATE INDEX std_index on concur_heap(f2); 2495COMMIT; 2496-- Failed builds are left invalid by VACUUM FULL, fixed by REINDEX 2497VACUUM FULL concur_heap; 2498REINDEX TABLE concur_heap; 2499ERROR: could not create unique index "concur_index3" 2500DETAIL: Key (f2)=(b) is duplicated. 2501DELETE FROM concur_heap WHERE f1 = 'b'; 2502VACUUM FULL concur_heap; 2503\d concur_heap 2504Table "public.concur_heap" 2505 Column | Type | Modifiers 2506--------+------+----------- 2507 f1 | text | 2508 f2 | text | 2509Indexes: 2510 "concur_index2" UNIQUE, btree (f1) 2511 "concur_index3" UNIQUE, btree (f2) INVALID 2512 "concur_heap_expr_idx" btree ((f2 || f1)) 2513 "concur_index1" btree (f2, f1) 2514 "concur_index4" btree (f2) WHERE f1 = 'a'::text 2515 "concur_index5" btree (f2) WHERE f1 = 'x'::text 2516 "std_index" btree (f2) 2517 2518REINDEX TABLE concur_heap; 2519\d concur_heap 2520Table "public.concur_heap" 2521 Column | Type | Modifiers 2522--------+------+----------- 2523 f1 | text | 2524 f2 | text | 2525Indexes: 2526 "concur_index2" UNIQUE, btree (f1) 2527 "concur_index3" UNIQUE, btree (f2) 2528 "concur_heap_expr_idx" btree ((f2 || f1)) 2529 "concur_index1" btree (f2, f1) 2530 "concur_index4" btree (f2) WHERE f1 = 'a'::text 2531 "concur_index5" btree (f2) WHERE f1 = 'x'::text 2532 "std_index" btree (f2) 2533 2534-- Temporary tables with concurrent builds and on-commit actions 2535-- CONCURRENTLY used with CREATE INDEX and DROP INDEX is ignored. 2536-- PRESERVE ROWS, the default. 2537CREATE TEMP TABLE concur_temp (f1 int, f2 text) 2538 ON COMMIT PRESERVE ROWS; 2539INSERT INTO concur_temp VALUES (1, 'foo'), (2, 'bar'); 2540CREATE INDEX CONCURRENTLY concur_temp_ind ON concur_temp(f1); 2541DROP INDEX CONCURRENTLY concur_temp_ind; 2542DROP TABLE concur_temp; 2543-- ON COMMIT DROP 2544BEGIN; 2545CREATE TEMP TABLE concur_temp (f1 int, f2 text) 2546 ON COMMIT DROP; 2547INSERT INTO concur_temp VALUES (1, 'foo'), (2, 'bar'); 2548-- Fails when running in a transaction. 2549CREATE INDEX CONCURRENTLY concur_temp_ind ON concur_temp(f1); 2550ERROR: CREATE INDEX CONCURRENTLY cannot run inside a transaction block 2551COMMIT; 2552-- ON COMMIT DELETE ROWS 2553CREATE TEMP TABLE concur_temp (f1 int, f2 text) 2554 ON COMMIT DELETE ROWS; 2555INSERT INTO concur_temp VALUES (1, 'foo'), (2, 'bar'); 2556CREATE INDEX CONCURRENTLY concur_temp_ind ON concur_temp(f1); 2557DROP INDEX CONCURRENTLY concur_temp_ind; 2558DROP TABLE concur_temp; 2559-- 2560-- Try some concurrent index drops 2561-- 2562DROP INDEX CONCURRENTLY "concur_index2"; -- works 2563DROP INDEX CONCURRENTLY IF EXISTS "concur_index2"; -- notice 2564NOTICE: index "concur_index2" does not exist, skipping 2565-- failures 2566DROP INDEX CONCURRENTLY "concur_index2", "concur_index3"; 2567ERROR: DROP INDEX CONCURRENTLY does not support dropping multiple objects 2568BEGIN; 2569DROP INDEX CONCURRENTLY "concur_index5"; 2570ERROR: DROP INDEX CONCURRENTLY cannot run inside a transaction block 2571ROLLBACK; 2572-- successes 2573DROP INDEX CONCURRENTLY IF EXISTS "concur_index3"; 2574DROP INDEX CONCURRENTLY "concur_index4"; 2575DROP INDEX CONCURRENTLY "concur_index5"; 2576DROP INDEX CONCURRENTLY "concur_index1"; 2577DROP INDEX CONCURRENTLY "concur_heap_expr_idx"; 2578\d concur_heap 2579Table "public.concur_heap" 2580 Column | Type | Modifiers 2581--------+------+----------- 2582 f1 | text | 2583 f2 | text | 2584Indexes: 2585 "std_index" btree (f2) 2586 2587DROP TABLE concur_heap; 2588-- 2589-- Test ADD CONSTRAINT USING INDEX 2590-- 2591CREATE TABLE cwi_test( a int , b varchar(10), c char); 2592-- add some data so that all tests have something to work with. 2593INSERT INTO cwi_test VALUES(1, 2), (3, 4), (5, 6); 2594CREATE UNIQUE INDEX cwi_uniq_idx ON cwi_test(a , b); 2595ALTER TABLE cwi_test ADD primary key USING INDEX cwi_uniq_idx; 2596\d cwi_test 2597 Table "public.cwi_test" 2598 Column | Type | Modifiers 2599--------+-----------------------+----------- 2600 a | integer | not null 2601 b | character varying(10) | not null 2602 c | character(1) | 2603Indexes: 2604 "cwi_uniq_idx" PRIMARY KEY, btree (a, b) 2605 2606\d cwi_uniq_idx 2607 Index "public.cwi_uniq_idx" 2608 Column | Type | Definition 2609--------+-----------------------+------------ 2610 a | integer | a 2611 b | character varying(10) | b 2612primary key, btree, for table "public.cwi_test" 2613 2614CREATE UNIQUE INDEX cwi_uniq2_idx ON cwi_test(b , a); 2615ALTER TABLE cwi_test DROP CONSTRAINT cwi_uniq_idx, 2616 ADD CONSTRAINT cwi_replaced_pkey PRIMARY KEY 2617 USING INDEX cwi_uniq2_idx; 2618NOTICE: ALTER TABLE / ADD CONSTRAINT USING INDEX will rename index "cwi_uniq2_idx" to "cwi_replaced_pkey" 2619\d cwi_test 2620 Table "public.cwi_test" 2621 Column | Type | Modifiers 2622--------+-----------------------+----------- 2623 a | integer | not null 2624 b | character varying(10) | not null 2625 c | character(1) | 2626Indexes: 2627 "cwi_replaced_pkey" PRIMARY KEY, btree (b, a) 2628 2629\d cwi_replaced_pkey 2630 Index "public.cwi_replaced_pkey" 2631 Column | Type | Definition 2632--------+-----------------------+------------ 2633 b | character varying(10) | b 2634 a | integer | a 2635primary key, btree, for table "public.cwi_test" 2636 2637DROP INDEX cwi_replaced_pkey; -- Should fail; a constraint depends on it 2638ERROR: cannot drop index cwi_replaced_pkey because constraint cwi_replaced_pkey on table cwi_test requires it 2639HINT: You can drop constraint cwi_replaced_pkey on table cwi_test instead. 2640DROP TABLE cwi_test; 2641-- 2642-- Check handling of indexes on system columns 2643-- 2644CREATE TABLE oid_table (a INT) WITH OIDS; 2645-- An index on the OID column should be allowed 2646CREATE INDEX ON oid_table (oid); 2647-- Other system columns cannot be indexed 2648CREATE INDEX ON oid_table (ctid); 2649ERROR: index creation on system columns is not supported 2650-- nor used in expressions 2651CREATE INDEX ON oid_table ((ctid >= '(1000,0)')); 2652ERROR: index creation on system columns is not supported 2653-- nor used in predicates 2654CREATE INDEX ON oid_table (a) WHERE ctid >= '(1000,0)'; 2655ERROR: index creation on system columns is not supported 2656DROP TABLE oid_table; 2657-- 2658-- Tests for IS NULL/IS NOT NULL with b-tree indexes 2659-- 2660SELECT unique1, unique2 INTO onek_with_null FROM onek; 2661INSERT INTO onek_with_null (unique1,unique2) VALUES (NULL, -1), (NULL, NULL); 2662CREATE UNIQUE INDEX onek_nulltest ON onek_with_null (unique2,unique1); 2663SET enable_seqscan = OFF; 2664SET enable_indexscan = ON; 2665SET enable_bitmapscan = ON; 2666SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL; 2667 count 2668------- 2669 2 2670(1 row) 2671 2672SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL AND unique2 IS NULL; 2673 count 2674------- 2675 1 2676(1 row) 2677 2678SELECT count(*) FROM onek_with_null WHERE unique1 IS NOT NULL; 2679 count 2680------- 2681 1000 2682(1 row) 2683 2684SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL AND unique2 IS NOT NULL; 2685 count 2686------- 2687 1 2688(1 row) 2689 2690SELECT count(*) FROM onek_with_null WHERE unique1 IS NOT NULL AND unique1 > 500; 2691 count 2692------- 2693 499 2694(1 row) 2695 2696SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL AND unique1 > 500; 2697 count 2698------- 2699 0 2700(1 row) 2701 2702DROP INDEX onek_nulltest; 2703CREATE UNIQUE INDEX onek_nulltest ON onek_with_null (unique2 desc,unique1); 2704SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL; 2705 count 2706------- 2707 2 2708(1 row) 2709 2710SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL AND unique2 IS NULL; 2711 count 2712------- 2713 1 2714(1 row) 2715 2716SELECT count(*) FROM onek_with_null WHERE unique1 IS NOT NULL; 2717 count 2718------- 2719 1000 2720(1 row) 2721 2722SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL AND unique2 IS NOT NULL; 2723 count 2724------- 2725 1 2726(1 row) 2727 2728SELECT count(*) FROM onek_with_null WHERE unique1 IS NOT NULL AND unique1 > 500; 2729 count 2730------- 2731 499 2732(1 row) 2733 2734SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL AND unique1 > 500; 2735 count 2736------- 2737 0 2738(1 row) 2739 2740DROP INDEX onek_nulltest; 2741CREATE UNIQUE INDEX onek_nulltest ON onek_with_null (unique2 desc nulls last,unique1); 2742SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL; 2743 count 2744------- 2745 2 2746(1 row) 2747 2748SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL AND unique2 IS NULL; 2749 count 2750------- 2751 1 2752(1 row) 2753 2754SELECT count(*) FROM onek_with_null WHERE unique1 IS NOT NULL; 2755 count 2756------- 2757 1000 2758(1 row) 2759 2760SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL AND unique2 IS NOT NULL; 2761 count 2762------- 2763 1 2764(1 row) 2765 2766SELECT count(*) FROM onek_with_null WHERE unique1 IS NOT NULL AND unique1 > 500; 2767 count 2768------- 2769 499 2770(1 row) 2771 2772SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL AND unique1 > 500; 2773 count 2774------- 2775 0 2776(1 row) 2777 2778DROP INDEX onek_nulltest; 2779CREATE UNIQUE INDEX onek_nulltest ON onek_with_null (unique2 nulls first,unique1); 2780SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL; 2781 count 2782------- 2783 2 2784(1 row) 2785 2786SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL AND unique2 IS NULL; 2787 count 2788------- 2789 1 2790(1 row) 2791 2792SELECT count(*) FROM onek_with_null WHERE unique1 IS NOT NULL; 2793 count 2794------- 2795 1000 2796(1 row) 2797 2798SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL AND unique2 IS NOT NULL; 2799 count 2800------- 2801 1 2802(1 row) 2803 2804SELECT count(*) FROM onek_with_null WHERE unique1 IS NOT NULL AND unique1 > 500; 2805 count 2806------- 2807 499 2808(1 row) 2809 2810SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL AND unique1 > 500; 2811 count 2812------- 2813 0 2814(1 row) 2815 2816DROP INDEX onek_nulltest; 2817-- Check initial-positioning logic too 2818CREATE UNIQUE INDEX onek_nulltest ON onek_with_null (unique2); 2819SET enable_seqscan = OFF; 2820SET enable_indexscan = ON; 2821SET enable_bitmapscan = OFF; 2822SELECT unique1, unique2 FROM onek_with_null 2823 ORDER BY unique2 LIMIT 2; 2824 unique1 | unique2 2825---------+--------- 2826 | -1 2827 147 | 0 2828(2 rows) 2829 2830SELECT unique1, unique2 FROM onek_with_null WHERE unique2 >= -1 2831 ORDER BY unique2 LIMIT 2; 2832 unique1 | unique2 2833---------+--------- 2834 | -1 2835 147 | 0 2836(2 rows) 2837 2838SELECT unique1, unique2 FROM onek_with_null WHERE unique2 >= 0 2839 ORDER BY unique2 LIMIT 2; 2840 unique1 | unique2 2841---------+--------- 2842 147 | 0 2843 931 | 1 2844(2 rows) 2845 2846SELECT unique1, unique2 FROM onek_with_null 2847 ORDER BY unique2 DESC LIMIT 2; 2848 unique1 | unique2 2849---------+--------- 2850 | 2851 278 | 999 2852(2 rows) 2853 2854SELECT unique1, unique2 FROM onek_with_null WHERE unique2 >= -1 2855 ORDER BY unique2 DESC LIMIT 2; 2856 unique1 | unique2 2857---------+--------- 2858 278 | 999 2859 0 | 998 2860(2 rows) 2861 2862SELECT unique1, unique2 FROM onek_with_null WHERE unique2 < 999 2863 ORDER BY unique2 DESC LIMIT 2; 2864 unique1 | unique2 2865---------+--------- 2866 0 | 998 2867 744 | 997 2868(2 rows) 2869 2870RESET enable_seqscan; 2871RESET enable_indexscan; 2872RESET enable_bitmapscan; 2873DROP TABLE onek_with_null; 2874-- 2875-- Check bitmap index path planning 2876-- 2877EXPLAIN (COSTS OFF) 2878SELECT * FROM tenk1 2879 WHERE thousand = 42 AND (tenthous = 1 OR tenthous = 3 OR tenthous = 42); 2880 QUERY PLAN 2881----------------------------------------------------------------------------------------------------------------------------------------- 2882 Bitmap Heap Scan on tenk1 2883 Recheck Cond: (((thousand = 42) AND (tenthous = 1)) OR ((thousand = 42) AND (tenthous = 3)) OR ((thousand = 42) AND (tenthous = 42))) 2884 -> BitmapOr 2885 -> Bitmap Index Scan on tenk1_thous_tenthous 2886 Index Cond: ((thousand = 42) AND (tenthous = 1)) 2887 -> Bitmap Index Scan on tenk1_thous_tenthous 2888 Index Cond: ((thousand = 42) AND (tenthous = 3)) 2889 -> Bitmap Index Scan on tenk1_thous_tenthous 2890 Index Cond: ((thousand = 42) AND (tenthous = 42)) 2891(9 rows) 2892 2893SELECT * FROM tenk1 2894 WHERE thousand = 42 AND (tenthous = 1 OR tenthous = 3 OR tenthous = 42); 2895 unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 2896---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+--------- 2897 42 | 5530 | 0 | 2 | 2 | 2 | 42 | 42 | 42 | 42 | 42 | 84 | 85 | QBAAAA | SEIAAA | OOOOxx 2898(1 row) 2899 2900EXPLAIN (COSTS OFF) 2901SELECT count(*) FROM tenk1 2902 WHERE hundred = 42 AND (thousand = 42 OR thousand = 99); 2903 QUERY PLAN 2904--------------------------------------------------------------------------------- 2905 Aggregate 2906 -> Bitmap Heap Scan on tenk1 2907 Recheck Cond: ((hundred = 42) AND ((thousand = 42) OR (thousand = 99))) 2908 -> BitmapAnd 2909 -> Bitmap Index Scan on tenk1_hundred 2910 Index Cond: (hundred = 42) 2911 -> BitmapOr 2912 -> Bitmap Index Scan on tenk1_thous_tenthous 2913 Index Cond: (thousand = 42) 2914 -> Bitmap Index Scan on tenk1_thous_tenthous 2915 Index Cond: (thousand = 99) 2916(11 rows) 2917 2918SELECT count(*) FROM tenk1 2919 WHERE hundred = 42 AND (thousand = 42 OR thousand = 99); 2920 count 2921------- 2922 10 2923(1 row) 2924 2925-- 2926-- Check behavior with duplicate index column contents 2927-- 2928CREATE TABLE dupindexcols AS 2929 SELECT unique1 as id, stringu2::text as f1 FROM tenk1; 2930CREATE INDEX dupindexcols_i ON dupindexcols (f1, id, f1 text_pattern_ops); 2931ANALYZE dupindexcols; 2932EXPLAIN (COSTS OFF) 2933 SELECT count(*) FROM dupindexcols 2934 WHERE f1 BETWEEN 'WA' AND 'ZZZ' and id < 1000 and f1 ~<~ 'YX'; 2935 QUERY PLAN 2936---------------------------------------------------------------------------------------------------------------- 2937 Aggregate 2938 -> Bitmap Heap Scan on dupindexcols 2939 Recheck Cond: ((f1 >= 'WA'::text) AND (f1 <= 'ZZZ'::text) AND (id < 1000) AND (f1 ~<~ 'YX'::text)) 2940 -> Bitmap Index Scan on dupindexcols_i 2941 Index Cond: ((f1 >= 'WA'::text) AND (f1 <= 'ZZZ'::text) AND (id < 1000) AND (f1 ~<~ 'YX'::text)) 2942(5 rows) 2943 2944SELECT count(*) FROM dupindexcols 2945 WHERE f1 BETWEEN 'WA' AND 'ZZZ' and id < 1000 and f1 ~<~ 'YX'; 2946 count 2947------- 2948 97 2949(1 row) 2950 2951-- 2952-- Check ordering of =ANY indexqual results (bug in 9.2.0) 2953-- 2954vacuum tenk1; -- ensure we get consistent plans here 2955explain (costs off) 2956SELECT unique1 FROM tenk1 2957WHERE unique1 IN (1,42,7) 2958ORDER BY unique1; 2959 QUERY PLAN 2960------------------------------------------------------- 2961 Index Only Scan using tenk1_unique1 on tenk1 2962 Index Cond: (unique1 = ANY ('{1,42,7}'::integer[])) 2963(2 rows) 2964 2965SELECT unique1 FROM tenk1 2966WHERE unique1 IN (1,42,7) 2967ORDER BY unique1; 2968 unique1 2969--------- 2970 1 2971 7 2972 42 2973(3 rows) 2974 2975explain (costs off) 2976SELECT thousand, tenthous FROM tenk1 2977WHERE thousand < 2 AND tenthous IN (1001,3000) 2978ORDER BY thousand; 2979 QUERY PLAN 2980------------------------------------------------------- 2981 Index Only Scan using tenk1_thous_tenthous on tenk1 2982 Index Cond: (thousand < 2) 2983 Filter: (tenthous = ANY ('{1001,3000}'::integer[])) 2984(3 rows) 2985 2986SELECT thousand, tenthous FROM tenk1 2987WHERE thousand < 2 AND tenthous IN (1001,3000) 2988ORDER BY thousand; 2989 thousand | tenthous 2990----------+---------- 2991 0 | 3000 2992 1 | 1001 2993(2 rows) 2994 2995SET enable_indexonlyscan = OFF; 2996explain (costs off) 2997SELECT thousand, tenthous FROM tenk1 2998WHERE thousand < 2 AND tenthous IN (1001,3000) 2999ORDER BY thousand; 3000 QUERY PLAN 3001-------------------------------------------------------------------------------------- 3002 Sort 3003 Sort Key: thousand 3004 -> Index Scan using tenk1_thous_tenthous on tenk1 3005 Index Cond: ((thousand < 2) AND (tenthous = ANY ('{1001,3000}'::integer[]))) 3006(4 rows) 3007 3008SELECT thousand, tenthous FROM tenk1 3009WHERE thousand < 2 AND tenthous IN (1001,3000) 3010ORDER BY thousand; 3011 thousand | tenthous 3012----------+---------- 3013 0 | 3000 3014 1 | 1001 3015(2 rows) 3016 3017RESET enable_indexonlyscan; 3018-- 3019-- Check elimination of constant-NULL subexpressions 3020-- 3021explain (costs off) 3022 select * from tenk1 where (thousand, tenthous) in ((1,1001), (null,null)); 3023 QUERY PLAN 3024------------------------------------------------------ 3025 Index Scan using tenk1_thous_tenthous on tenk1 3026 Index Cond: ((thousand = 1) AND (tenthous = 1001)) 3027(2 rows) 3028 3029-- 3030-- REINDEX (VERBOSE) 3031-- 3032CREATE TABLE reindex_verbose(id integer primary key); 3033\set VERBOSITY terse 3034REINDEX (VERBOSE) TABLE reindex_verbose; 3035INFO: index "reindex_verbose_pkey" was reindexed 3036DROP TABLE reindex_verbose; 3037-- 3038-- REINDEX SCHEMA 3039-- 3040REINDEX SCHEMA schema_to_reindex; -- failure, schema does not exist 3041ERROR: schema "schema_to_reindex" does not exist 3042CREATE SCHEMA schema_to_reindex; 3043SET search_path = 'schema_to_reindex'; 3044CREATE TABLE table1(col1 SERIAL PRIMARY KEY); 3045INSERT INTO table1 SELECT generate_series(1,400); 3046CREATE TABLE table2(col1 SERIAL PRIMARY KEY, col2 TEXT NOT NULL); 3047INSERT INTO table2 SELECT generate_series(1,400), 'abc'; 3048CREATE INDEX ON table2(col2); 3049CREATE MATERIALIZED VIEW matview AS SELECT col1 FROM table2; 3050CREATE INDEX ON matview(col1); 3051CREATE VIEW view AS SELECT col2 FROM table2; 3052CREATE TABLE reindex_before AS 3053SELECT oid, relname, relfilenode, relkind, reltoastrelid 3054 FROM pg_class 3055 where relnamespace = (SELECT oid FROM pg_namespace WHERE nspname = 'schema_to_reindex'); 3056INSERT INTO reindex_before 3057SELECT oid, 'pg_toast_TABLE', relfilenode, relkind, reltoastrelid 3058FROM pg_class WHERE oid IN 3059 (SELECT reltoastrelid FROM reindex_before WHERE reltoastrelid > 0); 3060INSERT INTO reindex_before 3061SELECT oid, 'pg_toast_TABLE_index', relfilenode, relkind, reltoastrelid 3062FROM pg_class where oid in 3063 (select indexrelid from pg_index where indrelid in 3064 (select reltoastrelid from reindex_before where reltoastrelid > 0)); 3065REINDEX SCHEMA schema_to_reindex; 3066CREATE TABLE reindex_after AS SELECT oid, relname, relfilenode, relkind 3067 FROM pg_class 3068 where relnamespace = (SELECT oid FROM pg_namespace WHERE nspname = 'schema_to_reindex'); 3069SELECT b.relname, 3070 b.relkind, 3071 CASE WHEN a.relfilenode = b.relfilenode THEN 'relfilenode is unchanged' 3072 ELSE 'relfilenode has changed' END 3073 FROM reindex_before b JOIN pg_class a ON b.oid = a.oid 3074 ORDER BY 1; 3075 relname | relkind | case 3076----------------------+---------+-------------------------- 3077 matview | m | relfilenode is unchanged 3078 matview_col1_idx | i | relfilenode has changed 3079 pg_toast_TABLE | t | relfilenode is unchanged 3080 pg_toast_TABLE_index | i | relfilenode has changed 3081 table1 | r | relfilenode is unchanged 3082 table1_col1_seq | S | relfilenode is unchanged 3083 table1_pkey | i | relfilenode has changed 3084 table2 | r | relfilenode is unchanged 3085 table2_col1_seq | S | relfilenode is unchanged 3086 table2_col2_idx | i | relfilenode has changed 3087 table2_pkey | i | relfilenode has changed 3088 view | v | relfilenode is unchanged 3089(12 rows) 3090 3091REINDEX SCHEMA schema_to_reindex; 3092BEGIN; 3093REINDEX SCHEMA schema_to_reindex; -- failure, cannot run in a transaction 3094ERROR: REINDEX SCHEMA cannot run inside a transaction block 3095END; 3096-- Failure for unauthorized user 3097CREATE ROLE regress_reindexuser NOLOGIN; 3098SET SESSION ROLE regress_reindexuser; 3099REINDEX SCHEMA schema_to_reindex; 3100ERROR: must be owner of schema schema_to_reindex 3101-- Clean up 3102RESET ROLE; 3103DROP ROLE regress_reindexuser; 3104SET client_min_messages TO 'warning'; 3105DROP SCHEMA schema_to_reindex CASCADE; 3106RESET client_min_messages; 3107