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 onek_unique2 ON onek USING btree(unique2 int4_ops);
  10 CREATE INDEX onek_hundred ON onek USING btree(hundred int4_ops);
  11 CREATE INDEX onek_stringu1 ON onek USING btree(stringu1 name_ops);
  12 CREATE INDEX tenk1_unique1 ON tenk1 USING btree(unique1 int4_ops);
  13 CREATE INDEX tenk1_unique2 ON tenk1 USING btree(unique2 int4_ops);
  14 CREATE INDEX tenk1_hundred ON tenk1 USING btree(hundred int4_ops);
  15 CREATE INDEX tenk1_thous_tenthous ON tenk1 (thousand, tenthous);
  16 CREATE INDEX tenk2_unique1 ON tenk2 USING btree(unique1 int4_ops);
  17 CREATE INDEX tenk2_unique2 ON tenk2 USING btree(unique2 int4_ops);
  18 CREATE INDEX tenk2_hundred ON tenk2 USING btree(hundred int4_ops);
  19 CREATE INDEX rix ON road USING btree (name text_ops);
  20 CREATE INDEX iix ON ihighway USING btree (name text_ops);
  21 CREATE INDEX six ON shighway USING btree (name text_ops);
  22 -- test comments
  23 COMMENT ON INDEX six_wrong IS 'bad index';
  24 ERROR:  relation "six_wrong" does not exist
  25 COMMENT ON INDEX six IS 'good index';
  26 COMMENT ON INDEX six IS NULL;
  27 --
  28 -- BTREE ascending/descending cases
  29 --
  30 -- we load int4/text from pure descending data (each key is a new
  31 -- low key) and name/f8 from pure ascending data (each key is a new
  32 -- high key).  we had a bug where new low keys would sometimes be
  33 -- "lost".
  34 --
  35 CREATE INDEX bt_i4_index ON bt_i4_heap USING btree (seqno int4_ops);
  36 CREATE INDEX bt_name_index ON bt_name_heap USING btree (seqno name_ops);
  37 CREATE INDEX bt_txt_index ON bt_txt_heap USING btree (seqno text_ops);
  38 CREATE INDEX bt_f8_index ON bt_f8_heap USING btree (seqno float8_ops);
  39 --
  40 -- BTREE partial indices
  41 --
  42 CREATE INDEX onek2_u1_prtl ON onek2 USING btree(unique1 int4_ops)
  43         where unique1 < 20 or unique1 > 980;
  44 CREATE INDEX onek2_u2_prtl ON onek2 USING btree(unique2 int4_ops)
  45         where stringu1 < 'B';
  46 CREATE INDEX onek2_stu1_prtl ON onek2 USING btree(stringu1 name_ops)
  47         where onek2.stringu1 >= 'J' and onek2.stringu1 < 'K';
  48 --
  49 -- GiST (rtree-equivalent opclasses only)
  50 --
  51 CREATE INDEX grect2ind ON fast_emp4000 USING gist (home_base);
  52 CREATE INDEX gpolygonind ON polygon_tbl USING gist (f1);
  53 CREATE INDEX gcircleind ON circle_tbl USING gist (f1);
  54 CREATE TEMP TABLE gpolygon_tbl AS
  55     SELECT polygon(home_base) AS f1 FROM slow_emp4000;
  56 CREATE TEMP TABLE gcircle_tbl AS
  57     SELECT circle(home_base) AS f1 FROM slow_emp4000;
  58 CREATE INDEX ggpolygonind ON gpolygon_tbl USING gist (f1);
  59 CREATE INDEX ggcircleind ON gcircle_tbl USING gist (f1);
  60 SET enable_seqscan = ON;
  61 SET enable_indexscan = OFF;
  62 SET enable_bitmapscan = OFF;
  63 SELECT * FROM fast_emp4000
  64     WHERE home_base @ '(200,200),(2000,1000)'::box
  65     ORDER BY (home_base[0])[0];
  66        home_base
  67 -----------------------
  68  (337,455),(240,359)
  69  (1444,403),(1346,344)
  70 (2 rows)
  71
  72 SELECT count(*) FROM fast_emp4000 WHERE home_base && '(1000,1000,0,0)'::box;
  73  count
  74 -------
  75      2
  76 (1 row)
  77
  78 SELECT count(*) FROM fast_emp4000 WHERE home_base IS NULL;
  79  count
  80 -------
  81    278
  82 (1 row)
  83
  84 SELECT * FROM polygon_tbl WHERE f1 ~ '((1,1),(2,2),(2,1))'::polygon
  85     ORDER BY (poly_center(f1))[0];
  86          f1
  87 ---------------------
  88  ((2,0),(2,4),(0,0))
  89 (1 row)
  90
  91 SELECT * FROM circle_tbl WHERE f1 && circle(point(1,-2), 1)
  92     ORDER BY area(f1);
  93       f1
  94 ---------------
  95  <(1,2),3>
  96  <(1,3),5>
  97  <(1,2),100>
  98  <(100,1),115>
  99 (4 rows)
 100
 101 SELECT count(*) FROM gpolygon_tbl WHERE f1 && '(1000,1000,0,0)'::polygon;
 102  count
 103 -------
 104      2
 105 (1 row)
 106
 107 SELECT count(*) FROM gcircle_tbl WHERE f1 && '<(500,500),500>'::circle;
 108  count
 109 -------
 110      2
 111 (1 row)
 112
 113 SET enable_seqscan = OFF;
 114 SET enable_indexscan = ON;
 115 SET enable_bitmapscan = ON;
 116 -- there's no easy way to check that these commands actually use
 117 -- the index, unfortunately.  (EXPLAIN would work, but its output
 118 -- changes too often for me to want to put an EXPLAIN in the test...)
 119 SELECT * FROM fast_emp4000
 120     WHERE home_base @ '(200,200),(2000,1000)'::box
 121     ORDER BY (home_base[0])[0];
 122        home_base
 123 -----------------------
 124  (337,455),(240,359)
 125  (1444,403),(1346,344)
 126 (2 rows)
 127
 128 SELECT count(*) FROM fast_emp4000 WHERE home_base && '(1000,1000,0,0)'::box;
 129  count
 130 -------
 131      2
 132 (1 row)
 133
 134 SELECT count(*) FROM fast_emp4000 WHERE home_base IS NULL;
 135  count
 136 -------
 137    278
 138 (1 row)
 139
 140 SELECT * FROM polygon_tbl WHERE f1 ~ '((1,1),(2,2),(2,1))'::polygon
 141     ORDER BY (poly_center(f1))[0];
 142          f1
 143 ---------------------
 144  ((2,0),(2,4),(0,0))
 145 (1 row)
 146
 147 SELECT * FROM circle_tbl WHERE f1 && circle(point(1,-2), 1)
 148     ORDER BY area(f1);
 149       f1
 150 ---------------
 151  <(1,2),3>
 152  <(1,3),5>
 153  <(1,2),100>
 154  <(100,1),115>
 155 (4 rows)
 156
 157 SELECT count(*) FROM gpolygon_tbl WHERE f1 && '(1000,1000,0,0)'::polygon;
 158  count
 159 -------
 160      2
 161 (1 row)
 162
 163 SELECT count(*) FROM gcircle_tbl WHERE f1 && '<(500,500),500>'::circle;
 164  count
 165 -------
 166      2
 167 (1 row)
 168
 169 RESET enable_seqscan;
 170 RESET enable_indexscan;
 171 RESET enable_bitmapscan;
 172 --
 173 -- GIN over int[] and text[]
 174 --
 175 SET enable_seqscan = OFF;
 176 SET enable_indexscan = ON;
 177 SET enable_bitmapscan = OFF;
 178 CREATE INDEX intarrayidx ON array_index_op_test USING gin (i);
 179 SELECT * FROM array_index_op_test WHERE i @> '{32}' ORDER BY seqno;
 180  seqno |                i                |                                                                 t
 181 -------+---------------------------------+------------------------------------------------------------------------------------------------------------------------------------
 182      6 | {39,35,5,94,17,92,60,32}        | {AAAAAAAAAAAAAAA35875,AAAAAAAAAAAAAAAA23657}
 183     74 | {32}                            | {AAAAAAAAAAAAAAAA1729,AAAAAAAAAAAAA22860,AAAAAA99807,AAAAA17383,AAAAAAAAAAAAAAA67062,AAAAAAAAAAA15165,AAAAAAAAAAA50956}
 184     77 | {97,15,32,17,55,59,18,37,50,39} | {AAAAAAAAAAAA67946,AAAAAA54032,AAAAAAAA81587,55847,AAAAAAAAAAAAAA28620,AAAAAAAAAAAAAAAAA43052,AAAAAA75463,AAAA49534,AAAAAAAA44066}
 185     89 | {40,32,17,6,30,88}              | {AA44673,AAAAAAAAAAA6119,AAAAAAAAAAAAAAAA23657,AAAAAAAAAAAAAAAAAA47955,AAAAAAAAAAAAAAAA33598,AAAAAAAAAAA33576,AA44673}
 186     98 | {38,34,32,89}                   | {AAAAAAAAAAAAAAAAAA71621,AAAA8857,AAAAAAAAAAAAAAAAAAA65037,AAAAAAAAAAAAAAAA31334,AAAAAAAAAA48845}
 187    100 | {85,32,57,39,49,84,32,3,30}     | {AAAAAAA80240,AAAAAAAAAAAAAAAA1729,AAAAA60038,AAAAAAAAAAA92631,AAAAAAAA9523}
 188 (6 rows)
 189
 190 SELECT * FROM array_index_op_test WHERE i && '{32}' ORDER BY seqno;
 191  seqno |                i                |                                                                 t
 192 -------+---------------------------------+------------------------------------------------------------------------------------------------------------------------------------
 193      6 | {39,35,5,94,17,92,60,32}        | {AAAAAAAAAAAAAAA35875,AAAAAAAAAAAAAAAA23657}
 194     74 | {32}                            | {AAAAAAAAAAAAAAAA1729,AAAAAAAAAAAAA22860,AAAAAA99807,AAAAA17383,AAAAAAAAAAAAAAA67062,AAAAAAAAAAA15165,AAAAAAAAAAA50956}
 195     77 | {97,15,32,17,55,59,18,37,50,39} | {AAAAAAAAAAAA67946,AAAAAA54032,AAAAAAAA81587,55847,AAAAAAAAAAAAAA28620,AAAAAAAAAAAAAAAAA43052,AAAAAA75463,AAAA49534,AAAAAAAA44066}
 196     89 | {40,32,17,6,30,88}              | {AA44673,AAAAAAAAAAA6119,AAAAAAAAAAAAAAAA23657,AAAAAAAAAAAAAAAAAA47955,AAAAAAAAAAAAAAAA33598,AAAAAAAAAAA33576,AA44673}
 197     98 | {38,34,32,89}                   | {AAAAAAAAAAAAAAAAAA71621,AAAA8857,AAAAAAAAAAAAAAAAAAA65037,AAAAAAAAAAAAAAAA31334,AAAAAAAAAA48845}
 198    100 | {85,32,57,39,49,84,32,3,30}     | {AAAAAAA80240,AAAAAAAAAAAAAAAA1729,AAAAA60038,AAAAAAAAAAA92631,AAAAAAAA9523}
 199 (6 rows)
 200
 201 SELECT * FROM array_index_op_test WHERE i @> '{17}' ORDER BY seqno;
 202  seqno |                i                |                                                                 t
 203 -------+---------------------------------+------------------------------------------------------------------------------------------------------------------------------------
 204      6 | {39,35,5,94,17,92,60,32}        | {AAAAAAAAAAAAAAA35875,AAAAAAAAAAAAAAAA23657}
 205     12 | {17,99,18,52,91,72,0,43,96,23}  | {AAAAA33250,AAAAAAAAAAAAAAAAAAA85420,AAAAAAAAAAA33576}
 206     15 | {17,14,16,63,67}                | {AA6416,AAAAAAAAAA646,AAAAA95309}
 207     19 | {52,82,17,74,23,46,69,51,75}    | {AAAAAAAAAAAAA73084,AAAAA75968,AAAAAAAAAAAAAAAA14047,AAAAAAA80240,AAAAAAAAAAAAAAAAAAA1205,A68938}
 208     53 | {38,17}                         | {AAAAAAAAAAA21658}
 209     65 | {61,5,76,59,17}                 | {AAAAAA99807,AAAAA64741,AAAAAAAAAAA53908,AA21643,AAAAAAAAA10012}
 210     77 | {97,15,32,17,55,59,18,37,50,39} | {AAAAAAAAAAAA67946,AAAAAA54032,AAAAAAAA81587,55847,AAAAAAAAAAAAAA28620,AAAAAAAAAAAAAAAAA43052,AAAAAA75463,AAAA49534,AAAAAAAA44066}
 211     89 | {40,32,17,6,30,88}              | {AA44673,AAAAAAAAAAA6119,AAAAAAAAAAAAAAAA23657,AAAAAAAAAAAAAAAAAA47955,AAAAAAAAAAAAAAAA33598,AAAAAAAAAAA33576,AA44673}
 212 (8 rows)
 213
 214 SELECT * FROM array_index_op_test WHERE i && '{17}' ORDER BY seqno;
 215  seqno |                i                |                                                                 t
 216 -------+---------------------------------+------------------------------------------------------------------------------------------------------------------------------------
 217      6 | {39,35,5,94,17,92,60,32}        | {AAAAAAAAAAAAAAA35875,AAAAAAAAAAAAAAAA23657}
 218     12 | {17,99,18,52,91,72,0,43,96,23}  | {AAAAA33250,AAAAAAAAAAAAAAAAAAA85420,AAAAAAAAAAA33576}
 219     15 | {17,14,16,63,67}                | {AA6416,AAAAAAAAAA646,AAAAA95309}
 220     19 | {52,82,17,74,23,46,69,51,75}    | {AAAAAAAAAAAAA73084,AAAAA75968,AAAAAAAAAAAAAAAA14047,AAAAAAA80240,AAAAAAAAAAAAAAAAAAA1205,A68938}
 221     53 | {38,17}                         | {AAAAAAAAAAA21658}
 222     65 | {61,5,76,59,17}                 | {AAAAAA99807,AAAAA64741,AAAAAAAAAAA53908,AA21643,AAAAAAAAA10012}
 223     77 | {97,15,32,17,55,59,18,37,50,39} | {AAAAAAAAAAAA67946,AAAAAA54032,AAAAAAAA81587,55847,AAAAAAAAAAAAAA28620,AAAAAAAAAAAAAAAAA43052,AAAAAA75463,AAAA49534,AAAAAAAA44066}
 224     89 | {40,32,17,6,30,88}              | {AA44673,AAAAAAAAAAA6119,AAAAAAAAAAAAAAAA23657,AAAAAAAAAAAAAAAAAA47955,AAAAAAAAAAAAAAAA33598,AAAAAAAAAAA33576,AA44673}
 225 (8 rows)
 226
 227 SELECT * FROM array_index_op_test WHERE i @> '{32,17}' ORDER BY seqno;
 228  seqno |                i                |                                                                 t
 229 -------+---------------------------------+------------------------------------------------------------------------------------------------------------------------------------
 230      6 | {39,35,5,94,17,92,60,32}        | {AAAAAAAAAAAAAAA35875,AAAAAAAAAAAAAAAA23657}
 231     77 | {97,15,32,17,55,59,18,37,50,39} | {AAAAAAAAAAAA67946,AAAAAA54032,AAAAAAAA81587,55847,AAAAAAAAAAAAAA28620,AAAAAAAAAAAAAAAAA43052,AAAAAA75463,AAAA49534,AAAAAAAA44066}
 232     89 | {40,32,17,6,30,88}              | {AA44673,AAAAAAAAAAA6119,AAAAAAAAAAAAAAAA23657,AAAAAAAAAAAAAAAAAA47955,AAAAAAAAAAAAAAAA33598,AAAAAAAAAAA33576,AA44673}
 233 (3 rows)
 234
 235 SELECT * FROM array_index_op_test WHERE i && '{32,17}' ORDER BY seqno;
 236  seqno |                i                |                                                                 t
 237 -------+---------------------------------+------------------------------------------------------------------------------------------------------------------------------------
 238      6 | {39,35,5,94,17,92,60,32}        | {AAAAAAAAAAAAAAA35875,AAAAAAAAAAAAAAAA23657}
 239     12 | {17,99,18,52,91,72,0,43,96,23}  | {AAAAA33250,AAAAAAAAAAAAAAAAAAA85420,AAAAAAAAAAA33576}
 240     15 | {17,14,16,63,67}                | {AA6416,AAAAAAAAAA646,AAAAA95309}
 241     19 | {52,82,17,74,23,46,69,51,75}    | {AAAAAAAAAAAAA73084,AAAAA75968,AAAAAAAAAAAAAAAA14047,AAAAAAA80240,AAAAAAAAAAAAAAAAAAA1205,A68938}
 242     53 | {38,17}                         | {AAAAAAAAAAA21658}
 243     65 | {61,5,76,59,17}                 | {AAAAAA99807,AAAAA64741,AAAAAAAAAAA53908,AA21643,AAAAAAAAA10012}
 244     74 | {32}                            | {AAAAAAAAAAAAAAAA1729,AAAAAAAAAAAAA22860,AAAAAA99807,AAAAA17383,AAAAAAAAAAAAAAA67062,AAAAAAAAAAA15165,AAAAAAAAAAA50956}
 245     77 | {97,15,32,17,55,59,18,37,50,39} | {AAAAAAAAAAAA67946,AAAAAA54032,AAAAAAAA81587,55847,AAAAAAAAAAAAAA28620,AAAAAAAAAAAAAAAAA43052,AAAAAA75463,AAAA49534,AAAAAAAA44066}
 246     89 | {40,32,17,6,30,88}              | {AA44673,AAAAAAAAAAA6119,AAAAAAAAAAAAAAAA23657,AAAAAAAAAAAAAAAAAA47955,AAAAAAAAAAAAAAAA33598,AAAAAAAAAAA33576,AA44673}
 247     98 | {38,34,32,89}                   | {AAAAAAAAAAAAAAAAAA71621,AAAA8857,AAAAAAAAAAAAAAAAAAA65037,AAAAAAAAAAAAAAAA31334,AAAAAAAAAA48845}
 248    100 | {85,32,57,39,49,84,32,3,30}     | {AAAAAAA80240,AAAAAAAAAAAAAAAA1729,AAAAA60038,AAAAAAAAAAA92631,AAAAAAAA9523}
 249 (11 rows)
 250
 251 SELECT * FROM array_index_op_test WHERE i <@ '{38,34,32,89}' ORDER BY seqno;
 252  seqno |       i       |                                                             t
 253 -------+---------------+----------------------------------------------------------------------------------------------------------------------------
 254     40 | {34}          | {AAAAAAAAAAAAAA10611,AAAAAAAAAAAAAAAAAAA1205,AAAAAAAAAAA50956,AAAAAAAAAAAAAAAA31334,AAAAA70466,AAAAAAAA81587,AAAAAAA74623}
 255     74 | {32}          | {AAAAAAAAAAAAAAAA1729,AAAAAAAAAAAAA22860,AAAAAA99807,AAAAA17383,AAAAAAAAAAAAAAA67062,AAAAAAAAAAA15165,AAAAAAAAAAA50956}
 256     98 | {38,34,32,89} | {AAAAAAAAAAAAAAAAAA71621,AAAA8857,AAAAAAAAAAAAAAAAAAA65037,AAAAAAAAAAAAAAAA31334,AAAAAAAAAA48845}
 257 (3 rows)
 258
 259 SELECT * FROM array_index_op_test WHERE i = '{47,77}' ORDER BY seqno;
 260  seqno |    i    |                                                        t
 261 -------+---------+-----------------------------------------------------------------------------------------------------------------
 262     95 | {47,77} | {AAAAAAAAAAAAAAAAA764,AAAAAAAAAAA74076,AAAAAAAAAA18107,AAAAA40681,AAAAAAAAAAAAAAA35875,AAAAA60038,AAAAAAA56483}
 263 (1 row)
 264
 265 CREATE INDEX textarrayidx ON array_index_op_test USING gin (t);
 266 SELECT * FROM array_index_op_test WHERE t @> '{AAAAAAAA72908}' ORDER BY seqno;
 267  seqno |           i           |                                                                     t
 268 -------+-----------------------+--------------------------------------------------------------------------------------------------------------------------------------------
 269     22 | {11,6,56,62,53,30}    | {AAAAAAAA72908}
 270     45 | {99,45}               | {AAAAAAAA72908,AAAAAAAAAAAAAAAAAAA17075,AA88409,AAAAAAAAAAAAAAAAAA36842,AAAAAAA48038,AAAAAAAAAAAAAA10611}
 271     72 | {22,1,16,78,20,91,83} | {47735,AAAAAAA56483,AAAAAAAAAAAAA93788,AA42406,AAAAAAAAAAAAA73084,AAAAAAAA72908,AAAAAAAAAAAAAAAAAA61286,AAAAA66674,AAAAAAAAAAAAAAAAA50407}
 272     79 | {45}                  | {AAAAAAAAAA646,AAAAAAAAAAAAAAAAAAA70415,AAAAAA43678,AAAAAAAA72908}
 273 (4 rows)
 274
 275 SELECT * FROM array_index_op_test WHERE t && '{AAAAAAAA72908}' ORDER BY seqno;
 276  seqno |           i           |                                                                     t
 277 -------+-----------------------+--------------------------------------------------------------------------------------------------------------------------------------------
 278     22 | {11,6,56,62,53,30}    | {AAAAAAAA72908}
 279     45 | {99,45}               | {AAAAAAAA72908,AAAAAAAAAAAAAAAAAAA17075,AA88409,AAAAAAAAAAAAAAAAAA36842,AAAAAAA48038,AAAAAAAAAAAAAA10611}
 280     72 | {22,1,16,78,20,91,83} | {47735,AAAAAAA56483,AAAAAAAAAAAAA93788,AA42406,AAAAAAAAAAAAA73084,AAAAAAAA72908,AAAAAAAAAAAAAAAAAA61286,AAAAA66674,AAAAAAAAAAAAAAAAA50407}
 281     79 | {45}                  | {AAAAAAAAAA646,AAAAAAAAAAAAAAAAAAA70415,AAAAAA43678,AAAAAAAA72908}
 282 (4 rows)
 283
 284 SELECT * FROM array_index_op_test WHERE t @> '{AAAAAAAAAA646}' ORDER BY seqno;
 285  seqno |        i         |                                 t
 286 -------+------------------+--------------------------------------------------------------------
 287     15 | {17,14,16,63,67} | {AA6416,AAAAAAAAAA646,AAAAA95309}
 288     79 | {45}             | {AAAAAAAAAA646,AAAAAAAAAAAAAAAAAAA70415,AAAAAA43678,AAAAAAAA72908}
 289     96 | {23,97,43}       | {AAAAAAAAAA646,A87088}
 290 (3 rows)
 291
 292 SELECT * FROM array_index_op_test WHERE t && '{AAAAAAAAAA646}' ORDER BY seqno;
 293  seqno |        i         |                                 t
 294 -------+------------------+--------------------------------------------------------------------
 295     15 | {17,14,16,63,67} | {AA6416,AAAAAAAAAA646,AAAAA95309}
 296     79 | {45}             | {AAAAAAAAAA646,AAAAAAAAAAAAAAAAAAA70415,AAAAAA43678,AAAAAAAA72908}
 297     96 | {23,97,43}       | {AAAAAAAAAA646,A87088}
 298 (3 rows)
 299
 300 SELECT * FROM array_index_op_test WHERE t @> '{AAAAAAAA72908,AAAAAAAAAA646}' ORDER BY seqno;
 301  seqno |  i   |                                 t
 302 -------+------+--------------------------------------------------------------------
 303     79 | {45} | {AAAAAAAAAA646,AAAAAAAAAAAAAAAAAAA70415,AAAAAA43678,AAAAAAAA72908}
 304 (1 row)
 305
 306 SELECT * FROM array_index_op_test WHERE t && '{AAAAAAAA72908,AAAAAAAAAA646}' ORDER BY seqno;
 307  seqno |           i           |                                                                     t
 308 -------+-----------------------+--------------------------------------------------------------------------------------------------------------------------------------------
 309     15 | {17,14,16,63,67}      | {AA6416,AAAAAAAAAA646,AAAAA95309}
 310     22 | {11,6,56,62,53,30}    | {AAAAAAAA72908}
 311     45 | {99,45}               | {AAAAAAAA72908,AAAAAAAAAAAAAAAAAAA17075,AA88409,AAAAAAAAAAAAAAAAAA36842,AAAAAAA48038,AAAAAAAAAAAAAA10611}
 312     72 | {22,1,16,78,20,91,83} | {47735,AAAAAAA56483,AAAAAAAAAAAAA93788,AA42406,AAAAAAAAAAAAA73084,AAAAAAAA72908,AAAAAAAAAAAAAAAAAA61286,AAAAA66674,AAAAAAAAAAAAAAAAA50407}
 313     79 | {45}                  | {AAAAAAAAAA646,AAAAAAAAAAAAAAAAAAA70415,AAAAAA43678,AAAAAAAA72908}
 314     96 | {23,97,43}            | {AAAAAAAAAA646,A87088}
 315 (6 rows)
 316
 317 SELECT * FROM array_index_op_test WHERE t <@ '{AAAAAAAA72908,AAAAAAAAAAAAAAAAAAA17075,AA88409,AAAAAAAAAAAAAAAAAA36842,AAAAAAA48038,AAAAAAAAAAAAAA10611}' ORDER BY seqno;
 318  seqno |         i          |                                                     t
 319 -------+--------------------+-----------------------------------------------------------------------------------------------------------
 320     22 | {11,6,56,62,53,30} | {AAAAAAAA72908}
 321     45 | {99,45}            | {AAAAAAAA72908,AAAAAAAAAAAAAAAAAAA17075,AA88409,AAAAAAAAAAAAAAAAAA36842,AAAAAAA48038,AAAAAAAAAAAAAA10611}
 322 (2 rows)
 323
 324 SELECT * FROM array_index_op_test WHERE t = '{AAAAAAAAAA646,A87088}' ORDER BY seqno;
 325  seqno |     i      |           t
 326 -------+------------+------------------------
 327     96 | {23,97,43} | {AAAAAAAAAA646,A87088}
 328 (1 row)
 329
 330 -- Repeat some of the above tests but exercising bitmapscans instead
 331 SET enable_indexscan = OFF;
 332 SET enable_bitmapscan = ON;
 333 SELECT * FROM array_index_op_test WHERE i @> '{32}' ORDER BY seqno;
 334  seqno |                i                |                                                                 t
 335 -------+---------------------------------+------------------------------------------------------------------------------------------------------------------------------------
 336      6 | {39,35,5,94,17,92,60,32}        | {AAAAAAAAAAAAAAA35875,AAAAAAAAAAAAAAAA23657}
 337     74 | {32}                            | {AAAAAAAAAAAAAAAA1729,AAAAAAAAAAAAA22860,AAAAAA99807,AAAAA17383,AAAAAAAAAAAAAAA67062,AAAAAAAAAAA15165,AAAAAAAAAAA50956}
 338     77 | {97,15,32,17,55,59,18,37,50,39} | {AAAAAAAAAAAA67946,AAAAAA54032,AAAAAAAA81587,55847,AAAAAAAAAAAAAA28620,AAAAAAAAAAAAAAAAA43052,AAAAAA75463,AAAA49534,AAAAAAAA44066}
 339     89 | {40,32,17,6,30,88}              | {AA44673,AAAAAAAAAAA6119,AAAAAAAAAAAAAAAA23657,AAAAAAAAAAAAAAAAAA47955,AAAAAAAAAAAAAAAA33598,AAAAAAAAAAA33576,AA44673}
 340     98 | {38,34,32,89}                   | {AAAAAAAAAAAAAAAAAA71621,AAAA8857,AAAAAAAAAAAAAAAAAAA65037,AAAAAAAAAAAAAAAA31334,AAAAAAAAAA48845}
 341    100 | {85,32,57,39,49,84,32,3,30}     | {AAAAAAA80240,AAAAAAAAAAAAAAAA1729,AAAAA60038,AAAAAAAAAAA92631,AAAAAAAA9523}
 342 (6 rows)
 343
 344 SELECT * FROM array_index_op_test WHERE i && '{32}' ORDER BY seqno;
 345  seqno |                i                |                                                                 t
 346 -------+---------------------------------+------------------------------------------------------------------------------------------------------------------------------------
 347      6 | {39,35,5,94,17,92,60,32}        | {AAAAAAAAAAAAAAA35875,AAAAAAAAAAAAAAAA23657}
 348     74 | {32}                            | {AAAAAAAAAAAAAAAA1729,AAAAAAAAAAAAA22860,AAAAAA99807,AAAAA17383,AAAAAAAAAAAAAAA67062,AAAAAAAAAAA15165,AAAAAAAAAAA50956}
 349     77 | {97,15,32,17,55,59,18,37,50,39} | {AAAAAAAAAAAA67946,AAAAAA54032,AAAAAAAA81587,55847,AAAAAAAAAAAAAA28620,AAAAAAAAAAAAAAAAA43052,AAAAAA75463,AAAA49534,AAAAAAAA44066}
 350     89 | {40,32,17,6,30,88}              | {AA44673,AAAAAAAAAAA6119,AAAAAAAAAAAAAAAA23657,AAAAAAAAAAAAAAAAAA47955,AAAAAAAAAAAAAAAA33598,AAAAAAAAAAA33576,AA44673}
 351     98 | {38,34,32,89}                   | {AAAAAAAAAAAAAAAAAA71621,AAAA8857,AAAAAAAAAAAAAAAAAAA65037,AAAAAAAAAAAAAAAA31334,AAAAAAAAAA48845}
 352    100 | {85,32,57,39,49,84,32,3,30}     | {AAAAAAA80240,AAAAAAAAAAAAAAAA1729,AAAAA60038,AAAAAAAAAAA92631,AAAAAAAA9523}
 353 (6 rows)
 354
 355 SELECT * FROM array_index_op_test WHERE i @> '{17}' ORDER BY seqno;
 356  seqno |                i                |                                                                 t
 357 -------+---------------------------------+------------------------------------------------------------------------------------------------------------------------------------
 358      6 | {39,35,5,94,17,92,60,32}        | {AAAAAAAAAAAAAAA35875,AAAAAAAAAAAAAAAA23657}
 359     12 | {17,99,18,52,91,72,0,43,96,23}  | {AAAAA33250,AAAAAAAAAAAAAAAAAAA85420,AAAAAAAAAAA33576}
 360     15 | {17,14,16,63,67}                | {AA6416,AAAAAAAAAA646,AAAAA95309}
 361     19 | {52,82,17,74,23,46,69,51,75}    | {AAAAAAAAAAAAA73084,AAAAA75968,AAAAAAAAAAAAAAAA14047,AAAAAAA80240,AAAAAAAAAAAAAAAAAAA1205,A68938}
 362     53 | {38,17}                         | {AAAAAAAAAAA21658}
 363     65 | {61,5,76,59,17}                 | {AAAAAA99807,AAAAA64741,AAAAAAAAAAA53908,AA21643,AAAAAAAAA10012}
 364     77 | {97,15,32,17,55,59,18,37,50,39} | {AAAAAAAAAAAA67946,AAAAAA54032,AAAAAAAA81587,55847,AAAAAAAAAAAAAA28620,AAAAAAAAAAAAAAAAA43052,AAAAAA75463,AAAA49534,AAAAAAAA44066}
 365     89 | {40,32,17,6,30,88}              | {AA44673,AAAAAAAAAAA6119,AAAAAAAAAAAAAAAA23657,AAAAAAAAAAAAAAAAAA47955,AAAAAAAAAAAAAAAA33598,AAAAAAAAAAA33576,AA44673}
 366 (8 rows)
 367
 368 SELECT * FROM array_index_op_test WHERE i && '{17}' ORDER BY seqno;
 369  seqno |                i                |                                                                 t
 370 -------+---------------------------------+------------------------------------------------------------------------------------------------------------------------------------
 371      6 | {39,35,5,94,17,92,60,32}        | {AAAAAAAAAAAAAAA35875,AAAAAAAAAAAAAAAA23657}
 372     12 | {17,99,18,52,91,72,0,43,96,23}  | {AAAAA33250,AAAAAAAAAAAAAAAAAAA85420,AAAAAAAAAAA33576}
 373     15 | {17,14,16,63,67}                | {AA6416,AAAAAAAAAA646,AAAAA95309}
 374     19 | {52,82,17,74,23,46,69,51,75}    | {AAAAAAAAAAAAA73084,AAAAA75968,AAAAAAAAAAAAAAAA14047,AAAAAAA80240,AAAAAAAAAAAAAAAAAAA1205,A68938}
 375     53 | {38,17}                         | {AAAAAAAAAAA21658}
 376     65 | {61,5,76,59,17}                 | {AAAAAA99807,AAAAA64741,AAAAAAAAAAA53908,AA21643,AAAAAAAAA10012}
 377     77 | {97,15,32,17,55,59,18,37,50,39} | {AAAAAAAAAAAA67946,AAAAAA54032,AAAAAAAA81587,55847,AAAAAAAAAAAAAA28620,AAAAAAAAAAAAAAAAA43052,AAAAAA75463,AAAA49534,AAAAAAAA44066}
 378     89 | {40,32,17,6,30,88}              | {AA44673,AAAAAAAAAAA6119,AAAAAAAAAAAAAAAA23657,AAAAAAAAAAAAAAAAAA47955,AAAAAAAAAAAAAAAA33598,AAAAAAAAAAA33576,AA44673}
 379 (8 rows)
 380
 381 SELECT * FROM array_index_op_test WHERE i @> '{32,17}' ORDER BY seqno;
 382  seqno |                i                |                                                                 t
 383 -------+---------------------------------+------------------------------------------------------------------------------------------------------------------------------------
 384      6 | {39,35,5,94,17,92,60,32}        | {AAAAAAAAAAAAAAA35875,AAAAAAAAAAAAAAAA23657}
 385     77 | {97,15,32,17,55,59,18,37,50,39} | {AAAAAAAAAAAA67946,AAAAAA54032,AAAAAAAA81587,55847,AAAAAAAAAAAAAA28620,AAAAAAAAAAAAAAAAA43052,AAAAAA75463,AAAA49534,AAAAAAAA44066}
 386     89 | {40,32,17,6,30,88}              | {AA44673,AAAAAAAAAAA6119,AAAAAAAAAAAAAAAA23657,AAAAAAAAAAAAAAAAAA47955,AAAAAAAAAAAAAAAA33598,AAAAAAAAAAA33576,AA44673}
 387 (3 rows)
 388
 389 SELECT * FROM array_index_op_test WHERE i && '{32,17}' ORDER BY seqno;
 390  seqno |                i                |                                                                 t
 391 -------+---------------------------------+------------------------------------------------------------------------------------------------------------------------------------
 392      6 | {39,35,5,94,17,92,60,32}        | {AAAAAAAAAAAAAAA35875,AAAAAAAAAAAAAAAA23657}
 393     12 | {17,99,18,52,91,72,0,43,96,23}  | {AAAAA33250,AAAAAAAAAAAAAAAAAAA85420,AAAAAAAAAAA33576}
 394     15 | {17,14,16,63,67}                | {AA6416,AAAAAAAAAA646,AAAAA95309}
 395     19 | {52,82,17,74,23,46,69,51,75}    | {AAAAAAAAAAAAA73084,AAAAA75968,AAAAAAAAAAAAAAAA14047,AAAAAAA80240,AAAAAAAAAAAAAAAAAAA1205,A68938}
 396     53 | {38,17}                         | {AAAAAAAAAAA21658}
 397     65 | {61,5,76,59,17}                 | {AAAAAA99807,AAAAA64741,AAAAAAAAAAA53908,AA21643,AAAAAAAAA10012}
 398     74 | {32}                            | {AAAAAAAAAAAAAAAA1729,AAAAAAAAAAAAA22860,AAAAAA99807,AAAAA17383,AAAAAAAAAAAAAAA67062,AAAAAAAAAAA15165,AAAAAAAAAAA50956}
 399     77 | {97,15,32,17,55,59,18,37,50,39} | {AAAAAAAAAAAA67946,AAAAAA54032,AAAAAAAA81587,55847,AAAAAAAAAAAAAA28620,AAAAAAAAAAAAAAAAA43052,AAAAAA75463,AAAA49534,AAAAAAAA44066}
 400     89 | {40,32,17,6,30,88}              | {AA44673,AAAAAAAAAAA6119,AAAAAAAAAAAAAAAA23657,AAAAAAAAAAAAAAAAAA47955,AAAAAAAAAAAAAAAA33598,AAAAAAAAAAA33576,AA44673}
 401     98 | {38,34,32,89}                   | {AAAAAAAAAAAAAAAAAA71621,AAAA8857,AAAAAAAAAAAAAAAAAAA65037,AAAAAAAAAAAAAAAA31334,AAAAAAAAAA48845}
 402    100 | {85,32,57,39,49,84,32,3,30}     | {AAAAAAA80240,AAAAAAAAAAAAAAAA1729,AAAAA60038,AAAAAAAAAAA92631,AAAAAAAA9523}
 403 (11 rows)
 404
 405 SELECT * FROM array_index_op_test WHERE i <@ '{38,34,32,89}' ORDER BY seqno;
 406  seqno |       i       |                                                             t
 407 -------+---------------+----------------------------------------------------------------------------------------------------------------------------
 408     40 | {34}          | {AAAAAAAAAAAAAA10611,AAAAAAAAAAAAAAAAAAA1205,AAAAAAAAAAA50956,AAAAAAAAAAAAAAAA31334,AAAAA70466,AAAAAAAA81587,AAAAAAA74623}
 409     74 | {32}          | {AAAAAAAAAAAAAAAA1729,AAAAAAAAAAAAA22860,AAAAAA99807,AAAAA17383,AAAAAAAAAAAAAAA67062,AAAAAAAAAAA15165,AAAAAAAAAAA50956}
 410     98 | {38,34,32,89} | {AAAAAAAAAAAAAAAAAA71621,AAAA8857,AAAAAAAAAAAAAAAAAAA65037,AAAAAAAAAAAAAAAA31334,AAAAAAAAAA48845}
 411 (3 rows)
 412
 413 SELECT * FROM array_index_op_test WHERE i = '{47,77}' ORDER BY seqno;
 414  seqno |    i    |                                                        t
 415 -------+---------+-----------------------------------------------------------------------------------------------------------------
 416     95 | {47,77} | {AAAAAAAAAAAAAAAAA764,AAAAAAAAAAA74076,AAAAAAAAAA18107,AAAAA40681,AAAAAAAAAAAAAAA35875,AAAAA60038,AAAAAAA56483}
 417 (1 row)
 418
 419 -- And try it with a multicolumn GIN index
 420 DROP INDEX intarrayidx, textarrayidx;
 421 CREATE INDEX botharrayidx ON array_index_op_test USING gin (i, t);
 422 SET enable_seqscan = OFF;
 423 SET enable_indexscan = ON;
 424 SET enable_bitmapscan = OFF;
 425 SELECT * FROM array_index_op_test WHERE i @> '{32}' ORDER BY seqno;
 426  seqno |                i                |                                                                 t
 427 -------+---------------------------------+------------------------------------------------------------------------------------------------------------------------------------
 428      6 | {39,35,5,94,17,92,60,32}        | {AAAAAAAAAAAAAAA35875,AAAAAAAAAAAAAAAA23657}
 429     74 | {32}                            | {AAAAAAAAAAAAAAAA1729,AAAAAAAAAAAAA22860,AAAAAA99807,AAAAA17383,AAAAAAAAAAAAAAA67062,AAAAAAAAAAA15165,AAAAAAAAAAA50956}
 430     77 | {97,15,32,17,55,59,18,37,50,39} | {AAAAAAAAAAAA67946,AAAAAA54032,AAAAAAAA81587,55847,AAAAAAAAAAAAAA28620,AAAAAAAAAAAAAAAAA43052,AAAAAA75463,AAAA49534,AAAAAAAA44066}
 431     89 | {40,32,17,6,30,88}              | {AA44673,AAAAAAAAAAA6119,AAAAAAAAAAAAAAAA23657,AAAAAAAAAAAAAAAAAA47955,AAAAAAAAAAAAAAAA33598,AAAAAAAAAAA33576,AA44673}
 432     98 | {38,34,32,89}                   | {AAAAAAAAAAAAAAAAAA71621,AAAA8857,AAAAAAAAAAAAAAAAAAA65037,AAAAAAAAAAAAAAAA31334,AAAAAAAAAA48845}
 433    100 | {85,32,57,39,49,84,32,3,30}     | {AAAAAAA80240,AAAAAAAAAAAAAAAA1729,AAAAA60038,AAAAAAAAAAA92631,AAAAAAAA9523}
 434 (6 rows)
 435
 436 SELECT * FROM array_index_op_test WHERE i && '{32}' ORDER BY seqno;
 437  seqno |                i                |                                                                 t
 438 -------+---------------------------------+------------------------------------------------------------------------------------------------------------------------------------
 439      6 | {39,35,5,94,17,92,60,32}        | {AAAAAAAAAAAAAAA35875,AAAAAAAAAAAAAAAA23657}
 440     74 | {32}                            | {AAAAAAAAAAAAAAAA1729,AAAAAAAAAAAAA22860,AAAAAA99807,AAAAA17383,AAAAAAAAAAAAAAA67062,AAAAAAAAAAA15165,AAAAAAAAAAA50956}
 441     77 | {97,15,32,17,55,59,18,37,50,39} | {AAAAAAAAAAAA67946,AAAAAA54032,AAAAAAAA81587,55847,AAAAAAAAAAAAAA28620,AAAAAAAAAAAAAAAAA43052,AAAAAA75463,AAAA49534,AAAAAAAA44066}
 442     89 | {40,32,17,6,30,88}              | {AA44673,AAAAAAAAAAA6119,AAAAAAAAAAAAAAAA23657,AAAAAAAAAAAAAAAAAA47955,AAAAAAAAAAAAAAAA33598,AAAAAAAAAAA33576,AA44673}
 443     98 | {38,34,32,89}                   | {AAAAAAAAAAAAAAAAAA71621,AAAA8857,AAAAAAAAAAAAAAAAAAA65037,AAAAAAAAAAAAAAAA31334,AAAAAAAAAA48845}
 444    100 | {85,32,57,39,49,84,32,3,30}     | {AAAAAAA80240,AAAAAAAAAAAAAAAA1729,AAAAA60038,AAAAAAAAAAA92631,AAAAAAAA9523}
 445 (6 rows)
 446
 447 SELECT * FROM array_index_op_test WHERE t @> '{AAAAAAA80240}' ORDER BY seqno;
 448  seqno |               i                |                                                                              t
 449 -------+--------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------
 450     19 | {52,82,17,74,23,46,69,51,75}   | {AAAAAAAAAAAAA73084,AAAAA75968,AAAAAAAAAAAAAAAA14047,AAAAAAA80240,AAAAAAAAAAAAAAAAAAA1205,A68938}
 451     30 | {26,81,47,91,34}               | {AAAAAAAAAAAAAAAAAAA70104,AAAAAAA80240}
 452     64 | {26,19,34,24,81,78}            | {A96617,AAAAAAAAAAAAAAAAAAA70104,A68938,AAAAAAAAAAA53908,AAAAAAAAAAAAAAA453,AA17009,AAAAAAA80240}
 453     82 | {34,60,4,79,78,16,86,89,42,50} | {AAAAA40681,AAAAAAAAAAAAAAAAAA12591,AAAAAAA80240,AAAAAAAAAAAAAAAA55798,AAAAAAAAAAAAAAAAAAA70104}
 454     88 | {41,90,77,24,6,24}             | {AAAA35194,AAAA35194,AAAAAAA80240,AAAAAAAAAAA46154,AAAAAA58494,AAAAAAAAAAAAAAAAAAA17075,AAAAAAAAAAAAAAAAAA59334,AAAAAAAAAAAAAAAAAAA91804,AA74433}
 455     97 | {54,2,86,65}                   | {47735,AAAAAAA99836,AAAAAAAAAAAAAAAAA6897,AAAAAAAAAAAAAAAA29150,AAAAAAA80240,AAAAAAAAAAAAAAAA98414,AAAAAAA56483,AAAAAAAAAAAAAAAA29150,AAAAAAA39692,AA21643}
 456    100 | {85,32,57,39,49,84,32,3,30}    | {AAAAAAA80240,AAAAAAAAAAAAAAAA1729,AAAAA60038,AAAAAAAAAAA92631,AAAAAAAA9523}
 457 (7 rows)
 458
 459 SELECT * FROM array_index_op_test WHERE t && '{AAAAAAA80240}' ORDER BY seqno;
 460  seqno |               i                |                                                                              t
 461 -------+--------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------
 462     19 | {52,82,17,74,23,46,69,51,75}   | {AAAAAAAAAAAAA73084,AAAAA75968,AAAAAAAAAAAAAAAA14047,AAAAAAA80240,AAAAAAAAAAAAAAAAAAA1205,A68938}
 463     30 | {26,81,47,91,34}               | {AAAAAAAAAAAAAAAAAAA70104,AAAAAAA80240}
 464     64 | {26,19,34,24,81,78}            | {A96617,AAAAAAAAAAAAAAAAAAA70104,A68938,AAAAAAAAAAA53908,AAAAAAAAAAAAAAA453,AA17009,AAAAAAA80240}
 465     82 | {34,60,4,79,78,16,86,89,42,50} | {AAAAA40681,AAAAAAAAAAAAAAAAAA12591,AAAAAAA80240,AAAAAAAAAAAAAAAA55798,AAAAAAAAAAAAAAAAAAA70104}
 466     88 | {41,90,77,24,6,24}             | {AAAA35194,AAAA35194,AAAAAAA80240,AAAAAAAAAAA46154,AAAAAA58494,AAAAAAAAAAAAAAAAAAA17075,AAAAAAAAAAAAAAAAAA59334,AAAAAAAAAAAAAAAAAAA91804,AA74433}
 467     97 | {54,2,86,65}                   | {47735,AAAAAAA99836,AAAAAAAAAAAAAAAAA6897,AAAAAAAAAAAAAAAA29150,AAAAAAA80240,AAAAAAAAAAAAAAAA98414,AAAAAAA56483,AAAAAAAAAAAAAAAA29150,AAAAAAA39692,AA21643}
 468    100 | {85,32,57,39,49,84,32,3,30}    | {AAAAAAA80240,AAAAAAAAAAAAAAAA1729,AAAAA60038,AAAAAAAAAAA92631,AAAAAAAA9523}
 469 (7 rows)
 470
 471 SELECT * FROM array_index_op_test WHERE i @> '{32}' AND t && '{AAAAAAA80240}' ORDER BY seqno;
 472  seqno |              i              |                                      t
 473 -------+-----------------------------+------------------------------------------------------------------------------
 474    100 | {85,32,57,39,49,84,32,3,30} | {AAAAAAA80240,AAAAAAAAAAAAAAAA1729,AAAAA60038,AAAAAAAAAAA92631,AAAAAAAA9523}
 475 (1 row)
 476
 477 SELECT * FROM array_index_op_test WHERE i && '{32}' AND t @> '{AAAAAAA80240}' ORDER BY seqno;
 478  seqno |              i              |                                      t
 479 -------+-----------------------------+------------------------------------------------------------------------------
 480    100 | {85,32,57,39,49,84,32,3,30} | {AAAAAAA80240,AAAAAAAAAAAAAAAA1729,AAAAA60038,AAAAAAAAAAA92631,AAAAAAAA9523}
 481 (1 row)
 482
 483 SET enable_indexscan = OFF;
 484 SET enable_bitmapscan = ON;
 485 SELECT * FROM array_index_op_test WHERE i @> '{32}' ORDER BY seqno;
 486  seqno |                i                |                                                                 t
 487 -------+---------------------------------+------------------------------------------------------------------------------------------------------------------------------------
 488      6 | {39,35,5,94,17,92,60,32}        | {AAAAAAAAAAAAAAA35875,AAAAAAAAAAAAAAAA23657}
 489     74 | {32}                            | {AAAAAAAAAAAAAAAA1729,AAAAAAAAAAAAA22860,AAAAAA99807,AAAAA17383,AAAAAAAAAAAAAAA67062,AAAAAAAAAAA15165,AAAAAAAAAAA50956}
 490     77 | {97,15,32,17,55,59,18,37,50,39} | {AAAAAAAAAAAA67946,AAAAAA54032,AAAAAAAA81587,55847,AAAAAAAAAAAAAA28620,AAAAAAAAAAAAAAAAA43052,AAAAAA75463,AAAA49534,AAAAAAAA44066}
 491     89 | {40,32,17,6,30,88}              | {AA44673,AAAAAAAAAAA6119,AAAAAAAAAAAAAAAA23657,AAAAAAAAAAAAAAAAAA47955,AAAAAAAAAAAAAAAA33598,AAAAAAAAAAA33576,AA44673}
 492     98 | {38,34,32,89}                   | {AAAAAAAAAAAAAAAAAA71621,AAAA8857,AAAAAAAAAAAAAAAAAAA65037,AAAAAAAAAAAAAAAA31334,AAAAAAAAAA48845}
 493    100 | {85,32,57,39,49,84,32,3,30}     | {AAAAAAA80240,AAAAAAAAAAAAAAAA1729,AAAAA60038,AAAAAAAAAAA92631,AAAAAAAA9523}
 494 (6 rows)
 495
 496 SELECT * FROM array_index_op_test WHERE i && '{32}' ORDER BY seqno;
 497  seqno |                i                |                                                                 t
 498 -------+---------------------------------+------------------------------------------------------------------------------------------------------------------------------------
 499      6 | {39,35,5,94,17,92,60,32}        | {AAAAAAAAAAAAAAA35875,AAAAAAAAAAAAAAAA23657}
 500     74 | {32}                            | {AAAAAAAAAAAAAAAA1729,AAAAAAAAAAAAA22860,AAAAAA99807,AAAAA17383,AAAAAAAAAAAAAAA67062,AAAAAAAAAAA15165,AAAAAAAAAAA50956}
 501     77 | {97,15,32,17,55,59,18,37,50,39} | {AAAAAAAAAAAA67946,AAAAAA54032,AAAAAAAA81587,55847,AAAAAAAAAAAAAA28620,AAAAAAAAAAAAAAAAA43052,AAAAAA75463,AAAA49534,AAAAAAAA44066}
 502     89 | {40,32,17,6,30,88}              | {AA44673,AAAAAAAAAAA6119,AAAAAAAAAAAAAAAA23657,AAAAAAAAAAAAAAAAAA47955,AAAAAAAAAAAAAAAA33598,AAAAAAAAAAA33576,AA44673}
 503     98 | {38,34,32,89}                   | {AAAAAAAAAAAAAAAAAA71621,AAAA8857,AAAAAAAAAAAAAAAAAAA65037,AAAAAAAAAAAAAAAA31334,AAAAAAAAAA48845}
 504    100 | {85,32,57,39,49,84,32,3,30}     | {AAAAAAA80240,AAAAAAAAAAAAAAAA1729,AAAAA60038,AAAAAAAAAAA92631,AAAAAAAA9523}
 505 (6 rows)
 506
 507 SELECT * FROM array_index_op_test WHERE t @> '{AAAAAAA80240}' ORDER BY seqno;
 508  seqno |               i                |                                                                              t
 509 -------+--------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------
 510     19 | {52,82,17,74,23,46,69,51,75}   | {AAAAAAAAAAAAA73084,AAAAA75968,AAAAAAAAAAAAAAAA14047,AAAAAAA80240,AAAAAAAAAAAAAAAAAAA1205,A68938}
 511     30 | {26,81,47,91,34}               | {AAAAAAAAAAAAAAAAAAA70104,AAAAAAA80240}
 512     64 | {26,19,34,24,81,78}            | {A96617,AAAAAAAAAAAAAAAAAAA70104,A68938,AAAAAAAAAAA53908,AAAAAAAAAAAAAAA453,AA17009,AAAAAAA80240}
 513     82 | {34,60,4,79,78,16,86,89,42,50} | {AAAAA40681,AAAAAAAAAAAAAAAAAA12591,AAAAAAA80240,AAAAAAAAAAAAAAAA55798,AAAAAAAAAAAAAAAAAAA70104}
 514     88 | {41,90,77,24,6,24}             | {AAAA35194,AAAA35194,AAAAAAA80240,AAAAAAAAAAA46154,AAAAAA58494,AAAAAAAAAAAAAAAAAAA17075,AAAAAAAAAAAAAAAAAA59334,AAAAAAAAAAAAAAAAAAA91804,AA74433}
 515     97 | {54,2,86,65}                   | {47735,AAAAAAA99836,AAAAAAAAAAAAAAAAA6897,AAAAAAAAAAAAAAAA29150,AAAAAAA80240,AAAAAAAAAAAAAAAA98414,AAAAAAA56483,AAAAAAAAAAAAAAAA29150,AAAAAAA39692,AA21643}
 516    100 | {85,32,57,39,49,84,32,3,30}    | {AAAAAAA80240,AAAAAAAAAAAAAAAA1729,AAAAA60038,AAAAAAAAAAA92631,AAAAAAAA9523}
 517 (7 rows)
 518
 519 SELECT * FROM array_index_op_test WHERE t && '{AAAAAAA80240}' ORDER BY seqno;
 520  seqno |               i                |                                                                              t
 521 -------+--------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------
 522     19 | {52,82,17,74,23,46,69,51,75}   | {AAAAAAAAAAAAA73084,AAAAA75968,AAAAAAAAAAAAAAAA14047,AAAAAAA80240,AAAAAAAAAAAAAAAAAAA1205,A68938}
 523     30 | {26,81,47,91,34}               | {AAAAAAAAAAAAAAAAAAA70104,AAAAAAA80240}
 524     64 | {26,19,34,24,81,78}            | {A96617,AAAAAAAAAAAAAAAAAAA70104,A68938,AAAAAAAAAAA53908,AAAAAAAAAAAAAAA453,AA17009,AAAAAAA80240}
 525     82 | {34,60,4,79,78,16,86,89,42,50} | {AAAAA40681,AAAAAAAAAAAAAAAAAA12591,AAAAAAA80240,AAAAAAAAAAAAAAAA55798,AAAAAAAAAAAAAAAAAAA70104}
 526     88 | {41,90,77,24,6,24}             | {AAAA35194,AAAA35194,AAAAAAA80240,AAAAAAAAAAA46154,AAAAAA58494,AAAAAAAAAAAAAAAAAAA17075,AAAAAAAAAAAAAAAAAA59334,AAAAAAAAAAAAAAAAAAA91804,AA74433}
 527     97 | {54,2,86,65}                   | {47735,AAAAAAA99836,AAAAAAAAAAAAAAAAA6897,AAAAAAAAAAAAAAAA29150,AAAAAAA80240,AAAAAAAAAAAAAAAA98414,AAAAAAA56483,AAAAAAAAAAAAAAAA29150,AAAAAAA39692,AA21643}
 528    100 | {85,32,57,39,49,84,32,3,30}    | {AAAAAAA80240,AAAAAAAAAAAAAAAA1729,AAAAA60038,AAAAAAAAAAA92631,AAAAAAAA9523}
 529 (7 rows)
 530
 531 SELECT * FROM array_index_op_test WHERE i @> '{32}' AND t && '{AAAAAAA80240}' ORDER BY seqno;
 532  seqno |              i              |                                      t
 533 -------+-----------------------------+------------------------------------------------------------------------------
 534    100 | {85,32,57,39,49,84,32,3,30} | {AAAAAAA80240,AAAAAAAAAAAAAAAA1729,AAAAA60038,AAAAAAAAAAA92631,AAAAAAAA9523}
 535 (1 row)
 536
 537 SELECT * FROM array_index_op_test WHERE i && '{32}' AND t @> '{AAAAAAA80240}' ORDER BY seqno;
 538  seqno |              i              |                                      t
 539 -------+-----------------------------+------------------------------------------------------------------------------
 540    100 | {85,32,57,39,49,84,32,3,30} | {AAAAAAA80240,AAAAAAAAAAAAAAAA1729,AAAAA60038,AAAAAAAAAAA92631,AAAAAAAA9523}
 541 (1 row)
 542
 543 RESET enable_seqscan;
 544 RESET enable_indexscan;
 545 RESET enable_bitmapscan;
 546 --
 547 -- HASH
 548 --
 549 CREATE INDEX hash_i4_index ON hash_i4_heap USING hash (random int4_ops);
 550 CREATE INDEX hash_name_index ON hash_name_heap USING hash (random name_ops);
 551 CREATE INDEX hash_txt_index ON hash_txt_heap USING hash (random text_ops);
 552 CREATE INDEX hash_f8_index ON hash_f8_heap USING hash (random float8_ops);
 553 -- CREATE INDEX hash_ovfl_index ON hash_ovfl_heap USING hash (x int4_ops);
 554 --
 555 -- Test functional index
 556 --
 557 CREATE TABLE func_index_heap (f1 text, f2 text);
 558 CREATE UNIQUE INDEX func_index_index on func_index_heap (textcat(f1,f2));
 559 INSERT INTO func_index_heap VALUES('ABC','DEF');
 560 INSERT INTO func_index_heap VALUES('AB','CDEFG');
 561 INSERT INTO func_index_heap VALUES('QWE','RTY');
 562 -- this should fail because of unique index:
 563 INSERT INTO func_index_heap VALUES('ABCD', 'EF');
 564 ERROR:  duplicate key value violates unique constraint "func_index_index"
 565 -- but this shouldn't:
 566 INSERT INTO func_index_heap VALUES('QWERTY');
 567 --
 568 -- Same test, expressional index
 569 --
 570 DROP TABLE func_index_heap;
 571 CREATE TABLE func_index_heap (f1 text, f2 text);
 572 CREATE UNIQUE INDEX func_index_index on func_index_heap ((f1 || f2) text_ops);
 573 INSERT INTO func_index_heap VALUES('ABC','DEF');
 574 INSERT INTO func_index_heap VALUES('AB','CDEFG');
 575 INSERT INTO func_index_heap VALUES('QWE','RTY');
 576 -- this should fail because of unique index:
 577 INSERT INTO func_index_heap VALUES('ABCD', 'EF');
 578 ERROR:  duplicate key value violates unique constraint "func_index_index"
 579 -- but this shouldn't:
 580 INSERT INTO func_index_heap VALUES('QWERTY');
 581 --
 582 -- Also try building functional, expressional, and partial indexes on
 583 -- tables that already contain data.
 584 --
 585 create unique index hash_f8_index_1 on hash_f8_heap(abs(random));
 586 create unique index hash_f8_index_2 on hash_f8_heap((seqno + 1), random);
 587 create unique index hash_f8_index_3 on hash_f8_heap(random) where seqno > 1000;
 588 --
 589 -- Try some concurrent index builds
 590 --
 591 -- Unfortunately this only tests about half the code paths because there are
 592 -- no concurrent updates happening to the table at the same time.
 593 CREATE TABLE concur_heap (f1 text, f2 text);
 594 -- empty table
 595 CREATE INDEX CONCURRENTLY concur_index1 ON concur_heap(f2,f1);
 596 INSERT INTO concur_heap VALUES  ('a','b');
 597 INSERT INTO concur_heap VALUES  ('b','b');
 598 -- unique index
 599 CREATE UNIQUE INDEX CONCURRENTLY concur_index2 ON concur_heap(f1);
 600 -- check if constraint is set up properly to be enforced
 601 INSERT INTO concur_heap VALUES ('b','x');
 602 ERROR:  duplicate key value violates unique constraint "concur_index2"
 603 -- check if constraint is enforced properly at build time
 604 CREATE UNIQUE INDEX CONCURRENTLY concur_index3 ON concur_heap(f2);
 605 ERROR:  could not create unique index "concur_index3"
 606 DETAIL:  Table contains duplicated values.
 607 -- test that expression indexes and partial indexes work concurrently
 608 CREATE INDEX CONCURRENTLY concur_index4 on concur_heap(f2) WHERE f1='a';
 609 CREATE INDEX CONCURRENTLY concur_index5 on concur_heap(f2) WHERE f1='x';
 610 CREATE INDEX CONCURRENTLY concur_index6 on concur_heap((f2||f1));
 611 -- You can't do a concurrent index build in a transaction
 612 BEGIN;
 613 CREATE INDEX CONCURRENTLY concur_index7 ON concur_heap(f1);
 614 ERROR:  CREATE INDEX CONCURRENTLY cannot run inside a transaction block
 615 COMMIT;
 616 -- But you can do a regular index build in a transaction
 617 BEGIN;
 618 CREATE INDEX std_index on concur_heap(f2);
 619 COMMIT;
 620 -- check to make sure that the failed indexes were cleaned up properly and the
 621 -- successful indexes are created properly. Notably that they do NOT have the
 622 -- "invalid" flag set.
 623 \d concur_heap
 624 Table "public.concur_heap"
 625  Column | Type | Modifiers
 626 --------+------+-----------
 627  f1     | text |
 628  f2     | text |
 629 Indexes:
 630     "concur_index2" UNIQUE, btree (f1)
 631     "concur_index3" UNIQUE, btree (f2) INVALID
 632     "concur_index1" btree (f2, f1)
 633     "concur_index4" btree (f2) WHERE f1 = 'a'::text
 634     "concur_index5" btree (f2) WHERE f1 = 'x'::text
 635     "concur_index6" btree ((f2 || f1))
 636     "std_index" btree (f2)
 637
 638 DROP TABLE concur_heap;
 639 --
 640 -- Tests for IS NULL with b-tree indexes
 641 --
 642 SELECT unique1, unique2 INTO onek_with_null FROM onek;
 643 INSERT INTO onek_with_null (unique1,unique2) VALUES (NULL, -1), (NULL, NULL);
 644 CREATE UNIQUE INDEX onek_nulltest ON onek_with_null (unique2,unique1);
 645 SET enable_seqscan = OFF;
 646 SET enable_indexscan = ON;
 647 SET enable_bitmapscan = ON;
 648 SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL;
 649  count
 650 -------
 651      2
 652 (1 row)
 653
 654 SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL AND unique2 IS NULL;
 655  count
 656 -------
 657      1
 658 (1 row)
 659
 660 DROP INDEX onek_nulltest;
 661 CREATE UNIQUE INDEX onek_nulltest ON onek_with_null (unique2 desc,unique1);
 662 SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL;
 663  count
 664 -------
 665      2
 666 (1 row)
 667
 668 SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL AND unique2 IS NULL;
 669  count
 670 -------
 671      1
 672 (1 row)
 673
 674 DROP INDEX onek_nulltest;
 675 CREATE UNIQUE INDEX onek_nulltest ON onek_with_null (unique2 desc nulls last,unique1);
 676 SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL;
 677  count
 678 -------
 679      2
 680 (1 row)
 681
 682 SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL AND unique2 IS NULL;
 683  count
 684 -------
 685      1
 686 (1 row)
 687
 688 DROP INDEX onek_nulltest;
 689 CREATE UNIQUE INDEX onek_nulltest ON onek_with_null (unique2  nulls first,unique1);
 690 SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL;
 691  count
 692 -------
 693      2
 694 (1 row)
 695
 696 SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL AND unique2 IS NULL;
 697  count
 698 -------
 699      1
 700 (1 row)
 701
 702 RESET enable_seqscan;
 703 RESET enable_indexscan;
 704 RESET enable_bitmapscan;
 705
 706 DROP TABLE onek_with_null;