granicus.if.org Git - postgresql/blob - 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 -- RTREE
  72 --
  73 -- rtrees use a quadratic page-splitting algorithm that takes a
  74 -- really, really long time.  we don't test all rtree opclasses
  75 -- in the regression test (we check them using the sequoia 2000
  76 -- benchmark).
  77 --
  78 CREATE INDEX rect2ind ON fast_emp4000 USING rtree (home_base);
  79
  80 -- there's no easy way to check that this command actually is using
  81 -- the index, unfortunately.  (EXPLAIN would work, but its output
  82 -- changes too often for me to want to put an EXPLAIN in the test...)
  83 SELECT * FROM fast_emp4000
  84     WHERE home_base @ '(200,200),(2000,1000)'::box
  85     ORDER BY home_base USING <<;
  86
  87 --
  88 -- HASH
  89 --
  90 CREATE INDEX hash_i4_index ON hash_i4_heap USING hash (random int4_ops);
  91
  92 CREATE INDEX hash_name_index ON hash_name_heap USING hash (random name_ops);
  93
  94 CREATE INDEX hash_txt_index ON hash_txt_heap USING hash (random text_ops);
  95
  96 CREATE INDEX hash_f8_index ON hash_f8_heap USING hash (random float8_ops);
  97
  98 -- CREATE INDEX hash_ovfl_index ON hash_ovfl_heap USING hash (x int4_ops);
  99
 100
 101 --
 102 -- Test functional index
 103 --
 104 CREATE TABLE func_index_heap (f1 text, f2 text);
 105 CREATE UNIQUE INDEX func_index_index on func_index_heap (textcat(f1,f2));
 106
 107 INSERT INTO func_index_heap VALUES('ABC','DEF');
 108 INSERT INTO func_index_heap VALUES('AB','CDEFG');
 109 INSERT INTO func_index_heap VALUES('QWE','RTY');
 110 -- this should fail because of unique index:
 111 INSERT INTO func_index_heap VALUES('ABCD', 'EF');
 112 -- but this shouldn't:
 113 INSERT INTO func_index_heap VALUES('QWERTY');
 114
 115
 116 --
 117 -- Same test, expressional index
 118 --
 119 DROP TABLE func_index_heap;
 120 CREATE TABLE func_index_heap (f1 text, f2 text);
 121 CREATE UNIQUE INDEX func_index_index on func_index_heap ((f1 || f2) text_ops);
 122
 123 INSERT INTO func_index_heap VALUES('ABC','DEF');
 124 INSERT INTO func_index_heap VALUES('AB','CDEFG');
 125 INSERT INTO func_index_heap VALUES('QWE','RTY');
 126 -- this should fail because of unique index:
 127 INSERT INTO func_index_heap VALUES('ABCD', 'EF');
 128 -- but this shouldn't:
 129 INSERT INTO func_index_heap VALUES('QWERTY');
 130
 131 --
 132 -- Also try building functional, expressional, and partial indexes on
 133 -- tables that already contain data.
 134 --
 135 create unique index hash_f8_index_1 on hash_f8_heap(abs(random));
 136 create unique index hash_f8_index_2 on hash_f8_heap((seqno + 1), random);
 137 create unique index hash_f8_index_3 on hash_f8_heap(random) where seqno > 1000;