[postgresql] / src / test / regress / expected / create_index.out

--
-- CREATE_INDEX
-- Create ancillary data structures (i.e. indices)
--
--
-- BTREE
--
CREATE INDEX onek_unique1 ON onek USING btree(unique1 int4_ops);
CREATE INDEX onek_unique2 ON onek USING btree(unique2 int4_ops);
CREATE INDEX onek_hundred ON onek USING btree(hundred int4_ops);
CREATE INDEX onek_stringu1 ON onek USING btree(stringu1 name_ops);
CREATE INDEX tenk1_unique1 ON tenk1 USING btree(unique1 int4_ops);
CREATE INDEX tenk1_unique2 ON tenk1 USING btree(unique2 int4_ops);
CREATE INDEX tenk1_hundred ON tenk1 USING btree(hundred int4_ops);
CREATE INDEX tenk1_thous_tenthous ON tenk1 (thousand, tenthous);
CREATE INDEX tenk2_unique1 ON tenk2 USING btree(unique1 int4_ops);
CREATE INDEX tenk2_unique2 ON tenk2 USING btree(unique2 int4_ops);
CREATE INDEX tenk2_hundred ON tenk2 USING btree(hundred int4_ops);
CREATE INDEX rix ON road USING btree (name text_ops);
CREATE INDEX iix ON ihighway USING btree (name text_ops);
CREATE INDEX six ON shighway USING btree (name text_ops);
-- test comments
COMMENT ON INDEX six_wrong IS 'bad index';
ERROR:  relation "six_wrong" does not exist
COMMENT ON INDEX six IS 'good index';
COMMENT ON INDEX six IS NULL;
--
-- BTREE ascending/descending cases
--
-- we load int4/text from pure descending data (each key is a new
-- low key) and name/f8 from pure ascending data (each key is a new
-- high key).  we had a bug where new low keys would sometimes be
-- "lost".
--
CREATE INDEX bt_i4_index ON bt_i4_heap USING btree (seqno int4_ops);
CREATE INDEX bt_name_index ON bt_name_heap USING btree (seqno name_ops);
CREATE INDEX bt_txt_index ON bt_txt_heap USING btree (seqno text_ops);
CREATE INDEX bt_f8_index ON bt_f8_heap USING btree (seqno float8_ops);
--
-- BTREE partial indices
--
CREATE INDEX onek2_u1_prtl ON onek2 USING btree(unique1 int4_ops)
        where unique1 < 20 or unique1 > 980;
CREATE INDEX onek2_u2_prtl ON onek2 USING btree(unique2 int4_ops)
        where stringu1 < 'B';
CREATE INDEX onek2_stu1_prtl ON onek2 USING btree(stringu1 name_ops)
        where onek2.stringu1 >= 'J' and onek2.stringu1 < 'K';
--
-- GiST (rtree-equivalent opclasses only)
--
CREATE INDEX grect2ind ON fast_emp4000 USING gist (home_base);
CREATE INDEX gpolygonind ON polygon_tbl USING gist (f1);
CREATE INDEX gcircleind ON circle_tbl USING gist (f1);
CREATE INDEX gpointind ON point_tbl USING gist (f1);
CREATE TEMP TABLE gpolygon_tbl AS
    SELECT polygon(home_base) AS f1 FROM slow_emp4000;
INSERT INTO gpolygon_tbl VALUES ( '(1000,0,0,1000)' );
INSERT INTO gpolygon_tbl VALUES ( '(0,1000,1000,1000)' );
CREATE TEMP TABLE gcircle_tbl AS
    SELECT circle(home_base) AS f1 FROM slow_emp4000;
CREATE INDEX ggpolygonind ON gpolygon_tbl USING gist (f1);
CREATE INDEX ggcircleind ON gcircle_tbl USING gist (f1);
SET enable_seqscan = ON;
SET enable_indexscan = OFF;
SET enable_bitmapscan = OFF;
SELECT * FROM fast_emp4000
    WHERE home_base @ '(200,200),(2000,1000)'::box
    ORDER BY (home_base[0])[0];
       home_base       
-----------------------
 (337,455),(240,359)
 (1444,403),(1346,344)
(2 rows)

SELECT count(*) FROM fast_emp4000 WHERE home_base && '(1000,1000,0,0)'::box;
 count 
-------
     2
(1 row)

SELECT count(*) FROM fast_emp4000 WHERE home_base IS NULL;
 count 
-------
   278
(1 row)

SELECT * FROM polygon_tbl WHERE f1 ~ '((1,1),(2,2),(2,1))'::polygon
    ORDER BY (poly_center(f1))[0];
         f1          
---------------------
 ((2,0),(2,4),(0,0))
(1 row)

SELECT * FROM circle_tbl WHERE f1 && circle(point(1,-2), 1)
    ORDER BY area(f1);
      f1       
---------------
 <(1,2),3>
 <(1,3),5>
 <(1,2),100>
 <(100,1),115>
(4 rows)

SELECT count(*) FROM gpolygon_tbl WHERE f1 && '(1000,1000,0,0)'::polygon;
 count 
-------
     2
(1 row)

SELECT count(*) FROM gcircle_tbl WHERE f1 && '<(500,500),500>'::circle;
 count 
-------
     2
(1 row)

SELECT count(*) FROM point_tbl WHERE f1 <@ box '(0,0,100,100)';
 count 
-------
     3
(1 row)

SELECT count(*) FROM point_tbl WHERE box '(0,0,100,100)' @> f1;
 count 
-------
     3
(1 row)

SELECT count(*) FROM point_tbl WHERE f1 <@ polygon '(0,0),(0,100),(100,100),(50,50),(100,0),(0,0)';
 count 
-------
     3
(1 row)

SELECT count(*) FROM point_tbl WHERE f1 <@ circle '<(50,50),50>';
 count 
-------
     1
(1 row)

SELECT count(*) FROM point_tbl p WHERE p.f1 << '(0.0, 0.0)';
 count 
-------
     3
(1 row)

SELECT count(*) FROM point_tbl p WHERE p.f1 >> '(0.0, 0.0)';
 count 
-------
     2
(1 row)

SELECT count(*) FROM point_tbl p WHERE p.f1 <^ '(0.0, 0.0)';
 count 
-------
     1
(1 row)

SELECT count(*) FROM point_tbl p WHERE p.f1 >^ '(0.0, 0.0)';
 count 
-------
     3
(1 row)

SELECT count(*) FROM point_tbl p WHERE p.f1 ~= '(-5, -12)';
 count 
-------
     1
(1 row)

SET enable_seqscan = OFF;
SET enable_indexscan = ON;
SET enable_bitmapscan = ON;
EXPLAIN (COSTS OFF)
SELECT * FROM fast_emp4000
    WHERE home_base @ '(200,200),(2000,1000)'::box
    ORDER BY (home_base[0])[0];
                              QUERY PLAN                              
----------------------------------------------------------------------
 Sort
   Sort Key: ((home_base[0])[0])
   ->  Bitmap Heap Scan on fast_emp4000
         Recheck Cond: (home_base @ '(2000,1000),(200,200)'::box)
         ->  Bitmap Index Scan on grect2ind
               Index Cond: (home_base @ '(2000,1000),(200,200)'::box)
(6 rows)

SELECT * FROM fast_emp4000
    WHERE home_base @ '(200,200),(2000,1000)'::box
    ORDER BY (home_base[0])[0];
       home_base       
-----------------------
 (337,455),(240,359)
 (1444,403),(1346,344)
(2 rows)

EXPLAIN (COSTS OFF)
SELECT count(*) FROM fast_emp4000 WHERE home_base && '(1000,1000,0,0)'::box;
                            QUERY PLAN                             
-------------------------------------------------------------------
 Aggregate
   ->  Bitmap Heap Scan on fast_emp4000
         Recheck Cond: (home_base && '(1000,1000),(0,0)'::box)
         ->  Bitmap Index Scan on grect2ind
               Index Cond: (home_base && '(1000,1000),(0,0)'::box)
(5 rows)

SELECT count(*) FROM fast_emp4000 WHERE home_base && '(1000,1000,0,0)'::box;
 count 
-------
     2
(1 row)

EXPLAIN (COSTS OFF)
SELECT count(*) FROM fast_emp4000 WHERE home_base IS NULL;
                  QUERY PLAN                   
-----------------------------------------------
 Aggregate
   ->  Bitmap Heap Scan on fast_emp4000
         Recheck Cond: (home_base IS NULL)
         ->  Bitmap Index Scan on grect2ind
               Index Cond: (home_base IS NULL)
(5 rows)

SELECT count(*) FROM fast_emp4000 WHERE home_base IS NULL;
 count 
-------
   278
(1 row)

EXPLAIN (COSTS OFF)
SELECT * FROM polygon_tbl WHERE f1 ~ '((1,1),(2,2),(2,1))'::polygon
    ORDER BY (poly_center(f1))[0];
                        QUERY PLAN                         
