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