1/*
2 * 1.1. test CREATE INDEX with buffered build
3 */
4
5-- Regular index with included columns
6CREATE TABLE tbl_gist (c1 int, c2 int, c3 int, c4 box);
7-- size is chosen to exceed page size and trigger actual truncation
8INSERT 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;
9CREATE INDEX tbl_gist_idx ON tbl_gist using gist (c4) INCLUDE (c1,c2,c3);
10SELECT pg_get_indexdef(i.indexrelid)
11FROM pg_index i JOIN pg_class c ON i.indexrelid = c.oid
12WHERE i.indrelid = 'tbl_gist'::regclass ORDER BY c.relname;
13SELECT * FROM tbl_gist where c4 <@ box(point(1,1),point(10,10));
14SET enable_bitmapscan TO off;
15EXPLAIN  (costs off) SELECT * FROM tbl_gist where c4 <@ box(point(1,1),point(10,10));
16SET enable_bitmapscan TO default;
17DROP TABLE tbl_gist;
18
19/*
20 * 1.2. test CREATE INDEX with inserts
21 */
22
23-- Regular index with included columns
24CREATE TABLE tbl_gist (c1 int, c2 int, c3 int, c4 box);
25-- size is chosen to exceed page size and trigger actual truncation
26CREATE INDEX tbl_gist_idx ON tbl_gist using gist (c4) INCLUDE (c1,c2,c3);
27INSERT 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;
28SELECT pg_get_indexdef(i.indexrelid)
29FROM pg_index i JOIN pg_class c ON i.indexrelid = c.oid
30WHERE i.indrelid = 'tbl_gist'::regclass ORDER BY c.relname;
31SELECT * FROM tbl_gist where c4 <@ box(point(1,1),point(10,10));
32SET enable_bitmapscan TO off;
33EXPLAIN  (costs off) SELECT * FROM tbl_gist where c4 <@ box(point(1,1),point(10,10));
34SET enable_bitmapscan TO default;
35DROP TABLE tbl_gist;
36
37/*
38 * 2. CREATE INDEX CONCURRENTLY
39 */
40CREATE TABLE tbl_gist (c1 int, c2 int, c3 int, c4 box);
41INSERT 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;
42CREATE INDEX CONCURRENTLY tbl_gist_idx ON tbl_gist using gist (c4) INCLUDE (c1,c2,c3);
43SELECT indexdef FROM pg_indexes WHERE tablename = 'tbl_gist' ORDER BY indexname;
44DROP TABLE tbl_gist;
45
46
47/*
48 * 3. REINDEX
49 */
50CREATE TABLE tbl_gist (c1 int, c2 int, c3 int, c4 box);
51INSERT 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;
52CREATE INDEX tbl_gist_idx ON tbl_gist using gist (c4) INCLUDE (c1,c3);
53SELECT indexdef FROM pg_indexes WHERE tablename = 'tbl_gist' ORDER BY indexname;
54REINDEX INDEX tbl_gist_idx;
55SELECT indexdef FROM pg_indexes WHERE tablename = 'tbl_gist' ORDER BY indexname;
56ALTER TABLE tbl_gist DROP COLUMN c1;
57SELECT indexdef FROM pg_indexes WHERE tablename = 'tbl_gist' ORDER BY indexname;
58DROP TABLE tbl_gist;
59
60/*
61 * 4. Update, delete values in indexed table.
62 */
63CREATE TABLE tbl_gist (c1 int, c2 int, c3 int, c4 box);
64INSERT 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;
65CREATE INDEX tbl_gist_idx ON tbl_gist using gist (c4) INCLUDE (c1,c3);
66UPDATE tbl_gist SET c1 = 100 WHERE c1 = 2;
67UPDATE tbl_gist SET c1 = 1 WHERE c1 = 3;
68DELETE FROM tbl_gist WHERE c1 = 5 OR c3 = 12;
69DROP TABLE tbl_gist;
70
71/*
72 * 5. Alter column type.
73 */
74CREATE TABLE tbl_gist (c1 int, c2 int, c3 int, c4 box);
75INSERT 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;
76CREATE INDEX tbl_gist_idx ON tbl_gist using gist (c4) INCLUDE (c1,c3);
77ALTER TABLE tbl_gist ALTER c1 TYPE bigint;
78ALTER TABLE tbl_gist ALTER c3 TYPE bigint;
79\d tbl_gist
80DROP TABLE tbl_gist;
81
82/*
83 * 6. EXCLUDE constraint.
84 */
85CREATE TABLE tbl_gist (c1 int, c2 int, c3 int, c4 box, EXCLUDE USING gist (c4 WITH &&) INCLUDE (c1, c2, c3));
86INSERT 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;
87INSERT 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;
88EXPLAIN  (costs off) SELECT * FROM tbl_gist where c4 <@ box(point(1,1),point(10,10));
89\d tbl_gist
90DROP TABLE tbl_gist;
91