-----------------------------------------------------------
 Sort
   Sort Key: ((poly_center(f1))[0])
   ->  Index Scan using gpolygonind on polygon_tbl
         Index Cond: (f1 ~ '((1,1),(2,2),(2,1))'::polygon)
(4 rows)

SELECT * FROM polygon_tbl WHERE f1 ~ '((1,1),(2,2),(2,1))'::polygon
    ORDER BY (poly_center(f1))[0];
         f1          
---------------------
 ((2,0),(2,4),(0,0))
(1 row)

EXPLAIN (COSTS OFF)
SELECT * FROM circle_tbl WHERE f1 && circle(point(1,-2), 1)
    ORDER BY area(f1);
                    QUERY PLAN                    
--------------------------------------------------
 Sort
   Sort Key: (area(f1))
   ->  Index Scan using gcircleind on circle_tbl
         Index Cond: (f1 && '<(1,-2),1>'::circle)
(4 rows)

SELECT * FROM circle_tbl WHERE f1 && circle(point(1,-2), 1)
    ORDER BY area(f1);
      f1       
---------------
 <(1,2),3>
 <(1,3),5>
 <(1,2),100>
 <(100,1),115>
(4 rows)

EXPLAIN (COSTS OFF)
SELECT count(*) FROM gpolygon_tbl WHERE f1 && '(1000,1000,0,0)'::polygon;
                            QUERY PLAN                            
------------------------------------------------------------------
 Aggregate
   ->  Bitmap Heap Scan on gpolygon_tbl
         Recheck Cond: (f1 && '((1000,1000),(0,0))'::polygon)
         ->  Bitmap Index Scan on ggpolygonind
               Index Cond: (f1 && '((1000,1000),(0,0))'::polygon)
(5 rows)

SELECT count(*) FROM gpolygon_tbl WHERE f1 && '(1000,1000,0,0)'::polygon;
 count 
-------
     2
(1 row)

EXPLAIN (COSTS OFF)
SELECT count(*) FROM gcircle_tbl WHERE f1 && '<(500,500),500>'::circle;
                         QUERY PLAN                          
-------------------------------------------------------------
 Aggregate
   ->  Bitmap Heap Scan on gcircle_tbl
         Recheck Cond: (f1 && '<(500,500),500>'::circle)
         ->  Bitmap Index Scan on ggcircleind
               Index Cond: (f1 && '<(500,500),500>'::circle)
(5 rows)

SELECT count(*) FROM gcircle_tbl WHERE f1 && '<(500,500),500>'::circle;
 count 
-------
     2
(1 row)

EXPLAIN (COSTS OFF)
SELECT count(*) FROM point_tbl WHERE f1 <@ box '(0,0,100,100)';
                     QUERY PLAN                     
----------------------------------------------------
 Aggregate
   ->  Index Scan using gpointind on point_tbl
         Index Cond: (f1 <@ '(100,100),(0,0)'::box)
(3 rows)

SELECT count(*) FROM point_tbl WHERE f1 <@ box '(0,0,100,100)';
 count 
-------
     3
(1 row)

EXPLAIN (COSTS OFF)
SELECT count(*) FROM point_tbl WHERE box '(0,0,100,100)' @> f1;
                     QUERY PLAN                     
----------------------------------------------------
 Aggregate
   ->  Index Scan using gpointind on point_tbl
         Index Cond: ('(100,100),(0,0)'::box @> f1)
(3 rows)

SELECT count(*) FROM point_tbl WHERE box '(0,0,100,100)' @> f1;
 count 
-------
     3
(1 row)

EXPLAIN (COSTS OFF)
SELECT count(*) FROM point_tbl WHERE f1 <@ polygon '(0,0),(0,100),(100,100),(50,50),(100,0),(0,0)';
                                       QUERY PLAN                                       
----------------------------------------------------------------------------------------
 Aggregate
   ->  Index Scan using gpointind on point_tbl
         Index Cond: (f1 <@ '((0,0),(0,100),(100,100),(50,50),(100,0),(0,0))'::polygon)
(3 rows)

SELECT count(*) FROM point_tbl WHERE f1 <@ polygon '(0,0),(0,100),(100,100),(50,50),(100,0),(0,0)';
 count 
-------
     3
(1 row)

EXPLAIN (COSTS OFF)
SELECT count(*) FROM point_tbl WHERE f1 <@ circle '<(50,50),50>';
                     QUERY PLAN                     
----------------------------------------------------
 Aggregate
   ->  Index Scan using gpointind on point_tbl
         Index Cond: (f1 <@ '<(50,50),50>'::circle)
(3 rows)

SELECT count(*) FROM point_tbl WHERE f1 <@ circle '<(50,50),50>';
 count 
-------
     1
(1 row)

EXPLAIN (COSTS OFF)
SELECT count(*) FROM point_tbl p WHERE p.f1 << '(0.0, 0.0)';
                   QUERY PLAN                    
-------------------------------------------------
 Aggregate
   ->  Index Scan using gpointind on point_tbl p
         Index Cond: (f1 << '(0,0)'::point)
(3 rows)

SELECT count(*) FROM point_tbl p WHERE p.f1 << '(0.0, 0.0)';
 count 
-------
     3
(1 row)

EXPLAIN (COSTS OFF)
SELECT count(*) FROM point_tbl p WHERE p.f1 >> '(0.0, 0.0)';
                   QUERY PLAN                    
-------------------------------------------------
 Aggregate
   ->  Index Scan using gpointind on point_tbl p
         Index Cond: (f1 >> '(0,0)'::point)
(3 rows)

SELECT count(*) FROM point_tbl p WHERE p.f1 >> '(0.0, 0.0)';
 count 
-------
     2
(1 row)

EXPLAIN (COSTS OFF)
SELECT count(*) FROM point_tbl p WHERE p.f1 <^ '(0.0, 0.0)';
                   QUERY PLAN                    
-------------------------------------------------
 Aggregate
   ->  Index Scan using gpointind on point_tbl p
         Index Cond: (f1 <^ '(0,0)'::point)
(3 rows)

SELECT count(*) FROM point_tbl p WHERE p.f1 <^ '(0.0, 0.0)';
 count 
-------
     1
(1 row)

EXPLAIN (COSTS OFF)
SELECT count(*) FROM point_tbl p WHERE p.f1 >^ '(0.0, 0.0)';
                   QUERY PLAN                    
-------------------------------------------------
 Aggregate
   ->  Index Scan using gpointind on point_tbl p
         Index Cond: (f1 >^ '(0,0)'::point)
(3 rows)

SELECT count(*) FROM point_tbl p WHERE p.f1 >^ '(0.0, 0.0)';
 count 
-------
     3
(1 row)

EXPLAIN (COSTS OFF)
SELECT count(*) FROM point_tbl p WHERE p.f1 ~= '(-5, -12)';
                   QUERY PLAN                    
-------------------------------------------------
 Aggregate
   ->  Index Scan using gpointind on point_tbl p
         Index Cond: (f1 ~= '(-5,-12)'::point)
(3 rows)

SELECT count(*) FROM point_tbl p WHERE p.f1 ~= '(-5, -12)';
 count 
-------
     1
(1 row)

RESET enable_seqscan;
RESET enable_indexscan;
RESET enable_bitmapscan;
--
-- GIN over int[] and text[]
--
SET enable_seqscan = OFF;
SET enable_indexscan = ON;
SET enable_bitmapscan = OFF;
CREATE INDEX intarrayidx ON array_index_op_test USING gin (i);
SELECT * FROM array_index_op_test WHERE i @> '{32}' ORDER BY seqno;
 seqno |                i                |                                                                 t                                                                  
-------+---------------------------------+------------------------------------------------------------------------------------------------------------------------------------
     6 | {39,35,5,94,17,92,60,32}        | {AAAAAAAAAAAAAAA35875,AAAAAAAAAAAAAAAA23657}
    74 | {32}                            | {AAAAAAAAAAAAAAAA1729,AAAAAAAAAAAAA22860,AAAAAA99807,AAAAA17383,AAAAAAAAAAAAAAA67062,AAAAAAAAAAA15165,AAAAAAAAAAA50956}
    77 | {97,15,32,17,55,59,18,37,50,39} | {AAAAAAAAAAAA67946,AAAAAA54032,AAAAAAAA81587,55847,AAAAAAAAAAAAAA28620,AAAAAAAAAAAAAAAAA43052,AAAAAA75463,AAAA49534,AAAAAAAA44066}
    89 | {40,32,17,6,30,88}              | {AA44673,AAAAAAAAAAA6119,AAAAAAAAAAAAAAAA23657,AAAAAAAAAAAAAAAAAA47955,AAAAAAAAAAAAAAAA33598,AAAAAAAAAAA33576,AA44673}
    98 | {38,34,32,89}                   | {AAAAAAAAAAAAAAAAAA71621,AAAA8857,AAAAAAAAAAAAAAAAAAA65037,AAAAAAAAAAAAAAAA31334,AAAAAAAAAA48845}
   100 | {85,32,57,39,49,84,32,3,30}     | {AAAAAAA80240,AAAAAAAAAAAAAAAA1729,AAAAA60038,AAAAAAAAAAA92631,AAAAAAAA9523}
