granicus.if.org Git - postgresql/blob - src/test/regress/expected/create_index.out

   1 --
   2 -- CREATE_INDEX
   3 -- Create ancillary data structures (i.e. indices)
   4 --
   5 --
   6 -- BTREE
   7 --
   8 CREATE INDEX onek_unique1 ON onek USING btree(unique1 int4_ops);
   9 CREATE INDEX IF NOT EXISTS onek_unique1 ON onek USING btree(unique1 int4_ops);
  10 NOTICE:  relation "onek_unique1" already exists, skipping
  11 CREATE INDEX IF NOT EXISTS ON onek USING btree(unique1 int4_ops);
  12 ERROR:  syntax error at or near "ON"
  13 LINE 1: CREATE INDEX IF NOT EXISTS ON onek USING btree(unique1 int4_...
  14                                    ^
  15 CREATE INDEX onek_unique2 ON onek USING btree(unique2 int4_ops);
  16 CREATE INDEX onek_hundred ON onek USING btree(hundred int4_ops);
  17 CREATE INDEX onek_stringu1 ON onek USING btree(stringu1 name_ops);
  18 CREATE INDEX tenk1_unique1 ON tenk1 USING btree(unique1 int4_ops);
  19 CREATE INDEX tenk1_unique2 ON tenk1 USING btree(unique2 int4_ops);
  20 CREATE INDEX tenk1_hundred ON tenk1 USING btree(hundred int4_ops);
  21 CREATE INDEX tenk1_thous_tenthous ON tenk1 (thousand, tenthous);
  22 CREATE INDEX tenk2_unique1 ON tenk2 USING btree(unique1 int4_ops);
  23 CREATE INDEX tenk2_unique2 ON tenk2 USING btree(unique2 int4_ops);
  24 CREATE INDEX tenk2_hundred ON tenk2 USING btree(hundred int4_ops);
  25 CREATE INDEX rix ON road USING btree (name text_ops);
  26 CREATE INDEX iix ON ihighway USING btree (name text_ops);
  27 CREATE INDEX six ON shighway USING btree (name text_ops);
  28 -- test comments
  29 COMMENT ON INDEX six_wrong IS 'bad index';
  30 ERROR:  relation "six_wrong" does not exist
  31 COMMENT ON INDEX six IS 'good index';
  32 COMMENT 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 --
  41 CREATE INDEX bt_i4_index ON bt_i4_heap USING btree (seqno int4_ops);
  42 CREATE INDEX bt_name_index ON bt_name_heap USING btree (seqno name_ops);
  43 CREATE INDEX bt_txt_index ON bt_txt_heap USING btree (seqno text_ops);
  44 CREATE INDEX bt_f8_index ON bt_f8_heap USING btree (seqno float8_ops);
  45 --
  46 -- BTREE partial indices
  47 --
  48 CREATE INDEX onek2_u1_prtl ON onek2 USING btree(unique1 int4_ops)
  49         where unique1 < 20 or unique1 > 980;
  50 CREATE INDEX onek2_u2_prtl ON onek2 USING btree(unique2 int4_ops)
  51         where stringu1 < 'B';
  52 CREATE 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 --
  57 CREATE INDEX grect2ind ON fast_emp4000 USING gist (home_base);
  58 CREATE INDEX gpolygonind ON polygon_tbl USING gist (f1);
  59 CREATE INDEX gcircleind ON circle_tbl USING gist (f1);
  60 INSERT INTO POINT_TBL(f1) VALUES (NULL);
  61 CREATE INDEX gpointind ON point_tbl USING gist (f1);
  62 CREATE TEMP TABLE gpolygon_tbl AS
  63     SELECT polygon(home_base) AS f1 FROM slow_emp4000;
  64 INSERT INTO gpolygon_tbl VALUES ( '(1000,0,0,1000)' );
  65 INSERT INTO gpolygon_tbl VALUES ( '(0,1000,1000,1000)' );
  66 CREATE TEMP TABLE gcircle_tbl AS
  67     SELECT circle(home_base) AS f1 FROM slow_emp4000;
  68 CREATE INDEX ggpolygonind ON gpolygon_tbl USING gist (f1);
  69 CREATE INDEX ggcircleind ON gcircle_tbl USING gist (f1);
  70 --
  71 -- SP-GiST
  72 --
  73 CREATE TABLE quad_point_tbl AS
  74     SELECT point(unique1,unique2) AS p FROM tenk1;
  75 INSERT INTO quad_point_tbl
  76     SELECT '(333.0,400.0)'::point FROM generate_series(1,1000);
  77 INSERT INTO quad_point_tbl VALUES (NULL), (NULL), (NULL);
  78 CREATE INDEX sp_quad_ind ON quad_point_tbl USING spgist (p);
  79 CREATE TABLE kd_point_tbl AS SELECT * FROM quad_point_tbl;
  80 CREATE INDEX sp_kd_ind ON kd_point_tbl USING spgist (p kd_point_ops);
  81 CREATE TABLE radix_text_tbl AS
  82     SELECT name AS t FROM road WHERE name !~ '^[0-9]';
  83 INSERT INTO radix_text_tbl
  84     SELECT 'P0123456789abcdef' FROM generate_series(1,1000);
  85 INSERT INTO radix_text_tbl VALUES ('P0123456789abcde');
  86 INSERT INTO radix_text_tbl VALUES ('P0123456789abcdefF');
  87 CREATE 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
  92 SET enable_seqscan = ON;
  93 SET enable_indexscan = OFF;
  94 SET enable_bitmapscan = OFF;
  95 SELECT * 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
 104 SELECT count(*) FROM fast_emp4000 WHERE home_base && '(1000,1000,0,0)'::box;
 105  count
 106 -------
 107      2
 108 (1 row)
 109
 110 SELECT count(*) FROM fast_emp4000 WHERE home_base IS NULL;
 111  count
 112 -------
 113    278
 114 (1 row)
 115
 116 SELECT * 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
 123 SELECT * 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
 133 SELECT count(*) FROM gpolygon_tbl WHERE f1 && '(1000,1000,0,0)'::polygon;
 134  count
 135 -------
 136      2
 137 (1 row)
 138
 139 SELECT count(*) FROM gcircle_tbl WHERE f1 && '<(500,500),500>'::circle;
 140  count
 141 -------
 142      2
 143 (1 row)
 144
 145 SELECT count(*) FROM point_tbl WHERE f1 <@ box '(0,0,100,100)';
 146  count
 147 -------
 148      3
 149 (1 row)
 150
 151 SELECT count(*) FROM point_tbl WHERE box '(0,0,100,100)' @> f1;
 152  count
 153 -------
 154      3
 155 (1 row)
 156
 157 SELECT 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
 163 SELECT count(*) FROM point_tbl WHERE f1 <@ circle '<(50,50),50>';
 164  count
 165 -------
 166      1
 167 (1 row)
 168
 169 SELECT count(*) FROM point_tbl p WHERE p.f1 << '(0.0, 0.0)';
 170  count
 171 -------
 172      3
 173 (1 row)
 174
 175 SELECT count(*) FROM point_tbl p WHERE p.f1 >> '(0.0, 0.0)';
 176  count
 177 -------
 178      2
 179 (1 row)
 180
 181 SELECT count(*) FROM point_tbl p WHERE p.f1 <^ '(0.0, 0.0)';
 182  count
 183 -------
 184      1
 185 (1 row)
 186
 187 SELECT count(*) FROM point_tbl p WHERE p.f1 >^ '(0.0, 0.0)';
 188  count
 189 -------
 190      3
 191 (1 row)
 192
 193 SELECT count(*) FROM point_tbl p WHERE p.f1 ~= '(-5, -12)';
 194  count
 195 -------
 196      1
 197 (1 row)
 198
 199 SELECT * 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
 211 SELECT * FROM point_tbl WHERE f1 IS NULL;
 212  f1
 213 ----
 214
 215 (1 row)
 216
 217 SELECT * 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
 228 SELECT * 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
 237 SELECT count(*) FROM quad_point_tbl WHERE p IS NULL;
 238  count
 239 -------
 240      3
 241 (1 row)
 242
 243 SELECT count(*) FROM quad_point_tbl WHERE p IS NOT NULL;
 244  count
 245 -------
 246  11000
 247 (1 row)
 248
 249 SELECT count(*) FROM quad_point_tbl;
 250  count
 251 -------
 252  11003
 253 (1 row)
 254
 255 SELECT count(*) FROM quad_point_tbl WHERE p <@ box '(200,200,1000,1000)';
 256  count
 257 -------
 258   1057
 259 (1 row)
 260
 261 SELECT count(*) FROM quad_point_tbl WHERE box '(200,200,1000,1000)' @> p;
 262  count
 263 -------
 264   1057
 265 (1 row)
 266
 267 SELECT count(*) FROM quad_point_tbl WHERE p << '(5000, 4000)';
 268  count
 269 -------
 270   6000
 271 (1 row)
 272
 273 SELECT count(*) FROM quad_point_tbl WHERE p >> '(5000, 4000)';
 274  count
 275 -------
 276   4999
 277 (1 row)
 278
 279 SELECT count(*) FROM quad_point_tbl WHERE p <^ '(5000, 4000)';
 280  count
 281 -------
 282   5000
 283 (1 row)
 284
 285 SELECT count(*) FROM quad_point_tbl WHERE p >^ '(5000, 4000)';
 286  count
 287 -------
 288   5999
 289 (1 row)
 290
 291 SELECT count(*) FROM quad_point_tbl WHERE p ~= '(4585, 365)';
 292  count
 293 -------
 294      1
 295 (1 row)
 296
 297 SELECT count(*) FROM radix_text_tbl WHERE t = 'P0123456789abcdef';
 298  count
 299 -------
 300   1000
 301 (1 row)
 302
 303 SELECT count(*) FROM radix_text_tbl WHERE t = 'P0123456789abcde';
 304  count
 305 -------
 306      1
 307 (1 row)
 308
 309 SELECT count(*) FROM radix_text_tbl WHERE t = 'P0123456789abcdefF';
 310  count
 311 -------
 312      1
 313 (1 row)
 314
 315 SELECT count(*) FROM radix_text_tbl WHERE t <    'Aztec                         Ct  ';
 316  count
 317 -------
 318    272
 319 (1 row)
 320
 321 SELECT count(*) FROM radix_text_tbl WHERE t ~<~  'Aztec                         Ct  ';
 322  count
 323 -------
 324    272
 325 (1 row)
 326
 327 SELECT count(*) FROM radix_text_tbl WHERE t <=   'Aztec                         Ct  ';
 328  count
 329 -------
 330    273
 331 (1 row)
 332
 333 SELECT count(*) FROM radix_text_tbl WHERE t ~<=~ 'Aztec                         Ct  ';
 334  count
 335 -------
 336    273
 337 (1 row)
 338
 339 SELECT count(*) FROM radix_text_tbl WHERE t =    'Aztec                         Ct  ';
 340  count
 341 -------
 342      1
 343 (1 row)
 344
 345 SELECT count(*) FROM radix_text_tbl WHERE t =    'Worth                         St  ';
 346  count
 347 -------
 348      2
 349 (1 row)
 350
 351 SELECT count(*) FROM radix_text_tbl WHERE t >=   'Worth                         St  ';
 352  count
 353 -------
 354     50
 355 (1 row)
 356
 357 SELECT count(*) FROM radix_text_tbl WHERE t ~>=~ 'Worth                         St  ';
 358  count
 359 -------
 360     50
 361 (1 row)
 362
 363 SELECT count(*) FROM radix_text_tbl WHERE t >    'Worth                         St  ';
 364  count
 365 -------
 366     48
 367 (1 row)
 368
 369 SELECT count(*) FROM radix_text_tbl WHERE t ~>~  'Worth                         St  ';
 370  count
 371 -------
 372     48
 373 (1 row)
 374
 375 SELECT * 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
 390 SELECT 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
 406 SET enable_seqscan = OFF;
 407 SET enable_indexscan = ON;
 408 SET enable_bitmapscan = OFF;
 409 EXPLAIN (COSTS OFF)
 410 SELECT * 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
 421 SELECT * 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
 430 EXPLAIN (COSTS OFF)
 431 SELECT 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
 439 SELECT count(*) FROM fast_emp4000 WHERE home_base && '(1000,1000,0,0)'::box;
 440  count
 441 -------
 442      2
 443 (1 row)
 444
 445 EXPLAIN (COSTS OFF)
 446 SELECT 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
 454 SELECT count(*) FROM fast_emp4000 WHERE home_base IS NULL;
 455  count
 456 -------
 457    278
 458 (1 row)
 459
 460 EXPLAIN (COSTS OFF)
 461 SELECT * 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
 471 SELECT * 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
 478 EXPLAIN (COSTS OFF)
 479 SELECT * 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
 489 SELECT * 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
 499 EXPLAIN (COSTS OFF)
 500 SELECT 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
 508 SELECT count(*) FROM gpolygon_tbl WHERE f1 && '(1000,1000,0,0)'::polygon;
 509  count
 510 -------
 511      2
 512 (1 row)
 513
 514 EXPLAIN (COSTS OFF)
 515 SELECT 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
 523 SELECT count(*) FROM gcircle_tbl WHERE f1 && '<(500,500),500>'::circle;
 524  count
 525 -------
 526      2
 527 (1 row)
 528
 529 EXPLAIN (COSTS OFF)
 530 SELECT 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
 538 SELECT count(*) FROM point_tbl WHERE f1 <@ box '(0,0,100,100)';
 539  count
 540 -------
 541      3
 542 (1 row)
 543
 544 EXPLAIN (COSTS OFF)
 545 SELECT 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
 553 SELECT count(*) FROM point_tbl WHERE box '(0,0,100,100)' @> f1;
 554  count
 555 -------
 556      3
 557 (1 row)
 558
 559 EXPLAIN (COSTS OFF)
 560 SELECT 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
 568 SELECT 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
 574 EXPLAIN (COSTS OFF)
 575 SELECT 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
 583 SELECT count(*) FROM point_tbl WHERE f1 <@ circle '<(50,50),50>';
 584  count
 585 -------
 586      1
 587 (1 row)
 588
 589 EXPLAIN (COSTS OFF)
 590 SELECT 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
 598 SELECT count(*) FROM point_tbl p WHERE p.f1 << '(0.0, 0.0)';
 599  count
 600 -------
 601      3
 602 (1 row)
 603
 604 EXPLAIN (COSTS OFF)
 605 SELECT 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
 613 SELECT count(*) FROM point_tbl p WHERE p.f1 >> '(0.0, 0.0)';
 614  count
 615 -------
 616      2
 617 (1 row)
 618
 619 EXPLAIN (COSTS OFF)
 620 SELECT 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
 628 SELECT count(*) FROM point_tbl p WHERE p.f1 <^ '(0.0, 0.0)';
 629  count
 630 -------
 631      1
 632 (1 row)
 633
 634 EXPLAIN (COSTS OFF)
 635 SELECT 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
 643 SELECT count(*) FROM point_tbl p WHERE p.f1 >^ '(0.0, 0.0)';
 644  count
 645 -------
 646      3
 647 (1 row)
 648
 649 EXPLAIN (COSTS OFF)
 650 SELECT 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
 658 SELECT count(*) FROM point_tbl p WHERE p.f1 ~= '(-5, -12)';
 659  count
 660 -------
 661      1
 662 (1 row)
 663
 664 EXPLAIN (COSTS OFF)
 665 SELECT * 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
 672 SELECT * 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
 684 EXPLAIN (COSTS OFF)
 685 SELECT * 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
 692 SELECT * FROM point_tbl WHERE f1 IS NULL;
 693  f1
 694 ----
 695
 696 (1 row)
 697
 698 EXPLAIN (COSTS OFF)
 699 SELECT * 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
 707 SELECT * 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
 718 EXPLAIN (COSTS OFF)
 719 SELECT * 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
 727 SELECT * 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
 736 EXPLAIN (COSTS OFF)
 737 SELECT 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
 745 SELECT count(*) FROM quad_point_tbl WHERE p IS NULL;
 746  count
 747 -------
 748      3
 749 (1 row)
 750
 751 EXPLAIN (COSTS OFF)
 752 SELECT 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
 760 SELECT count(*) FROM quad_point_tbl WHERE p IS NOT NULL;
 761  count
 762 -------
 763  11000
 764 (1 row)
 765
 766 EXPLAIN (COSTS OFF)
 767 SELECT 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
 774 SELECT count(*) FROM quad_point_tbl;
 775  count
 776 -------
 777  11003
 778 (1 row)
 779
 780 EXPLAIN (COSTS OFF)
 781 SELECT 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
 789 SELECT count(*) FROM quad_point_tbl WHERE p <@ box '(200,200,1000,1000)';
 790  count
 791 -------
 792   1057
 793 (1 row)
 794
 795 EXPLAIN (COSTS OFF)
 796 SELECT 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
 804 SELECT count(*) FROM quad_point_tbl WHERE box '(200,200,1000,1000)' @> p;
 805  count
 806 -------
 807   1057
 808 (1 row)
 809
 810 EXPLAIN (COSTS OFF)
 811 SELECT 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
 819 SELECT count(*) FROM quad_point_tbl WHERE p << '(5000, 4000)';
 820  count
 821 -------
 822   6000
 823 (1 row)
 824
 825 EXPLAIN (COSTS OFF)
 826 SELECT 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
 834 SELECT count(*) FROM quad_point_tbl WHERE p >> '(5000, 4000)';
 835  count
 836 -------
 837   4999
 838 (1 row)
 839
 840 EXPLAIN (COSTS OFF)
 841 SELECT 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
 849 SELECT count(*) FROM quad_point_tbl WHERE p <^ '(5000, 4000)';
 850  count
 851 -------
 852   5000
 853 (1 row)
 854
 855 EXPLAIN (COSTS OFF)
 856 SELECT 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
 864 SELECT count(*) FROM quad_point_tbl WHERE p >^ '(5000, 4000)';
 865  count
 866 -------
 867   5999
 868 (1 row)
 869
 870 EXPLAIN (COSTS OFF)
 871 SELECT 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
 879 SELECT count(*) FROM quad_point_tbl WHERE p ~= '(4585, 365)';
 880  count
 881 -------
 882      1
 883 (1 row)
 884
 885 EXPLAIN (COSTS OFF)
 886 SELECT 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
 894 SELECT count(*) FROM kd_point_tbl WHERE p <@ box '(200,200,1000,1000)';
 895  count
 896 -------
 897   1057
 898 (1 row)
 899
 900 EXPLAIN (COSTS OFF)
 901 SELECT 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
 909 SELECT count(*) FROM kd_point_tbl WHERE box '(200,200,1000,1000)' @> p;
 910  count
 911 -------
 912   1057
 913 (1 row)
 914
 915 EXPLAIN (COSTS OFF)
 916 SELECT 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
 924 SELECT count(*) FROM kd_point_tbl WHERE p << '(5000, 4000)';
 925  count
 926 -------
 927   6000
 928 (1 row)
 929
 930 EXPLAIN (COSTS OFF)
 931 SELECT 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
 939 SELECT count(*) FROM kd_point_tbl WHERE p >> '(5000, 4000)';
 940  count
 941 -------
 942   4999
 943 (1 row)
 944
 945 EXPLAIN (COSTS OFF)
 946 SELECT 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
 954 SELECT count(*) FROM kd_point_tbl WHERE p <^ '(5000, 4000)';
 955  count
 956 -------
 957   5000
 958 (1 row)
 959
 960 EXPLAIN (COSTS OFF)
 961 SELECT 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
 969 SELECT count(*) FROM kd_point_tbl WHERE p >^ '(5000, 4000)';
 970  count
 971 -------
 972   5999
 973 (1 row)
 974
 975 EXPLAIN (COSTS OFF)
 976 SELECT 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
 984 SELECT count(*) FROM kd_point_tbl WHERE p ~= '(4585, 365)';
 985  count
 986 -------
 987      1
 988 (1 row)
 989
 990 EXPLAIN (COSTS OFF)
 991 SELECT 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
 999 SELECT count(*) FROM radix_text_tbl WHERE t = 'P0123456789abcdef';
1000  count
1001 -------
1002   1000
1003 (1 row)
1004
1005 EXPLAIN (COSTS OFF)
1006 SELECT 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
1014 SELECT count(*) FROM radix_text_tbl WHERE t = 'P0123456789abcde';
1015  count
1016 -------
1017      1
1018 (1 row)
1019
1020 EXPLAIN (COSTS OFF)
1021 SELECT 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
1029 SELECT count(*) FROM radix_text_tbl WHERE t = 'P0123456789abcdefF';
1030  count
1031 -------
1032      1
1033 (1 row)
1034
1035 EXPLAIN (COSTS OFF)
1036 SELECT 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
1044 SELECT count(*) FROM radix_text_tbl WHERE t <    'Aztec                         Ct  ';
1045  count
1046 -------
1047    272
1048 (1 row)
1049
1050 EXPLAIN (COSTS OFF)
1051 SELECT 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
1059 SELECT count(*) FROM radix_text_tbl WHERE t ~<~  'Aztec                         Ct  ';
1060  count
1061 -------
1062    272
1063 (1 row)
1064
1065 EXPLAIN (COSTS OFF)
1066 SELECT 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
1074 SELECT count(*) FROM radix_text_tbl WHERE t <=   'Aztec                         Ct  ';
1075  count
1076 -------
1077    273
1078 (1 row)
1079
1080 EXPLAIN (COSTS OFF)
1081 SELECT 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
1089 SELECT count(*) FROM radix_text_tbl WHERE t ~<=~ 'Aztec                         Ct  ';
1090  count
1091 -------
1092    273
1093 (1 row)
1094
1095 EXPLAIN (COSTS OFF)
1096 SELECT 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
1104 SELECT count(*) FROM radix_text_tbl WHERE t =    'Aztec                         Ct  ';
1105  count
1106 -------
1107      1
1108 (1 row)
1109
1110 EXPLAIN (COSTS OFF)
1111 SELECT 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
1119 SELECT count(*) FROM radix_text_tbl WHERE t =    'Worth                         St  ';
1120  count
1121 -------
1122      2
1123 (1 row)
1124
1125 EXPLAIN (COSTS OFF)
1126 SELECT 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
1134 SELECT count(*) FROM radix_text_tbl WHERE t >=   'Worth                         St  ';
1135  count
1136 -------
1137     50
1138 (1 row)
1139
1140 EXPLAIN (COSTS OFF)
1141 SELECT 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
1149 SELECT count(*) FROM radix_text_tbl WHERE t ~>=~ 'Worth                         St  ';
1150  count
1151 -------
1152     50
1153 (1 row)
1154
1155 EXPLAIN (COSTS OFF)
1156 SELECT 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
1164 SELECT count(*) FROM radix_text_tbl WHERE t >    'Worth                         St  ';
1165  count
1166 -------
1167     48
1168 (1 row)
1169
1170 EXPLAIN (COSTS OFF)
1171 SELECT 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
1179 SELECT count(*) FROM radix_text_tbl WHERE t ~>~  'Worth                         St  ';
1180  count
1181 -------
1182     48
1183 (1 row)
1184
1185 EXPLAIN (COSTS OFF)
1186 SELECT * 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
1194 SELECT * 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
1209 EXPLAIN (COSTS OFF)
1210 SELECT 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
1218 SELECT 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
1234 SET enable_seqscan = OFF;
1235 SET enable_indexscan = OFF;
1236 SET enable_bitmapscan = ON;
1237 EXPLAIN (COSTS OFF)
1238 SELECT * 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
1249 SELECT * 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
1258 EXPLAIN (COSTS OFF)
1259 SELECT 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
1269 SELECT count(*) FROM quad_point_tbl WHERE p IS NULL;
1270  count
1271 -------
1272      3
1273 (1 row)
1274
1275 EXPLAIN (COSTS OFF)
1276 SELECT 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
1286 SELECT count(*) FROM quad_point_tbl WHERE p IS NOT NULL;
1287  count
1288 -------
1289  11000
1290 (1 row)
1291
1292 EXPLAIN (COSTS OFF)
1293 SELECT 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
1301 SELECT count(*) FROM quad_point_tbl;
1302  count
1303 -------
1304  11003
1305 (1 row)
1306
1307 EXPLAIN (COSTS OFF)
1308 SELECT 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
1318 SELECT count(*) FROM quad_point_tbl WHERE p <@ box '(200,200,1000,1000)';
1319  count
1320 -------
1321   1057
1322 (1 row)
1323
1324 EXPLAIN (COSTS OFF)
1325 SELECT 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
1335 SELECT count(*) FROM quad_point_tbl WHERE box '(200,200,1000,1000)' @> p;
1336  count
1337 -------
1338   1057
1339 (1 row)
1340
1341 EXPLAIN (COSTS OFF)
1342 SELECT 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
1352 SELECT count(*) FROM quad_point_tbl WHERE p << '(5000, 4000)';
1353  count
1354 -------
1355   6000
1356 (1 row)
1357
1358 EXPLAIN (COSTS OFF)
1359 SELECT 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
1369 SELECT count(*) FROM quad_point_tbl WHERE p >> '(5000, 4000)';
1370  count
1371 -------
1372   4999
1373 (1 row)
1374
1375 EXPLAIN (COSTS OFF)
1376 SELECT 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
1386 SELECT count(*) FROM quad_point_tbl WHERE p <^ '(5000, 4000)';
1387  count
1388 -------
1389   5000
1390 (1 row)
1391
1392 EXPLAIN (COSTS OFF)
1393 SELECT 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
1403 SELECT count(*) FROM quad_point_tbl WHERE p >^ '(5000, 4000)';
1404  count
1405 -------
1406   5999
1407 (1 row)
1408
1409 EXPLAIN (COSTS OFF)
1410 SELECT 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
1420 SELECT count(*) FROM quad_point_tbl WHERE p ~= '(4585, 365)';
1421  count
1422 -------
1423      1
1424 (1 row)
1425
1426 EXPLAIN (COSTS OFF)
1427 SELECT 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
1437 SELECT count(*) FROM kd_point_tbl WHERE p <@ box '(200,200,1000,1000)';
1438  count
1439 -------
1440   1057
1441 (1 row)
1442
1443 EXPLAIN (COSTS OFF)
1444 SELECT 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
1454 SELECT count(*) FROM kd_point_tbl WHERE box '(200,200,1000,1000)' @> p;
1455  count
1456 -------
1457   1057
1458 (1 row)
1459
1460 EXPLAIN (COSTS OFF)
1461 SELECT 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
1471 SELECT count(*) FROM kd_point_tbl WHERE p << '(5000, 4000)';
1472  count
1473 -------
1474   6000
1475 (1 row)
1476
1477 EXPLAIN (COSTS OFF)
1478 SELECT 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
1488 SELECT count(*) FROM kd_point_tbl WHERE p >> '(5000, 4000)';
1489  count
1490 -------
1491   4999
1492 (1 row)
1493
1494 EXPLAIN (COSTS OFF)
1495 SELECT 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
1505 SELECT count(*) FROM kd_point_tbl WHERE p <^ '(5000, 4000)';
1506  count
1507 -------
1508   5000
1509 (1 row)
1510
1511 EXPLAIN (COSTS OFF)
1512 SELECT 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
1522 SELECT count(*) FROM kd_point_tbl WHERE p >^ '(5000, 4000)';
1523  count
1524 -------
1525   5999
1526 (1 row)
1527
1528 EXPLAIN (COSTS OFF)
1529 SELECT 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
1539 SELECT count(*) FROM kd_point_tbl WHERE p ~= '(4585, 365)';
1540  count
1541 -------
1542      1
1543 (1 row)
1544
1545 EXPLAIN (COSTS OFF)
1546 SELECT 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
1556 SELECT count(*) FROM radix_text_tbl WHERE t = 'P0123456789abcdef';
1557  count
1558 -------
1559   1000
1560 (1 row)
1561
1562 EXPLAIN (COSTS OFF)
1563 SELECT 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
1573 SELECT count(*) FROM radix_text_tbl WHERE t = 'P0123456789abcde';
1574  count
1575 -------
1576      1
1577 (1 row)
1578
1579 EXPLAIN (COSTS OFF)
1580 SELECT 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
1590 SELECT count(*) FROM radix_text_tbl WHERE t = 'P0123456789abcdefF';
1591  count
1592 -------
1593      1
1594 (1 row)
1595
1596 EXPLAIN (COSTS OFF)
1597 SELECT 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
1607 SELECT count(*) FROM radix_text_tbl WHERE t <    'Aztec                         Ct  ';
1608  count
1609 -------
1610    272
1611 (1 row)
1612
1613 EXPLAIN (COSTS OFF)
1614 SELECT 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
1624 SELECT count(*) FROM radix_text_tbl WHERE t ~<~  'Aztec                         Ct  ';
1625  count
1626 -------
1627    272
1628 (1 row)
1629
1630 EXPLAIN (COSTS OFF)
1631 SELECT 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
1641 SELECT count(*) FROM radix_text_tbl WHERE t <=   'Aztec                         Ct  ';
1642  count
1643 -------
1644    273
1645 (1 row)
1646
1647 EXPLAIN (COSTS OFF)
1648 SELECT 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
1658 SELECT count(*) FROM radix_text_tbl WHERE t ~<=~ 'Aztec                         Ct  ';
1659  count
1660 -------
1661    273
1662 (1 row)
1663
1664 EXPLAIN (COSTS OFF)
1665 SELECT 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
1675 SELECT count(*) FROM radix_text_tbl WHERE t =    'Aztec                         Ct  ';
1676  count
1677 -------
1678      1
1679 (1 row)
1680
1681 EXPLAIN (COSTS OFF)
1682 SELECT 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
1692 SELECT count(*) FROM radix_text_tbl WHERE t =    'Worth                         St  ';
1693  count
1694 -------
1695      2
1696 (1 row)
1697
1698 EXPLAIN (COSTS OFF)
1699 SELECT 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
1709 SELECT count(*) FROM radix_text_tbl WHERE t >=   'Worth                         St  ';
1710  count
1711 -------
1712     50
1713 (1 row)
1714
1715 EXPLAIN (COSTS OFF)
1716 SELECT 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
1726 SELECT count(*) FROM radix_text_tbl WHERE t ~>=~ 'Worth                         St  ';
1727  count
1728 -------
1729     50
1730 (1 row)
1731
1732 EXPLAIN (COSTS OFF)
1733 SELECT 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
1743 SELECT count(*) FROM radix_text_tbl WHERE t >    'Worth                         St  ';
1744  count
1745 -------
1746     48
1747 (1 row)
1748
1749 EXPLAIN (COSTS OFF)
1750 SELECT 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
1760 SELECT count(*) FROM radix_text_tbl WHERE t ~>~  'Worth                         St  ';
1761  count
1762 -------
1763     48
1764 (1 row)
1765
1766 RESET enable_seqscan;
1767 RESET enable_indexscan;
1768 RESET enable_bitmapscan;
1769 --
1770 -- GIN over int[] and text[]
1771 --
1772 -- Note: GIN currently supports only bitmap scans, not plain indexscans
1773 --
1774 SET enable_seqscan = OFF;
1775 SET enable_indexscan = OFF;
1776 SET enable_bitmapscan = ON;
1777 CREATE INDEX intarrayidx ON array_index_op_test USING gin (i);
1778 explain (costs off)
1779 SELECT * 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
1790 SELECT * 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
1801 SELECT * 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
1812 SELECT * 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
1825 SELECT * 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
1838 SELECT * 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
1846 SELECT * 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
1862 SELECT * 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
1871 SELECT * 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
1877 SELECT * FROM array_index_op_test WHERE i = '{}' ORDER BY seqno;
1878  seqno | i  | t
1879 -------+----+----
1880    101 | {} | {}
1881 (1 row)
1882
1883 SELECT * 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
1990 SELECT * FROM array_index_op_test WHERE i && '{}' ORDER BY seqno;
1991  seqno | i | t
1992 -------+---+---
1993 (0 rows)
1994
1995 SELECT * FROM array_index_op_test WHERE i <@ '{}' ORDER BY seqno;
1996  seqno | i  | t
1997 -------+----+----
1998    101 | {} | {}
1999 (1 row)
2000
2001 SELECT * FROM array_op_test WHERE i = '{NULL}' ORDER BY seqno;
2002  seqno |   i    |   t
2003 -------+--------+--------
2004    102 | {NULL} | {NULL}
2005 (1 row)
2006
2007 SELECT * FROM array_op_test WHERE i @> '{NULL}' ORDER BY seqno;
2008  seqno | i | t
2009 -------+---+---
2010 (0 rows)
2011
2012 SELECT * FROM array_op_test WHERE i && '{NULL}' ORDER BY seqno;
2013  seqno | i | t
2014 -------+---+---
2015 (0 rows)
2016
2017 SELECT * FROM array_op_test WHERE i <@ '{NULL}' ORDER BY seqno;
2018  seqno | i  | t
2019 -------+----+----
2020    101 | {} | {}
2021 (1 row)
2022
2023 CREATE INDEX textarrayidx ON array_index_op_test USING gin (t);
2024 explain (costs off)
2025 SELECT * 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
2036 SELECT * 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
2045 SELECT * 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
2054 SELECT * 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
2062 SELECT * 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
2070 SELECT * 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
2076 SELECT * 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
2087 SELECT * 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
2095 SELECT * 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
2101 SELECT * FROM array_index_op_test WHERE t = '{}' ORDER BY seqno;
2102  seqno | i  | t
2103 -------+----+----
2104    101 | {} | {}
2105 (1 row)
2106
2107 SELECT * 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
2214 SELECT * FROM array_index_op_test WHERE t && '{}' ORDER BY seqno;
2215  seqno | i | t
2216 -------+---+---
2217 (0 rows)
2218
2219 SELECT * 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
2226 DROP INDEX intarrayidx, textarrayidx;
2227 CREATE INDEX botharrayidx ON array_index_op_test USING gin (i, t);
2228 SELECT * 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
2239 SELECT * 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
2250 SELECT * 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
2262 SELECT * 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
2274 SELECT * 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
2280 SELECT * 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
2286 SELECT * FROM array_index_op_test WHERE t = '{}' ORDER BY seqno;
2287  seqno | i  | t
2288 -------+----+----
2289    101 | {} | {}
2290 (1 row)
2291
2292 SELECT * FROM array_op_test WHERE i = '{NULL}' ORDER BY seqno;
2293  seqno |   i    |   t
2294 -------+--------+--------
2295    102 | {NULL} | {NULL}
2296 (1 row)
2297
2298 SELECT * FROM array_op_test WHERE i <@ '{NULL}' ORDER BY seqno;
2299  seqno | i  | t
2300 -------+----+----
2301    101 | {} | {}
2302 (1 row)
2303
2304 RESET enable_seqscan;
2305 RESET enable_indexscan;
2306 RESET 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 --
2311 CREATE TABLE array_gin_test (a int[]);
2312 INSERT INTO array_gin_test SELECT ARRAY[1, g%5, g] FROM generate_series(1, 10000) g;
2313 CREATE INDEX array_gin_test_idx ON array_gin_test USING gin (a);
2314 SELECT COUNT(*) FROM array_gin_test WHERE a @> '{2}';
2315  count
2316 -------
2317   2000
2318 (1 row)
2319
2320 DROP TABLE array_gin_test;
2321 --
2322 -- Test GIN index's reloptions
2323 --
2324 CREATE 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
2331 gin, for table "public.array_index_op_test"
2332 Options: fastupdate=on, gin_pending_list_limit=128
2333
2334 --
2335 -- HASH
2336 --
2337 CREATE INDEX hash_i4_index ON hash_i4_heap USING hash (random int4_ops);
2338 WARNING:  hash indexes are not WAL-logged and their use is discouraged
2339 CREATE INDEX hash_name_index ON hash_name_heap USING hash (random name_ops);
2340 WARNING:  hash indexes are not WAL-logged and their use is discouraged
2341 CREATE INDEX hash_txt_index ON hash_txt_heap USING hash (random text_ops);
2342 WARNING:  hash indexes are not WAL-logged and their use is discouraged
2343 CREATE INDEX hash_f8_index ON hash_f8_heap USING hash (random float8_ops);
2344 WARNING:  hash indexes are not WAL-logged and their use is discouraged
2345 CREATE UNLOGGED TABLE unlogged_hash_table (id int4);
2346 CREATE INDEX unlogged_hash_index ON unlogged_hash_table USING hash (id int4_ops);
2347 DROP TABLE unlogged_hash_table;
2348 -- CREATE INDEX hash_ovfl_index ON hash_ovfl_heap USING hash (x int4_ops);
2349 --
2350 -- Test functional index
2351 --
2352 CREATE TABLE func_index_heap (f1 text, f2 text);
2353 CREATE UNIQUE INDEX func_index_index on func_index_heap (textcat(f1,f2));
2354 INSERT INTO func_index_heap VALUES('ABC','DEF');
2355 INSERT INTO func_index_heap VALUES('AB','CDEFG');
2356 INSERT INTO func_index_heap VALUES('QWE','RTY');
2357 -- this should fail because of unique index:
2358 INSERT INTO func_index_heap VALUES('ABCD', 'EF');
2359 ERROR:  duplicate key value violates unique constraint "func_index_index"
2360 DETAIL:  Key (textcat(f1, f2))=(ABCDEF) already exists.
2361 -- but this shouldn't:
2362 INSERT INTO func_index_heap VALUES('QWERTY');
2363 --
2364 -- Same test, expressional index
2365 --
2366 DROP TABLE func_index_heap;
2367 CREATE TABLE func_index_heap (f1 text, f2 text);
2368 CREATE UNIQUE INDEX func_index_index on func_index_heap ((f1 || f2) text_ops);
2369 INSERT INTO func_index_heap VALUES('ABC','DEF');
2370 INSERT INTO func_index_heap VALUES('AB','CDEFG');
2371 INSERT INTO func_index_heap VALUES('QWE','RTY');
2372 -- this should fail because of unique index:
2373 INSERT INTO func_index_heap VALUES('ABCD', 'EF');
2374 ERROR:  duplicate key value violates unique constraint "func_index_index"
2375 DETAIL:  Key ((f1 || f2))=(ABCDEF) already exists.
2376 -- but this shouldn't:
2377 INSERT INTO func_index_heap VALUES('QWERTY');
2378 --
2379 -- Also try building functional, expressional, and partial indexes on
2380 -- tables that already contain data.
2381 --
2382 create unique index hash_f8_index_1 on hash_f8_heap(abs(random));
2383 create unique index hash_f8_index_2 on hash_f8_heap((seqno + 1), random);
2384 create unique index hash_f8_index_3 on hash_f8_heap(random) where seqno > 1000;
2385 --
2386 -- Try some concurrent index builds
2387 --
2388 -- Unfortunately this only tests about half the code paths because there are
2389 -- no concurrent updates happening to the table at the same time.
2390 CREATE TABLE concur_heap (f1 text, f2 text);
2391 -- empty table
2392 CREATE INDEX CONCURRENTLY concur_index1 ON concur_heap(f2,f1);
2393 CREATE INDEX CONCURRENTLY IF NOT EXISTS concur_index1 ON concur_heap(f2,f1);
2394 NOTICE:  relation "concur_index1" already exists, skipping
2395 INSERT INTO concur_heap VALUES  ('a','b');
2396 INSERT INTO concur_heap VALUES  ('b','b');
2397 -- unique index
2398 CREATE UNIQUE INDEX CONCURRENTLY concur_index2 ON concur_heap(f1);
2399 CREATE UNIQUE INDEX CONCURRENTLY IF NOT EXISTS concur_index2 ON concur_heap(f1);
2400 NOTICE:  relation "concur_index2" already exists, skipping
2401 -- check if constraint is set up properly to be enforced
2402 INSERT INTO concur_heap VALUES ('b','x');
2403 ERROR:  duplicate key value violates unique constraint "concur_index2"
2404 DETAIL:  Key (f1)=(b) already exists.
2405 -- check if constraint is enforced properly at build time
2406 CREATE UNIQUE INDEX CONCURRENTLY concur_index3 ON concur_heap(f2);
2407 ERROR:  could not create unique index "concur_index3"
2408 DETAIL:  Key (f2)=(b) is duplicated.
2409 -- test that expression indexes and partial indexes work concurrently
2410 CREATE INDEX CONCURRENTLY concur_index4 on concur_heap(f2) WHERE f1='a';
2411 CREATE INDEX CONCURRENTLY concur_index5 on concur_heap(f2) WHERE f1='x';
2412 -- here we also check that you can default the index name
2413 CREATE INDEX CONCURRENTLY on concur_heap((f2||f1));
2414 -- You can't do a concurrent index build in a transaction
2415 BEGIN;
2416 CREATE INDEX CONCURRENTLY concur_index7 ON concur_heap(f1);
2417 ERROR:  CREATE INDEX CONCURRENTLY cannot run inside a transaction block
2418 COMMIT;
2419 -- But you can do a regular index build in a transaction
2420 BEGIN;
2421 CREATE INDEX std_index on concur_heap(f2);
2422 COMMIT;
2423 -- Failed builds are left invalid by VACUUM FULL, fixed by REINDEX
2424 VACUUM FULL concur_heap;
2425 REINDEX TABLE concur_heap;
2426 ERROR:  could not create unique index "concur_index3"
2427 DETAIL:  Key (f2)=(b) is duplicated.
2428 DELETE FROM concur_heap WHERE f1 = 'b';
2429 VACUUM FULL concur_heap;
2430 \d concur_heap
2431 Table "public.concur_heap"
2432  Column | Type | Modifiers
2433 --------+------+-----------
2434  f1     | text |
2435  f2     | text |
2436 Indexes:
2437     "concur_index2" UNIQUE, btree (f1)
2438     "concur_index3" UNIQUE, btree (f2) INVALID
2439     "concur_heap_expr_idx" btree ((f2 || f1))
2440     "concur_index1" btree (f2, f1)
2441     "concur_index4" btree (f2) WHERE f1 = 'a'::text
2442     "concur_index5" btree (f2) WHERE f1 = 'x'::text
2443     "std_index" btree (f2)
2444
2445 REINDEX TABLE concur_heap;
2446 \d concur_heap
2447 Table "public.concur_heap"
2448  Column | Type | Modifiers
2449 --------+------+-----------
2450  f1     | text |
2451  f2     | text |
2452 Indexes:
2453     "concur_index2" UNIQUE, btree (f1)
2454     "concur_index3" UNIQUE, btree (f2)
2455     "concur_heap_expr_idx" btree ((f2 || f1))
2456     "concur_index1" btree (f2, f1)
2457     "concur_index4" btree (f2) WHERE f1 = 'a'::text
2458     "concur_index5" btree (f2) WHERE f1 = 'x'::text
2459     "std_index" btree (f2)
2460
2461 --
2462 -- Try some concurrent index drops
2463 --
2464 DROP INDEX CONCURRENTLY "concur_index2";                                -- works
2465 DROP INDEX CONCURRENTLY IF EXISTS "concur_index2";              -- notice
2466 NOTICE:  index "concur_index2" does not exist, skipping
2467 -- failures
2468 DROP INDEX CONCURRENTLY "concur_index2", "concur_index3";
2469 ERROR:  DROP INDEX CONCURRENTLY does not support dropping multiple objects
2470 BEGIN;
2471 DROP INDEX CONCURRENTLY "concur_index5";
2472 ERROR:  DROP INDEX CONCURRENTLY cannot run inside a transaction block
2473 ROLLBACK;
2474 -- successes
2475 DROP INDEX CONCURRENTLY IF EXISTS "concur_index3";
2476 DROP INDEX CONCURRENTLY "concur_index4";
2477 DROP INDEX CONCURRENTLY "concur_index5";
2478 DROP INDEX CONCURRENTLY "concur_index1";
2479 DROP INDEX CONCURRENTLY "concur_heap_expr_idx";
2480 \d concur_heap
2481 Table "public.concur_heap"
2482  Column | Type | Modifiers
2483 --------+------+-----------
2484  f1     | text |
2485  f2     | text |
2486 Indexes:
2487     "std_index" btree (f2)
2488
2489 DROP TABLE concur_heap;
2490 --
2491 -- Test ADD CONSTRAINT USING INDEX
2492 --
2493 CREATE TABLE cwi_test( a int , b varchar(10), c char);
2494 -- add some data so that all tests have something to work with.
2495 INSERT INTO cwi_test VALUES(1, 2), (3, 4), (5, 6);
2496 CREATE UNIQUE INDEX cwi_uniq_idx ON cwi_test(a , b);
2497 ALTER TABLE cwi_test ADD primary key USING INDEX cwi_uniq_idx;
2498 \d cwi_test
2499           Table "public.cwi_test"
2500  Column |         Type          | Modifiers
2501 --------+-----------------------+-----------
2502  a      | integer               | not null
2503  b      | character varying(10) | not null
2504  c      | character(1)          |
2505 Indexes:
2506     "cwi_uniq_idx" PRIMARY KEY, btree (a, b)
2507
2508 \d cwi_uniq_idx
2509          Index "public.cwi_uniq_idx"
2510  Column |         Type          | Definition
2511 --------+-----------------------+------------
2512  a      | integer               | a
2513  b      | character varying(10) | b
2514 primary key, btree, for table "public.cwi_test"
2515
2516 CREATE UNIQUE INDEX cwi_uniq2_idx ON cwi_test(b , a);
2517 ALTER TABLE cwi_test DROP CONSTRAINT cwi_uniq_idx,
2518         ADD CONSTRAINT cwi_replaced_pkey PRIMARY KEY
2519                 USING INDEX cwi_uniq2_idx;
2520 NOTICE:  ALTER TABLE / ADD CONSTRAINT USING INDEX will rename index "cwi_uniq2_idx" to "cwi_replaced_pkey"
2521 \d cwi_test
2522           Table "public.cwi_test"
2523  Column |         Type          | Modifiers
2524 --------+-----------------------+-----------
2525  a      | integer               | not null
2526  b      | character varying(10) | not null
2527  c      | character(1)          |
2528 Indexes:
2529     "cwi_replaced_pkey" PRIMARY KEY, btree (b, a)
2530
2531 \d cwi_replaced_pkey
2532       Index "public.cwi_replaced_pkey"
2533  Column |         Type          | Definition
2534 --------+-----------------------+------------
2535  b      | character varying(10) | b
2536  a      | integer               | a
2537 primary key, btree, for table "public.cwi_test"
2538
2539 DROP INDEX cwi_replaced_pkey;   -- Should fail; a constraint depends on it
2540 ERROR:  cannot drop index cwi_replaced_pkey because constraint cwi_replaced_pkey on table cwi_test requires it
2541 HINT:  You can drop constraint cwi_replaced_pkey on table cwi_test instead.
2542 DROP TABLE cwi_test;
2543 --
2544 -- Tests for IS NULL/IS NOT NULL with b-tree indexes
2545 --
2546 SELECT unique1, unique2 INTO onek_with_null FROM onek;
2547 INSERT INTO onek_with_null (unique1,unique2) VALUES (NULL, -1), (NULL, NULL);
2548 CREATE UNIQUE INDEX onek_nulltest ON onek_with_null (unique2,unique1);
2549 SET enable_seqscan = OFF;
2550 SET enable_indexscan = ON;
2551 SET enable_bitmapscan = ON;
2552 SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL;
2553  count
2554 -------
2555      2
2556 (1 row)
2557
2558 SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL AND unique2 IS NULL;
2559  count
2560 -------
2561      1
2562 (1 row)
2563
2564 SELECT count(*) FROM onek_with_null WHERE unique1 IS NOT NULL;
2565  count
2566 -------
2567   1000
2568 (1 row)
2569
2570 SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL AND unique2 IS NOT NULL;
2571  count
2572 -------
2573      1
2574 (1 row)
2575
2576 SELECT count(*) FROM onek_with_null WHERE unique1 IS NOT NULL AND unique1 > 500;
2577  count
2578 -------
2579    499
2580 (1 row)
2581
2582 SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL AND unique1 > 500;
2583  count
2584 -------
2585      0
2586 (1 row)
2587
2588 DROP INDEX onek_nulltest;
2589 CREATE UNIQUE INDEX onek_nulltest ON onek_with_null (unique2 desc,unique1);
2590 SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL;
2591  count
2592 -------
2593      2
2594 (1 row)
2595
2596 SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL AND unique2 IS NULL;
2597  count
2598 -------
2599      1
2600 (1 row)
2601
2602 SELECT count(*) FROM onek_with_null WHERE unique1 IS NOT NULL;
2603  count
2604 -------
2605   1000
2606 (1 row)
2607
2608 SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL AND unique2 IS NOT NULL;
2609  count
2610 -------
2611      1
2612 (1 row)
2613
2614 SELECT count(*) FROM onek_with_null WHERE unique1 IS NOT NULL AND unique1 > 500;
2615  count
2616 -------
2617    499
2618 (1 row)
2619
2620 SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL AND unique1 > 500;
2621  count
2622 -------
2623      0
2624 (1 row)
2625
2626 DROP INDEX onek_nulltest;
2627 CREATE UNIQUE INDEX onek_nulltest ON onek_with_null (unique2 desc nulls last,unique1);
2628 SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL;
2629  count
2630 -------
2631      2
2632 (1 row)
2633
2634 SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL AND unique2 IS NULL;
2635  count
2636 -------
2637      1
2638 (1 row)
2639
2640 SELECT count(*) FROM onek_with_null WHERE unique1 IS NOT NULL;
2641  count
2642 -------
2643   1000
2644 (1 row)
2645
2646 SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL AND unique2 IS NOT NULL;
2647  count
2648 -------
2649      1
2650 (1 row)
2651
2652 SELECT count(*) FROM onek_with_null WHERE unique1 IS NOT NULL AND unique1 > 500;
2653  count
2654 -------
2655    499
2656 (1 row)
2657
2658 SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL AND unique1 > 500;
2659  count
2660 -------
2661      0
2662 (1 row)
2663
2664 DROP INDEX onek_nulltest;
2665 CREATE UNIQUE INDEX onek_nulltest ON onek_with_null (unique2  nulls first,unique1);
2666 SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL;
2667  count
2668 -------
2669      2
2670 (1 row)
2671
2672 SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL AND unique2 IS NULL;
2673  count
2674 -------
2675      1
2676 (1 row)
2677
2678 SELECT count(*) FROM onek_with_null WHERE unique1 IS NOT NULL;
2679  count
2680 -------
2681   1000
2682 (1 row)
2683
2684 SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL AND unique2 IS NOT NULL;
2685  count
2686 -------
2687      1
2688 (1 row)
2689
2690 SELECT count(*) FROM onek_with_null WHERE unique1 IS NOT NULL AND unique1 > 500;
2691  count
2692 -------
2693    499
2694 (1 row)
2695
2696 SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL AND unique1 > 500;
2697  count
2698 -------
2699      0
2700 (1 row)
2701
2702 DROP INDEX onek_nulltest;
2703 -- Check initial-positioning logic too
2704 CREATE UNIQUE INDEX onek_nulltest ON onek_with_null (unique2);
2705 SET enable_seqscan = OFF;
2706 SET enable_indexscan = ON;
2707 SET enable_bitmapscan = OFF;
2708 SELECT unique1, unique2 FROM onek_with_null
2709   ORDER BY unique2 LIMIT 2;
2710  unique1 | unique2
2711 ---------+---------
2712          |      -1
2713      147 |       0
2714 (2 rows)
2715
2716 SELECT unique1, unique2 FROM onek_with_null WHERE unique2 >= -1
2717   ORDER BY unique2 LIMIT 2;
2718  unique1 | unique2
2719 ---------+---------
2720          |      -1
2721      147 |       0
2722 (2 rows)
2723
2724 SELECT unique1, unique2 FROM onek_with_null WHERE unique2 >= 0
2725   ORDER BY unique2 LIMIT 2;
2726  unique1 | unique2
2727 ---------+---------
2728      147 |       0
2729      931 |       1
2730 (2 rows)
2731
2732 SELECT unique1, unique2 FROM onek_with_null
2733   ORDER BY unique2 DESC LIMIT 2;
2734  unique1 | unique2
2735 ---------+---------
2736          |
2737      278 |     999
2738 (2 rows)
2739
2740 SELECT unique1, unique2 FROM onek_with_null WHERE unique2 >= -1
2741   ORDER BY unique2 DESC LIMIT 2;
2742  unique1 | unique2
2743 ---------+---------
2744      278 |     999
2745        0 |     998
2746 (2 rows)
2747
2748 SELECT unique1, unique2 FROM onek_with_null WHERE unique2 < 999
2749   ORDER BY unique2 DESC LIMIT 2;
2750  unique1 | unique2
2751 ---------+---------
2752        0 |     998
2753      744 |     997
2754 (2 rows)
2755
2756 RESET enable_seqscan;
2757 RESET enable_indexscan;
2758 RESET enable_bitmapscan;
2759 DROP TABLE onek_with_null;
2760 --
2761 -- Check bitmap index path planning
2762 --
2763 EXPLAIN (COSTS OFF)
2764 SELECT * FROM tenk1
2765   WHERE thousand = 42 AND (tenthous = 1 OR tenthous = 3 OR tenthous = 42);
2766                                                                QUERY PLAN
2767 -----------------------------------------------------------------------------------------------------------------------------------------
2768  Bitmap Heap Scan on tenk1
2769    Recheck Cond: (((thousand = 42) AND (tenthous = 1)) OR ((thousand = 42) AND (tenthous = 3)) OR ((thousand = 42) AND (tenthous = 42)))
2770    ->  BitmapOr
2771          ->  Bitmap Index Scan on tenk1_thous_tenthous
2772                Index Cond: ((thousand = 42) AND (tenthous = 1))
2773          ->  Bitmap Index Scan on tenk1_thous_tenthous
2774                Index Cond: ((thousand = 42) AND (tenthous = 3))
2775          ->  Bitmap Index Scan on tenk1_thous_tenthous
2776                Index Cond: ((thousand = 42) AND (tenthous = 42))
2777 (9 rows)
2778
2779 SELECT * FROM tenk1
2780   WHERE thousand = 42 AND (tenthous = 1 OR tenthous = 3 OR tenthous = 42);
2781  unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4
2782 ---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+---------
2783       42 |    5530 |   0 |    2 |   2 |      2 |      42 |       42 |          42 |        42 |       42 |  84 |   85 | QBAAAA   | SEIAAA   | OOOOxx
2784 (1 row)
2785
2786 EXPLAIN (COSTS OFF)
2787 SELECT count(*) FROM tenk1
2788   WHERE hundred = 42 AND (thousand = 42 OR thousand = 99);
2789                                    QUERY PLAN
2790 ---------------------------------------------------------------------------------
2791  Aggregate
2792    ->  Bitmap Heap Scan on tenk1
2793          Recheck Cond: ((hundred = 42) AND ((thousand = 42) OR (thousand = 99)))
2794          ->  BitmapAnd
2795                ->  Bitmap Index Scan on tenk1_hundred
2796                      Index Cond: (hundred = 42)
2797                ->  BitmapOr
2798                      ->  Bitmap Index Scan on tenk1_thous_tenthous
2799                            Index Cond: (thousand = 42)
2800                      ->  Bitmap Index Scan on tenk1_thous_tenthous
2801                            Index Cond: (thousand = 99)
2802 (11 rows)
2803
2804 SELECT count(*) FROM tenk1
2805   WHERE hundred = 42 AND (thousand = 42 OR thousand = 99);
2806  count
2807 -------
2808     10
2809 (1 row)
2810
2811 --
2812 -- Check behavior with duplicate index column contents
2813 --
2814 CREATE TABLE dupindexcols AS
2815   SELECT unique1 as id, stringu2::text as f1 FROM tenk1;
2816 CREATE INDEX dupindexcols_i ON dupindexcols (f1, id, f1 text_pattern_ops);
2817 ANALYZE dupindexcols;
2818 EXPLAIN (COSTS OFF)
2819   SELECT count(*) FROM dupindexcols
2820     WHERE f1 BETWEEN 'WA' AND 'ZZZ' and id < 1000 and f1 ~<~ 'YX';
2821                                                    QUERY PLAN
2822 ----------------------------------------------------------------------------------------------------------------
2823  Aggregate
2824    ->  Bitmap Heap Scan on dupindexcols
2825          Recheck Cond: ((f1 >= 'WA'::text) AND (f1 <= 'ZZZ'::text) AND (id < 1000) AND (f1 ~<~ 'YX'::text))
2826          ->  Bitmap Index Scan on dupindexcols_i
2827                Index Cond: ((f1 >= 'WA'::text) AND (f1 <= 'ZZZ'::text) AND (id < 1000) AND (f1 ~<~ 'YX'::text))
2828 (5 rows)
2829
2830 SELECT count(*) FROM dupindexcols
2831   WHERE f1 BETWEEN 'WA' AND 'ZZZ' and id < 1000 and f1 ~<~ 'YX';
2832  count
2833 -------
2834     97
2835 (1 row)
2836
2837 --
2838 -- Check ordering of =ANY indexqual results (bug in 9.2.0)
2839 --
2840 vacuum tenk1;           -- ensure we get consistent plans here
2841 explain (costs off)
2842 SELECT unique1 FROM tenk1
2843 WHERE unique1 IN (1,42,7)
2844 ORDER BY unique1;
2845                       QUERY PLAN
2846 -------------------------------------------------------
2847  Index Only Scan using tenk1_unique1 on tenk1
2848    Index Cond: (unique1 = ANY ('{1,42,7}'::integer[]))
2849 (2 rows)
2850
2851 SELECT unique1 FROM tenk1
2852 WHERE unique1 IN (1,42,7)
2853 ORDER BY unique1;
2854  unique1
2855 ---------
2856        1
2857        7
2858       42
2859 (3 rows)
2860
2861 explain (costs off)
2862 SELECT thousand, tenthous FROM tenk1
2863 WHERE thousand < 2 AND tenthous IN (1001,3000)
2864 ORDER BY thousand;
2865                       QUERY PLAN
2866 -------------------------------------------------------
2867  Index Only Scan using tenk1_thous_tenthous on tenk1
2868    Index Cond: (thousand < 2)
2869    Filter: (tenthous = ANY ('{1001,3000}'::integer[]))
2870 (3 rows)
2871
2872 SELECT thousand, tenthous FROM tenk1
2873 WHERE thousand < 2 AND tenthous IN (1001,3000)
2874 ORDER BY thousand;
2875  thousand | tenthous
2876 ----------+----------
2877         0 |     3000
2878         1 |     1001
2879 (2 rows)
2880
2881 SET enable_indexonlyscan = OFF;
2882 explain (costs off)
2883 SELECT thousand, tenthous FROM tenk1
2884 WHERE thousand < 2 AND tenthous IN (1001,3000)
2885 ORDER BY thousand;
2886                                       QUERY PLAN
2887 --------------------------------------------------------------------------------------
2888  Sort
2889    Sort Key: thousand
2890    ->  Index Scan using tenk1_thous_tenthous on tenk1
2891          Index Cond: ((thousand < 2) AND (tenthous = ANY ('{1001,3000}'::integer[])))
2892 (4 rows)
2893
2894 SELECT thousand, tenthous FROM tenk1
2895 WHERE thousand < 2 AND tenthous IN (1001,3000)
2896 ORDER BY thousand;
2897  thousand | tenthous
2898 ----------+----------
2899         0 |     3000
2900         1 |     1001
2901 (2 rows)
2902
2903 RESET enable_indexscan;
2904 --
2905 -- Check elimination of constant-NULL subexpressions
2906 --
2907 explain (costs off)
2908   select * from tenk1 where (thousand, tenthous) in ((1,1001), (null,null));
2909                       QUERY PLAN
2910 ------------------------------------------------------
2911  Index Scan using tenk1_thous_tenthous on tenk1
2912    Index Cond: ((thousand = 1) AND (tenthous = 1001))
2913 (2 rows)
2914
2915 --
2916 -- REINDEX (VERBOSE)
2917 --
2918 CREATE TABLE reindex_verbose(id integer primary key);
2919 \set VERBOSITY terse
2920 REINDEX (VERBOSE) TABLE reindex_verbose;
2921 INFO:  index "reindex_verbose_pkey" was reindexed
2922 DROP TABLE reindex_verbose;
2923 --
2924 -- REINDEX SCHEMA
2925 --
2926 REINDEX SCHEMA schema_to_reindex; -- failure, schema does not exist
2927 ERROR:  schema "schema_to_reindex" does not exist
2928 CREATE SCHEMA schema_to_reindex;
2929 SET search_path = 'schema_to_reindex';
2930 CREATE TABLE table1(col1 SERIAL PRIMARY KEY);
2931 INSERT INTO table1 SELECT generate_series(1,400);
2932 CREATE TABLE table2(col1 SERIAL PRIMARY KEY, col2 TEXT NOT NULL);
2933 INSERT INTO table2 SELECT generate_series(1,400), 'abc';
2934 CREATE INDEX ON table2(col2);
2935 CREATE MATERIALIZED VIEW matview AS SELECT col1 FROM table2;
2936 CREATE INDEX ON matview(col1);
2937 CREATE VIEW view AS SELECT col2 FROM table2;
2938 CREATE TABLE reindex_before AS
2939 SELECT oid, relname, relfilenode, relkind, reltoastrelid
2940         FROM pg_class
2941         where relnamespace = (SELECT oid FROM pg_namespace WHERE nspname = 'schema_to_reindex');
2942 INSERT INTO reindex_before
2943 SELECT oid, 'pg_toast_TABLE', relfilenode, relkind, reltoastrelid
2944 FROM pg_class WHERE oid IN
2945         (SELECT reltoastrelid FROM reindex_before WHERE reltoastrelid > 0);
2946 INSERT INTO reindex_before
2947 SELECT oid, 'pg_toast_TABLE_index', relfilenode, relkind, reltoastrelid
2948 FROM pg_class where oid in
2949         (select indexrelid from pg_index where indrelid in
2950                 (select reltoastrelid from reindex_before where reltoastrelid > 0));
2951 REINDEX SCHEMA schema_to_reindex;
2952 CREATE TABLE reindex_after AS SELECT oid, relname, relfilenode, relkind
2953         FROM pg_class
2954         where relnamespace = (SELECT oid FROM pg_namespace WHERE nspname = 'schema_to_reindex');
2955 SELECT  b.relname,
2956         b.relkind,
2957         CASE WHEN a.relfilenode = b.relfilenode THEN 'relfilenode is unchanged'
2958         ELSE 'relfilenode has changed' END
2959   FROM reindex_before b JOIN pg_class a ON b.oid = a.oid
2960   ORDER BY 1;
2961        relname        | relkind |           case
2962 ----------------------+---------+--------------------------
2963  matview              | m       | relfilenode is unchanged
2964  matview_col1_idx     | i       | relfilenode has changed
2965  pg_toast_TABLE       | t       | relfilenode is unchanged
2966  pg_toast_TABLE_index | i       | relfilenode has changed
2967  table1               | r       | relfilenode is unchanged
2968  table1_col1_seq      | S       | relfilenode is unchanged
2969  table1_pkey          | i       | relfilenode has changed
2970  table2               | r       | relfilenode is unchanged
2971  table2_col1_seq      | S       | relfilenode is unchanged
2972  table2_col2_idx      | i       | relfilenode has changed
2973  table2_pkey          | i       | relfilenode has changed
2974  view                 | v       | relfilenode is unchanged
2975 (12 rows)
2976
2977 REINDEX SCHEMA schema_to_reindex;
2978 BEGIN;
2979 REINDEX SCHEMA schema_to_reindex; -- failure, cannot run in a transaction
2980 ERROR:  REINDEX SCHEMA cannot run inside a transaction block
2981 END;
2982 -- Failure for unauthorized user
2983 CREATE ROLE regression_reindexuser NOLOGIN;
2984 SET SESSION ROLE regression_reindexuser;
2985 REINDEX SCHEMA schema_to_reindex;
2986 ERROR:  must be owner of schema schema_to_reindex
2987 -- Clean up
2988 RESET ROLE;
2989 DROP ROLE regression_reindexuser;
2990 SET client_min_messages TO 'warning';
2991 DROP SCHEMA schema_to_reindex CASCADE;
2992 RESET client_min_messages;