src/test/regress/sql/create_index.sql

   1 --
   2 -- CREATE_INDEX
   3 -- Create ancillary data structures (i.e. indices)
   4 --
   5
   6 --
   7 -- BTREE
   8 --
   9 CREATE INDEX onek_unique1 ON onek USING btree(unique1 int4_ops);
  10
  11 CREATE INDEX onek_unique2 ON onek USING btree(unique2 int4_ops);
  12
  13 CREATE INDEX onek_hundred ON onek USING btree(hundred int4_ops);
  14
  15 CREATE INDEX onek_stringu1 ON onek USING btree(stringu1 name_ops);
  16
  17 CREATE INDEX tenk1_unique1 ON tenk1 USING btree(unique1 int4_ops);
  18
  19 CREATE INDEX tenk1_unique2 ON tenk1 USING btree(unique2 int4_ops);
  20
  21 CREATE INDEX tenk1_hundred ON tenk1 USING btree(hundred int4_ops);
  22
  23 CREATE INDEX tenk1_thous_tenthous ON tenk1 (thousand, tenthous);
  24
  25 CREATE INDEX tenk2_unique1 ON tenk2 USING btree(unique1 int4_ops);
  26
  27 CREATE INDEX tenk2_unique2 ON tenk2 USING btree(unique2 int4_ops);
  28
  29 CREATE INDEX tenk2_hundred ON tenk2 USING btree(hundred int4_ops);
  30
  31 CREATE INDEX rix ON road USING btree (name text_ops);
  32
  33 CREATE INDEX iix ON ihighway USING btree (name text_ops);
  34
  35 CREATE INDEX six ON shighway USING btree (name text_ops);
  36
  37 -- test comments
  38 COMMENT ON INDEX six_wrong IS 'bad index';
  39 COMMENT ON INDEX six IS 'good index';
  40 COMMENT ON INDEX six IS NULL;
  41
  42 --
  43 -- BTREE ascending/descending cases
  44 --
  45 -- we load int4/text from pure descending data (each key is a new
  46 -- low key) and name/f8 from pure ascending data (each key is a new
  47 -- high key).  we had a bug where new low keys would sometimes be
  48 -- "lost".
  49 --
  50 CREATE INDEX bt_i4_index ON bt_i4_heap USING btree (seqno int4_ops);
  51
  52 CREATE INDEX bt_name_index ON bt_name_heap USING btree (seqno name_ops);
  53
  54 CREATE INDEX bt_txt_index ON bt_txt_heap USING btree (seqno text_ops);
  55
  56 CREATE INDEX bt_f8_index ON bt_f8_heap USING btree (seqno float8_ops);
  57
  58 --
  59 -- BTREE partial indices
  60 --
  61 CREATE INDEX onek2_u1_prtl ON onek2 USING btree(unique1 int4_ops)
  62         where unique1 < 20 or unique1 > 980;
  63
  64 CREATE INDEX onek2_u2_prtl ON onek2 USING btree(unique2 int4_ops)
  65         where stringu1 < 'B';
  66
  67 CREATE INDEX onek2_stu1_prtl ON onek2 USING btree(stringu1 name_ops)
  68         where onek2.stringu1 >= 'J' and onek2.stringu1 < 'K';
  69
  70 --
  71 -- GiST (rtree-equivalent opclasses only)
  72 --
  73 CREATE INDEX grect2ind ON fast_emp4000 USING gist (home_base);
  74
  75 CREATE INDEX gpolygonind ON polygon_tbl USING gist (f1);
  76
  77 CREATE INDEX gcircleind ON circle_tbl USING gist (f1);
  78
  79 CREATE TEMP TABLE gpolygon_tbl AS
  80     SELECT polygon(home_base) AS f1 FROM slow_emp4000;
  81
  82 CREATE TEMP TABLE gcircle_tbl AS
  83     SELECT circle(home_base) AS f1 FROM slow_emp4000;
  84
  85 CREATE INDEX ggpolygonind ON gpolygon_tbl USING gist (f1);
  86
  87 CREATE INDEX ggcircleind ON gcircle_tbl USING gist (f1);
  88
  89 SET enable_seqscan = ON;
  90 SET enable_indexscan = OFF;
  91 SET enable_bitmapscan = OFF;
  92
  93 SELECT * FROM fast_emp4000
  94     WHERE home_base @ '(200,200),(2000,1000)'::box
  95     ORDER BY (home_base[0])[0];
  96
  97 SELECT count(*) FROM fast_emp4000 WHERE home_base && '(1000,1000,0,0)'::box;
  98
  99 SELECT count(*) FROM fast_emp4000 WHERE home_base IS NULL;
 100
 101 SELECT * FROM polygon_tbl WHERE f1 ~ '((1,1),(2,2),(2,1))'::polygon
 102     ORDER BY (poly_center(f1))[0];
 103
 104 SELECT * FROM circle_tbl WHERE f1 && circle(point(1,-2), 1)
 105     ORDER BY area(f1);
 106
 107 SELECT count(*) FROM gpolygon_tbl WHERE f1 && '(1000,1000,0,0)'::polygon;
 108
 109 SELECT count(*) FROM gcircle_tbl WHERE f1 && '<(500,500),500>'::circle;
 110
 111 SET enable_seqscan = OFF;
 112 SET enable_indexscan = ON;
 113 SET enable_bitmapscan = ON;
 114
 115 -- there's no easy way to check that these commands actually use
 116 -- the index, unfortunately.  (EXPLAIN would work, but its output
 117 -- changes too often for me to want to put an EXPLAIN in the test...)
 118 SELECT * FROM fast_emp4000
 119     WHERE home_base @ '(200,200),(2000,1000)'::box
 120     ORDER BY (home_base[0])[0];
 121
 122 SELECT count(*) FROM fast_emp4000 WHERE home_base && '(1000,1000,0,0)'::box;
 123
 124 SELECT count(*) FROM fast_emp4000 WHERE home_base IS NULL;
 125
 126 SELECT * FROM polygon_tbl WHERE f1 ~ '((1,1),(2,2),(2,1))'::polygon
 127     ORDER BY (poly_center(f1))[0];
 128
 129 SELECT * FROM circle_tbl WHERE f1 && circle(point(1,-2), 1)
 130     ORDER BY area(f1);
 131
 132 SELECT count(*) FROM gpolygon_tbl WHERE f1 && '(1000,1000,0,0)'::polygon;
 133
 134 SELECT count(*) FROM gcircle_tbl WHERE f1 && '<(500,500),500>'::circle;
 135
 136 RESET enable_seqscan;
 137 RESET enable_indexscan;
 138 RESET enable_bitmapscan;
 139
 140 --
 141 -- GIN over int[] and text[]
 142 --
 143
 144 SET enable_seqscan = OFF;
 145 SET enable_indexscan = ON;
 146 SET enable_bitmapscan = OFF;
 147
 148 CREATE INDEX intarrayidx ON array_index_op_test USING gin (i);
 149
 150 SELECT * FROM array_index_op_test WHERE i @> '{32}' ORDER BY seqno;
 151 SELECT * FROM array_index_op_test WHERE i && '{32}' ORDER BY seqno;
 152 SELECT * FROM array_index_op_test WHERE i @> '{17}' ORDER BY seqno;
 153 SELECT * FROM array_index_op_test WHERE i && '{17}' ORDER BY seqno;
 154 SELECT * FROM array_index_op_test WHERE i @> '{32,17}' ORDER BY seqno;
 155 SELECT * FROM array_index_op_test WHERE i && '{32,17}' ORDER BY seqno;
 156 SELECT * FROM array_index_op_test WHERE i <@ '{38,34,32,89}' ORDER BY seqno;
 157 SELECT * FROM array_index_op_test WHERE i = '{47,77}' ORDER BY seqno;
 158
 159 CREATE INDEX textarrayidx ON array_index_op_test USING gin (t);
 160
 161 SELECT * FROM array_index_op_test WHERE t @> '{AAAAAAAA72908}' ORDER BY seqno;
 162 SELECT * FROM array_index_op_test WHERE t && '{AAAAAAAA72908}' ORDER BY seqno;
 163 SELECT * FROM array_index_op_test WHERE t @> '{AAAAAAAAAA646}' ORDER BY seqno;
 164 SELECT * FROM array_index_op_test WHERE t && '{AAAAAAAAAA646}' ORDER BY seqno;
 165 SELECT * FROM array_index_op_test WHERE t @> '{AAAAAAAA72908,AAAAAAAAAA646}' ORDER BY seqno;
 166 SELECT * FROM array_index_op_test WHERE t && '{AAAAAAAA72908,AAAAAAAAAA646}' ORDER BY seqno;
 167 SELECT * FROM array_index_op_test WHERE t <@ '{AAAAAAAA72908,AAAAAAAAAAAAAAAAAAA17075,AA88409,AAAAAAAAAAAAAAAAAA36842,AAAAAAA48038,AAAAAAAAAAAAAA10611}' ORDER BY seqno;
 168 SELECT * FROM array_index_op_test WHERE t = '{AAAAAAAAAA646,A87088}' ORDER BY seqno;
 169
 170 -- Repeat some of the above tests but exercising bitmapscans instead
 171 SET enable_indexscan = OFF;
 172 SET enable_bitmapscan = ON;
 173
 174 SELECT * FROM array_index_op_test WHERE i @> '{32}' ORDER BY seqno;
 175 SELECT * FROM array_index_op_test WHERE i && '{32}' ORDER BY seqno;
 176 SELECT * FROM array_index_op_test WHERE i @> '{17}' ORDER BY seqno;
 177 SELECT * FROM array_index_op_test WHERE i && '{17}' ORDER BY seqno;
 178 SELECT * FROM array_index_op_test WHERE i @> '{32,17}' ORDER BY seqno;
 179 SELECT * FROM array_index_op_test WHERE i && '{32,17}' ORDER BY seqno;
 180 SELECT * FROM array_index_op_test WHERE i <@ '{38,34,32,89}' ORDER BY seqno;
 181 SELECT * FROM array_index_op_test WHERE i = '{47,77}' ORDER BY seqno;
 182
 183 -- And try it with a multicolumn GIN index
 184
 185 DROP INDEX intarrayidx, textarrayidx;
 186
 187 CREATE INDEX botharrayidx ON array_index_op_test USING gin (i, t);
 188
 189 SET enable_seqscan = OFF;
 190 SET enable_indexscan = ON;
 191 SET enable_bitmapscan = OFF;
 192
 193 SELECT * FROM array_index_op_test WHERE i @> '{32}' ORDER BY seqno;
 194 SELECT * FROM array_index_op_test WHERE i && '{32}' ORDER BY seqno;
 195 SELECT * FROM array_index_op_test WHERE t @> '{AAAAAAA80240}' ORDER BY seqno;
 196 SELECT * FROM array_index_op_test WHERE t && '{AAAAAAA80240}' ORDER BY seqno;
 197 SELECT * FROM array_index_op_test WHERE i @> '{32}' AND t && '{AAAAAAA80240}' ORDER BY seqno;
 198 SELECT * FROM array_index_op_test WHERE i && '{32}' AND t @> '{AAAAAAA80240}' ORDER BY seqno;
 199
 200 SET enable_indexscan = OFF;
 201 SET enable_bitmapscan = ON;
 202
 203 SELECT * FROM array_index_op_test WHERE i @> '{32}' ORDER BY seqno;
 204 SELECT * FROM array_index_op_test WHERE i && '{32}' ORDER BY seqno;
 205 SELECT * FROM array_index_op_test WHERE t @> '{AAAAAAA80240}' ORDER BY seqno;
 206 SELECT * FROM array_index_op_test WHERE t && '{AAAAAAA80240}' ORDER BY seqno;
 207 SELECT * FROM array_index_op_test WHERE i @> '{32}' AND t && '{AAAAAAA80240}' ORDER BY seqno;
 208 SELECT * FROM array_index_op_test WHERE i && '{32}' AND t @> '{AAAAAAA80240}' ORDER BY seqno;
 209
 210 RESET enable_seqscan;
 211 RESET enable_indexscan;
 212 RESET enable_bitmapscan;
 213
 214 --
 215 -- HASH
 216 --
 217 CREATE INDEX hash_i4_index ON hash_i4_heap USING hash (random int4_ops);
 218
 219 CREATE INDEX hash_name_index ON hash_name_heap USING hash (random name_ops);
 220
 221 CREATE INDEX hash_txt_index ON hash_txt_heap USING hash (random text_ops);
 222
 223 CREATE INDEX hash_f8_index ON hash_f8_heap USING hash (random float8_ops);
 224
 225 -- CREATE INDEX hash_ovfl_index ON hash_ovfl_heap USING hash (x int4_ops);
 226
 227
 228 --
 229 -- Test functional index
 230 --
 231 CREATE TABLE func_index_heap (f1 text, f2 text);
 232 CREATE UNIQUE INDEX func_index_index on func_index_heap (textcat(f1,f2));
 233
 234 INSERT INTO func_index_heap VALUES('ABC','DEF');
 235 INSERT INTO func_index_heap VALUES('AB','CDEFG');
 236 INSERT INTO func_index_heap VALUES('QWE','RTY');
 237 -- this should fail because of unique index:
 238 INSERT INTO func_index_heap VALUES('ABCD', 'EF');
 239 -- but this shouldn't:
 240 INSERT INTO func_index_heap VALUES('QWERTY');
 241
 242
 243 --
 244 -- Same test, expressional index
 245 --
 246 DROP TABLE func_index_heap;
 247 CREATE TABLE func_index_heap (f1 text, f2 text);
 248 CREATE UNIQUE INDEX func_index_index on func_index_heap ((f1 || f2) text_ops);
 249
 250 INSERT INTO func_index_heap VALUES('ABC','DEF');
 251 INSERT INTO func_index_heap VALUES('AB','CDEFG');
 252 INSERT INTO func_index_heap VALUES('QWE','RTY');
 253 -- this should fail because of unique index:
 254 INSERT INTO func_index_heap VALUES('ABCD', 'EF');
 255 -- but this shouldn't:
 256 INSERT INTO func_index_heap VALUES('QWERTY');
 257
 258 --
 259 -- Also try building functional, expressional, and partial indexes on
 260 -- tables that already contain data.
 261 --
 262 create unique index hash_f8_index_1 on hash_f8_heap(abs(random));
 263 create unique index hash_f8_index_2 on hash_f8_heap((seqno + 1), random);
 264 create unique index hash_f8_index_3 on hash_f8_heap(random) where seqno > 1000;
 265
 266 --
 267 -- Try some concurrent index builds
 268 --
 269 -- Unfortunately this only tests about half the code paths because there are
 270 -- no concurrent updates happening to the table at the same time.
 271
 272 CREATE TABLE concur_heap (f1 text, f2 text);
 273 -- empty table
 274 CREATE INDEX CONCURRENTLY concur_index1 ON concur_heap(f2,f1);
 275 INSERT INTO concur_heap VALUES  ('a','b');
 276 INSERT INTO concur_heap VALUES  ('b','b');
 277 -- unique index
 278 CREATE UNIQUE INDEX CONCURRENTLY concur_index2 ON concur_heap(f1);
 279 -- check if constraint is set up properly to be enforced
 280 INSERT INTO concur_heap VALUES ('b','x');
 281 -- check if constraint is enforced properly at build time
 282 CREATE UNIQUE INDEX CONCURRENTLY concur_index3 ON concur_heap(f2);
 283 -- test that expression indexes and partial indexes work concurrently
 284 CREATE INDEX CONCURRENTLY concur_index4 on concur_heap(f2) WHERE f1='a';
 285 CREATE INDEX CONCURRENTLY concur_index5 on concur_heap(f2) WHERE f1='x';
 286 CREATE INDEX CONCURRENTLY concur_index6 on concur_heap((f2||f1));
 287
 288 -- You can't do a concurrent index build in a transaction
 289 BEGIN;
 290 CREATE INDEX CONCURRENTLY concur_index7 ON concur_heap(f1);
 291 COMMIT;
 292
 293 -- But you can do a regular index build in a transaction
 294 BEGIN;
 295 CREATE INDEX std_index on concur_heap(f2);
 296 COMMIT;
 297
 298 -- check to make sure that the failed indexes were cleaned up properly and the
 299 -- successful indexes are created properly. Notably that they do NOT have the
 300 -- "invalid" flag set.
 301
 302 \d concur_heap
 303
 304 DROP TABLE concur_heap;
 305
 306 --
 307 -- Tests for IS NULL with b-tree indexes
 308 --
 309
 310 SELECT unique1, unique2 INTO onek_with_null FROM onek;
 311 INSERT INTO onek_with_null (unique1,unique2) VALUES (NULL, -1), (NULL, NULL);
 312 CREATE UNIQUE INDEX onek_nulltest ON onek_with_null (unique2,unique1);
 313
 314 SET enable_seqscan = OFF;
 315 SET enable_indexscan = ON;
 316 SET enable_bitmapscan = ON;
 317
 318 SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL;
 319 SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL AND unique2 IS NULL;
 320
 321 DROP INDEX onek_nulltest;
 322
 323 CREATE UNIQUE INDEX onek_nulltest ON onek_with_null (unique2 desc,unique1);
 324
 325 SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL;
 326 SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL AND unique2 IS NULL;
 327
 328 DROP INDEX onek_nulltest;
 329
 330 CREATE UNIQUE INDEX onek_nulltest ON onek_with_null (unique2 desc nulls last,unique1);
 331
 332 SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL;
 333 SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL AND unique2 IS NULL;
 334
 335 DROP INDEX onek_nulltest;
 336
 337 CREATE UNIQUE INDEX onek_nulltest ON onek_with_null (unique2  nulls first,unique1);
 338
 339 SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL;
 340 SELECT count(*) FROM onek_with_null WHERE unique1 IS NULL AND unique2 IS NULL;
 341
 342 RESET enable_seqscan;
 343 RESET enable_indexscan;
 344 RESET enable_bitmapscan;
 345
 346 DROP TABLE onek_with_null;