(6 rows)

SELECT * FROM array_index_op_test WHERE i && '{32}' ORDER BY seqno;
 seqno |                i                |                                                                 t                                                                  
-------+---------------------------------+------------------------------------------------------------------------------------------------------------------------------------
     6 | {39,35,5,94,17,92,60,32}        | {AAAAAAAAAAAAAAA35875,AAAAAAAAAAAAAAAA23657}
    74 | {32}                            | {AAAAAAAAAAAAAAAA1729,AAAAAAAAAAAAA22860,AAAAAA99807,AAAAA17383,AAAAAAAAAAAAAAA67062,AAAAAAAAAAA15165,AAAAAAAAAAA50956}
    77 | {97,15,32,17,55,59,18,37,50,39} | {AAAAAAAAAAAA67946,AAAAAA54032,AAAAAAAA81587,55847,AAAAAAAAAAAAAA28620,AAAAAAAAAAAAAAAAA43052,AAAAAA75463,AAAA49534,AAAAAAAA44066}
    89 | {40,32,17,6,30,88}              | {AA44673,AAAAAAAAAAA6119,AAAAAAAAAAAAAAAA23657,AAAAAAAAAAAAAAAAAA47955,AAAAAAAAAAAAAAAA33598,AAAAAAAAAAA33576,AA44673}
    98 | {38,34,32,89}                   | {AAAAAAAAAAAAAAAAAA71621,AAAA8857,AAAAAAAAAAAAAAAAAAA65037,AAAAAAAAAAAAAAAA31334,AAAAAAAAAA48845}
   100 | {85,32,57,39,49,84,32,3,30}     | {AAAAAAA80240,AAAAAAAAAAAAAAAA1729,AAAAA60038,AAAAAAAAAAA92631,AAAAAAAA9523}
(6 rows)

SELECT * FROM array_index_op_test WHERE i @> '{17}' ORDER BY seqno;
 seqno |                i                |                                                                 t                                                                  
-------+---------------------------------+------------------------------------------------------------------------------------------------------------------------------------
     6 | {39,35,5,94,17,92,60,32}        | {AAAAAAAAAAAAAAA35875,AAAAAAAAAAAAAAAA23657}
    12 | {17,99,18,52,91,72,0,43,96,23}  | {AAAAA33250,AAAAAAAAAAAAAAAAAAA85420,AAAAAAAAAAA33576}
    15 | {17,14,16,63,67}                | {AA6416,AAAAAAAAAA646,AAAAA95309}
    19 | {52,82,17,74,23,46,69,51,75}    | {AAAAAAAAAAAAA73084,AAAAA75968,AAAAAAAAAAAAAAAA14047,AAAAAAA80240,AAAAAAAAAAAAAAAAAAA1205,A68938}
    53 | {38,17}                         | {AAAAAAAAAAA21658}
    65 | {61,5,76,59,17}                 | {AAAAAA99807,AAAAA64741,AAAAAAAAAAA53908,AA21643,AAAAAAAAA10012}
    77 | {97,15,32,17,55,59,18,37,50,39} | {AAAAAAAAAAAA67946,AAAAAA54032,AAAAAAAA81587,55847,AAAAAAAAAAAAAA28620,AAAAAAAAAAAAAAAAA43052,AAAAAA75463,AAAA49534,AAAAAAAA44066}
    89 | {40,32,17,6,30,88}              | {AA44673,AAAAAAAAAAA6119,AAAAAAAAAAAAAAAA23657,AAAAAAAAAAAAAAAAAA47955,AAAAAAAAAAAAAAAA33598,AAAAAAAAAAA33576,AA44673}
(8 rows)

SELECT * FROM array_index_op_test WHERE i && '{17}' ORDER BY seqno;
 seqno |                i                |                                                                 t                                                                  
-------+---------------------------------+------------------------------------------------------------------------------------------------------------------------------------
     6 | {39,35,5,94,17,92,60,32}        | {AAAAAAAAAAAAAAA35875,AAAAAAAAAAAAAAAA23657}
    12 | {17,99,18,52,91,72,0,43,96,23}  | {AAAAA33250,AAAAAAAAAAAAAAAAAAA85420,AAAAAAAAAAA33576}
    15 | {17,14,16,63,67}                | {AA6416,AAAAAAAAAA646,AAAAA95309}
    19 | {52,82,17,74,23,46,69,51,75}    | {AAAAAAAAAAAAA73084,AAAAA75968,AAAAAAAAAAAAAAAA14047,AAAAAAA80240,AAAAAAAAAAAAAAAAAAA1205,A68938}
    53 | {38,17}                         | {AAAAAAAAAAA21658}
    65 | {61,5,76,59,17}                 | {AAAAAA99807,AAAAA64741,AAAAAAAAAAA53908,AA21643,AAAAAAAAA10012}
    77 | {97,15,32,17,55,59,18,37,50,39} | {AAAAAAAAAAAA67946,AAAAAA54032,AAAAAAAA81587,55847,AAAAAAAAAAAAAA28620,AAAAAAAAAAAAAAAAA43052,AAAAAA75463,AAAA49534,AAAAAAAA44066}
    89 | {40,32,17,6,30,88}              | {AA44673,AAAAAAAAAAA6119,AAAAAAAAAAAAAAAA23657,AAAAAAAAAAAAAAAAAA47955,AAAAAAAAAAAAAAAA33598,AAAAAAAAAAA33576,AA44673}
(8 rows)

SELECT * FROM array_index_op_test WHERE i @> '{32,17}' ORDER BY seqno;
 seqno |                i                |                                                                 t                                                                  
-------+---------------------------------+------------------------------------------------------------------------------------------------------------------------------------
     6 | {39,35,5,94,17,92,60,32}        | {AAAAAAAAAAAAAAA35875,AAAAAAAAAAAAAAAA23657}
    77 | {97,15,32,17,55,59,18,37,50,39} | {AAAAAAAAAAAA67946,AAAAAA54032,AAAAAAAA81587,55847,AAAAAAAAAAAAAA28620,AAAAAAAAAAAAAAAAA43052,AAAAAA75463,AAAA49534,AAAAAAAA44066}
    89 | {40,32,17,6,30,88}              | {AA44673,AAAAAAAAAAA6119,AAAAAAAAAAAAAAAA23657,AAAAAAAAAAAAAAAAAA47955,AAAAAAAAAAAAAAAA33598,AAAAAAAAAAA33576,AA44673}
(3 rows)

SELECT * FROM array_index_op_test WHERE i && '{32,17}' ORDER BY seqno;
 seqno |                i                |                                                                 t                                                                  
-------+---------------------------------+------------------------------------------------------------------------------------------------------------------------------------
     6 | {39,35,5,94,17,92,60,32}        | {AAAAAAAAAAAAAAA35875,AAAAAAAAAAAAAAAA23657}
    12 | {17,99,18,52,91,72,0,43,96,23}  | {AAAAA33250,AAAAAAAAAAAAAAAAAAA85420,AAAAAAAAAAA33576}
    15 | {17,14,16,63,67}                | {AA6416,AAAAAAAAAA646,AAAAA95309}
    19 | {52,82,17,74,23,46,69,51,75}    | {AAAAAAAAAAAAA73084,AAAAA75968,AAAAAAAAAAAAAAAA14047,AAAAAAA80240,AAAAAAAAAAAAAAAAAAA1205,A68938}
    53 | {38,17}                         | {AAAAAAAAAAA21658}
    65 | {61,5,76,59,17}                 | {AAAAAA99807,AAAAA64741,AAAAAAAAAAA53908,AA21643,AAAAAAAAA10012}
    74 | {32}                            | {AAAAAAAAAAAAAAAA1729,AAAAAAAAAAAAA22860,AAAAAA99807,AAAAA17383,AAAAAAAAAAAAAAA67062,AAAAAAAAAAA15165,AAAAAAAAAAA50956}
    77 | {97,15,32,17,55,59,18,37,50,39} | {AAAAAAAAAAAA67946,AAAAAA54032,AAAAAAAA81587,55847,AAAAAAAAAAAAAA28620,AAAAAAAAAAAAAAAAA43052,AAAAAA75463,AAAA49534,AAAAAAAA44066}
    89 | {40,32,17,6,30,88}              | {AA44673,AAAAAAAAAAA6119,AAAAAAAAAAAAAAAA23657,AAAAAAAAAAAAAAAAAA47955,AAAAAAAAAAAAAAAA33598,AAAAAAAAAAA33576,AA44673}
    98 | {38,34,32,89}                   | {AAAAAAAAAAAAAAAAAA71621,AAAA8857,AAAAAAAAAAAAAAAAAAA65037,AAAAAAAAAAAAAAAA31334,AAAAAAAAAA48845}
   100 | {85,32,57,39,49,84,32,3,30}     | {AAAAAAA80240,AAAAAAAAAAAAAAAA1729,AAAAA60038,AAAAAAAAAAA92631,AAAAAAAA9523}
