src/test/regress/expected/index_including_gist.out

   1 /*
   2  * 1.1. test CREATE INDEX with buffered build
   3  */
   4 -- Regular index with included columns
   5 CREATE TABLE tbl_gist (c1 int, c2 int, c3 int, c4 box);
   6 -- size is chosen to exceed page size and trigger actual truncation
   7 INSERT INTO tbl_gist SELECT x, 2*x, 3*x, box(point(x,x+1),point(2*x,2*x+1)) FROM generate_series(1,8000) AS x;
   8 CREATE INDEX tbl_gist_idx ON tbl_gist using gist (c4) INCLUDE (c1,c2,c3);
   9 SELECT pg_get_indexdef(i.indexrelid)
  10 FROM pg_index i JOIN pg_class c ON i.indexrelid = c.oid
  11 WHERE i.indrelid = 'tbl_gist'::regclass ORDER BY c.relname;
  12                                   pg_get_indexdef
  13 -----------------------------------------------------------------------------------
  14  CREATE INDEX tbl_gist_idx ON public.tbl_gist USING gist (c4) INCLUDE (c1, c2, c3)
  15 (1 row)
  16
  17 SELECT * FROM tbl_gist where c4 <@ box(point(1,1),point(10,10));
  18  c1 | c2 | c3 |     c4
  19 ----+----+----+-------------
  20   1 |  2 |  3 | (2,3),(1,2)
  21   2 |  4 |  6 | (4,5),(2,3)
  22   3 |  6 |  9 | (6,7),(3,4)
  23   4 |  8 | 12 | (8,9),(4,5)
  24 (4 rows)
  25
  26 SET enable_bitmapscan TO off;
  27 EXPLAIN  (costs off) SELECT * FROM tbl_gist where c4 <@ box(point(1,1),point(10,10));
  28                    QUERY PLAN
  29 ------------------------------------------------
  30  Index Only Scan using tbl_gist_idx on tbl_gist
  31    Index Cond: (c4 <@ '(10,10),(1,1)'::box)
  32 (2 rows)
  33
  34 SET enable_bitmapscan TO default;
  35 DROP TABLE tbl_gist;
  36 /*
  37  * 1.2. test CREATE INDEX with inserts
  38  */
  39 -- Regular index with included columns
  40 CREATE TABLE tbl_gist (c1 int, c2 int, c3 int, c4 box);
  41 -- size is chosen to exceed page size and trigger actual truncation
  42 CREATE INDEX tbl_gist_idx ON tbl_gist using gist (c4) INCLUDE (c1,c2,c3);
  43 INSERT INTO tbl_gist SELECT x, 2*x, 3*x, box(point(x,x+1),point(2*x,2*x+1)) FROM generate_series(1,8000) AS x;
  44 SELECT pg_get_indexdef(i.indexrelid)
  45 FROM pg_index i JOIN pg_class c ON i.indexrelid = c.oid
  46 WHERE i.indrelid = 'tbl_gist'::regclass ORDER BY c.relname;
  47                                   pg_get_indexdef
  48 -----------------------------------------------------------------------------------
  49  CREATE INDEX tbl_gist_idx ON public.tbl_gist USING gist (c4) INCLUDE (c1, c2, c3)
  50 (1 row)
  51
  52 SELECT * FROM tbl_gist where c4 <@ box(point(1,1),point(10,10));
  53  c1 | c2 | c3 |     c4
  54 ----+----+----+-------------
  55   1 |  2 |  3 | (2,3),(1,2)
  56   2 |  4 |  6 | (4,5),(2,3)
  57   3 |  6 |  9 | (6,7),(3,4)
  58   4 |  8 | 12 | (8,9),(4,5)
  59 (4 rows)
  60
  61 SET enable_bitmapscan TO off;
  62 EXPLAIN  (costs off) SELECT * FROM tbl_gist where c4 <@ box(point(1,1),point(10,10));
  63                    QUERY PLAN
  64 ------------------------------------------------
  65  Index Only Scan using tbl_gist_idx on tbl_gist
  66    Index Cond: (c4 <@ '(10,10),(1,1)'::box)
  67 (2 rows)
  68
  69 SET enable_bitmapscan TO default;
  70 DROP TABLE tbl_gist;
  71 /*
  72  * 2. CREATE INDEX CONCURRENTLY
  73  */
  74 CREATE TABLE tbl_gist (c1 int, c2 int, c3 int, c4 box);
  75 INSERT INTO tbl_gist SELECT x, 2*x, 3*x, box(point(x,x+1),point(2*x,2*x+1)) FROM generate_series(1,10) AS x;
  76 CREATE INDEX CONCURRENTLY tbl_gist_idx ON tbl_gist using gist (c4) INCLUDE (c1,c2,c3);
  77 SELECT indexdef FROM pg_indexes WHERE tablename = 'tbl_gist' ORDER BY indexname;
  78                                      indexdef
  79 -----------------------------------------------------------------------------------
  80  CREATE INDEX tbl_gist_idx ON public.tbl_gist USING gist (c4) INCLUDE (c1, c2, c3)
  81 (1 row)
  82
  83 DROP TABLE tbl_gist;
  84 /*
  85  * 3. REINDEX
  86  */
  87 CREATE TABLE tbl_gist (c1 int, c2 int, c3 int, c4 box);
  88 INSERT INTO tbl_gist SELECT x, 2*x, 3*x, box(point(x,x+1),point(2*x,2*x+1)) FROM generate_series(1,10) AS x;
  89 CREATE INDEX tbl_gist_idx ON tbl_gist using gist (c4) INCLUDE (c1,c3);
  90 SELECT indexdef FROM pg_indexes WHERE tablename = 'tbl_gist' ORDER BY indexname;
  91                                    indexdef
  92 -------------------------------------------------------------------------------
  93  CREATE INDEX tbl_gist_idx ON public.tbl_gist USING gist (c4) INCLUDE (c1, c3)
  94 (1 row)
  95
  96 REINDEX INDEX tbl_gist_idx;
  97 SELECT indexdef FROM pg_indexes WHERE tablename = 'tbl_gist' ORDER BY indexname;
  98                                    indexdef
  99 -------------------------------------------------------------------------------
 100  CREATE INDEX tbl_gist_idx ON public.tbl_gist USING gist (c4) INCLUDE (c1, c3)
 101 (1 row)
 102
 103 ALTER TABLE tbl_gist DROP COLUMN c1;
 104 SELECT indexdef FROM pg_indexes WHERE tablename = 'tbl_gist' ORDER BY indexname;
 105  indexdef
 106 ----------
 107 (0 rows)
 108
 109 DROP TABLE tbl_gist;
 110 /*
 111  * 4. Update, delete values in indexed table.
 112  */
 113 CREATE TABLE tbl_gist (c1 int, c2 int, c3 int, c4 box);
 114 INSERT INTO tbl_gist SELECT x, 2*x, 3*x, box(point(x,x+1),point(2*x,2*x+1)) FROM generate_series(1,10) AS x;
 115 CREATE INDEX tbl_gist_idx ON tbl_gist using gist (c4) INCLUDE (c1,c3);
 116 UPDATE tbl_gist SET c1 = 100 WHERE c1 = 2;
 117 UPDATE tbl_gist SET c1 = 1 WHERE c1 = 3;
 118 DELETE FROM tbl_gist WHERE c1 = 5 OR c3 = 12;
 119 DROP TABLE tbl_gist;
 120 /*
 121  * 5. Alter column type.
 122  */
 123 CREATE TABLE tbl_gist (c1 int, c2 int, c3 int, c4 box);
 124 INSERT INTO tbl_gist SELECT x, 2*x, 3*x, box(point(x,x+1),point(2*x,2*x+1)) FROM generate_series(1,10) AS x;
 125 CREATE INDEX tbl_gist_idx ON tbl_gist using gist (c4) INCLUDE (c1,c3);
 126 ALTER TABLE tbl_gist ALTER c1 TYPE bigint;
 127 ALTER TABLE tbl_gist ALTER c3 TYPE bigint;
 128 \d tbl_gist
 129               Table "public.tbl_gist"
 130  Column |  Type   | Collation | Nullable | Default
 131 --------+---------+-----------+----------+---------
 132  c1     | bigint  |           |          |
 133  c2     | integer |           |          |
 134  c3     | bigint  |           |          |
 135  c4     | box     |           |          |
 136 Indexes:
 137     "tbl_gist_idx" gist (c4) INCLUDE (c1, c3)
 138
 139 DROP TABLE tbl_gist;
 140 /*
 141  * 6. EXCLUDE constraint.
 142  */
 143 CREATE TABLE tbl_gist (c1 int, c2 int, c3 int, c4 box, EXCLUDE USING gist (c4 WITH &&) INCLUDE (c1, c2, c3));
 144 INSERT INTO tbl_gist SELECT x, 2*x, 3*x, box(point(x,x+1),point(2*x,2*x+1)) FROM generate_series(1,10) AS x;
 145 ERROR:  conflicting key value violates exclusion constraint "tbl_gist_c4_c1_c2_c3_excl"
 146 DETAIL:  Key (c4)=((4,5),(2,3)) conflicts with existing key (c4)=((2,3),(1,2)).
 147 INSERT INTO tbl_gist SELECT x, 2*x, 3*x, box(point(3*x,2*x),point(3*x+1,2*x+1)) FROM generate_series(1,10) AS x;
 148 EXPLAIN  (costs off) SELECT * FROM tbl_gist where c4 <@ box(point(1,1),point(10,10));
 149                          QUERY PLAN
 150 -------------------------------------------------------------
 151  Index Only Scan using tbl_gist_c4_c1_c2_c3_excl on tbl_gist
 152    Index Cond: (c4 <@ '(10,10),(1,1)'::box)
 153 (2 rows)
 154
 155 \d tbl_gist
 156               Table "public.tbl_gist"
 157  Column |  Type   | Collation | Nullable | Default
 158 --------+---------+-----------+----------+---------
 159  c1     | integer |           |          |
 160  c2     | integer |           |          |
 161  c3     | integer |           |          |
 162  c4     | box     |           |          |
 163 Indexes:
 164     "tbl_gist_c4_c1_c2_c3_excl" EXCLUDE USING gist (c4 WITH &&) INCLUDE (c1, c2, c3)
 165
 166 DROP TABLE tbl_gist;