(11 rows)

SELECT * FROM array_index_op_test WHERE i <@ '{38,34,32,89}' ORDER BY seqno;
 seqno |       i       |                                                             t                                                              
-------+---------------+----------------------------------------------------------------------------------------------------------------------------
    40 | {34}          | {AAAAAAAAAAAAAA10611,AAAAAAAAAAAAAAAAAAA1205,AAAAAAAAAAA50956,AAAAAAAAAAAAAAAA31334,AAAAA70466,AAAAAAAA81587,AAAAAAA74623}
    74 | {32}          | {AAAAAAAAAAAAAAAA1729,AAAAAAAAAAAAA22860,AAAAAA99807,AAAAA17383,AAAAAAAAAAAAAAA67062,AAAAAAAAAAA15165,AAAAAAAAAAA50956}
    98 | {38,34,32,89} | {AAAAAAAAAAAAAAAAAA71621,AAAA8857,AAAAAAAAAAAAAAAAAAA65037,AAAAAAAAAAAAAAAA31334,AAAAAAAAAA48845}
(3 rows)

SELECT * FROM array_index_op_test WHERE i = '{47,77}' ORDER BY seqno;
 seqno |    i    |                                                        t                                                        
-------+---------+-----------------------------------------------------------------------------------------------------------------
    95 | {47,77} | {AAAAAAAAAAAAAAAAA764,AAAAAAAAAAA74076,AAAAAAAAAA18107,AAAAA40681,AAAAAAAAAAAAAAA35875,AAAAA60038,AAAAAAA56483}
(1 row)

CREATE INDEX textarrayidx ON array_index_op_test USING gin (t);
SELECT * FROM array_index_op_test WHERE t @> '{AAAAAAAA72908}' ORDER BY seqno;
 seqno |           i           |                                                                     t                                                                      
-------+-----------------------+--------------------------------------------------------------------------------------------------------------------------------------------
    22 | {11,6,56,62,53,30}    | {AAAAAAAA72908}
    45 | {99,45}               | {AAAAAAAA72908,AAAAAAAAAAAAAAAAAAA17075,AA88409,AAAAAAAAAAAAAAAAAA36842,AAAAAAA48038,AAAAAAAAAAAAAA10611}
    72 | {22,1,16,78,20,91,83} | {47735,AAAAAAA56483,AAAAAAAAAAAAA93788,AA42406,AAAAAAAAAAAAA73084,AAAAAAAA72908,AAAAAAAAAAAAAAAAAA61286,AAAAA66674,AAAAAAAAAAAAAAAAA50407}
    79 | {45}                  | {AAAAAAAAAA646,AAAAAAAAAAAAAAAAAAA70415,AAAAAA43678,AAAAAAAA72908}
(4 rows)

SELECT * FROM array_index_op_test WHERE t && '{AAAAAAAA72908}' ORDER BY seqno;
 seqno |           i           |                                                                     t                                                                      
-------+-----------------------+--------------------------------------------------------------------------------------------------------------------------------------------
    22 | {11,6,56,62,53,30}    | {AAAAAAAA72908}
    45 | {99,45}               | {AAAAAAAA72908,AAAAAAAAAAAAAAAAAAA17075,AA88409,AAAAAAAAAAAAAAAAAA36842,AAAAAAA48038,AAAAAAAAAAAAAA10611}
    72 | {22,1,16,78,20,91,83} | {47735,AAAAAAA56483,AAAAAAAAAAAAA93788,AA42406,AAAAAAAAAAAAA73084,AAAAAAAA72908,AAAAAAAAAAAAAAAAAA61286,AAAAA66674,AAAAAAAAAAAAAAAAA50407}
    79 | {45}                  | {AAAAAAAAAA646,AAAAAAAAAAAAAAAAAAA70415,AAAAAA43678,AAAAAAAA72908}
(4 rows)

SELECT * FROM array_index_op_test WHERE t @> '{AAAAAAAAAA646}' ORDER BY seqno;
 seqno |        i         |                                 t                                  
-------+------------------+--------------------------------------------------------------------
    15 | {17,14,16,63,67} | {AA6416,AAAAAAAAAA646,AAAAA95309}
    79 | {45}             | {AAAAAAAAAA646,AAAAAAAAAAAAAAAAAAA70415,AAAAAA43678,AAAAAAAA72908}
    96 | {23,97,43}       | {AAAAAAAAAA646,A87088}
(3 rows)

SELECT * FROM array_index_op_test WHERE t && '{AAAAAAAAAA646}' ORDER BY seqno;
 seqno |        i         |                                 t                                  
-------+------------------+--------------------------------------------------------------------
    15 | {17,14,16,63,67} | {AA6416,AAAAAAAAAA646,AAAAA95309}
    79 | {45}             | {AAAAAAAAAA646,AAAAAAAAAAAAAAAAAAA70415,AAAAAA43678,AAAAAAAA72908}
    96 | {23,97,43}       | {AAAAAAAAAA646,A87088}
(3 rows)

SELECT * FROM array_index_op_test WHERE t @> '{AAAAAAAA72908,AAAAAAAAAA646}' ORDER BY seqno;
 seqno |  i   |                                 t                                  
-------+------+--------------------------------------------------------------------
    79 | {45} | {AAAAAAAAAA646,AAAAAAAAAAAAAAAAAAA70415,AAAAAA43678,AAAAAAAA72908}
(1 row)

SELECT * FROM array_index_op_test WHERE t && '{AAAAAAAA72908,AAAAAAAAAA646}' ORDER BY seqno;
 seqno |           i           |                                                                     t                                                                      
-------+-----------------------+--------------------------------------------------------------------------------------------------------------------------------------------
    15 | {17,14,16,63,67}      | {AA6416,AAAAAAAAAA646,AAAAA95309}
    22 | {11,6,56,62,53,30}    | {AAAAAAAA72908}
    45 | {99,45}               | {AAAAAAAA72908,AAAAAAAAAAAAAAAAAAA17075,AA88409,AAAAAAAAAAAAAAAAAA36842,AAAAAAA48038,AAAAAAAAAAAAAA10611}
    72 | {22,1,16,78,20,91,83} | {47735,AAAAAAA56483,AAAAAAAAAAAAA93788,AA42406,AAAAAAAAAAAAA73084,AAAAAAAA72908,AAAAAAAAAAAAAAAAAA61286,AAAAA66674,AAAAAAAAAAAAAAAAA50407}
    79 | {45}                  | {AAAAAAAAAA646,AAAAAAAAAAAAAAAAAAA70415,AAAAAA43678,AAAAAAAA72908}
    96 | {23,97,43}            | {AAAAAAAAAA646,A87088}
(6 rows)

SELECT * FROM array_index_op_test WHERE t <@ '{AAAAAAAA72908,AAAAAAAAAAAAAAAAAAA17075,AA88409,AAAAAAAAAAAAAAAAAA36842,AAAAAAA48038,AAAAAAAAAAAAAA10611}' ORDER BY seqno;
 seqno |         i          |                                                     t                                                     
-------+--------------------+-----------------------------------------------------------------------------------------------------------
    22 | {11,6,56,62,53,30} | {AAAAAAAA72908}
    45 | {99,45}            | {AAAAAAAA72908,AAAAAAAAAAAAAAAAAAA17075,AA88409,AAAAAAAAAAAAAAAAAA36842,AAAAAAA48038,AAAAAAAAAAAAAA10611}
(2 rows)

SELECT * FROM array_index_op_test WHERE t = '{AAAAAAAAAA646,A87088}' ORDER BY seqno;
 seqno |     i      |           t            
-------+------------+------------------------
    96 | {23,97,43} | {AAAAAAAAAA646,A87088}
(1 row)

-- Repeat some of the above tests but exercising bitmapscans instead
SET enable_indexscan = OFF;
SET enable_bitmapscan = ON;
SELECT * FROM array_index_op_test WHERE i @> '{32}' ORDER BY seqno;
 seqno |                i                |                                                                 t                                                                  
-------+---------------------------------+------------------------------------------------------------------------------------------------------------------------------------
     6 | {39,35,5,94,17,92,60,32}        | {AAAAAAAAAAAAAAA35875,AAAAAAAAAAAAAAAA23657}
    74 | {32}                            | {AAAAAAAAAAAAAAAA1729,AAAAAAAAAAAAA22860,AAAAAA99807,AAAAA17383,AAAAAAAAAAAAAAA67062,AAAAAAAAAAA15165,AAAAAAAAAAA50956}
    77 | {97,15,32,17,55,59,18,37,50,39} | {AAAAAAAAAAAA67946,AAAAAA54032,AAAAAAAA81587,55847,AAAAAAAAAAAAAA28620,AAAAAAAAAAAAAAAAA43052,AAAAAA75463,AAAA49534,AAAAAAAA44066}
    89 | {40,32,17,6,30,88}              | {AA44673,AAAAAAAAAAA6119,AAAAAAAAAAAAAAAA23657,AAAAAAAAAAAAAAAAAA47955,AAAAAAAAAAAAAAAA33598,AAAAAAAAAAA33576,AA44673}
    98 | {38,34,32,89}                   | {AAAAAAAAAAAAAAAAAA71621,AAAA8857,AAAAAAAAAAAAAAAAAAA65037,AAAAAAAAAAAAAAAA31334,AAAAAAAAAA48845}
   100 | {85,32,57,39,49,84,32,3,30}     | {AAAAAAA80240,AAAAAAAAAAAAAAAA1729,AAAAA60038,AAAAAAAAAAA92631,AAAAAAAA9523}
(6 rows)

SELECT * FROM array_index_op_test WHERE i && '{32}' ORDER BY seqno;
 seqno |                i                |                                                                 t                                                                  
-------+---------------------------------+------------------------------------------------------------------------------------------------------------------------------------
     6 | {39,35,5,94,17,92,60,32}        | {AAAAAAAAAAAAAAA35875,AAAAAAAAAAAAAAAA23657}
    74 | {32}                            | {AAAAAAAAAAAAAAAA1729,AAAAAAAAAAAAA22860,AAAAAA99807,AAAAA17383,AAAAAAAAAAAAAAA67062,AAAAAAAAAAA15165,AAAAAAAAAAA50956}
    77 | {97,15,32,17,55,59,18,37,50,39} | {AAAAAAAAAAAA67946,AAAAAA54032,AAAAAAAA81587,55847,AAAAAAAAAAAAAA28620,AAAAAAAAAAAAAAAAA43052,AAAAAA75463,AAAA49534,AAAAAAAA44066}
    89 | {40,32,17,6,30,88}              | {AA44673,AAAAAAAAAAA6119,AAAAAAAAAAAAAAAA23657,AAAAAAAAAAAAAAAAAA47955,AAAAAAAAAAAAAAAA33598,AAAAAAAAAAA33576,AA44673}
    98 | {38,34,32,89}                   | {AAAAAAAAAAAAAAAAAA71621,AAAA8857,AAAAAAAAAAAAAAAAAAA65037,AAAAAAAAAAAAAAAA31334,AAAAAAAAAA48845}
   100 | {85,32,57,39,49,84,32,3,30}     | {AAAAAAA80240,AAAAAAAAAAAAAAAA1729,AAAAA60038,AAAAAAAAAAA92631,AAAAAAAA9523}
(6 rows)

SELECT * FROM array_index_op_test WHERE i @> '{17}' ORDER BY seqno;
 seqno |                i                |                                                                 t                                                                  
-------+---------------------------------+------------------------------------------------------------------------------------------------------------------------------------
     6 | {39,35,5,94,17,92,60,32}        | {AAAAAAAAAAAAAAA35875,AAAAAAAAAAAAAAAA23657}
    12 | {17,99,18,52,91,72,0,43,96,23}  | {AAAAA33250,AAAAAAAAAAAAAAAAAAA85420,AAAAAAAAAAA33576}
    15 | {17,14,16,63,67}                | {AA6416,AAAAAAAAAA646,AAAAA95309}
    19 | {52,82,17,74,23,46,69,51,75}    | {AAAAAAAAAAAAA73084,AAAAA75968,AAAAAAAAAAAAAAAA14047,AAAAAAA80240,AAAAAAAAAAAAAAAAAAA1205,A68938}
    53 | {38,17}                         | {AAAAAAAAAAA21658}
    65 | {61,5,76,59,17}                 | {AAAAAA99807,AAAAA64741,AAAAAAAAAAA53908,AA21643,AAAAAAAAA10012}
    77 | {97,15,32,17,55,59,18,37,50,39} | {AAAAAAAAAAAA67946,AAAAAA54032,AAAAAAAA81587,55847,AAAAAAAAAAAAAA28620,AAAAAAAAAAAAAAAAA43052,AAAAAA75463,AAAA49534,AAAAAAAA44066}
    89 | {40,32,17,6,30,88}              | {AA44673,AAAAAAAAAAA6119,AAAAAAAAAAAAAAAA23657,AAAAAAAAAAAAAAAAAA47955,AAAAAAAAAAAAAAAA33598,AAAAAAAAAAA33576,AA44673}
(8 rows)

SELECT * FROM array_index_op_test WHERE i && '{17}' ORDER BY seqno;
 seqno |                i                |                                                                 t                                                                  
-------+---------------------------------+------------------------------------------------------------------------------------------------------------------------------------
     6 | {39,35,5,94,17,92,60,32}        | {AAAAAAAAAAAAAAA35875,AAAAAAAAAAAAAAAA23657}
    12 | {17,99,18,52,91,72,0,43,96,23}  | {AAAAA33250,AAAAAAAAAAAAAAAAAAA85420,AAAAAAAAAAA33576}
    15 | {17,14,16,63,67}                | {AA6416,AAAAAAAAAA646,AAAAA95309}
    19 | {52,82,17,74,23,46,69,51,75}    | {AAAAAAAAAAAAA73084,AAAAA75968,AAAAAAAAAAAAAAAA14047,AAAAAAA80240,AAAAAAAAAAAAAAAAAAA1205,A68938}
    53 | {38,17}                         | {AAAAAAAAAAA21658}
    65 | {61,5,76,59,17}                 | {AAAAAA99807,AAAAA64741,AAAAAAAAAAA53908,AA21643,AAAAAAAAA10012}
    77 | {97,15,32,17,55,59,18,37,50,39} | {AAAAAAAAAAAA67946,AAAAAA54032,AAAAAAAA81587,55847,AAAAAAAAAAAAAA28620,AAAAAAAAAAAAAAAAA43052,AAAAAA75463,AAAA49534,AAAAAAAA44066}
    89 | {40,32,17,6,30,88}              | {AA44673,AAAAAAAAAAA6119,AAAAAAAAAAAAAAAA23657,AAAAAAAAAAAAAAAAAA47955,AAAAAAAAAAAAAAAA33598,AAAAAAAAAAA33576,AA44673}
(8 rows)

SELECT * FROM array_index_op_test WHERE i @> '{32,17}' ORDER BY seqno;
 seqno |                i                |                                                                 t                                                                  
-------+---------------------------------+------------------------------------------------------------------------------------------------------------------------------------
     6 | {39,35,5,94,17,92,60,32}        | {AAAAAAAAAAAAAAA35875,AAAAAAAAAAAAAAAA23657}
    77 | {97,15,32,17,55,59,18,37,50,39} | {AAAAAAAAAAAA67946,AAAAAA54032,AAAAAAAA81587,55847,AAAAAAAAAAAAAA28620,AAAAAAAAAAAAAAAAA43052,AAAAAA75463,AAAA49534,AAAAAAAA44066}
    89 | {40,32,17,6,30,88}              | {AA44673,AAAAAAAAAAA6119,AAAAAAAAAAAAAAAA23657,AAAAAAAAAAAAAAAAAA47955,AAAAAAAAAAAAAAAA33598,AAAAAAAAAAA33576,AA44673}
(3 rows)

SELECT * FROM array_index_op_test WHERE i && '{32,17}' ORDER BY seqno;
 seqno |                i                |                                                                 t                                                                  
-------+---------------------------------+------------------------------------------------------------------------------------------------------------------------------------
     6 | {39,35,5,94,17,92,60,32}        | {AAAAAAAAAAAAAAA35875,AAAAAAAAAAAAAAAA23657}
    12 | {17,99,18,52,91,72,0,43,96,23}  | {AAAAA33250,AAAAAAAAAAAAAAAAAAA85420,AAAAAAAAAAA33576}
    15 | {17,14,16,63,67}                | {AA6416,AAAAAAAAAA646,AAAAA95309}
    19 | {52,82,17,74,23,46,69,51,75}    | {AAAAAAAAAAAAA73084,AAAAA75968,AAAAAAAAAAAAAAAA14047,AAAAAAA80240,AAAAAAAAAAAAAAAAAAA1205,A68938}
    53 | {38,17}                         | {AAAAAAAAAAA21658}
    65 | {61,5,76,59,17}                 | {AAAAAA99807,AAAAA64741,AAAAAAAAAAA53908,AA21643,AAAAAAAAA10012}
    74 | {32}                            | {AAAAAAAAAAAAAAAA1729,AAAAAAAAAAAAA22860,AAAAAA99807,AAAAA17383,AAAAAAAAAAAAAAA67062,AAAAAAAAAAA15165,AAAAAAAAAAA50956}
    77 | {97,15,32,17,55,59,18,37,50,39} | {AAAAAAAAAAAA67946,AAAAAA54032,AAAAAAAA81587,55847,AAAAAAAAAAAAAA28620,AAAAAAAAAAAAAAAAA43052,AAAAAA75463,AAAA49534,AAAAAAAA44066}
    89 | {40,32,17,6,30,88}              | {AA44673,AAAAAAAAAAA6119,AAAAAAAAAAAAAAAA23657,AAAAAAAAAAAAAAAAAA47955,AAAAAAAAAAAAAAAA33598,AAAAAAAAAAA33576,AA44673}
    98 | {38,34,32,89}                   | {AAAAAAAAAAAAAAAAAA71621,AAAA8857,AAAAAAAAAAAAAAAAAAA65037,AAAAAAAAAAAAAAAA31334,AAAAAAAAAA48845}
   100 | {85,32,57,39,49,84,32,3,30}     | {AAAAAAA80240,AAAAAAAAAAAAAAAA1729,AAAAA60038,AAAAAAAAAAA92631,AAAAAAAA9523}
(11 rows)

SELECT * FROM array_index_op_test WHERE i <@ '{38,34,32,89}' ORDER BY seqno;
 seqno |       i       |                                                             t                                                              
-------+---------------+----------------------------------------------------------------------------------------------------------------------------
    40 | {34}          | {AAAAAAAAAAAAAA10611,AAAAAAAAAAAAAAAAAAA1205,AAAAAAAAAAA50956,AAAAAAAAAAAAAAAA31334,AAAAA70466,AAAAAAAA81587,AAAAAAA74623}
    74 | {32}          | {AAAAAAAAAAAAAAAA1729,AAAAAAAAAAAAA22860,AAAAAA99807,AAAAA17383,AAAAAAAAAAAAAAA67062,AAAAAAAAAAA15165,AAAAAAAAAAA50956}
    98 | {38,34,32,89} | {AAAAAAAAAAAAAAAAAA71621,AAAA8857,AAAAAAAAAAAAAAAAAAA65037,AAAAAAAAAAAAAAAA31334,AAAAAAAAAA48845}
(3 rows)

SELECT * FROM array_index_op_test WHERE i = '{47,77}' ORDER BY seqno;
 seqno |    i    |                                                        t                                                        
-------+---------+-----------------------------------------------------------------------------------------------------------------
    95 | {47,77} | {AAAAAAAAAAAAAAAAA764,AAAAAAAAAAA74076,AAAAAAAAAA18107,AAAAA40681,AAAAAAAAAAAAAAA35875,AAAAA60038,AAAAAAA56483}
(1 row)

-- And try it with a multicolumn GIN index
DROP INDEX intarrayidx, textarrayidx;
CREATE INDEX botharrayidx ON array_index_op_test USING gin (i, t);
SET enable_seqscan = OFF;
SET enable_indexscan = ON;
SET enable_bitmapscan = OFF;
SELECT * FROM array_index_op_test WHERE i @> '{32}' ORDER BY seqno;
 seqno |                i                |                                                                 t                                                                  
-------+---------------------------------+------------------------------------------------------------------------------------------------------------------------------------
     6 | {39,35,5,94,17,92,60,32}        | {AAAAAAAAAAAAAAA35875,AAAAAAAAAAAAAAAA23657}
    74 | {32}                            | {AAAAAAAAAAAAAAAA1729,AAAAAAAAAAAAA22860,AAAAAA99807,AAAAA17383,AAAAAAAAAAAAAAA67062,AAAAAAAAAAA15165,AAAAAAAAAAA50956}
    77 | {97,15,32,17,55,59,18,37,50,39} | {AAAAAAAAAAAA67946,AAAAAA54032,AAAAAAAA81587,55847,AAAAAAAAAAAAAA28620,AAAAAAAAAAAAAAAAA43052,AAAAAA75463,AAAA49534,AAAAAAAA44066}
    89 | {40,32,17,6,30,88}              | {AA44673,AAAAAAAAAAA6119,AAAAAAAAAAAAAAAA23657,AAAAAAAAAAAAAAAAAA47955,AAAAAAAAAAAAAAAA33598,AAAAAAAAAAA33576,AA44673}
    98 | {38,34,32,89}                   | {AAAAAAAAAAAAAAAAAA71621,AAAA8857,AAAAAAAAAAAAAAAAAAA65037,AAAAAAAAAAAAAAAA31334,AAAAAAAAAA48845}
   100 | {85,32,57,39,49,84,32,3,30}     | {AAAAAAA80240,AAAAAAAAAAAAAAAA1729,AAAAA60038,AAAAAAAAAAA92631,AAAAAAAA9523}
(6 rows)

SELECT * FROM array_index_op_test WHERE i && '{32}' ORDER BY seqno;
 seqno |                i                |                                                                 t                                                                  
-------+---------------------------------+------------------------------------------------------------------------------------------------------------------------------------
     6 | {39,35,5,94,17,92,60,32}        | {AAAAAAAAAAAAAAA35875,AAAAAAAAAAAAAAAA23657}
    74 | {32}                            | {AAAAAAAAAAAAAAAA1729,AAAAAAAAAAAAA22860,AAAAAA99807,AAAAA17383,AAAAAAAAAAAAAAA67062,AAAAAAAAAAA15165,AAAAAAAAAAA50956}
    77 | {97,15,32,17,55,59,18,37,50,39} | {AAAAAAAAAAAA67946,AAAAAA54032,AAAAAAAA81587,55847,AAAAAAAAAAAAAA28620,AAAAAAAAAAAAAAAAA43052,AAAAAA75463,AAAA49534,AAAAAAAA44066}
    89 | {40,32,17,6,30,88}              | {AA44673,AAAAAAAAAAA6119,AAAAAAAAAAAAAAAA23657,AAAAAAAAAAAAAAAAAA47955,AAAAAAAAAAAAAAAA33598,AAAAAAAAAAA33576,AA44673}
    98 | {38,34,32,89}                   | {AAAAAAAAAAAAAAAAAA71621,AAAA8857,AAAAAAAAAAAAAAAAAAA65037,AAAAAAAAAAAAAAAA31334,AAAAAAAAAA48845}
   100 | {85,32,57,39,49,84,32,3,30}     | {AAAAAAA80240,AAAAAAAAAAAAAAAA1729,AAAAA60038,AAAAAAAAAAA92631,AAAAAAAA9523}
(6 rows)

SELECT * FROM array_index_op_test WHERE t @> '{AAAAAAA80240}' ORDER BY seqno;
 seqno |               i                |                                                                              t                                                                              
-------+--------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------
    19 | {52,82,17,74,23,46,69,51,75}   | {AAAAAAAAAAAAA73084,AAAAA75968,AAAAAAAAAAAAAAAA14047,AAAAAAA80240,AAAAAAAAAAAAAAAAAAA1205,A68938}
    30 | {26,81,47,91,34}               | {AAAAAAAAAAAAAAAAAAA70104,AAAAAAA80240}
    64 | {26,19,34,24,81,78}            | {A96617,AAAAAAAAAAAAAAAAAAA70104,A68938,AAAAAAAAAAA53908,AAAAAAAAAAAAAAA453,AA17009,AAAAAAA80240}
    82 | {34,60,4,79,78,16,86,89,42,50} | {AAAAA40681,AAAAAAAAAAAAAAAAAA12591,AAAAAAA80240,AAAAAAAAAAAAAAAA55798,AAAAAAAAAAAAAAAAAAA70104}
    88 | {41,90,77,24,6,24}             | {AAAA35194,AAAA35194,AAAAAAA80240,AAAAAAAAAAA46154,AAAAAA58494,AAAAAAAAAAAAAAAAAAA17075,AAAAAAAAAAAAAAAAAA59334,AAAAAAAAAAAAAAAAAAA91804,AA74433}
    97 | {54,2,86,65}                   | {47735,AAAAAAA99836,AAAAAAAAAAAAAAAAA6897,AAAAAAAAAAAAAAAA29150,AAAAAAA80240,AAAAAAAAAAAAAAAA98414,AAAAAAA56483,AAAAAAAAAAAAAAAA29150,AAAAAAA39692,AA21643}
   100 | {85,32,57,39,49,84,32,3,30}    | {AAAAAAA80240,AAAAAAAAAAAAAAAA1729,AAAAA60038,AAAAAAAAAAA92631,AAAAAAAA9523}
(7 rows)

SELECT * FROM array_index_op_test WHERE t && '{AAAAAAA80240}' ORDER BY seqno;
 seqno |               i                |                                                                              t                                                                              
-------+--------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------
    19 | {52,82,17,74,23,46,69,51,75}   | {AAAAAAAAAAAAA73084,AAAAA75968,AAAAAAAAAAAAAAAA14047,AAAAAAA80240,AAAAAAAAAAAAAAAAAAA1205,A68938}
    30 | {26,81,47,91,34}               | {AAAAAAAAAAAAAAAAAAA70104,AAAAAAA80240}
    64 | {26,19,34,24,81,78}            | {A96617,AAAAAAAAAAAAAAAAAAA70104,A68938,AAAAAAAAAAA53908,AAAAAAAAAAAAAAA453,AA17009,AAAAAAA80240}
    82 | {34,60,4,79,78,16,86,89,42,50} | {AAAAA40681,AAAAAAAAAAAAAAAAAA12591,AAAAAAA80240,AAAAAAAAAAAAAAAA55798,AAAAAAAAAAAAAAAAAAA70104}
    88 | {41,90,77,24,6,24}             | {AAAA35194,AAAA35194,AAAAAAA80240,AAAAAAAAAAA46154,AAAAAA58494,AAAAAAAAAAAAAAAAAAA17075,AAAAAAAAAAAAAAAAAA59334,AAAAAAAAAAAAAAAAAAA91804,AA74433}
    97 | {54,2,86,65}                   | {47735,AAAAAAA99836,AAAAAAAAAAAAAAAAA6897,AAAAAAAAAAAAAAAA29150,AAAAAAA80240,AAAAAAAAAAAAAAAA98414,AAAAAAA56483,AAAAAAAAAAAAAAAA29150,AAAAAAA39692,AA21643}
   100 | {85,32,57,39,49,84,32,3,30}    | {AAAAAAA80240,AAAAAAAAAAAAAAAA1729,AAAAA60038,AAAAAAAAAAA92631,AAAAAAAA9523}
(7 rows)

SELECT * FROM array_index_op_test WHERE i @> '{32}' AND t && '{AAAAAAA80240}' ORDER BY seqno;
 seqno |              i              |                                      t                                       
-------+-----------------------------+------------------------------------------------------------------------------
   100 | {85,32,57,39,49,84,32,3,30} | {AAAAAAA80240,AAAAAAAAAAAAAAAA1729,AAAAA60038,AAAAAAAAAAA92631,AAAAAAAA9523}
(1 row)

SELECT * FROM array_index_op_test WHERE i && '{32}' AND t @> '{AAAAAAA80240}' ORDER BY seqno;
 seqno |              i              |                                      t                                       
-------+-----------------------------+------------------------------------------------------------------------------
   100 | {85,32,57,39,49,84,32,3,30} | {AAAAAAA80240,AAAAAAAAAAAAAAAA1729,AAAAA60038,AAAAAAAAAAA92631,AAAAAAAA9523}
(1 row)

SET enable_indexscan = OFF;
SET enable_bitmapscan = ON;
SELECT * FROM array_index_op_test WHERE i @> '{32}' ORDER BY seqno;
 seqno |                i                |                                                                 t                                                                  
-------+---------------------------------+------------------------------------------------------------------------------------------------------------------------------------
     6 | {39,35,5,94,17,92,60,32}        | {AAAAAAAAAAAAAAA35875,AAAAAAAAAAAAAAAA23657}
    74 | {32}                            | {AAAAAAAAAAAAAAAA1729,AAAAAAAAAAAAA22860,AAAAAA99807,AAAAA17383,AAAAAAAAAAAAAAA67062,AAAAAAAAAAA15165,AAAAAAAAAAA50956}
    77 | {97,15,32,17,55,59,18,37,50,39} | {AAAAAAAAAAAA67946,AAAAAA54032,AAAAAAAA81587,55847,AAAAAAAAAAAAAA28620,AAAAAAAAAAAAAAAAA43052,AAAAAA75463,AAAA49534,AAAAAAAA44066}
    89 | {40,32,17,6,30,88}              | {AA44673,AAAAAAAAAAA6119,AAAAAAAAAAAAAAAA23657,AAAAAAAAAAAAAAAAAA47955,AAAAAAAAAAAAAAAA33598,AAAAAAAAAAA33576,AA44673}
    98 | {38,34,32,89}                   | {AAAAAAAAAAAAAAAAAA71621,AAAA8857,AAAAAAAAAAAAAAAAAAA65037,AAAAAAAAAAAAAAAA31334,AAAAAAAAAA48845}
   100 | {85,32,57,39,49,84,32,3,30}     | {AAAAAAA80240,AAAAAAAAAAAAAAAA1729,AAAAA60038,AAAAAAAAAAA92631,AAAAAAAA9523}
(6 rows)

SELECT * FROM array_index_op_test WHERE i && '{32}' ORDER BY seqno;
 seqno |                i                |                                                                 t                                                                  
-------+---------------------------------+------------------------------------------------------------------------------------------------------------------------------------
     6 | {39,35,5,94,17,92,60,32}        | {AAAAAAAAAAAAAAA35875,AAAAAAAAAAAAAAAA23657}
    74 | {32}                            | {AAAAAAAAAAAAAAAA1729,AAAAAAAAAAAAA22860,AAAAAA99807,AAAAA17383,AAAAAAAAAAAAAAA67062,AAAAAAAAAAA15165,AAAAAAAAAAA50956}
    77 | {97,15,32,17,55,59,18,37,50,39} | {AAAAAAAAAAAA67946,AAAAAA54032,AAAAAAAA81587,55847,AAAAAAAAAAAAAA28620,AAAAAAAAAAAAAAAAA43052,AAAAAA75463,AAAA49534,AAAAAAAA44066}
    89 | {40,32,17,6,30,88}              | {AA44673,AAAAAAAAAAA6119,AAAAAAAAAAAAAAAA23657,AAAAAAAAAAAAAAAAAA47955,AAAAAAAAAAAAAAAA33598,AAAAAAAAAAA33576,AA44673}
    98 | {38,34,32,89}                   | {AAAAAAAAAAAAAAAAAA71621,AAAA8857,AAAAAAAAAAAAAAAAAAA65037,AAAAAAAAAAAAAAAA31334,AAAAAAAAAA48845}
   100 | {85,32,57,39,49,84,32,3,30}     | {AAAAAAA80240,AAAAAAAAAAAAAAAA1729,AAAAA60038,AAAAAAAAAAA92631,AAAAAAAA9523}
(6 rows)

SELECT * FROM array_index_op_test WHERE t @> '{AAAAAAA80240}' ORDER BY seqno;
 seqno |               i                |                                                                              t                                                                              
-------+--------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------
    19 | {52,82,17,74,23,46,69,51,75}   | {AAAAAAAAAAAAA73084,AAAAA75968,AAAAAAAAAAAAAAAA14047,AAAAAAA80240,AAAAAAAAAAAAAAAAAAA1205,A68938}
    30 | {26,81,47,91,34}               | {AAAAAAAAAAAAAAAAAAA70104,AAAAAAA80240}
    64 | {26,19,34,24,81,78}            | {A96617,AAAAAAAAAAAAAAAAAAA70104,A68938,AAAAAAAAAAA53908,AAAAAAAAAAAAAAA453,AA17009,AAAAAAA80240}
    82 | {34,60,4,79,78,16,86,89,42,50} | {AAAAA40681,AAAAAAAAAAAAAAAAAA12591,AAAAAAA80240,AAAAAAAAAAAAAAAA55798,AAAAAAAAAAAAAAAAAAA70104}
    88 | {41,90,77,24,6,24}             | {AAAA35194,AAAA35194,AAAAAAA80240,AAAAAAAAAAA46154,AAAAAA58494,AAAAAAAAAAAAAAAAAAA17075,AAAAAAAAAAAAAAAAAA59334,AAAAAAAAAAAAAAAAAAA91804,AA74433}
    97 | {54,2,86,65}                   | {47735,AAAAAAA99836,AAAAAAAAAAAAAAAAA6897,AAAAAAAAAAAAAAAA29150,AAAAAAA80240,AAAAAAAAAAAAAAAA98414,AAAAAAA56483,AAAAAAAAAAAAAAAA29150,AAAAAAA39692,AA21643}
   100 | {85,32,57,39,49,84,32,3,30}    | {AAAAAAA80240,AAAAAAAAAAAAAAAA1729,AAAAA60038,AAAAAAAAAAA92631,AAAAAAAA9523}
(7 rows)

SELECT * FROM array_index_op_test WHERE t && '{AAAAAAA80240}' ORDER BY seqno;
 seqno |               i                |                                                                              t                                                                              
-------+--------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------
    19 | {52,82,17,74,23,46,69,51,75}   | {AAAAAAAAAAAAA73084,AAAAA75968,AAAAAAAAAAAAAAAA14047,AAAAAAA80240,AAAAAAAAAAAAAAAAAAA1205,A68938}
    30 | {26,81,47,91,34}               | {AAAAAAAAAAAAAAAAAAA70104,AAAAAAA80240}
    64 | {26,19,34,24,81,78}            | {A96617,AAAAAAAAAAAAAAAAAAA70104,A68938,AAAAAAAAAAA53908,AAAAAAAAAAAAAAA453,AA17009,AAAAAAA80240}
    82 | {34,60,4,79,78,16,86,89,42,50} | {AAAAA40681,AAAAAAAAAAAAAAAAAA12591,AAAAAAA80240,AAAAAAAAAAAAAAAA55798,AAAAAAAAAAAAAAAAAAA70104}
    88 | {41,90,77,24,6,24}             | {AAAA35194,AAAA35194,AAAAAAA80240,AAAAAAAAAAA46154,AAAAAA58494,AAAAAAAAAAAAAAAAAAA17075,AAAAAAAAAAAAAAAAAA59334,AAAAAAAAAAAAAAAAAAA91804,AA74433}
    97 | {54,2,86,65}                   | {47735,AAAAAAA99836,AAAAAAAAAAAAAAAAA6897,AAAAAAAAAAAAAAAA29150,AAAAAAA80240,AAAAAAAAAAAAAAAA98414,AAAAAAA56483,AAAAAAAAAAAAAAAA29150,AAAAAAA39692,AA21643}
   100 | {85,32,57,39,49,84,32,3,30}    | {AAAAAAA80240,AAAAAAAAAAAAAAAA1729,AAAAA60038,AAAAAAAAAAA92631,AAAAAAAA9523}
(7 rows)

SELECT * FROM array_index_op_test WHERE i @> '{32}' AND t && '{AAAAAAA80240}' ORDER BY seqno;
 seqno |              i              |                                      t                                       
-------+-----------------------------+------------------------------------------------------------------------------
   100 | {85,32,57,39,49,84,32,3,30} | {AAAAAAA80240,AAAAAAAAAAAAAAAA1729,AAAAA60038,AAAAAAAAAAA92631,AAAAAAAA9523}
(1 row)

SELECT * FROM array_index_op_test WHERE i && '{32}' AND t @> '{AAAAAAA80240}' ORDER BY seqno;
 seqno |              i              |                                      t                                       
-------+-----------------------------+------------------------------------------------------------------------------
   100 | {85,32,57,39,49,84,32,3,30} | {AAAAAAA80240,AAAAAAAAAAAAAAAA1729,AAAAA60038,AAAAAAAAAAA92631,AAAAAAAA9523}
(1 row)

RESET enable_seqscan;
RESET enable_indexscan;
RESET enable_bitmapscan;
--
-- HASH
--
CREATE INDEX hash_i4_index ON hash_i4_heap USING hash (random int4_ops);
CREATE INDEX hash_name_index ON hash_name_heap USING hash (random name_ops);
CREATE INDEX hash_txt_index ON hash_txt_heap USING hash (random text_ops);
CREATE INDEX hash_f8_index ON hash_f8_heap USING hash (random float8_ops);
-- CREATE INDEX hash_ovfl_index ON hash_ovfl_heap USING hash (x int4_ops);
--
-- Test functional index
--
CREATE TABLE func_index_heap (f1 text, f2 text);
CREATE UNIQUE INDEX func_index_index on func_index_heap (textcat(f1,f2));
INSERT INTO func_index_heap VALUES('ABC','DEF');
INSERT INTO func_index_heap VALUES('AB','CDEFG');
INSERT INTO func_index_heap VALUES('QWE','RTY');
-- this should fail because of unique index:
INSERT INTO func_index_heap VALUES('ABCD', 'EF');
ERROR:  duplicate key value violates unique constraint "func_index_index"
DETAIL:  Key (textcat(f1, f2))=(ABCDEF) already exists.
-- but this shouldn't:
INSERT INTO func_index_heap VALUES('QWERTY');
--
-- Same test, expressional index
--
DROP TABLE func_index_heap;
CREATE TABLE func_index_heap (f1 text, f2 text);
CREATE UNIQUE INDEX func_index_index on func_index_heap ((f1 || f2) text_ops);
INSERT INTO func_index_heap VALUES('ABC','DEF');
INSERT INTO func_index_heap VALUES('AB','CDEFG');
INSERT INTO func_index_heap VALUES('QWE','RTY');
-- this should fail because of unique index:
INSERT INTO func_index_heap VALUES('ABCD', 'EF');
ERROR:  duplicate key value violates unique constraint "func_index_index"
DETAIL:  Key ((f1 || f2))=(ABCDEF) already exists.
-- but this shouldn't:
INSERT INTO func_index_heap VALUES('QWERTY');
--
-- Also try building functional, expressional, and partial indexes on
-- tables that already contain data.
--
create unique index hash_f8_index_1 on hash_f8_heap(abs(random));
create unique index hash_f8_index_2 on hash_f8_heap((seqno + 1), random);
create unique index hash_f8_index_3 on hash_f8_heap(random) where seqno > 1000;
--
-- Try some concurrent index builds
--
-- Unfortunately this only tests about half the code paths because there are
-- no concurrent updates happening to the table at the same time.
CREATE TABLE concur_heap (f1 text, f2 text);
-- empty table
CREATE INDEX CONCURRENTLY concur_index1 ON concur_heap(f2,f1);
INSERT INTO concur_heap VALUES  ('a','b');
INSERT INTO concur_heap VALUES  ('b','b');
-- unique index
CREATE UNIQUE INDEX CONCURRENTLY concur_index2 ON concur_heap(f1);
-- check if constraint is set up properly to be enforced
INSERT INTO concur_heap VALUES ('b','x');
ERROR:  duplicate key value violates unique constraint "concur_index2"
DETAIL:  Key (f1)=(b) already exists.
-- check if constraint is enforced properly at build time
CREATE UNIQUE INDEX CONCURRENTLY concur_index3 ON concur_heap(f2);
ERROR:  could not create unique index "concur_index3"
DETAIL:  Key (f2)=(b) is duplicated.
-- test that expression indexes and partial indexes work concurrently
CREATE INDEX CONCURRENTLY concur_index4 on concur_heap(f2) WHERE f1='a';
CREATE INDEX CONCURRENTLY concur_index5 on concur_heap(f2) WHERE f1='x';
-- here we also check that you can default the index name
CREATE INDEX CONCURRENTLY on concur_heap((f2||f1));
-- You can't do a concurrent index build in a transaction
BEGIN;
CREATE INDEX CONCURRENTLY concur_index7 ON concur_heap(f1);
ERROR:  CREATE INDEX CONCURRENTLY cannot run inside a transaction block
COMMIT;
-- But you can do a regular index build in a transaction
BEGIN;
CREATE INDEX std_index on concur_heap(f2);
COMMIT;
-- check to make sure that the failed indexes were cleaned up properly and the
-- successful indexes are created properly. Notably that they do NOT have the
-- "invalid" flag set.
\d concur_heap
Table "public.concur_heap"
 Column | Type | Modifiers 
--------+------+-----------
 f1     | text | 
 f2     | text | 
Indexes:
    "concur_index2" UNIQUE, btree (f1)
    "concur_index3" UNIQUE, btree (f2) INVALID
    "concur_heap_expr_idx" btree ((f2 || f1))
    "concur_index1" btree (f2, f1)
    "concur_index4" btree (f2) WHERE f1 = 'a'::text
    "concur_index5" btree (f2) WHERE f1 = 'x'::text
    "std_index" btree (f2)

DROP TABLE concur_heap;
--
-- Tests for IS NULL/IS NOT NULL with b-tree indexes
--
SELECT unique1, unique2 INTO onek_with_null FROM onek;
INSERT INTO onek_with_null (unique1,unique2) VALUES (NULL, -1), (NULL, NULL);
CREATE UNIQUE INDEX onek_nulltest ON onek_with_null (unique2,unique1);
SET enable_seqscan = OFF;
SET enable_indexscan = ON;
SET enable_bitmapscan = ON;
SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL;
 count 
-------
     2
(1 row)

SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL AND unique2 IS NULL;
 count 
-------
     1
(1 row)

SELECT count(*) FROM onek_with_null WHERE unique1 IS NOT NULL;
 count 
-------
  1000
(1 row)

SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL AND unique2 IS NOT NULL;
 count 
-------
     1
(1 row)

DROP INDEX onek_nulltest;
CREATE UNIQUE INDEX onek_nulltest ON onek_with_null (unique2 desc,unique1);
SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL;
 count 
-------
     2
(1 row)

SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL AND unique2 IS NULL;
 count 
-------
     1
(1 row)

SELECT count(*) FROM onek_with_null WHERE unique1 IS NOT NULL;
 count 
-------
  1000
(1 row)

SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL AND unique2 IS NOT NULL;
 count 
-------
     1
(1 row)

DROP INDEX onek_nulltest;
CREATE UNIQUE INDEX onek_nulltest ON onek_with_null (unique2 desc nulls last,unique1);
SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL;
 count 
-------
     2
(1 row)

SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL AND unique2 IS NULL;
 count 
-------
     1
(1 row)

SELECT count(*) FROM onek_with_null WHERE unique1 IS NOT NULL;
 count 
-------
  1000
(1 row)

SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL AND unique2 IS NOT NULL;
 count 
-------
     1
(1 row)

DROP INDEX onek_nulltest;
CREATE UNIQUE INDEX onek_nulltest ON onek_with_null (unique2  nulls first,unique1);
SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL;
 count 
-------
     2
(1 row)

SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL AND unique2 IS NULL;
 count 
-------
     1
(1 row)

SELECT count(*) FROM onek_with_null WHERE unique1 IS NOT NULL;
 count 
-------
  1000
(1 row)

SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL AND unique2 IS NOT NULL;
 count 
-------
     1
(1 row)

RESET enable_seqscan;
RESET enable_indexscan;
RESET enable_bitmapscan;
DROP TABLE onek_with_null;